SQLのDATE_TRUNCで月別集計する方法:注文件数・売上と期間指定
DATE_TRUNCは、日時を月や日などの単位にそろえるPostgreSQLの関数です。月初にそろえた値でGROUP BYすると、月別の注文件数や売上を求められます。1件ずつの注文日時が月ごとの結果に変わる様子を見ていきましょう。
例で使う表
ordersの1行は注文1件です。ordered_atは注文日時(timestamp without time zone)、amountは金額(円)です。すべて同じ店舗の現地時刻として扱います。2025年と2026年の9月、2026年11月・12月の注文を使い、年の違いも確認します。2026年10月には注文がありません。ORDER BYなしの結果の行順は一例で、保証されません。
| id | ordered_at | amount |
|---|---|---|
| 1 | 2025-09-10 10:00:00 | 1000 |
| 2 | 2026-09-01 00:00:00 | 2000 |
| 3 | 2026-09-30 23:59:59.999999 | 3000 |
| 4 | 2026-11-01 00:00:00 | 4000 |
| 5 | 2026-11-20 12:00:00 | 5000 |
| 6 | 2026-12-01 00:00:00 | 6000 |
月別集計・DATE_TRUNCの基本構文
SELECT *, DATE_TRUNC('month', ordered_at)
FROM orders;DATE_TRUNCで注文日時を月初にそろえる
DATE_TRUNC('month', ordered_at)は、注文日時をその月の1日0時にそろえます。'month'は月単位の指定です。ID2の9月1日0時とID3の9月30日の夜は、ともに2026年9月1日0時になります。元のordered_atを書き換えず、結果の末尾に月を表す列を追加しています。timestamp without time zoneを渡しているので、返る値も同じ型の日時です。2025年9月は2026年9月と別の値になります。
SELECT *, DATE_TRUNC('month', ordered_at)
FROM orders;| id | ordered_at | amount | date_trunc |
|---|---|---|---|
| 1 | 2025-09-10 10:00:00 | 1000 | 2025-09-01 00:00:00 |
| 2 | 2026-09-01 00:00:00 | 2000 | 2026-09-01 00:00:00 |
| 3 | 2026-09-30 23:59:59.999999 | 3000 | 2026-09-01 00:00:00 |
| 4 | 2026-11-01 00:00:00 | 4000 | 2026-11-01 00:00:00 |
| 5 | 2026-11-20 12:00:00 | 5000 | 2026-11-01 00:00:00 |
| 6 | 2026-12-01 00:00:00 | 6000 | 2026-12-01 00:00:00 |
GROUP BYとCOUNTで月別の件数を求める
月初にそろえた値をGROUP BYに書くと、同じ月の注文を1グループにできます。2026年9月はID2・3で2件、11月はID4・5で2件です。2025年9月と2026年9月は別々の行になります。元の注文日時をそのままGROUP BYにすると、違う日の注文は別グループになってしまいます。結果列date_truncは月初の日時、countはその月の件数です。
SELECT DATE_TRUNC('month', ordered_at), COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('month', ordered_at);| date_trunc | count |
|---|---|
| 2025-09-01 00:00:00 | 1 |
| 2026-09-01 00:00:00 | 2 |
| 2026-11-01 00:00:00 | 2 |
| 2026-12-01 00:00:00 | 1 |
SUMで月別の売上を合計する
件数の代わりにSUM(amount)を使うと、月別の金額合計になります。2026年9月は2000 + 3000で5000円、11月は4000 + 5000で9000円です。月のグループを作る式は同じDATE_TRUNCのままで、求める指標だけを変えています。ここではすべての注文金額を売上として扱います。
SELECT DATE_TRUNC('month', ordered_at), SUM(amount)
FROM orders
GROUP BY DATE_TRUNC('month', ordered_at);| date_trunc | sum |
|---|---|
| 2025-09-01 00:00:00 | 1000 |
| 2026-09-01 00:00:00 | 5000 |
| 2026-11-01 00:00:00 | 9000 |
| 2026-12-01 00:00:00 | 6000 |
WHEREで期間を絞り、月順に集計結果を表示する
2026年9月から11月までの注文を集計するには、注文日時を9月1日0時以上、12月1日0時未満で選んでから月別に集計します。2025年9月のID1と12月1日ちょうどのID6は入りません。最後のORDER BYに月初へそろえる式を書くと、9月5000円、11月9000円の順になります。10月には注文がないので、10月0円の行は自動では作られません。
SELECT DATE_TRUNC('month', ordered_at), SUM(amount)
FROM orders
WHERE ordered_at >= TIMESTAMP '2026-09-01 00:00:00'
AND ordered_at < TIMESTAMP '2026-12-01 00:00:00'
GROUP BY DATE_TRUNC('month', ordered_at)
ORDER BY DATE_TRUNC('month', ordered_at);| date_trunc | sum |
|---|---|
| 2026-09-01 00:00:00 | 5000 |
| 2026-11-01 00:00:00 | 9000 |
月別集計・DATE_TRUNCでよくある間違い
元の注文日時でGROUP BYしない
SELECTだけをDATE_TRUNCにして、GROUP BY ordered_atとすると、異なる日時の注文が別グループのままになります。同じ月初を持つ行が複数表示され、月別の合計になりません。SELECTとGROUP BYの両方に、月を表す同じ式を使います。
実際に1問解いてみる
DATE_TRUNC・月初
全注文の全列の末尾に、注文日時を月初0時にそろえた値をDATE_TRUNCで追加してください。別名は付けず、行順は問いません。
関連構文
月別集計・DATE_TRUNCについてよくある質問
- 注文がない月も0件・0円で表示できますか?
- このページのGROUP BYだけでは表示されません。入力行がない月にはグループが作られないためです。すべての月を表示するには、月の一覧を別途用意して集計結果とLEFT JOINし、未一致の集計値をCOALESCEで0にする構成が必要です。
- 月の番号だけで集計してもよいですか?
- 年をまたぐデータでは、月の番号だけをキーにすると異なる年の同じ月を混ぜてしまいます。DATE_TRUNCの月初には年も含まれるので、2025年9月と2026年9月を別々に集計できます。
- DATE型の列を月別集計するには?
- この教材と同じくタイムゾーンを持たない日時として扱うなら、DATE_TRUNC('month', ordered_on::timestamp)のように日時型への変換を明示できます。::timestampはPostgreSQLの型変換の書き方です。
- タイムゾーン付きの注文日時でも同じですか?
- timestamp with time zoneを渡した場合は、どの地域の時刻で月初を決めるかに注意します。タイムゾーンを指定しなければセッションのTimeZoneが使われます。このページではtimestamp without time zoneにそろえ、タイムゾーン変換は扱っていません。
参考文献
内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:DATE_TRUNC — 日時の切り詰め単位、戻り値とタイムゾーン
- PostgreSQL公式:GROUP BYとHAVING — 式でのグループ化と集約
- PostgreSQL公式:集約関数 — COUNT・SUMの集計