カテゴリー: SQL

仕事上で一定期間ごとの集計をするSQLを作る必要があったのでご紹介。お役に立てれば。

たとえば 日時(time)、と値(value)という２フィールドを持つテーブルがあったとする。これを当該日付の値と、当該日付から過去25日の平均値を同時に出したい場合（過去２５日間の平均値と比べられる結果がほしい）、以下のようにすれば求められる。

SELECT
    A.time --日時
    A.value, -- 現在値
    AVG(B.value), -- 平均値
    STDDEV(B.value) -- 標準偏差値
FROM table_name A
INNER JOIN table_name B -- 同じテーブルをJOINする
ON
    --過去の日時のものを範囲を持たせてJOINの条件とする
    B.time BETWEEN (A.time - INTERVAL 25 DAY) AND A.time
;

まぁSQLを見てもらえればわかると思うけど簡単に解説すると、同じテーブルを結合していて、結合の際に結合条件であるtimeフィールドに幅を持たせて結合させている点がポイントだ。

これを使えば、わざわざスクリプトなどでループをまわしながら計算させなくとも、SQL一発で結果が得られる。

なお、このSQLはMySQL用なのでほかのDBMSでどうやるかは自分でしらべてくれろ。INTERVALのやり方だったりJOIN句の書き方だったりに違いがあるだけで基本的なアイディアは使えると思う。

月単位で集計、とか、年単位で集計、などはほかにいろいろ紹介しているページはあったものの、過去２５日間の平均値（や標準偏差など）と現在値の比較を一発のSQLで実行する方法が探しても無かったので書いてみた。