PostgreSQLでログ件数などを時刻から月別、曜日別に集計します。
そのためのSQLの作り方メモです。
やりたいこと
下記のような「log」テーブルを想定し、user_id毎に「log」テーブルのレコードの件数を集計します。
| id | user_id | logged_at |
|---|---|---|
| 1 | AAA101 | 2020-01-06 00:00:00 |
| 2 | AAA101 | 2020-01-07 00:00:00 |
| 3 | AAA101 | 2020-02-06 00:00:00 |
| 4 | AAA101 | 2020-03-12 00:00:00 |
| 5 | AAA101 | 2020-03-19 00:00:00 |
| 6 | BBB102 | 2020-01-12 00:00:00 |
| 7 | BBB102 | 2020-02-09 00:00:00 |
| 8 | BBB102 | 2020-03-11 00:00:00 |
| 9 | BBB102 | 2020-03-21 00:00:00 |
| 10 | BBB102 | 2020-03-28 00:00:00 |
| 11 | CCC103 | 2020-01-19 00:00:00 |
| 12 | CCC103 | 2020-01-20 00:00:00 |
| 13 | CCC103 | 2020-01-21 00:00:00 |
| 14 | CCC103 | 2020-01-22 00:00:00 |
| 15 | CCC103 | 2020-02-20 00:00:00 |
| 16 | CCC103 | 2020-02-21 00:00:00 |
| 17 | CCC103 | 2020-03-28 00:00:00 |
例えば、月別に集計すると、、、
| user_id | 1月 | 2月 | 3月 |
|---|---|---|---|
| AAA101 | 2 | 1 | 2 |
| BBB102 | 1 | 1 | 3 |
| CCC103 | 4 | 2 | 1 |
例えば、曜日別に集計すると、、、
| user_id | 日曜日 | 月曜日 | 火曜日 | 水曜日 | 木曜日 | 金曜日 | 土曜日 |
|---|---|---|---|---|---|---|---|
| AAA101 | 0 | 1 | 1 | 0 | 3 | 0 | 0 |
| BBB102 | 2 | 0 | 0 | 1 | 0 | 0 | 2 |
| CCC103 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
といった感じです！
Version
- PostgreSQL 11.4
SQL
利用する関数
| 関数 | 種類 | 説明 |
|---|---|---|
| DATE_TRUNC | 日付/時刻関数と演算子 | 指定された精度で切り捨てます。今回は'month'を指定したので月より後ろが「01 00:00:00」(1日の0時)として切り捨てられています。 |
| EXTRACT | 日付/時刻関数と演算子 | 日付/時刻の値から年や時刻などの部分フィールドを抽出する。今回は'dow'を指定したので日曜日~土曜日が「0~6」として返却されます。 |
| CASE | 条件式 | 条件式を記載できます。今回は月別では切り捨てた月初日であれば1、それ以外は0として、曜日別では各曜日で１or０を返却しています。 |
| SUM | 集約関数 | 合計します。今回は条件式の結果を集計しています。 |
月別
SELECT
user_id,
SUM(CASE DATE_TRUNC('month', logged_at) WHEN '2020-01-01' THEN 1 ELSE 0 END) AS "1月",
SUM(CASE DATE_TRUNC('month', logged_at) WHEN '2020-02-01' THEN 1 ELSE 0 END) AS "2月",
SUM(CASE DATE_TRUNC('month', logged_at) WHEN '2020-03-01' THEN 1 ELSE 0 END) AS "3月"
FROM
log
GROUP BY
user_id
ORDER BY
user_id;
'month'(月)のほかにも'hour'(時)、'century'(世紀)なども使えます。
曜日別
SELECT
user_id,
SUM(CASE EXTRACT(dow FROM logged_at) WHEN 0 THEN 1 ELSE 0 END) AS "日曜日",
SUM(CASE EXTRACT(dow FROM logged_at) WHEN 1 THEN 1 ELSE 0 END) AS "月曜日",
SUM(CASE EXTRACT(dow FROM logged_at) WHEN 2 THEN 1 ELSE 0 END) AS "火曜日",
SUM(CASE EXTRACT(dow FROM logged_at) WHEN 3 THEN 1 ELSE 0 END) AS "水曜日",
SUM(CASE EXTRACT(dow FROM logged_at) WHEN 4 THEN 1 ELSE 0 END) AS "木曜日",
SUM(CASE EXTRACT(dow FROM logged_at) WHEN 5 THEN 1 ELSE 0 END) AS "金曜日",
SUM(CASE EXTRACT(dow FROM logged_at) WHEN 6 THEN 1 ELSE 0 END) AS "土曜日"
FROM
log
GROUP BY
user_id
ORDER BY
user_id;
'dow'(曜日)のほかにも'day'(日)、'decade'(十年)なども使えます。