PostgreSQLで時系列データを30分ごとに集計する方法【0件の時間帯も表示】

このサイトはアフィリエイト広告(Amazonアソシエイト含む)を掲載しています。
PostgreSQLの日時グルーピングを示す文字と、日別・月別・時間別の集計グラフ

PostgreSQLで30分ごとなど任意の間隔に集計しデータがない時間帯も0件で表示したい場合はgenerate_seriesで時間軸を作って対象テーブルをLEFT JOINします。0件の時間帯を補う必要がない場合はdate_binを使うとより短いSQLで集計できます。

目次

date_trunc・date_bin・generate_seriesの使い分け

既存データを30分単位にまとめるだけならdate_binでも集計できます。でもこっちはデータが1件もない時間帯は結果に現れません。00:30のデータがなくても「00:30|0件」のように行を残したい場合にgenerate_seriesとLEFT JOINの組み合わせが役立ちます。

方法向いているケース30分単位データなしを0件表示
date_trunc時・日・月などの単位でまとめる△×
date_bin15分・30分など任意間隔でまとめる○×
generate_series + LEFT JOIN欠損時間帯も含めたい○○

0件の時間帯を補わない場合はPostgreSQL 14以降のdate_binで30分単位にまとめられます。後半で作成するaccess_logを使うと当日分の集計は次のように書けます。

SQL
SELECT
    date_bin(
        interval '30 minute'
        , access_time
        , CURRENT_DATE::timestamp
    ) AS time_slot
    , count(*) AS cnt
FROM
    access_log
WHERE
    access_time >= CURRENT_DATE::timestamp
    AND access_time < CURRENT_DATE::timestamp + interval '1 day'
GROUP BY
    1
ORDER BY
    1;

第3引数は時間を区切る基準点です。この例では当日の00:00を基準に30分ずつ区切ります。データがない時間帯の行は生成されません。なお、date_binの間隔には月以上の単位を指定できません。

generate_seriesを使ったグルーピングの基本

考え方の概要

たとえば1日分のアクセスログを30分ごとに集計したい場合、最終的に欲しい結果はこういう形ですよね。

SQL – 実行結果
 time_slot | cnt
-----------+-----
 00:00     | 197
 00:30     | 199
 01:00     | 202
 ...(以下省略)

この「30分刻みの時間軸」を自分で作り出してしまうのがgenerate_series関数の役割です。生成した時間軸を基準にして実テーブルをLEFT JOINすることで、データが存在しない時間帯も0件として取得できます。

SQLの全体像(テンプレート)

30分間隔でグルーピングする汎用テンプレートです。【 】で囲んだ箇所を自身の環境に合わせて書き換えてください。

SQL
SELECT
    to_char(time_table, 'hh24:mi')   AS time_slot
    , count(【対象カラム】)          AS cnt
FROM
    generate_series(
        【開始タイムスタンプ】           -- 例: '2025-11-28 00:00:00'::timestamp
        , 【終了タイムスタンプ】         -- 例: '2025-11-28 23:59:59'::timestamp
        , interval '30 minute'         -- JOIN条件の時間幅も同じ値にする
    ) AS time_table
    LEFT JOIN 【対象テーブル】 ON (
        【対象カラム】 >= time_table
        AND 【対象カラム】 < time_table + interval '30 minute'
    )
GROUP BY
    time_table
ORDER BY
    time_table;

LEFT JOINを使う理由
INNER JOINにするとデータが存在しない時間帯の行が消えてしまいます。グラフ化や外部ツールへの連携を想定する場合は0件でも行を出力するためにLEFT JOINを使うのがベターです。

generate_series関数とは

基本的な動作

generate_seriesは開始値から終了値までを指定の間隔で連続生成する集合返却関数(Set Returning Function)です。タイムスタンプ型にも対応していてFROM句に置くと1カラムのテーブルのように扱えます。

generate_series(start, stop, step interval)

引数型説明
starttimestamp / timestamptz開始日時。
stoptimestamp / timestamptz生成する日時の上限。刻み幅に一致する場合は、この日時も結果に含まれます。各時間枠で数える範囲はJOIN条件で決まります。
stepinterval刻み幅。

公式ドキュメントはこちらで確認できます。
PostgreSQL: Documentation: 18: 9.26. Set Returning Functions

生成されるタイムスタンプのイメージ

30分間隔で'2025-11-28 00:00:00'〜'2025-11-28 01:30:00'を指定すると次のような値が生成されます。

SQL – 実行結果
2025-11-28 00:00:00
2025-11-28 00:30:00
2025-11-28 01:00:00
2025-11-28 01:30:00

これを軸にLEFT JOINすると各行にマッチしたレコードが紐付いてきます。

