Window関数

Window関数

NTT オープンソースソフトウェアセンタ 板垣 貴裕

 

Window関数で使える集計関数と利用例を解説します。 Window関数は OLAP などで使われる複雑な集計クエリを効率よく処理するための構文です。 Window関数は PostgreSQL 8.4で初めて導入され、9.0でさらに拡張されました。

解説

Window関数はテーブルを区間ごとに集計する機能です。集約関数 (GROUP BY) に似ていますが、Window関数では複数の行がまとめられることはなく、行それぞれが返却されます。また、処理中の行以外の行の値を読み取ることも可能です。

Window関数は以下の構文を使います。

  • 関数(...) OVER (PARTITION BY ...) : 区間に分割
  • 関数(...) OVER (ORDER BY ...) : 区間ごとに並び替え

関数には以下の表に挙げたものと、通常の集約関数 (countやsumなど) が使用できます。lag() や lead() は自己結合 (セルフジョイン; Self-Join) の代わりに使用することで、処理を大幅に効率化できる場合もあります。

利用可能なWindow関数
関数 説明
row_number() 行番号
rank() ランキング (同率で番号を飛ばす)
dense_rank() ランキング (同率で番号を飛ばさない)
percent_rank() ランキング (%で表示) : (rank - 1) / (全行数 - 1)
cume_dist() percent_rank に類似 : (現在の行の位置) / (全行数)
ntile(N) ランキング (1..N に分割)
lag(value, offset, default) ソート状態での前の行の値
lead(value, offset, default) ソート状態での後の行の値
first_value(value) 最初の値
last_value(value) 最後の値
nth_value(value, N) N番目の値 (1から数える)
 

また、CREATE FUNCTION 文でユーザ定義のWindow関数を追加することもできますが、バージョン 8.4 ではC言語で関数を記述する必要があります。SQLやPL/pgSQLは使えないので、敷居は高いかもしれません。

例

典型的なWindow関数の使い方を挙げます。この他にも、これまで複雑なSQLが必要だったさまざまなケースを効率化できる可能性がありますので、SQLパズルだと思って試してみてください。。

連番付与

連番付与を行います。ソートした後、番号をふるのがポイントです。

SELECT row_number() OVER (), *
  FROM (SELECT * FROM tbl ORDER BY sortkey) AS t;

ただし、結果の最大行数を指定するには、row_number との値の比較ではなく、これまでどおり LIMIT 句を使ってください。row_number を使う方法は標準SQLには準拠しているのですが、LIMIT 句の処理で行われる数々の最適化が行われなくなってしまいます。性能面を考えると、現時点では LIMIT を使い続けたほうが良さそうです。

全体に対する割合で取得

上位10%を取得します。ただし、全体に対する割合を計算するには、結局は全体を取得する必要があることに注意してください。LIMIT 句での件数指定とは異なり、クエリの負荷を抑える効果はほとんどありません。

SELECT ... FROM
  (SELECT *, cume_dist() OVER (ORDER BY i) AS rank
   FROM tbl) AS t
WHERE rank <= 0.1;

ちなみに、「LIMIT 10%」や「LIMIT count(*) / 10」という記述はできません。

PARTITION BY と ORDER BY の組み合わせ

PARTITION BY と ORDER BY は同時に使用することもできます。以下の例では、地域ごとに身長の低い順に番号付けしています。

SELECT id, 地域, 身長,
       rank() OVER (PARTITION BY 地域 ORDER BY 身長)
  FROM tbl;

歯抜けIDの検索

列 id でソートした際に1つ前の行と比較して連番になっていない行を抜き出します。 これまでは NOT IN や NOT EXISTS などコストの高いSQLを使う必要がありましたが、前の行を取得する lag() 関数を使うと、ただ1回のテーブルスキャンで処理することができます。

SELECT id, prev_id
  FROM (SELECT id,
               lag(id, 1, 0) OVER (ORDER BY id) AS prev_id
          FROM tbl) AS t
 WHERE id <> prev_id + 1;

ちなみに、Window関数を使わない場合には以下のような記述が必要になります。自己結合を行うコストの高いクエリです。

SELECT id FROM tbl WHERE id NOT IN (SELECT id + 1 FROM tbl);
SELECT id FROM tbl WHERE NOT EXISTS
 (SELECT 1 FROM tbl AS tbl_prev WHERE tbl.id = tbl_prev.id + 1);

外部リンク

(2010年4月20日更新)