PostgreSQLの時系列データで押さえたいSQL 7題

はじめに

サーバー監視のメトリクスのような「一定間隔で溜まる時系列データ」は、集計・欠測の検出・重複の扱い・索引の設計と、SQLの基本が一通り出てくる題材です。この記事では、そうしたデータでよく書くSQLを7題に絞り、「なぜその書き方になるのか」を実際に動かした結果と一緒に整理します。

動作確認はPostgreSQL 16で行いました。題材は、ホストごとのCPU・メモリ使用率を10分間隔で記録するテーブルです。

CREATE TABLE metric_definitions (
  metric_name text PRIMARY KEY,
  label       text NOT NULL
);

CREATE TABLE metrics (
  id           bigserial PRIMARY KEY,
  host_id      int  NOT NULL,
  collector_id text NOT NULL,                       -- 収集エージェントの識別子
  metric_name  text NOT NULL REFERENCES metric_definitions (metric_name),
  measured_at  timestamptz NOT NULL,
  value        numeric,
  status       text NOT NULL CHECK (status IN ('ok', 'missing')),
  CONSTRAINT uq_metrics UNIQUE (host_id, collector_id, metric_name, measured_at)
);

status = 'missing' は「取得に失敗した」ことを示す行で、value は NULL です。

1. グループごとの最新1件(ROW_NUMBER)

ホストごと・メトリクスごとに、最新の1行を取り出します。

SELECT host_id, metric_name, measured_at, value
FROM (
  SELECT host_id, metric_name, measured_at, value,
         ROW_NUMBER() OVER (
           PARTITION BY host_id, metric_name
           ORDER BY measured_at DESC
         ) AS rn
  FROM metrics
  WHERE status = 'ok'
) t
WHERE rn = 1;

ROW_NUMBER() OVER (...) は、行を減らさずに「グループ内の順位」の列を足すウィンドウ関数です。PARTITION BY がグループの区切り、ORDER BY が順位の付け方です。

ポイントは3つあります。

• SELECTを2段にする理由。 SQLは FROM → WHERE → SELECT の順に評価されるため、SELECT で作った rn は同じ階層の WHERE から見えません。サブクエリ、または共通テーブル式(Common Table Expression、以下CTE。WITH 句で作る名前付きの一時的な表)で包んで、外側で rn = 1 と絞ります。
• GROUP BY と MAX(measured_at) では解けない。 最大の時刻は取れますが、その行の value を一緒に取り出せません(value は GROUP BY にも集計関数にも入っていないためエラーになります)。
• PostgreSQLなら DISTINCT ON でも書ける。 短く書けますが、PostgreSQL固有の構文です。

SELECT DISTINCT ON (host_id, metric_name)
       host_id, metric_name, measured_at, value
FROM metrics
WHERE status = 'ok'
ORDER BY host_id, metric_name, measured_at DESC;

最新の行を取る条件(status = 'ok')は、順位を振る前の内側の WHERE に書きます。外側に書くと、欠測の行が1位になってから消えてしまい、本来2位だった有効な行が結果に出ません。

2. 前回値との差(LAG)

1つ前の行の値を持ってくるのが LAG です。急変の検知などに使います。

WITH d AS (
  SELECT measured_at, value,
         value - LAG(value) OVER (
           PARTITION BY host_id, metric_name
           ORDER BY measured_at
         ) AS diff
  FROM metrics
  WHERE host_id = 1 AND metric_name = 'cpu' AND status = 'ok'
)
SELECT measured_at, value, diff
FROM d
WHERE abs(diff) > 20
ORDER BY measured_at;

      measured_at       | value |  diff
------------------------+-------+--------
 2026-10-01 12:00:00+09 | 76.60 |  31.51
 2026-10-01 12:10:00+09 | 48.21 | -28.39

