夕蛙のなく頃に

データアナリストとして学んだことや趣味で勉強し始めたIoTをアウトプットする

PostgreSQLで最頻値を求める

SQL

なにこれ

ユーザー毎の最も行動する時間が知りたくなった。

すなわちユーザー毎の行動時間(hour)の最頻値を集計したい。

テストデータ

column1: 2019-01-01〜2019-03-31までの時刻がバラバラなdatetime
column2: ランダムな日本語文字1字(user_idに相当)

SELECT
  '2019-01-01'::date
    + cast(((random() * 90)::int % 90)::text || ' days' AS INTERVAL)
    + cast(((random() * 24)::int % 24)::text || ' hours' AS INTERVAL) AS created_at,
  chr(12353 + (random() * 100)::int) AS user_id
FROM generate_series(1,10000);
created_at user_id
2019-03-09 03:00:00.000000 ぷ
2019-03-07 01:00:00.000000 と
2019-02-12 12:00:00.000000 り
2019-03-12 08:00:00.000000 と
2019-02-20 17:00:00.000000 わ

最頻値を求める

WITH base AS (
    SELECT
      '2019-01-01' :: DATE
      + cast(((random() * 90) :: INT % 90) :: TEXT || ' days' AS INTERVAL)
      + cast(((random() * 24) :: INT % 24) :: TEXT || ' hours' AS INTERVAL) AS created_at,
      chr(12353 + (random() * 100) :: INT)                                  AS user_id
    FROM
      generate_series(1, 10000)
)
SELECT
  DISTINCT ON (user_id)
  user_id,
  to_char(created_at + INTERVAL '9 hours', 'HH24') AS hh,
  count(*) AS cnt
FROM
  base
GROUP BY
  user_id,
  hh
ORDER BY
  user_id,
  cnt DESC

user_id, HH24をkeyにして集計。
集計結果に対して、user_id毎に集計されたcount(*)を降順にソートしたのち、user_idをkeyにして重複を除いている。

参考記事