フラグを用いたテクニカルなCOUNT DISTINCT
弊社のDMT用SQLで謎な書き方があった。
謎SQL
テーブルtbl1の中身はlogテーブルで以下のような感じだとする(ユーザーは複数セグメントに割り振られている)。
user_id | segment | log_time | log_date | log_hh | log_mm |
---|---|---|---|---|---|
100 | 10代 | 2018-03-01 00:01:00 | 2018-03-01 | 00 | 01 |
100 | 10代 | 2018-03-01 00:01:01 | 2018-03-01 | 00 | 01 |
100 | 男性 | 2018-03-01 00:01:00 | 2018-03-01 | 00 | 01 |
100 | 男性 | 2018-03-01 00:01:01 | 2018-03-01 | 00 | 01 |
101 | 10代 | 2018-03-01 00:01:00 | 2018-03-01 | 00 | 01 |
101 | 10代 | 2018-03-01 00:01:01 | 2018-03-01 | 00 | 01 |
101 | 男性 | 2018-03-01 00:01:00 | 2018-03-01 | 00 | 01 |
101 | 男性 | 2018-03-01 00:01:01 | 2018-03-01 | 00 | 01 |
200 | 20代 | 2018-03-01 00:01:00 | 2018-03-01 | 00 | 01 |
200 | 20代 | 2018-03-01 00:01:01 | 2018-03-01 | 00 | 01 |
200 | 女性 | 2018-03-01 00:01:00 | 2018-03-01 | 00 | 01 |
200 | 女性 | 2018-03-01 00:01:01 | 2018-03-01 | 00 | 01 |
問題のSQLは以下。 内容としては、segment毎のPVとユニーク数を出したいっぽい。
CREATE TABLE log_dmt WITH sub1 AS ( SELECT user_id ,segment ,log_time ,log_date ,log_hh ,log_mm FROM log_tbl ) ,sub2 AS ( SELECT user_id ,segment ,log_time ,log_date ,log_hh ,log_mm ,ROW_NUMBER() OVER ( PARTITION BY user_id ,segment ,log_date ,log_hh ORDER BY log_time ) AS hh_uu_no ,ROW_NUMBER() OVER ( PARTITION BY user_id ,segment ,log_date ,log_hh ,log_mm ORDER BY log_time ) AS mm_uu_no FROM sub1 ) --セグメント単位で集計 SELECT segment ,log_time ,log_date ,log_hh ,log_mm ,SUM(1) AS tmp_pv ,COUNT(DISTINCT user_id) AS time_uu ,SUM(CASE WHEN hh_uu_no = 1 THEN 1 ELSE 0 END) AS tmp_hh_uu ,SUM(CASE WHEN mm_uu_no = 1 THEN 1 ELSE 0 END) AS tmp_mm_uu FROM sub2 GROUP BY segment ,log_time ,log_date ,log_hh ,log_mm ) ;
謎な点
- 何故ROW_NUMBER関数を噛ませているか
- その結果の1のものだけで集計してtmp_hh_uu, tmp_mm_uuというものを作っている。
謎の意味
tmp_hhがどう使われているか。
例えば下記のようにして使うとその日の各時単位でのsegment毎UUが取れる。
SELECT segment ,log_date ,log_hh ,SUM(tmp_hh_uu) AS hh_uu FROM log_dmt GROUP BY segment ,log_date ,log_hh ;
解釈としては、ROW_NUMBER関数によってPARTITION=各user_idがそのsegment, log_date, log_hhにおいての初回ログ(ROW_NUMBER = 1)を数えている。 各user_idはそのsegment, log_date, log_hhにおいて1であるのは1回だけで、しかも必ず現れる。 そのため、1の数を数える(ROW_NUMBER = 1のものを1、それ以外を0とした初回ログフラグ的なものをSUMする)ことでその日の各時単位でのsegment毎UUが取れるということになる。
ちなみに、各時分単位の場合下記になる。
SELECT segment ,log_date ,log_hh ,log_mm ,SUM(tmp_mm_uu) AS mm_uu FROM log_dmt GROUP BY segment ,log_date ,log_hh ,log_mm ;
何故そんな変な作りか
最終的に作られたテーブルは user_id単位からsegment単位 でのlog_timeログになっている。
user_idからsegmentになることでレコード数は減っているが、log_timeより荒い時間粒度のlog_day,log_hh単位などにする場合、 user_idの情報がないため COUNT(DISTINCT user_id)を使うことでsegment内でのUUを求めることができない。
しかし、このロジックを使うことでより荒い時間粒度でもUUを求めることができる。
はじめて見た使い方だけれども、そう考えると合理的な作り方だなーと思った。
割と普通の書き方なんだろか。