概要
- DMT用SQLで謎な書き方があった。
- よくよく考えてみると理にかなっている書き方だった。
- レコード数削減のためtimestampで格納しているlogテーブルをuser_id単位からsegment単位にしてテーブル作成。
- そのときsegment単位にしているため、毎時or 毎分でのUUがCOUNT(DISTINCT user_id)で求めれないところを、別のやり方でUU算出を可能にしている。
謎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を求めることができる。
はじめて見た使い方だけれども、そう考えると合理的な作り方だなーと思った。
割と普通の書き方なんだろか。