MENU
  1. ホーム
  2. システム開発
  3. データベース
  4. PostgreSQLで時系列データを時間間隔でグルーピングする方法【generate_series】

PostgreSQLで時系列データを時間間隔でグルーピングする方法【generate_series】

データベース
当ページのリンクには広告が含まれています。
PostgreSQLでtimestamp型データを日別・月別・時間別・任意粒度でグルーピング集計する方法を解説し、DATE_TRUNC関数や実用的なSQL例を紹介する記事

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欠損時間帯も含めたい○○

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'           -- ← ここを変えると間隔が変わる
    ) 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終了日時。
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分ごとの件数が出来上がりです。

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

intervalの値を変えるだけでさまざまな集計粒度に対応できます。

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

実際にやってみる

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

Step 1：テーブルを作成する

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

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

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

あわせて読みたい
DockerでPostgreSQL環境を構築する手順【docker-compose対応】 Docker ComposeでPostgreSQL 16を起動し、psqlやGUIツールから接続する手順、Volumeによるデータ永続化まで解説します。

Step 2：テストデータを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 3：30分ごとの件数を集計する

当日1日分を30分ごとに集計するクエリを実行します。date_trunc('day', now())で今日の00:00:00を動的に取得しているので日付をハードコードしなくて済みます。

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

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

集計したデータをExcelでグラフ化する！

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

Q&A

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

date_trunc('day', now())の部分をリテラルのタイムスタンプに変えるだけでOKです。

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

timestamp with time zone（timestamptz）でも使える？

使えます。generate_seriesはタイムスタンプ型・タイムゾーン付きタイムスタンプ型の両方をサポートしています。引数とテーブルのカラムの型を合わせておくと暗黙的な型変換によるインデックスへの影響を避けられます。

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

access_timeカラムにインデックスを張るとJOINのパフォーマンスが大幅に改善します。

SQL
CREATE INDEX idx_access_log_access_time ON access_log (access_time);

集計対象の期間が広い場合はパーティショニングも検討してみてください。

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

JOINがLEFT JOINではなくINNER JOINになっている可能性があります。確認してみてください。またcount(*)ではなくcount(対象カラム)を使うことで、JOINが成立しなかった行（NULLの行）は自動的に0として扱われるので、こちらも合わせて確認してみてください。

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

あわせて読みたい
データベースまとめ PostgreSQLの接続設定や時系列SQL、Dockerを使ったPostgreSQL・SQL Server環境構築記事をまとめたページです。

まとめ

  • generate_seriesでタイムスタンプの連続値を生成してそれを軸にLEFT JOINすることで、任意の時間間隔での集計が実現できるよ。
  • intervalの値を変えるだけで10分・30分・1時間など粒度を自由に切り替えできるので使い回しがきいて便利だよ。
  • LEFT JOINを使うことでデータが存在しない時間帯も0件として出力されるのがうれしいポイントだよ。
  • date_trunc('day', now())で動的に今日の開始日時を取得するようにしておくと日付のハードコードを避けられてスマートだよ。
  • 集計結果をそのままExcelに貼り付けてグラフ化できるから報告資料づくりにも直結してくれるよ。
データベース
PostgreSQL SQL
よかったらシェアしてね！
  • URLをコピーしました！
  • URLをコピーしました！
目次