【PostgreSQL】ユーザーが連続で記録した日数ランキングのsql

概要

ストリークを出すために必要。

railsでありがちなテーブル構成で考える。

テーブル

- users
  - id

- posts
 - user_id
 - created_at::datetime

アソシエーション

user has_many posts

SQL

-- 重複した記録日を一つにする
WITH created_at_and_user_ids AS (
  SELECT user_id, created_at::DATE AS created_at
    FROM posts
    GROUP BY created_at::DATE, user_id
),
-- ユーザーIDと記録した日を一覧にする
users_by_post_created_at AS (
  SELECT
    user_id,
    LAG(user_id, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS lag_user_id,
    LAG(created_at, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS lag_created_at
  FROM
    created_at_and_user_ids
),

-- 連続でない日が1, 連続の日を0とする
not_continued_dates AS (
  SELECT
    *,
    CASE WHEN user_id = lag_user_id AND lag_created_at <> (created_at - INTERVAL '1 days' ) THEN 1
    ELSE 0
    END AS not_continued_date_flg
    FROM users_by_post_created_at
),

-- 続いている日毎に集計する
streak_counts_by_users AS (
  SELECT *,
    SUM(not_continued_date_flg) OVER (PARTITION BY user_id ORDER BY created_at) AS sum_not_cont_date_flg
  FROM not_continued_dates
),

-- 最大連続日数とuser_idを取得する
res AS (
  SELECT
    user_id
    ,SUM(1) AS continued_days
  FROM
    streak_counts_by_users
  GROUP BY
    sum_not_cont_date_flg, user_id
)
SELECT * FROM res ORDER BY continued_days desc

資料・メモ

下記参考を参考にpostgreSQLで動くものを作成した

『継続して○○した日数』の最大値をSQLで求める - TVISION INSIGHTS Tech Blog

PARTITIONの復習用 SQL PARTITION BYの基本と効率的に集計する便利な方法

lag関数は直前の行の値を取得する なので、lag(user_id, 1)は、一つ前の行のuser_idを取得する