• ウィンドウ関数は WHERE に直接書けない。 WHERE abs(value - LAG(value) OVER (...)) > 20 と書くと window functions are not allowed in WHERE になります。CTE(WITH)で名前を付けた表を先に作り、後ろのクエリで絞ります。
• PARTITION BY を忘れない。 複数のホストを一度に処理するとき、省略すると別ホストの値が「1つ前」になってしまいます。エラーは出ず、それらしい数字が出るので気づきにくいバグです。
• 先頭の行は LAG が NULL になり、差も NULL。 abs(NULL) > 20 は真にならないので、WHERE で自然に落ちます。
• LAG は「表にある1つ前の行」を返す。 途中の行が欠けていると、その差は10分間の変化ではなく、欠けた分を含む期間の変化です。

3. 欠測の検出(generate_series と LEFT JOIN)

「あるはずなのに無い行」は、WHERE では探せません。WHERE は表に実在する行を絞り込む道具だからです。先に「あるはずの時刻の一覧」を作り、実データを重ねて、重ならなかったものを拾います。

WITH slots AS (
  SELECT generate_series(
    timestamptz '2026-10-01 00:00+09',
    timestamptz '2026-10-01 23:50+09',
    interval '10 minutes'
  ) AS slot
)
SELECT s.slot
FROM slots s
LEFT JOIN metrics m
  ON  m.measured_at = s.slot
  AND m.host_id = 1
  AND m.metric_name = 'cpu'
WHERE m.id IS NULL
ORDER BY s.slot;

1日は10分刻みで144スロットです。LEFT JOIN は左の表の行を、相手がいなくても残し、右側の列を NULL にします。普通の JOIN(INNER JOIN)では、相手のいない欠測のスロットが消えてしまいます。

右の表の条件は ON に書く。 これが最もよくある間違いです。

-- 悪い例:欠測が0行になる
FROM slots s
LEFT JOIN metrics m ON m.measured_at = s.slot
WHERE m.host_id = 1 AND m.metric_name = 'cpu' AND m.id IS NULL;

LEFT JOIN で補われた欠測の行は、右側の列がすべて NULL です。WHERE は結合が終わった後に評価されるので、NULL = 1 は判定不能となり、その行が落ちます。さらに、ON に時刻の条件しかないと、同じ時刻の別ホスト・別メトリクスの行とつながってしまい、m.id が NULL になりません。ON に書けば、この2つを同時に防げます。

WHERE に書いてよいのは、左の表の列の条件と、m.id IS NULL のような NULL を調べる条件だけです。

欠測の件数だけが欲しいときは、SELECT count(*) に変えます。

4. タイムゾーンを意識した日次集計

DBの timestamptz は内部ではUTCで保持され、表示や date_trunc は接続のタイムゾーン設定に従います。日本時間の暦日で集計したいときは、AT TIME ZONE で明示します。

SELECT date_trunc('day', m.measured_at AT TIME ZONE 'Asia/Tokyo') AS day,
       d.label,
       round(avg(m.value), 2) AS avg_value,
       count(*)               AS samples
FROM metrics m
JOIN metric_definitions d ON d.metric_name = m.metric_name
WHERE m.host_id = 1
  AND m.metric_name = 'mem'
  AND m.status = 'ok'
  AND m.measured_at >= '2026-10-01 00:00+09'
  AND m.measured_at <  '2026-10-04 00:00+09'
GROUP BY 1, 2
ORDER BY 1;

         day         |    label     | avg_value | samples
---------------------+--------------+-----------+---------
 2026-10-01 00:00:00 | メモリ使用率 |     50.19 |     144
 2026-10-02 00:00:00 | メモリ使用率 |     50.45 |     144
 2026-10-03 00:00:00 | メモリ使用率 |     50.19 |     144

• AT TIME ZONE は date_trunc の内側に置く。 接続のタイムゾーンがUTCのときに date_trunc('day', measured_at) だけで集計すると、日付の境目が日本時間の朝9時になり、日ごとの件数が 54 / 144 / 90 とばらつきます。エラーは出ず、結果がそれらしく見えるのが厄介です。外側に置くと、先にUTCの日付で丸められた後に変換されるので、意味がありません。
• 期間は >= と < の組み合わせ(半開区間)で書く。 BETWEEN は両端を含むので、日次で回すと境界の1行が前日と翌日の両方に入ります。
• 集計しない列はすべて GROUP BY に入れる。 結合先の d.label も対象です。GROUP BY 1, 2 の位置指定は短いですが、SELECT句の並べ替えで壊れるので、実務では列名で書くほうが安全です。
• 欠測行を平均に混ぜない。 status = 'ok' を明示します。

