センサーデータ、アクセスログ、株価、IoTデータなど、時系列データはあらゆる業界で不可欠です。本章では、時系列データベース（TimescaleDBなど）の活用から、**可視化に適したSQL集計**、**異常検知の実践パターン**まで、データ分析の現場で役立つ知識とテクニックを解説します。
時系列DB（TimescaleDBなど）の拡張機能活用
TimescaleDB は、PostgreSQLをベースにした時系列DB拡張で、大量・高頻度データの取り扱いに最適化されています。
1. ハイパーテーブルの作成
-- 通常テーブルを作成 CREATE TABLE temperature_readings ( time TIMESTAMPTZ NOT NULL, sensor_id INT, value DOUBLE PRECISION ); -- 時系列データ用に変換（TimescaleDB） SELECT create_hypertable('temperature_readings', 'time'); これにより、内部的に自動でパーティショニング（チャンク化）され、読み書き性能が大幅に向上します。
2. Time-Bucket関数による簡易集計
-- 5分ごとの平均値を求める SELECT time_bucket('5 minutes', time) AS bucket, AVG(value) FROM temperature_readings GROUP BY bucket ORDER BY bucket; time_bucketはTimescaleDB独自の関数で、柔軟な時系列のグルーピングが可能です。
ヒートマップ集計 / 日別・週次推移の可視化データ準備
可視化ツールやBIに渡すためのデータは、SQLレベルで「見せやすい形」に加工しておくことが重要です。
1. 日別×時間帯のヒートマップ集計（アクセスログなど）
SELECT DATE(access_time) AS access_day, EXTRACT(HOUR FROM access_time) AS hour, COUNT(*) AS access_count FROM access_logs GROUP BY access_day, hour ORDER BY access_day, hour; BIツールに渡すことで、縦（日付）×横（時間）のヒートマップが作れます。
2. 週次推移（週ごとの集計）
SELECT DATE_TRUNC('week', created_at) AS week_start, COUNT(*) AS signups FROM users GROUP BY week_start ORDER BY week_start; 曜日ごとの分析をしたい場合は TO_CHAR(created_at, 'Day') を使ってもOKです。
3. 欠損の補完：generate_seriesで日付を補間
-- 日付の穴埋めをしたいとき SELECT d.day, COALESCE(SUM(amount), 0) AS total FROM generate_series('2025-09-01'::date, '2025-09-30'::date, '1 day') AS d(day) LEFT JOIN sales ON d.day = DATE(sales.sale_date) GROUP BY d.day ORDER BY d.day; これにより、0件の日も可視化対象として扱えるようになります。
異常検知用SQLパターン（突発値・欠損検知）
時系列データでは、突発的な異常値（スパイク）や値の欠損（ギャップ）を検出するニーズが高く、SQLでもある程度の検出が可能です。
1. スパイク検出（前日比が異常に高い）
SELECT sale_date, sales, sales - LAG(sales) OVER (ORDER BY sale_date) AS diff FROM daily_sales WHERE ABS(sales - LAG(sales) OVER (ORDER BY sale_date)) > 10000; LAG() 関数を使って、前回値との差分が閾値を超えたら異常として検出。
2. 欠損検出（一定間隔での抜け）
-- 1日ごとにデータがある前提で欠損日を検出 SELECT d.day FROM generate_series('2025-09-01'::date, '2025-09-30'::date, '1 day') AS d(day) LEFT JOIN temperature_readings t ON d.day = DATE(t.time) WHERE t.time IS NULL; データがない日付を生成してJOINし、NULLを返す日＝欠損日として検出します。
3. 閾値超えアラートの作成
SELECT * FROM sensor_data WHERE value > 80 AND time >= NOW() - INTERVAL '1 hour'; 監視用途のSQLアラートとして、過去1時間で閾値を超えた件数を定期チェック可能。
まとめ
時系列分析は、パフォーマンス・可視化・異常検知といった観点から、データ設計とSQLの工夫が不可欠です。
- TimescaleDBで高速な時系列処理が可能に
- ヒートマップや週次推移はSQLで見せ方を整形
- 異常検知はLAG, generate_series, 統計的な閾値で対応
これらのスキルを活かすことで、リアルタイムモニタリングやデータドリブン経営を支える高度な分析基盤を構築できます。
