PostgreSQLで時系列データを任意の時間間隔でグルーピングするSQL実装例

やり方
実際にやってみる

やり方

概要

例えば30分毎にまとめたい場合は次のようにします。
00:00～00:29間の件数、00:30～00:59間の件数、01:00～01:29間の件数のような形です。

SQL

select
    to_char(time_table, 'hh24:mi')
    , count(【対象カラム】)
from
    generate_series(
        【開始タイムスタンプ】
        , 【終了タイムスタンプ】
        , interval '30 minute'
    ) time_table
    left join 【対象テーブル】 on (
        【対象カラム】 >= time_table
        and 【対象カラム】 < time_table + interval '30 minute'
    ) 
group by
    time_table
order by
    time_table;

仕組み

generate_series関数で下記のように30分毎のタイムスタンプを作成します。
上記ではhh24:miでフォーマットしています。

00:00
00:30
01:00
...（以下省略）

これに集計したいテーブルのデータをLEFT JOINします。

00:00  2025-11-28 00:04:00
00:00  2025-11-28 00:16:00
00:00  2025-11-28 00:29:00
00:30  2025-11-28 00:35:00
00:30  2025-11-28 00:51:00
01:00  2025-11-28 01:00:00
01:00  2025-11-28 01:11:00
01:00  2025-11-28 01:22:00
01:00  2025-11-28 01:23:00

generate_series関数で作成した項目でグルーピングしてカウントすると出来上がりです。

00:00  3
00:30  2
01:00  4

generate_series関数について

startからstopまでstep intervalの間隔で連続したデータを作成します。fromで指定することによって1カラムのテーブルのような形で利用できます。

9.24. Set Returning Functions

Function
generate_series(start, stop, step interval)

Argument Type
timestamp or timestamp with time zone

Return Type
setof timestamp or setof timestamp with time zone (same as argument type)

Description
Generate a series of values, from start to stop with a step size of step

PostgreSQL: Documentation: 9.6: Set Returning Functions
https://www.postgresql.org/docs/9.6/functions-srf.html

実際にやってみる

テーブル作成

1日分のアクセス数を30分毎に集計してみるパターンでやってみます。
別にタイムスタンプだけで良いと思いますが~~とりあえずアクセスログっぽい感じでユーザー名とかも入れておきます~~。

psql

create table access_log (
    id bigserial
    , user_name varchar(255)
    , access_time timestamp
);
CREATE TABLE

テストデータを作成

1分単位のランダムなタイムスタンプを1万件ほど作成します。ユーザー名は、どうしましょう・・・とりあえずテキトーな文字をいれます。~~余計なものいれるからこうなる・・・~~

psql

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件ほど出来上がったデータを確認してみるといい感じに分単位のランダムなタイムスタンプとなっていることが確認できます。

psql

select * from access_log limit 10;
  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

集約したカウントを取得する

今日という1日間の30分ごとの件数を取得します。

psql

select
    to_char(time_table, 'hh24:mi')
    , count(access_log.access_time) 
from
    generate_series( 
        date_trunc('day', now())
        , date_trunc('day', now()) + interval '23 hour 59 minute 59 second'
        , interval '30 minute'
    ) time_table 
    left join access_log on (
        access_log.access_time >= time_table 
        and access_log.access_time < time_table + interval '30 minute'
    ) 
group by
    time_table 
order by
    time_table;
 00:00   |   197
 00:30   |   199
 01:00   |   202
 01:30   |   217
 02:00   |   218
 02:30   |   194
 03:00   |   203
 03:30   |   217
 04:00   |   214
 04:30   |   185
 05:00   |   219
 05:30   |   195
 06:00   |   216
 06:30   |   221
 07:00   |   195
 07:30   |   205
 08:00   |   208
 08:30   |   192
 09:00   |   225
 09:30   |   216
 10:00   |   214
 10:30   |   210
 11:00   |   213
 11:30   |   202
 12:00   |   206
 12:30   |   223
 13:00   |   195
 13:30   |   219
 14:00   |   204
 14:30   |   197
 15:00   |   197
 15:30   |   234
 16:00   |   188
 16:30   |   193
 17:00   |   190
 17:30   |   231
 18:00   |   224
 18:30   |   185
 19:00   |   215
 19:30   |   193
 20:00   |   217
 20:30   |   205
 21:00   |   215
 21:30   |   212
 22:00   |   223
 22:30   |   226
 23:00   |   224
 23:30   |   207

これをそのままエクセルに持っていけばワンクリックでグラフにできますね！