5. 直前N日の基準値と、件数を添える理由

「今の値が、過去30日の同じ時間帯と比べて普通か」を判定するための基準値を出します。

WITH p AS (
  SELECT timestamptz '2026-10-03 12:00+09' AS t      -- 基準時刻は引数で渡す
)
SELECT round(avg(m.value), 2) AS avg_value,
       count(m.value)         AS n
FROM metrics m
CROSS JOIN p
WHERE m.host_id = 1
  AND m.metric_name = 'cpu'
  AND m.status = 'ok'
  AND m.measured_at >= p.t - interval '30 days'
  AND m.measured_at <  p.t                           -- 基準時刻より後は使わない
  AND abs(extract(epoch FROM
        (m.measured_at AT TIME ZONE 'Asia/Tokyo')::time
      - (p.t           AT TIME ZONE 'Asia/Tokyo')::time)) <= 3600;   -- 前後1時間

• 平均には必ず件数を添える。 avg だけでは、144件の平均か1件だけの値かを区別できません。データが0件のとき avg は 0 ではなく NULL になります。「件数が少なすぎるので使わない」という判断を呼び出し側ができるように、件数も同時に返します。count(*) は行数、count(value) は NULL でない値の数で、平均の元になった件数を知りたいなら後者が正確です。
• now() を直接書かず、基準時刻を引数で渡す。 実行する時刻で結果が変わるので、テストが再現できず、「あの判定はどのデータで計算したか」も後から確かめられません。
• ::time は日付を捨てて時刻だけにそろえる。 DBの時刻はUTCなので、先に AT TIME ZONE で日本時間にしてから変換します。
• 日跨ぎに注意。 基準が0:30 のとき、23:30 との素の差は 82,800秒で「1時間以内」に入りません。時刻を24時間の円周上の距離として測り、least(gap, 86400 - gap) <= 3600(gap は上の abs(extract(...)) の値)とします。これを忘れると、夜中の基準値だけ集める件数が狂います。

6. 冪等な取込(ON CONFLICT)

同じデータを何度投入しても、結果が変わらないようにします。再送、障害復旧後の再投入、手動での再実行、二重起動と、同じ値が複数回届く場面はいくらでもあります。

重複を排除しているのは、アプリの if 文ではなく、一意制約(uq_metrics)です。ON CONFLICT は、その制約に衝突したときの動き方を指定するだけで、制約が無いと書けません。

INSERT INTO metrics (host_id, collector_id, metric_name, measured_at, value, status)
VALUES (1, 'agent-a', 'cpu', '2026-10-01 00:20+09', 42.5, 'ok')
ON CONFLICT (host_id, collector_id, metric_name, measured_at)
DO NOTHING
RETURNING id;

1回目は id が1行返り、2回目以降は0行です(値が違っても、先に入った値は変わりません)。この返り値の有無で、新規か重複かを見分けられます。

欠測の行に、後から実測値が届く場合。 DO NOTHING だと、欠測のまま残ってしまいます。かといって無条件に DO UPDATE で上書きすると、先に入っていた正しい値が、後から来た不正な値で壊れます。「既存が欠測で、今回が実測のときだけ更新する」条件を付けます。

INSERT INTO metrics (host_id, collector_id, metric_name, measured_at, value, status)
VALUES (1, 'agent-a', 'cpu', '2026-10-01 00:20+09', 42.5, 'ok')
ON CONFLICT (host_id, collector_id, metric_name, measured_at)
DO UPDATE SET value = excluded.value, status = 'ok'
WHERE metrics.status = 'missing' AND excluded.status = 'ok'
RETURNING (xmax = 0) AS inserted;

