本文へ移動
売上・LTV分析SQL 更新日: 2026年5月2日 約24分で読めます

曜日・時間帯別の売上をSQLで集計する方法｜注文集中時間を可視化する

メルマガの配信時刻は「なんとなく」で決めていないか

「メルマガは火曜の朝10時に送るのがいい」という話をよく聞く。確かに一般論としてそう言われることはある。しかし自社の顧客が本当に火曜の朝10時に行動しているかは、自分のデータで確かめるほかない。

注文データには order_date や created_at のタイムスタンプが必ず記録されている。
これを曜日・時間帯に分解して集計すると、「この店の顧客は土曜の夜21時に最も購買する」「平日昼12時のランチタイムにスパイクがある」といった自社固有のパターンが見える。

このパターンをヒートマップ形式（縦軸：曜日、横軸：時間帯、セルの濃さ：注文数）で可視化することで、メルマガ配信時刻・プッシュ通知のタイミング・セールの開始時間を「感覚」ではなく「データ」で設計できるようになる。

今回は EXTRACT で曜日・時間を分解し、CASE WHEN ピボットでヒートマップ用テーブルをSQLで出力する方法を解説する。

使用するテーブル

-- orders テーブル
-- order_id      : 注文ID
-- customer_id   : 顧客ID
-- created_at    : 注文作成日時（TIMESTAMP型）※ order_date が DATE型のみの場合は注意
-- total_amount  : 注文金額
-- status        : 'completed' / 'cancelled'

order_date（DATE型）と created_at（TIMESTAMP型）の違い
時間帯分析には時刻情報が必要なため、TIMESTAMP型のカラムが必要だ。order_date が DATE型しかない場合は時刻情報が失われているため時間帯集計はできない。Treasure Data では多くの場合 created_at や time（UNIX時間）が使える。UNIX時間の場合は FROM_UNIXTIME(time) でTIMESTAMPに変換してから使う。

STEP 1 ― 曜日と時間帯を抽出する

EXTRACT 関数で注文日時から曜日（0=日曜〜6=土曜）と時間（0〜23）を取り出す。

-- STEP1: 注文日時から曜日・時間帯を抽出する

WITH order_time AS (
    SELECT
        order_id,
        customer_id,
        total_amount,

        -- タイムゾーンをJSTに変換してから分解
        -- Treasure Data（Presto）の場合
        created_at AT TIME ZONE 'Asia/Tokyo'  AS jst_time,

        -- 曜日（0=日曜, 1=月曜, ... 6=土曜）
        -- Prestoでは DOW (day of week) を使う
        EXTRACT(DOW FROM (created_at AT TIME ZONE 'Asia/Tokyo'))  AS dow,

        -- 時間帯（0〜23）
        EXTRACT(HOUR FROM (created_at AT TIME ZONE 'Asia/Tokyo'))  AS hour_of_day
    FROM orders
    WHERE status = 'completed'
)
SELECT
    dow,
    hour_of_day,
    COUNT(order_id)  AS order_count
FROM order_time
GROUP BY dow, hour_of_day
ORDER BY dow, hour_of_day;

出力イメージ（縦長形式）

dowhour_of_dayorder_count
0（日）048
0（日）131
………
0（日）21312
0（日）22287
1（月）042
………

この縦長形式は BIツールへのそのまま渡せる。ただし人間が眺めて傾向を掴むには読みにくい。次のSTEPでピボット（横展開）する。

タイムゾーンの落とし穴
データベースに格納されているタイムスタンプがUTCの場合、そのまま集計すると「深夜0〜8時」に実際は日本時間の朝9〜17時の注文が集まってしまう。AT TIME ZONE 'Asia/Tokyo' で変換するのを忘れずに。Treasure DataはデフォルトUTC格納なので特に注意が必要。

STEP 2 ― CASE WHEN で時間帯を横展開（ピボット）する

縦長の集計を「縦軸：曜日、横軸：時間帯」の2次元テーブルに変換する。

全24時間を列にするとカラムが多くなるため、まず「4時間ごとのブロック」でまとめたバージョンを作る。

-- STEP2a: 4時間ブロックでまとめたヒートマップ（概要版）

