フラグを用いたテクニカルなCOUNT DISTINCT

弊社のDMT用SQLで謎な書き方があった。

謎SQL

テーブルtbl1の中身はlogテーブルで以下のような感じだとする(ユーザーは複数セグメントに割り振られている)。

user_id	segment	log_time	log_date	log_mm
100	10代	2018-03-01 00:01:00	2018-03-01	01
100	10代	2018-03-01 00:01:01	2018-03-01	01
100	男性	2018-03-01 00:01:00	2018-03-01	01
100	男性	2018-03-01 00:01:01	2018-03-01	01
101	10代	2018-03-01 00:01:00	2018-03-01	01
101	10代	2018-03-01 00:01:01	2018-03-01	01
101	男性	2018-03-01 00:01:00	2018-03-01	01
101	男性	2018-03-01 00:01:01	2018-03-01	01
200	20代	2018-03-01 00:01:00	2018-03-01	01
200	20代	2018-03-01 00:01:01	2018-03-01	01
200	女性	2018-03-01 00:01:00	2018-03-01	01
200	女性	2018-03-01 00:01:01	2018-03-01	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を求めることができる。

はじめて見た使い方だけれども、そう考えると合理的な作り方だなーと思った。
割と普通の書き方なんだろか。