【トレジャーデータ】JOINを使って複数のテーブルからデータ抽出する複雑なSQLを書く

目的：自社サイトの記事別UUを抽出し、かつ、他のサイトも閲覧している重複UU及び自社サイトを閲覧して実際に商品を購入しているUUの抽出

結果のイメージ 

article_name	自社サイトUU	自社サイトと他社サイトの重複UU	自社サイトと購買者の重複UU
山とは？	1000	40	5
川とは？	100	20	2
海とは？	10	1	1

＊後に下記のコードはWITH句を使って改善した
【トレジャーデータ】WITH句を使って複雑なSQLの可読性・効率性を改善

SELECT
  mysite.article_name,
  MAX(mysite.my_uu) AS my_uu,
  MAX(othersite.other_uu) AS other_uu,
  MAX(order.order_uu) AS oder_uu
FROM (
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS my_uu
    FROM
      データベース名.mysite_log
    WHERE
-- Sfariを除外したいときに記述
      browser NOT LIKE '%Safari%'
-- 期間を指定
      AND TD_TIME_RANGE(time,
        '2016-06-01',
        '2016-07-01',
        'JST')
    GROUP BY
      article_name
  ) mysite
JOIN (
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS othersite_uu
    FROM
      データベース名.mysite_log
    WHERE
      browser NOT LIKE '%Safari%'
      AND TD_TIME_RANGE(time,
        '2016-06-01',
        '2016-07-01',
        'JST')
      AND user_id IN(
        SELECT
          user_id
        FROM
          othersite_log
        WHERE
          TD_TIME_RANGE(time,
            '2016-06-01',
            '2016-07-01',
            'jst')
      )
    GROUP BY
      article_name
  ) othersite
  ON (
    mysite.article_name = othersite.td_article
  )
JOIN
  (
-- 下記はサイト閲覧ユーザID（user_id）と商品購入者ID（order_id）が異なる場合を想定
-- そのためuser_idとorder_idが紐付けられたテーブル（userid_orderid_matching）を利用
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS order_uu
    FROM
      データベース名.mysite_log
    WHERE
      browser NOT LIKE '%Safari%'
      AND TD_TIME_RANGE(time,
        '2016-06-01',
        '2016-07-01',
        'JST')
      AND user_id IN(
        SELECT
          user_id
        FROM (
            SELECT
              user_id,
              order_id     -- 商品購入者のID
            FROM
--商品購入者IDをサイト閲覧ユーザIDの照合テーブル
              データベース名.userid_orderid_matching
          ) matching
        JOIN (
            SELECT
              order_id
            FROM
              データベース名.order_log
            WHERE
              TD_TIME_RANGE(time,
                '2016-06-01',
                '2016-07-01',
                'jst')
          ) order_log
          ON (
            matching.order_id = order_log.order_id
          )
      )
    GROUP BY
      article_name
  ) order
  ON (
    mysite.article_name = order.article_name
  )
GROUP BY
  mysite.article_name
ORDER BY
  mysite_uu DESC


 ◇補足
期間設定を下記に変更することで、
過去30日間のデータを毎日自動集計することも可能。
＊Scheduleの設定は別途必要

WHERE
  TD_TIME_RANGE(time,
    TD_TIME_ADD(TD_SCHEDULED_TIME(),
      '-30d'),
    TD_SCHEDULED_TIME(),
    'JST')


◇参照サイト
 「組合せ計算」から出発する，データ分析のための「JOIN」
*トレジャーデータのアカウントがないと見れない
https://support.treasuredata.com/hc/ja/articles/215646908

SELECT構文：JOINを使ってテーブルを結合する
http://rfs.jp/sb/sql/s03/03_3.html