window関数で移動平均を求めてみたら幸せがおとずれた

はじめに

postgresのwindow関数を使って移動平均を求めてみた。サブクエリを使って移動平均を求めているソースコードを書き直してみたら、書きやすいしパフォーマンスは良いし、感動したのでメモ。

window関数とは？

window関数とは、GROUP BYのように行を区切って集計できる機能。GROUP BYとの違いは、GROUP BYが行をグループごとに一つにまとめてしまうのに対し、window関数はグループ内のそれぞれの行のデータを使った集計ができる。例えば、グループ内の最初の行の値を求めるfirst_valueのような関数が使える。加えてGROUP BYと同じように、avgのような集計関数も使える。

例えば、ある行の前３行から後ろ５行を選択したいときは、ROWS BETWEEN 3 PRECEDING AND 5 FOLLOWINGのように書ける。

以下のSQLでweightカラムの過去５項目の移動平均を求められる。

SELECT avg(t.weight) OVER w AS moving_avg
FROM my_table AS t
WINDOW w AS (ORDER BY t.created_at DESC
             ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING)
ORDER BY created_at DESC;

weightカラムの値が移動平均の2倍以上の行だけを抽出する

weightカラムの値が移動平均の2倍以上の行だけを抽出したい。元のサブクエリを使ったSQLが以下。1000行ほどのテーブルで30秒以上かかっていた。

SELECT *
FROM my_table t_a
WHERE weight / 2 >
      (SELECT avg(t_b.weight) FROM my_table t_b
       WHERE (SELECT count(*) FROM my_table t_c
              WHERE t_c.created_at between t_b.created_at and t_a.created_at)
       BETWEEN 1 and 5)
       ORDER BY created_at DESC;

window関数を使って書き直したSQLが以下。1000行ほどのテーブルなら一瞬で終わる。

SELECT *
FROM
  (SELECT *, avg(d.weight) OVER w AS moving_avg
   FROM my_table AS t
   WINDOW w AS (ORDER BY t.created_at DESC
                ROWS BETWEEN 1 FOLLOWING AND 5 FOLLOWING)
  ) AS t
WHERE t.weight / moving_avg > 2
ORDER BY created_at DESC

おわりに

ちょっとした工夫で大幅にパフォーマンスが改善するのは、幸せ。