実行結果
time_table           | access_time
---------------------+---------------------
2025-11-28 00:00:00  | 2025-11-28 00:04:00
2025-11-28 00:00:00  | 2025-11-28 00:16:00
2025-11-28 00:00:00  | 2025-11-28 00:29:00
2025-11-28 00:30:00  | 2025-11-28 00:35:00
2025-11-28 00:30:00  | 2025-11-28 00:51:00
2025-11-28 01:00:00  | 2025-11-28 01:00:00
...

あとはtime_tableでGROUP BY&COUNTするだけで30分ごとの件数が出来上がりです。

間隔を変えるだけで柔軟に対応できる

集計間隔を変えるときはgenerate_seriesの第3引数とJOIN条件で加算する時間幅を同じ値にします。15分ごとなら両方をinterval '15 minutes'にしてください。片方だけ変えると同じデータを重複して数えたり集計されない時間帯ができたりします。

集計粒度intervalの記述例
10分ごとinterval '10 minute'
15分ごとinterval '15 minute'
30分ごとinterval '30 minute'
1時間ごとinterval '1 hour'
1日ごとinterval '1 day'

実際にやってみる

アクセスログを想定したテーブルを作成しテストデータを挿入して、集計クエリを動かしてみます。

Step

テーブルを作成する

1日分のアクセス数を30分ごとに集計するパターンで試します。タイムスタンプだけで十分だったのにアクセスログっぽい感じにしたくてユーザー名まで入れてしまいました。

SQL
CREATE TABLE access_log (
    id          BIGSERIAL
    , user_name VARCHAR(255)
    , access_time TIMESTAMP
);
実行結果
CREATE TABLE

ローカル環境でPostgreSQLを動かすにはDockerが便利です。以下の記事も参考にどうぞ。

Step

テストデータを10,000件作成する

1分単位のランダムなタイムスタンプを1万件ほど作成します。user_nameはmd5で適当な文字列を入れています。余計なものを入れるからこうなる……

SQL
INSERT INTO access_log (user_name, access_time)
SELECT
    md5(clock_timestamp()::TEXT)
    , date_trunc('day', now()) + trunc(random() * 60 * 24) * interval '1 minute'
FROM
    generate_series(1, 10000);
実行結果
INSERT 0 10000

10件ほど確認してみると分単位のランダムなタイムスタンプになっていることが確認できます。

SQL
SELECT * FROM access_log LIMIT 10;
実行結果
 id |             user_name            |     access_time
----+----------------------------------+---------------------
  1 | a02f0a047a994ee920cfdb7940cecef8 | 2025-11-28 03:41:00
  2 | bb46b16afc0737f4aeb6565d8b556474 | 2025-11-28 05:21:00
  3 | 9e60031d328a3cf28321f8425a6e7142 | 2025-11-28 13:20:00
  4 | 927c172e60ca72524f7990e43b0b332e | 2025-11-28 15:32:00
  5 | e5e03ff6319232121ae27af22a3e7828 | 2025-11-28 20:25:00
  6 | fbd4c4d1b34aa66311fd40a38ecbca5c | 2025-11-28 05:07:00
  7 | 0a0ac15ac15790d15246cfdb5a1d03e4 | 2025-11-28 16:39:00
  8 | c981fa58ced8d1187c2a3821e9e2375e | 2025-11-28 00:54:00
  9 | c27e463b24d6296afcd3ae08071823b0 | 2025-11-28 09:02:00
 10 | fb102f549419560de544a7ace61935cc | 2025-11-28 11:32:00
Step

30分ごとの件数を集計する

当日1日分を30分ごとに集計します。CURRENT_DATE::timestampで当日の00:00を取得し、23:30までの48個の時間枠を作ります。各時間枠は開始時刻を含み30分後の時刻を含まない条件で数えるため、00:30ちょうどのデータは00:30の枠に入ります。

SQL
SELECT
    to_char(time_table, 'hh24:mi') AS time_slot,
    count(access_log.access_time) AS cnt
FROM
    generate_series(
        CURRENT_DATE::timestamp,
        CURRENT_DATE::timestamp + interval '23 hours 30 minutes',
        interval '30 minutes'
    ) AS time_table
LEFT JOIN access_log ON (
    access_log.access_time >= time_table
    AND access_log.access_time < time_table + interval '30 minutes'
)
GROUP BY
    time_table
ORDER BY
    time_table;
実行結果
 time_slot | cnt
-----------+-----
 00:00     | 197
 00:30     | 199
 01:00     | 202
 01:30     | 217
 02:00     | 218
 ...(中略)
 23:00     | 224
 23:30     | 207
Step

Excelとかでグラフ化に利用しやすい!

