なにこれ
ユーザー毎の最も行動する時間が知りたくなった。
すなわちユーザー毎の行動時間(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にして重複を除いている。
参考記事
- ランダムなマルチバイト文字列の生成について goodbyegangster.hatenablog.com