注目の投稿

【kepler.gl】コロナ対策による人流の変化も地図上に可視化(各種メディアで報道)

kepler.glのサイト画面 kepler.glを使ってコロナ対策の効果を分析したところ、テレビ、新聞、ネットのメディアから問い合わせや報道依頼が殺到。今も、土日返上で都内や全国の人流変化を分析しています。この記事では人流変化の可視化に便利なkepler.glにつ...

2016年9月30日金曜日

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

前回、【トレジャーデータ】WITH句を使って複雑なSQLの可読性・効率性を改善
前々回、【トレジャーデータ】JOINを使って複数のテーブルからデータ抽出する複雑なSQLを書く
では、記事別に自社サイトUU、自社サイト×他社サイト重複UU、自社サイト×購買者重複UUを集計するSQLを書いた。

今回は、下記のような結果をイメージしてSQLを書いてみる。

日にち自社サイトUU自社サイトと他社サイトの重複UU自社サイトと購買者の重複UU
2016-06-011000012020
2016-06-021200013030
2016-06-031500020015

テーブルとしては、
  • 自社サイトログ: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になっていただろう。


1 件のコメント :

  1. 友人によれば、
    JOIN(select from mystite_with)
    ではなく、
    JOIN mysite_with
    でも良いとのこと。
    なるほど

    返信削除

注: コメントを投稿できるのは、このブログのメンバーだけです。