この結果をそのままExcelに貼り付けると簡単にグラフ化できますね!

30分ごとのアクセスログ件数を表計算ソフトで棒グラフにした画面

報告資料づくりに直結してくれるのも generate_seriesの便利なところですね!

0件の時間帯と境界時刻を確認する

0件の時間帯も残ることを確認するため、2件だけのデータで集計してみます。このSQLはテーブルを作成・変更せずに実行できます。00:30台のデータを入れず、01:00ちょうどのデータがどの枠に入るかも確認します。

SQL
WITH sample_log(access_time) AS (
    VALUES
        ('2025-11-28 00:10:00'::timestamp)
        , ('2025-11-28 01:00:00'::timestamp)
)
SELECT
    to_char(time_table, 'hh24:mi')   AS time_slot
    , count(sample_log.access_time) AS cnt
FROM
    generate_series(
        '2025-11-28 00:00:00'::timestamp
        , '2025-11-28 01:00:00'::timestamp
        , interval '30 minute'
    ) AS time_table
    LEFT JOIN sample_log ON (
        sample_log.access_time >= time_table
        AND sample_log.access_time < time_table + interval '30 minute'
    )
GROUP BY
    time_table
ORDER BY
    time_table;
実行結果
 time_slot | cnt
-----------+-----
 00:00     |   1
 00:30     |   0
 01:00     |   1
(3 rows)

00:30の行は残り件数が0になります。01:00ちょうどのデータは01:00の枠に入ります。

迷いやすいポイント

特定の日付範囲を指定したい

generate_seriesの第1引数に開始日時、第2引数に最後の時間枠の開始日時を指定します。2025年11月1日の1日分を30分ごとに集計する場合は、00:00から23:30まで生成します。JOIN条件はそのまま30分幅にしてください。

SQL
generate_series(
    '2025-11-01 00:00:00'::timestamp
    , '2025-11-01 23:30:00'::timestamp
    , interval '30 minute'
)

この指定では最後の枠で23:30以上・翌日00:00未満のデータを数えます。日付だけの変更ではテーブルに保存済みの日時は変わりません。Step 2で作った当日のデータを使う場合は、集計対象の日付も合わせてください。

timestamp with time zone(timestamptz)でも使える?

使えます。対象カラムがtimestamptzの場合は生成する時間軸の開始・終了もtimestamptzにそろえてください。日付の区切りや結果の表示はセッションのTimeZone設定に関係するため、どのタイムゾーンを基準に集計するかも確認してください。夏時間がある地域では現地時刻の1日が常に24時間にならない場合があります。

大量データで実行速度が遅い場合の対策は?

access_timeの範囲検索に使えるインデックスがあるか確認します。対象期間がテーブル全体の一部に限られる場合などは、インデックスによって読み取るデータを減らせる可能性があります。

SQL
CREATE INDEX idx_access_log_access_time ON access_log (access_time);

効果はデータ量や対象期間によって変わります。集計SELECTにEXPLAIN (ANALYZE, BUFFERS)を付け、実行時間や読み取り量を確認してください。この指定ではSELECTが実際に実行されます。テーブルの大半を読む集計ではインデックスを使わない実行計画が選ばれることもあります。


出力されたcost・rows・BUFFERSの読み方はEXPLAINとEXPLAIN ANALYZEの違いをまとめた記事で確認できます。

データが存在しない時間帯が出力されない

結合がLEFT JOINになっているか確認してください。結合先のaccess_logに対する絞り込み条件をWHERE句へ追加するとデータがない時間帯の行まで除外されることがあります。例えば特定ユーザーだけを数える場合はaccess_log.user_name = '対象ユーザー'をJOINのON条件へ追加します。

行は残るものの件数が1になる場合はcount(*)を使っていないか確認してください。このSQLではcount(access_log.access_time)を使い、結合先がNULLになる行を数えないようにします。


PostgreSQLやDocker環境構築の記事をまとめて確認したい場合はデータベース記事一覧もどうぞ。

まとめ

  • generate_seriesでタイムスタンプの連続値を生成してそれを軸にLEFT JOINすることで、任意の時間間隔での集計が実現できるよ。
  • intervalの値を変えるだけで10分・30分・1時間など粒度を自由に切り替えできるので使い回しがきいて便利だよ。
  • LEFT JOINを使うことでデータが存在しない時間帯も0件として出力されるのがうれしいポイントだよ。
  • 実践例ではCURRENT_DATE::timestampで当日の00:00を取得しているので毎回日付を書き換えずに集計できるよ。
  • 集計結果をそのままExcelに貼り付けてグラフ化できるから報告資料づくりにも直結してくれるよ。
  • URLをコピーしました!
目次