• excluded は「挿入しようとした行」を指す特別な名前です。既存の行は テーブル名.列 で参照します。
• DO UPDATE の WHERE が偽なら、更新も挿入もされず、RETURNING は0行になります。
• RETURNING の結果は3通りです。true は新規挿入、false は欠測の補完(更新)、0行は何もしなかった(重複)です。xmax = 0 は内部の仕組みを使った判別方法です。

try / except で重複エラーを捕まえる方法は避ける。 PostgreSQLは、トランザクション中に1回エラーが起きると、そのトランザクション全体が中断状態になります。

BEGIN;
INSERT ... (重複)  → ERROR: duplicate key value violates unique constraint
INSERT ... (新規)  → ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;

30件をまとめて取り込む途中で1件が重複しただけで、後ろの新規分も入りません。ON CONFLICT DO NOTHING ならエラーが出ず、トランザクションが生きたまま最後まで進みます。

7. 複合インデックスの列順

次のクエリを速くしたいとします。

SELECT * FROM metrics
WHERE host_id = 1 AND metric_name = 'cpu' AND measured_at >= '2026-10-02 00:00+09'
ORDER BY measured_at DESC;

CREATE INDEX ix_metrics_host_metric_time
  ON metrics (host_id, metric_name, measured_at DESC);

等値で絞る列を先に、範囲や並び替えに使う列を最後に置く。 複合インデックスは、電話帳(都道府県 → 姓 → 名)のように、左の列から順に並んでいます。ホストとメトリクスを = で指定すれば、索引の上で1か所の固まりに飛べ、その中は時刻順に並んでいるので、範囲をそのまま続けて読めます。時刻を先頭にすると、全ホスト・全メトリクスが混ざった範囲を読んでから振り分けることになり、読む量が増えます。

先頭の列の条件が欠けると、索引は使えない。

EXPLAIN SELECT * FROM metrics
WHERE metric_name = 'cpu' AND measured_at >= '2026-10-03 00:00+09';

Seq Scan on metrics
  Filter: ((measured_at >= ...) AND (metric_name = 'cpu'::text))

先頭の host_id が無いので、全行を端から読み(Seq Scan)、1行ずつ Filter で判定します。条件が3列そろっているクエリでは、Bitmap Index Scan の Index Cond に3列が並び、索引で絞り込めます。Index Cond は索引で絞る条件、Filter は読んだ行に1行ずつ当てる条件です。

一意制約も内部は索引で、列順が効く。 uq_metrics は (host_id, collector_id, metric_name, measured_at) の順なので、collector_id が間に挟まっています。collector_id を指定しないクエリでは、3番目以降の metric_name と measured_at を索引で絞れません。データの意味(このエージェントはこのメトリクスだけを測る)をDBは知らないからです。メトリクスで引く参照系には、collector_id を飛ばした別の索引を用意します。

列に関数を掛けた条件は Index Cond になれない。 5題目の (measured_at AT TIME ZONE ...)::time のような条件は、索引の並び順に沿っていないので、Filter に回ります。先に索引で絞れる条件(直前30日の範囲)で行数を減らしてから、残った行にだけ関数の条件を当てる形にします。

使われない索引は消す。 索引は、行を書き込むたびにすべて更新されます。使われない索引は、書き込みを遅くし、場所を取るだけです。そのクエリが実装に存在しないこと、残してもSeq Scanのほうが速いことを確かめたうえで、削除を判断します。

おわりに

時系列データを扱うSQLは、次のようにまとめられます。

• ウィンドウ関数(ROW_NUMBER・LAG)は、行を減らさずに列を足す。WHERE には書けないので、サブクエリかCTEで包む
• 「無い行」は、期待する一覧を作って LEFT JOIN で探す。右の表の条件は ON に書く
• 日付で区切るときは、AT TIME ZONE を date_trunc の内側に置き、期間は半開区間にする
• 平均には件数を添え、基準時刻は引数で渡す
• 冪等さは一意制約に任せ、ON CONFLICT で重複を静かに処理する。条件付きの DO UPDATE で、正しい値を壊さない
• 複合インデックスは、等値の列を先に、範囲の列を最後に置く。先頭の列を欠くと使えない