WITH order_time AS (
    SELECT
        order_id,
        EXTRACT(DOW  FROM (created_at AT TIME ZONE 'Asia/Tokyo'))   AS dow,
        EXTRACT(HOUR FROM (created_at AT TIME ZONE 'Asia/Tokyo'))   AS hour_of_day
    FROM orders
    WHERE status = 'completed'
),
with_label AS (
    SELECT
        order_id,
        -- 曜日を日本語ラベルに変換
        CASE dow
            WHEN 0 THEN '0_日'
            WHEN 1 THEN '1_月'
            WHEN 2 THEN '2_火'
            WHEN 3 THEN '3_水'
            WHEN 4 THEN '4_木'
            WHEN 5 THEN '5_金'
            WHEN 6 THEN '6_土'
        END  AS day_label,
        hour_of_day
    FROM order_time
)
SELECT
    day_label                                                           AS 曜日,
    COUNT(CASE WHEN hour_of_day BETWEEN  0 AND  3 THEN 1 END)          AS 深夜_0_3時,
    COUNT(CASE WHEN hour_of_day BETWEEN  4 AND  7 THEN 1 END)          AS 早朝_4_7時,
    COUNT(CASE WHEN hour_of_day BETWEEN  8 AND 11 THEN 1 END)          AS 午前_8_11時,
    COUNT(CASE WHEN hour_of_day BETWEEN 12 AND 15 THEN 1 END)          AS 昼_12_15時,
    COUNT(CASE WHEN hour_of_day BETWEEN 16 AND 19 THEN 1 END)          AS 夕方_16_19時,
    COUNT(CASE WHEN hour_of_day BETWEEN 20 AND 23 THEN 1 END)          AS 夜_20_23時,
    COUNT(order_id)                                                     AS 合計
FROM with_label
GROUP BY day_label
ORDER BY day_label;

出力イメージ

曜日深夜_0_3時早朝_4_7時午前_8_11時昼_12_15時夕方_16_19時夜_20_23時合計
0_日142892844123986892,014
1_月98723123983125421,734
2_火104682983872985121,667
3_水110743084013215381,752
4_木98713023953185281,712
5_金118762894124126241,931
6_土189983124784127412,230

土曜の夜（20〜23時）が741件と全セルで最多。日曜と金曜の夜も多い。早朝（4〜7時）はどの曜日も少ない。この傾向が見えたら、メルマガ配信を「土曜の19〜20時」に設定すると開封→購買の転換率が高まる可能性がある。

STEP 3 ― 1時間単位の完全ヒートマップを作る（完成版）

より細かく1時間単位で全24時間を横展開する。カラム数が多くなるが、ピークの時間帯を正確に特定できる。

-- STEP3: 1時間単位の完全ヒートマップ（完成版）

WITH order_time AS (
    SELECT
        order_id,
        EXTRACT(DOW  FROM (created_at AT TIME ZONE 'Asia/Tokyo'))  AS dow,
        EXTRACT(HOUR FROM (created_at AT TIME ZONE 'Asia/Tokyo'))  AS hour_of_day
    FROM orders
    WHERE status = 'completed'
),
with_label AS (
    SELECT
        order_id,
        CASE dow
            WHEN 0 THEN '0_日'
            WHEN 1 THEN '1_月'
            WHEN 2 THEN '2_火'
            WHEN 3 THEN '3_水'
            WHEN 4 THEN '4_木'
            WHEN 5 THEN '5_金'
            WHEN 6 THEN '6_土'
        END  AS day_label,
        hour_of_day
    FROM order_time
)
SELECT
    day_label                                                     AS 曜日,
    COUNT(CASE WHEN hour_of_day =  0 THEN 1 END)                 AS h00,
    COUNT(CASE WHEN hour_of_day =  1 THEN 1 END)                 AS h01,
    COUNT(CASE WHEN hour_of_day =  2 THEN 1 END)                 AS h02,
    COUNT(CASE WHEN hour_of_day =  3 THEN 1 END)                 AS h03,
    COUNT(CASE WHEN hour_of_day =  4 THEN 1 END)                 AS h04,
    COUNT(CASE WHEN hour_of_day =  5 THEN 1 END)                 AS h05,
    COUNT(CASE WHEN hour_of_day =  6 THEN 1 END)                 AS h06,
    COUNT(CASE WHEN hour_of_day =  7 THEN 1 END)                 AS h07,
    COUNT(CASE WHEN hour_of_day =  8 THEN 1 END)                 AS h08,
    COUNT(CASE WHEN hour_of_day =  9 THEN 1 END)                 AS h09,
    COUNT(CASE WHEN hour_of_day = 10 THEN 1 END)                 AS h10,
    COUNT(CASE WHEN hour_of_day = 11 THEN 1 END)                 AS h11,
    COUNT(CASE WHEN hour_of_day = 12 THEN 1 END)                 AS h12,
    COUNT(CASE WHEN hour_of_day = 13 THEN 1 END)                 AS h13,
    COUNT(CASE WHEN hour_of_day = 14 THEN 1 END)                 AS h14,
    COUNT(CASE WHEN hour_of_day = 15 THEN 1 END)                 AS h15,
    COUNT(CASE WHEN hour_of_day = 16 THEN 1 END)                 AS h16,
    COUNT(CASE WHEN hour_of_day = 17 THEN 1 END)                 AS h17,
    COUNT(CASE WHEN hour_of_day = 18 THEN 1 END)                 AS h18,
    COUNT(CASE WHEN hour_of_day = 19 THEN 1 END)                 AS h19,
    COUNT(CASE WHEN hour_of_day = 20 THEN 1 END)                 AS h20,
    COUNT(CASE WHEN hour_of_day = 21 THEN 1 END)                 AS h21,
    COUNT(CASE WHEN hour_of_day = 22 THEN 1 END)                 AS h22,
    COUNT(CASE WHEN hour_of_day = 23 THEN 1 END)                 AS h23,
    COUNT(order_id)                                              AS 合計
