【トレジャーデータ】SQL：複数のテーブル間で重複するユーザ数の日時の推移を集計する

前回、【トレジャーデータ】WITH句を使って複雑なSQLの可読性・効率性を改善
前々回、【トレジャーデータ】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になっていただろう。