概要

  • 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を求めることができる。

はじめて見た使い方だけれども、そう考えると合理的な作り方だなーと思った。

割と普通の書き方なんだろか。

Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.