FROM with_label
GROUP BY day_label
ORDER BY day_label;

STEP 4 ― 「件数」ではなく「構成比」でヒートマップを作る

件数の絶対値では「土曜は元々注文が多い」という曜日効果が混入する。「この時間帯は曜日の中でどれくらいの割合を占めるか」という構成比ヒートマップのほうが、時間帯のピークを正確に捉えられる。

-- STEP4: 構成比ヒートマップ（%表示）

WITH order_time AS (
    SELECT
        order_id,
        EXTRACT(DOW  FROM (created_at AT TIME ZONE 'Asia/Tokyo'))  AS dow,
        EXTRACT(HOUR FROM (created_at AT TIME ZONE 'Asia/Tokyo'))  AS hour_of_day
    FROM orders
    WHERE status = 'completed'
),
with_label AS (
    SELECT
        order_id,
        CASE dow
            WHEN 0 THEN '0_日'  WHEN 1 THEN '1_月'  WHEN 2 THEN '2_火'
            WHEN 3 THEN '3_水'  WHEN 4 THEN '4_木'  WHEN 5 THEN '5_金'
            WHEN 6 THEN '6_土'
        END  AS day_label,
        hour_of_day
    FROM order_time
),
daily_totals AS (
    -- 曜日ごとの合計件数（構成比の分母）
    SELECT day_label, COUNT(order_id) AS day_total
    FROM with_label
    GROUP BY day_label
),
hourly_counts AS (
    SELECT
        day_label,
        hour_of_day,
        COUNT(order_id)  AS hour_count
    FROM with_label
    GROUP BY day_label, hour_of_day
)
SELECT
    hc.day_label                                              AS 曜日,
    -- 各時間帯の構成比（その曜日の注文数に占める割合）
    ROUND(MAX(CASE WHEN hc.hour_of_day =  8 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h08_pct,
    ROUND(MAX(CASE WHEN hc.hour_of_day =  9 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h09_pct,
    ROUND(MAX(CASE WHEN hc.hour_of_day = 10 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h10_pct,
    ROUND(MAX(CASE WHEN hc.hour_of_day = 11 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h11_pct,
    ROUND(MAX(CASE WHEN hc.hour_of_day = 12 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h12_pct,
    ROUND(MAX(CASE WHEN hc.hour_of_day = 13 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h13_pct,
    ROUND(MAX(CASE WHEN hc.hour_of_day = 18 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h18_pct,
    ROUND(MAX(CASE WHEN hc.hour_of_day = 19 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h19_pct,
    ROUND(MAX(CASE WHEN hc.hour_of_day = 20 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h20_pct,
    ROUND(MAX(CASE WHEN hc.hour_of_day = 21 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h21_pct,
    ROUND(MAX(CASE WHEN hc.hour_of_day = 22 THEN hc.hour_count END) * 100.0 / dt.day_total, 1)  AS h22_pct
FROM hourly_counts  hc
JOIN daily_totals   dt  ON hc.day_label = dt.day_label
GROUP BY hc.day_label, dt.day_total
ORDER BY hc.day_label;

出力イメージ（主要時間帯のみ抜粋）

曜日h10_pcth12_pcth20_pcth21_pcth22_pct
0_日4.86.29.110.49.8
1_月5.16.88.99.28.4
5_金4.96.49.811.210.1
6_土4.26.99.410.810.2

日曜・金曜・土曜の21時台が10〜11%台と突出している。「21時に送るメルマガ」の根拠をデータで示せる。

応用：顧客セグメント別の行動時間帯を比較する

「ロイヤル顧客」と「新規顧客」で購買時間帯のパターンが違うことがある。セグメント別にヒートマップを作ると、配信時刻をセグメントごとに最適化できる。

-- セグメント別の購買ピーク時間帯比較

WITH order_time AS (
    SELECT
        o.order_id,
        o.customer_id,
        EXTRACT(HOUR FROM (o.created_at AT TIME ZONE 'Asia/Tokyo'))  AS hour_of_day,
        -- RFMやLTVで事前に計算したセグメントと結合する想定
        seg.segment_name
    FROM orders           o
    JOIN customer_segments seg  ON o.customer_id = seg.customer_id
    WHERE o.status = 'completed'
)
SELECT
    segment_name,
    hour_of_day,
    COUNT(order_id)  AS order_count,
    ROUND(
        COUNT(order_id) * 100.0
        / SUM(COUNT(order_id)) OVER (PARTITION BY segment_name)
    , 1)             AS pct_within_segment
FROM order_time
GROUP BY segment_name, hour_of_day
ORDER BY segment_name, hour_of_day;

出力イメージ（一部）

segment_namehour_of_dayorder_countpct_within_segment
ロイヤル顧客122848.4
ロイヤル顧客2141212.2
新規顧客121986.9
新規顧客212679.3
休眠復活顧客2014211.8

ロイヤル顧客は21時のピークが12.2%と高く、新規顧客は分散している。「ロイヤル向けは21時配信、新規向けは昼12時も狙える」という分けた設計の根拠になる。

実務での運用ヒント

① 季節・キャンペーン期間を除外して「平常時」を見る

年末セール期間中は通常と異なる時間帯に注文が集中する。こういったイベント期間を除外した「平常時のヒートマップ」と「セール期間のヒートマップ」を分けて作ると、より精度の高い洞察が得られる。WHERE 句でキャンペーン期間を除外するだけでよい。

② 縦長形式のままBIツールに渡す

STEP1 の縦長形式（dow, hour_of_day, order_count）をそのまま Looker Studio や Tableau に渡し、ツール側でヒートマップを描画する方が柔軟だ。SQL側でのピボットは「SQLで完結させたいとき」や「Excelに貼り付けたいとき」に使う。

③ EXTRACT(DOW ...) の曜日番号はエンジンによって異なる

Presto（Treasure Data）では日曜=0、月曜=1、…、土曜=6だ。MySQLの DAYOFWEEK() は日曜=1、月曜=2、…、土曜=7とズレる。BigQueryの EXTRACT(DAYOFWEEK ...) も日曜=1始まりだ。環境を変えたときに曜日ラベルがズレないよう、CASE による日本語変換を必ず挟むのがよい習慣だ。

まとめ

今回のクエリの流れを振り返る。

  1. EXTRACT(DOW ...) と EXTRACT(HOUR ...) で注文日時から曜日・時間帯を取り出す（タイムゾーン変換を忘れずに）
  2. CASE dow WHEN 0 THEN '日' ... で曜日ラベルを付与し、並び順を制御する
  3. COUNT(CASE WHEN hour_of_day = N THEN 1 END) の並びで**横展開（ピボット）**する
  4. 絶対件数だけでなく、曜日内の構成比でも見ることで曜日効果を除いたピークを掴む
  5. 顧客セグメント別に分けると、誰に・いつ送るかの設計が精緻になる

「土曜の21時」というピークを確認できた時点で、それをそのままメルマガ配信時刻に反映するのが最短の活用だ。さらにA/Bテストで「土曜21時 vs 従来の火曜10時」を比較すれば、このヒートマップ分析の投資対効果を数字で証明できる

次のアクション

SQLやデータ活用を、手元で試しながら理解する

記事で読んだ考え方を、SQL練習場や関連カテゴリの記事でさらに深掘りできます。相談やご依頼もお問い合わせページから受け付けています。

SQL練習場で試す お問い合わせ

この記事を書いた人：martechfarm

Treasure Data Top Lapidarist Award受賞。

SQL / Digdag / Python / CDP設計 / CRM設計を横断し、企業のデータ活用を支援。

実績・支援内容を見る →

MarTech Farmをもっと見る

今すぐ購読し、続きを読んで、すべてのアーカイブにアクセスしましょう。

続きを読む