ウィンドウ関数を使おう
SQL において、窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されている。分析関数やOLAP機能と呼ばれる場合もある。
プロローグ
こちらの記事より
相関サブクエリ入門
所属部署の平均年齢もあわせて表示したい場合
SELECTの列の指定で、WHERE句と同じ式を書けば実現できる。
(DRY原則に反しているので、もっとスマートなやり方があれば教えてください。)SELECT name, age, department, (SELECT AVG(age) as avg_age FROM Employees as e2 WHERE e1.department = e2.department) FROM Employees as e1 WHERE e1.age < (SELECT AVG(age) as avg_age FROM Employees as e2 WHERE e1.department = e2.department); name | age | department | avg_age -----------+-----+------------+--------------------- Sato | 23 | 営業 | 32.0000000000000000 Takahashi | 35 | 開発 | 39.3333333333333333 (2 rows)
私はこの記事のコメントでJOINを使う方法を提案しました。
SELECT
e1.name,
e1.age,
e1.department,
e2.avg_age
FROM
employees e1
LEFT JOIN
(SELECT department, AVG(age) avg_age
FROM Employees
GROUP BY department) e2
USING (department)
WHERE
e1.age < e2.avg_age;
name | age | department | avg_age
-----------+-----+------------+---------------------
Sato | 23 | 営業 | 32.0000000000000000
Takahashi | 35 | 開発 | 39.3333333333333333
(2 rows)
しかし、通常のレコードのカラムに集約関数も併記するだけなら、ウィンドウ関数を利用することでこれ以上に完結に書けます。
ウィンドウ関数を活用した場合
SELECT
name,
age,
department,
AVG(age) OVER ( PARTITION BY department) avg_age
FROM Employees
name | age | department | avg_age
-----------+-----+------------+---------------------
Sato | 23 | 営業 | 32.0000000000000000
Suzuki | 35 | 営業 | 32.0000000000000000
Saito | 38 | 営業 | 32.0000000000000000
Yamada | 42 | 開発 | 39.3333333333333333
Tanaka | 41 | 開発 | 39.3333333333333333
Takahashi | 35 | 開発 | 39.3333333333333333
ウィンドウ関数の結果に対してなにか条件付けしたい場合はサブクエリを使います。
(ウィンドウ関数にはHaving的な集約関数の結果を直接条件指定できるものはありません)
SELECT * FROM (
SELECT
name,
age,
department,
AVG(age) OVER ( PARTITION BY department) avg_age
FROM Employees
) as tmp
WHERE tmp.age < tmp.avg_age
name | age | department | avg_age
-----------+-----+------------+---------------------
Sato | 23 | 営業 | 32.0000000000000000
Takahashi | 35 | 開発 | 39.3333333333333333
(2 rows)
ウィンドウ関数とは?
<ウィンドウ関数> OVER (PARTITION BY <列名(分類用)> ORDER BY <列名(ソート用)>)
OVER句の中のPARTITION BY, ORDER BYは省略可能である。
ウィンドウ関数として使える関数
- 集約関数
- SUM
- AVG
- COUNT
- MAX
- MIN
- ウィンドウ専用関数
- RANK
- DENSE_RANK
- ROW_NUMBER
Order句を使って移動平均をとったり、ウィンドウ専用関数で色々できるが今回は省略
パフォーマンスについて(加筆予定)
実行結果が
name | age | department | avg_age
-----------+-----+------------+---------------------
Sato | 23 | 営業 | 32.0000000000000000
Takahashi | 35 | 開発 | 39.3333333333333333
となる各3クエリについて比較
SELECT name,
age,
department,
(SELECT AVG(age) as avg_age
FROM Employees as e2
WHERE e1.department = e2.department)
FROM Employees as e1
WHERE e1.age < (SELECT AVG(age) as avg_age
FROM Employees as e2
WHERE e1.department = e2.department);
-----------------------------------------------------------------------------
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on employees e1 (cost=0.00..18282.40 rows=240 width=112) (actual time=0.834..0.883 rows=2 loops=1)
Filter: ((age)::numeric < (SubPlan 2))
Rows Removed by Filter: 4
SubPlan 1
-> Aggregate (cost=19.01..19.02 rows=1 width=32) (actual time=0.034..0.034 rows=1 loops=2)
-> Seq Scan on employees e2 (cost=0.00..19.00 rows=4 width=4) (actual time=0.005..0.006 rows=3 loops=2)
Filter: ((e1.department)::text = (department)::text)
Rows Removed by Filter: 3
SubPlan 2
-> Aggregate (cost=19.01..19.02 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=6)
-> Seq Scan on employees e2_1 (cost=0.00..19.00 rows=4 width=4) (actual time=0.007..0.008 rows=3 loops=6)
Filter: ((e1.department)::text = (department)::text)
Rows Removed by Filter: 3
Planning time: 1.227 ms
Execution time: 4.502 ms
(15 rows)
SELECT
e1.name,
e1.age,
e1.department,
e2.avg_age
FROM
employees e1
LEFT JOIN
(SELECT department, AVG(age) avg_age
FROM Employees
GROUP BY department) e2
USING (department)
WHERE
e1.age < e2.avg_age;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=27.80..75.60 rows=240 width=112) (actual time=7.913..7.953 rows=2 loops=1)
Hash Cond: ((e1.department)::text = (e2.department)::text)
Join Filter: ((e1.age)::numeric < e2.avg_age)
Rows Removed by Join Filter: 4
-> Seq Scan on employees e1 (cost=0.00..17.20 rows=720 width=80) (actual time=2.488..2.494 rows=6 loops=1)
-> Hash (cost=25.30..25.30 rows=200 width=70) (actual time=4.409..4.409 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on e2 (cost=20.80..25.30 rows=200 width=70) (actual time=3.024..3.033 rows=2 loops=1)
-> HashAggregate (cost=20.80..23.30 rows=200 width=70) (actual time=3.024..3.032 rows=2 loops=1)
Group Key: employees.department
-> Seq Scan on employees (cost=0.00..17.20 rows=720 width=42) (actual time=0.008..0.015 rows=6 loops=1)
Planning time: 6.069 ms
Execution time: 8.804 ms
(13 rows)
SELECT * FROM (
SELECT
name,
age,
department,
AVG(age) OVER ( PARTITION BY department) avg_age
FROM Employees
) as tmp
WHERE tmp.age < tmp.avg_age
-------------------------------------------------------------------------------
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Subquery Scan on tmp (cost=51.37..74.77 rows=240 width=112) (actual time=6.839..6.850 rows=2 loops=1)
Filter: ((tmp.age)::numeric < tmp.avg_age)
Rows Removed by Filter: 4
-> WindowAgg (cost=51.37..63.97 rows=720 width=112) (actual time=6.829..6.839 rows=6 loops=1)
-> Sort (cost=51.37..53.17 rows=720 width=80) (actual time=6.777..6.779 rows=6 loops=1)
Sort Key: employees.department
Sort Method: quicksort Memory: 25kB
-> Seq Scan on employees (cost=0.00..17.20 rows=720 width=80) (actual time=0.026..0.028 rows=6 loops=1)
Planning time: 0.104 ms
Execution time: 9.871 ms
(10 rows)
ウィンドウ関数のほうが2倍近く遅い(涙)
ただこのテーブルは小規模でindexも無いため、本番に近い環境だとどうなるかはわからない(苦し紛れ)
ちなみに2回目以降の計測はキャッシュをつかっているのか、
全ての問い合わせで差がなく0.2ms程度だった
まだまだわからないことが多いので今後加筆予定
個人的に気に入っている点
極論を言えばウィンドウ関数で出来ることはJOINやサブクエリの巧妙な組み合わせで実現できる。
でもSELECT句にたったこの一行の追加で
<ウィンドウ関数> OVER (PARTITION BY <列名(分類用)> ORDER BY <列名(ソート用)>)
調査中のレコードが属するグループの集計情報が調査できたるのは素敵なことだと思う。
使うべき人
- ジャンルやセットなど、業務で分類に使われているカラムが大量に含まれるテーブルを扱う人
- GROUP BYで指定していないカラムが書けなくて怒っている人
- Ambiguous Groupsアンチパターンでやらかしたことのある人
- 全PG/SE
参考
https://www.postgresql.jp/document/8.4/html/tutorial-window.html
http://lets.postgresql.jp/documents/technical/window_functions/
実行計画について:http://www.slideshare.net/MikiShimogai/postgre-sql-explain