前々回、【トレジャーデータ】JOINを使って複数のテーブルからデータ抽出する複雑なSQLを書く
では、記事別に自社サイトUU、自社サイト×他社サイト重複UU、自社サイト×購買者重複UUを集計するSQLを書いた。
今回は、下記のような結果をイメージしてSQLを書いてみる。
| 日にち | 自社サイトUU | 自社サイトと他社サイトの重複UU | 自社サイトと購買者の重複UU |
| 2016-06-01 | 10000 | 120 | 20 |
| 2016-06-02 | 12000 | 130 | 30 |
| 2016-06-03 | 15000 | 200 | 15 |
テーブルとしては、
- 自社サイトログ：mysite_log(time, article_name, user_id)
- 他社サイトログ：othersite_log(time, article_name, user_id)
- 販売ログ：order_log(time, item_id, order_user_id)
- 商品ログ：item_info(time, item_id, brand_name)
ここで、販売ログは、order_user_idという独自のuser_idを持つ。そのため、
- id照合テーブル：userid_matching(user_id, order_user_id)
また、今回、自社サイトと購買者の重複UUは特定のブランドのみで抽出することも条件に入れて集計している。
SQLのコードは下記の通り、
WITH mysite_with AS(
-- 自社サイトUU
SELECT
td_time_format(time,
'yyyy-MM-dd',
'jst') AS days,
user_id
FROM
log_db.mysite_log
WHERE
AND TD_TIME_RANGE(time,
'2016-06-01',
'2016-09-01',
'JST')
GROUP BY
td_time_format(time,
'yyyy-MM-dd',
'jst'),
user_id
),
othersite_with AS(
-- 自社サイトと他サイトの重複UU
SELECT
td_time_format(time,
'yyyy-MM-dd',
'jst') AS days,
user_id
FROM
log_db.othersite_log
WHERE
TD_TIME_RANGE(time,
'2016-06-01',
'2016-09-01',
'jst')
GROUP BY
td_time_format(time,
'yyyy-MM-dd',
'jst'),
user_id
),
order_with AS(
-- 自社サイトと販売データの重複uu
SELECT
order_log.days,
match.user_id
FROM (
SELECT
user_id,
order_user_id
FROM
log_db.userid_matching--サイト側user_idと販売側order_user_idを称号するテーブル
) match
JOIN (
SELECT
td_time_format(time,
'yyyy-MM-dd',
'jst') AS days,
order_user_id
FROM
log_db.order_log
WHERE
TD_TIME_RANGE(time,
'2016-06-01',
'2016-09-01',
'jst')
AND item_id IN(
SELECT
item_id
FROM
info_db.item_info -- ブランド名などのアイテム情報が格納されたテーブル
WHERE
brand_name = 'xxx' -- ブランド名を指定する場合
)
GROUP BY
td_time_format(time,
'yyyy-MM-dd',
'jst'),
order_user_id
) order_log
ON (
match.order_user_id = order_log.order_user_id
)
) SELECT
mysite.days,
MAX(mysite.uu) AS mysite_uu,
MAX(wp.wp_uu) AS wp_uu,
MAX(ord.ord_uu) AS order_uu
FROM (
SELECT
days,
COUNT(DISTINCT imid) AS uu
FROM
mysite_with
GROUP BY
days
) mysite LEFT
JOIN (
SELECT
mysite_with_othersite.days,
COUNT(DISTINCT mysite_with_othersite.user_id) AS othersite_uu
FROM (
SELECT
days,
user_id
FROM
mysite_with
) mysite_with_othersite
JOIN (
SELECT
days,
user_id
FROM
othersite_with
) othersite_with_1
ON (
mysite_with_othersite.days = othersite_with_1.days
AND mysite_with_othersite.user_id = othersite_with_1.user_id
)
GROUP BY
mysite_with_othersite.days
) othersite
ON (
mysite.days = othersite.days
) LEFT
JOIN (
SELECT
mysite_with_order.days,
COUNT(DISTINCT mysite_with_order.user_id) AS ord_uu
FROM (
SELECT
days,
user_id
FROM
mysite_with
) mysite_with_order
JOIN (
SELECT
days,
user_id
FROM
order_with
) order_with_order
ON (
mysite_with_order.days = order_with_order.days
AND mysite_with_order.imid = order_with_order.imid
)
GROUP BY
mysite_with_order.days
) order
ON (
mysite.days = order.days
)
GROUP BY
mysite.days
ORDER BY
mysite.days
以前、WITH句を使わないで複数のテーブルをJOINさせた無駄に複雑なSQLを書いていたのだが、もし、今回もWITH句を使わずに書いていたらものすごく可読性の悪いSQLになっていただろう。
