『継続して○○した日数』とその最大値をSQLで求める

こんにちわ。データ部の長野です。

TVISION INSIGHTSのデータ部では、複雑なデータ抽出をする機会が多々あります。
今回は最近おこなった複雑なデータ抽出ロジックの1つ、 「『継続して○○した日数』とその最大値」 をSQLで求める方法を紹介します！

======= 2021/06/26追記 =======

記事中に積み上げ和を表現するのに、SUM(not_cont_date_flg) OVER (PARTITION BY user_id) AS cum_not_cont_date_flgというコード記述があるのですが、以下のような御指摘を受けました。

先日の「連続で結果xが出た回数」の時もでてきたのだけど、もしかしてPostgreSQL/redshiftとBigQueryでwindow関数のsumの挙動が違う？BigQueryだと積み上げにならないでウィンドウごとの和（user_idごとのフラグの数）になる。「ROWS BETWEEN UNBOUNDED~」を入れないと積み上げにならない。 https://t.co/pRWULYlrGG

— しんゆう＠データ分析とインテリジェンス (@data_analyst_) 2021年6月25日

御指摘の通り、BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW が無いと**積み上げ和ではなく、PARTITION内での総和となってしまいます。

たしかに。僕もいつも累積和のときはROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWいれてるんですが、記事だと入ってないですね。

いま試してみたんですが、redshiftでもこれいれないと積み上げにならないっぽいですね（ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGになる） pic.twitter.com/sNe9Sho6GC

— 森下光之助 (@dropout009) 2021年6月25日

そのため該当箇所をSUM(not_cont_date_flg) OVER (PARTITION BY user_id ORDER BY user_id, log_date BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_not_cont_date_flgに修正致しました。

御指摘ありがとうございますm( )m

======= 追記ここまで =======

前提

DBはRedshiftを想定しています。
以下のようなユーザーログから『継続してアクセスした日数』を求め、そこからその最大値を求めます。
ソーシャルゲームなどのアクセスログを想定するとイメージがつきやすいかと思います。

『継続して○○した日数』をどうやってSQLで求めるか？がポイントとなります。

※TVISION INSIGHTSのサービスにソーシャルゲームはないため、今回例とするアクセスログはあくまでイメージなのでご注意ください。

データイメージ

下図のような「同じuser_idの連続している日」を集計領域にして計算する必要があります。

ポイント

• 「同じuser_idの連続している日」部分を集計領域にした集計関数を使う。
• そのために、「同じuser_idが連続している日」という集計領域をするための判定列を作成する。

1.同一user_idでの手前のuser_id, log_dateを取得

「同じuser_idの連続している日」を判定するための準備として、
いったん手前のレコードの値をLAG関数を用いて取得します。

SELECT
  LAG(user_id,1) OVER (PARTITION BY user_id ORDER BY log_date) AS lag_user_id
  ,LAG(log_date,1) OVER (PARTITION BY user_id ORDER BY log_date) AS lag_log_date
FROM
  access_log

2.同一user_idで「日付が連続していないか」を判定

1.で取得したLAG関数を用いた列を利用して「日付が 連続していないか」を判定します。 連続していないとはつまり、現在の日付log_date - 1日が、先ほど作成した手前の日付lag_log_dateと一致しないことを指します。

詳細は3.で後述しますが、ここでは「連続しているか」ではなく「連続していないか」を判定して、
「連続していない」場合は1、「連続している」場合は0を取るようにします。

SELECT
  CASE WHEN user_id = lag_user_id AND log_date <> DATEADD(day,-1,lag_log_date) THEN 1
   ELSE 0
  END AS not_cont_date_flg
FROM
  access_log2

3.「日付が連続していないか」の積み上げ和を作成

今回のポイントである「同じuser_idの連続している日」という集計領域を、積み上げ和を利用して実現します。

同じ集計領域に所属させる、つまりGROUP BYで指定する列内の値を同じにするためにはどうしたらいいでしょうか。
その場合、例えば下表のように、同じ集計領域では0を足し、別の集計領域になったときに1を足すような積み上げ和を作成することで実現できます。

今回、同じ集計領域としたいのは「同じuser_idの連続している日」レコードです。 そのため、「同じuser_idで日が連続している」ときは0、違う場合は1。言い換えると、「同じuser_id」「日付が連続していない」でないときは1、違う場合は0になる列、つまりを作成し、この列に対して積み上げ和をすると、集計領域の指定用列を作成できます。 このような理由から、2.で「日付が 連続していないか」としてnot_cont_date_flg列を作成しました。

一般化した言い方をすると、 「集計領域が同じ場合は0、集計領域が変わる切れ目で1となるような列を指定した積み上げ和を作成する」と集計領域の指定用列が出来上がります。

SELECT
  SUM(not_cont_date_flg) OVER (PARTITION BY user_id ORDER BY user_id, log_date BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_not_cont_date_flg
FROM
  access_log3

4.cum_not_cont_date_flgで集計をおこない、レコード数を数える

3.で集計領域の指定用列cum_not_cont_date_flgを作成しました。
そのため、cum_not_cont_date_flgをGROUP BYに指定することで、目的である「同じuser_idの連続している日」を求めることができました。

SELECT
    cum_not_cont_date_flg
    ,SUM(1) AS cont_days
FROM
    access_log4
WHERE
    pay_flg = 1
GROUP BY
    cum_not_cont_date_flg
;

5.user_id毎に最大連続日数を取得する

あとは、user_id毎に「同じuser_idの連続している日」の最大値を取得します。

SELECT
    user_id
    ,MAX(cont_days) AS max_cont_days
FROM
    access_log5
GROUP BY
  user_id
;

まとめ

『継続して○○した日数』を求めたい場合、
- 1. 複雑な集計領域に分けてGROUP BYしたい
- 2. 集計領域が同じ場合は0、集計領域が異なった瞬間の切れ目に1を取る列を作成する
- 3.作成した列の積み上げ和を計算すると、集計領域ごとに別の値が割り振られた列が作成される

これらを意識すると作成することができます。

冒頭で申し上げたように、TVISION INSIGHTSはソーシャルゲーム会社ではなく、テレビの視聴調査会社です。

tvisioninsights.co.jp

そのため、今回例とした「アクセスログ」は、実際の業務では「視聴者の秒単位での視聴ログ」となります。
TVISION INSIGHTSでは視聴ログとして、TVを注視しているかどうかのパラメータを取れます。
そのため、「視聴者が1CM内でテレビを連続して注視している最大秒数」を求める場合などに、このロジックを使用しました。