週次で集計するときの関数メモ
ログイン履歴などを管理する下記のようなスキーマのテーブルがあったとして
| id | created_at |
|---|---|
| 6710 | 2021-02-19 00:27:54.748366 |
| 10626 | 2021-06-25 01:00:56.936368 |
| 10843 | 2021-07-01 07:20:16.189046 |
| 10846 | 2021-07-01 07:41:09.651137 |
| 11429 | 2021-07-20 10:59:55.524838 |
| 11242 | 2021-07-13 03:06:57.809488 |
| 11239 | 2021-07-13 02:48:30.681351 |
| 11802 | 2021-08-05 12:43:27.207129 |
| 12504 | 2021-08-28 09:16:16.231289 |
| 12703 | 2021-09-04 00:23:35.515418 |
date_trunc関数を利用しれば週の初めの日付(月曜日）が一発で取得できる
SELECT id
,created_at
,date_trunc('week', created_at) as week_start_day
FROM users
| id | created_at | week_start_day |
|---|---|---|
| 6710 | 2021-02-19 00:27:54.748366 | 2021-02-15 00:00:00 |
| 10626 | 2021-06-25 01:00:56.936368 | 2021-06-21 00:00:00 |
| 10843 | 2021-07-01 07:20:16.189046 | 2021-06-28 00:00:00 |
| 10846 | 2021-07-01 07:41:09.651137 | 2021-06-28 00:00:00 |
| 11429 | 2021-07-20 10:59:55.524838 | 2021-07-19 00:00:00 |
| 11242 | 2021-07-13 03:06:57.809488 | 2021-07-12 00:00:00 |
| 11239 | 2021-07-13 02:48:30.681351 | 2021-07-12 00:00:00 |
| 11802 | 2021-08-05 12:43:27.207129 | 2021-08-02 00:00:00 |
| 12504 | 2021-08-28 09:16:16.231289 | 2021-08-23 00:00:00 |
| 12703 | 2021-09-04 00:23:35.515418 | 2021-08-30 00:00:00 |
あとは week_start_day で集計すれば週次集計ができる。