日付の切り捨てとバケット化
DATE_TRUNCなどの同等機能を使って、週・月・四半期ごとにグループ化します。
「日付の切り捨てとバケット化」はCoddyKit上の無料Coding Interview Prepレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはCoding Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 Coding Interview Prepコースには全4レッスンが含まれています。
なぜ日付のバケット化が問われるのか
「週ごとの売上」や「月ごとのアクティブユーザー」は、アナリスト職の面接で頻出する題材です。ここで試されているのは、正確なタイムスタンプをより粗いバケットにまとめ、行をグループ化できるかどうかです。
初学者がよくする間違いは、月番号だけを取り出して、異なる年の同じ月をまとめてしまうことです。実務的な答えは切り捨てです。すべてのタイムスタンプを、その期間の開始時点に対応付けます。
- 週、月、四半期、年のバケット
DATE_TRUNCと各方言に相当する関数- グラフの軸が正しく揃うようにグループ化すること
DATE_TRUNC：中核となるツール
PostgreSQL では、DATE_TRUNC(unit, ts) を使うと、指定した単位より細かい部分がすべてゼロになります。'month' に切り捨てると、3月のどのタイムスタンプも 2024-03-01 00:00:00 になります。
戻り値は引き続きタイムスタンプなので、時系列順に並べ替えられ、きれいにグループ化できます。レポート作成で最も役立つ日付関数です。
SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-17 14:30:00');
-- 2024-03-01 00:00:00月ごとの売上をグループ化する
典型的な実践例です。タイムスタンプを月単位に切り捨ててから、グループ化して合計します。バケットに年の情報も含まれるため、2023年1月と2024年1月は別々に扱われます。
切り捨てた値で並べ替えると、グラフにそのまま使える、見やすい時系列になります。
SELECT
DATE_TRUNC('month', order_ts) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;EXTRACT と DATE_TRUNC の違い
面接では、この違いを直接確認されることがあります。どちらも期間の情報を取り出しますが、答える問いが異なります。
EXTRACT(MONTH FROM ts)は、年に関係なく3月なら3という数値を返すため、季節性の分析に適しています。DATE_TRUNC('month', ts)は特定の月の開始時点を返すため、年を区別でき、時系列に適しています。
月次トレンドのグラフで EXTRACT(MONTH ...) を使ってグループ化すると、気付かないうちに異なる年が混ざってしまいます。
-- Seasonality: which month is busiest on average?
SELECT EXTRACT(MONTH FROM order_ts) AS month_num, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;
-- Time series: month-by-month trend (years kept separate)
SELECT DATE_TRUNC('month', order_ts) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;週のバケットと月曜日の問題
週単位のグループ化には、面接官が好んで問う微妙な論点があります。週はいつ始まるのでしょうか。PostgreSQL の DATE_TRUNC('week', ts) は常に月曜日（ISO週）に切り捨てます。
業務上、日曜日始まりの週が必要なら、ずらしが必要です。よく使われる方法は、日付を1日戻してから切り捨て、その後1日進めることです。
-- ISO week (Monday start)
SELECT DATE_TRUNC('week', order_ts) AS iso_week FROM orders;
-- Sunday-start week
SELECT DATE_TRUNC('week', order_ts + INTERVAL '1 day') - INTERVAL '1 day'
AS sunday_week
FROM orders;四半期のバケット
四半期単位のレポートは、金融に近い職種でよく使われます。DATE_TRUNC('quarter', ts) は、任意のタイムスタンプをその四半期の初日に対応付けます。つまり、1月1日、4月1日、7月1日、10月1日のいずれかになります。
四半期を番号で表したい場合は、EXTRACT(QUARTER ...) と年を組み合わせます。
SELECT
DATE_TRUNC('quarter', order_ts) AS quarter_start,
EXTRACT(YEAR FROM order_ts) || '-Q'
|| EXTRACT(QUARTER FROM order_ts) AS quarter_label,
SUM(amount) AS revenue
FROM orders
GROUP BY 1, 2
ORDER BY 1;MySQL には DATE_TRUNC がない
方言をまたいだ質問でよくあるものに、「MySQL に DATE_TRUNC がない場合、月単位でバケット化するにはどうしますか」があります。移植性を重視した答えは、日付を必要な粒度までフォーマットすることです。
DATE_FORMAT(ts, '%Y-%m-01')は、月初をテキストまたは日付として返します。DATE_FORMAT(ts, '%Y-%m')は、2024-03のような並べ替え可能な文字列キーを返します。
週については、MySQL の YEARWEEK() に、週の開始曜日を制御するモード引数を指定できます。
-- MySQL month bucket
SELECT DATE_FORMAT(order_ts, '%Y-%m-01') AS month, SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1;SQL Server でのバケット化
SQL Server には従来、直接的な切り捨て関数がなかったため、候補者は DATEFROMPARTS や DATEADD/DATEDIFF のイディオムを使っていました。現在のバージョン（2022以降）では DATETRUNC が追加されています。
「基準時点からの単位数を数え、その数を再び加算する」という古典的なイディオムは、どのバージョンでも使えるため、知っておく価値があります。
-- Portable SQL Server month truncation
SELECT DATEADD(month, DATEDIFF(month, 0, order_ts), 0) AS month_start
FROM orders;
-- SQL Server 2022+
SELECT DATETRUNC(month, order_ts) AS month_start FROM orders;時系列の欠損を埋める
切り捨てるだけでは、行が0件の期間が失われます。注文が1件もない月は、単に結果に現れません。面接では、この点に気付けるかどうかが試されます。
解決策は、すべての期間からなる完全なスパインを生成し、そこにデータをLEFT JOINすることです。Postgres では generate_series を使ってスパインを作成できます。
SELECT
cal.month,
COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(DATE '2024-01-01', DATE '2024-12-01',
INTERVAL '1 month') AS cal(month)
LEFT JOIN orders o
ON DATE_TRUNC('month', o.order_ts) = cal.month
GROUP BY cal.month
ORDER BY cal.month;発展例：週ごとのアクティブユーザー
バケット化と重複を除いたカウントを組み合わせます。「週次アクティブユーザー」とは、実際のプロダクト分析でよく求められる、週のバケットごとのユニークユーザー数です。
イベントのタイムスタンプを週単位に切り捨ててから、COUNT(DISTINCT user_id) を使います。アクティビティが0の週も表示するために週のスパインを結合すると説明できれば、さらに評価されます。
SELECT
DATE_TRUNC('week', event_ts) AS week,
COUNT(DISTINCT user_id) AS wau
FROM events
GROUP BY 1
ORDER BY 1;インデックス付きの列をバケット化する
触れておきたいパフォーマンス上の注意点が1つあります。WHERE 句の中で日付列を DATE_TRUNC で包むと、プランナーがその列のインデックスを使えなくなることがあります。
GROUP BY で使う分には問題ありませんが、絞り込みでは、計算した境界値と元の列を比較してください。前に扱った半開区間のパターンが、ここでも適用できます。
-- Avoid in WHERE: DATE_TRUNC('month', order_ts) = '2024-03-01'
-- Prefer:
SELECT * FROM orders
WHERE order_ts >= DATE '2024-03-01'
AND order_ts < DATE '2024-04-01';確認問題
年を分けたまま月次トレンドのグラフを作るには、どのツールを選べばよいでしょうか。
復習：日付の切り捨てとバケット化
覚えておくこと：
DATE_TRUNC(unit, ts)はタイムスタンプを期間の開始時点に対応付け、年も区別するため、時系列に適したツールです。EXTRACTは単純な数値を返します。季節性には適していますが、異なる年が混ざります。- Postgres の週は月曜日に始まります。日曜日始まりが必要なら、ずらしてください。
- MySQL では
DATE_FORMATを使います。古い SQL Server ではDATEADD(DATEDIFF(...))のイディオムを使い、2022以降ではDATETRUNCが使えます。 - 空の期間を表示するには、生成した日付スパイン + LEFT JOINを使い、インデックスの利用を維持するために
WHERE句ではDATE_TRUNCを使わないようにします。
よくある質問
「日付の切り捨てとバケット化」レッスンは無料ですか？
はい。「日付の切り捨てとバケット化」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、Coding Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 Coding Interview Prepコースには全4レッスンが含まれています。
「日付の切り捨てとバケット化」で何を学びますか？
DATE_TRUNCなどの同等機能を使って、週・月・四半期ごとにグループ化します。 ブラウザで直接実行するハンズオンコードでCoding Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
Coding Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのCoding Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。
「日付の切り捨てとバケット化」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このCoding Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのCoding Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- 日付の計算とインターバル
- 日付の切り捨てとバケット化
- 文字列の解析とフォーマット
- タイムゾーンとタイムスタンプ