LoginSignup
26
17

More than 5 years have passed since last update.

@gooddoog

【ウィンドウ関数】Group By で指定していないカラムも出したい時は【分析関数】

Posted at

ウィンドウ関数を使おう

SQL において、窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されている。分析関数やOLAP機能と呼ばれる場合もある。

wikipediaより

プロローグ

こちらの記事より
相関サブクエリ入門

所属部署の平均年齢もあわせて表示したい場合

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を使う方法を提案しました。

提案SQL
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)
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;
                                                          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)
Window関数+サブクエリ化して条件指定
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

26
17
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
26
17