[Redshift] ウィンドウ関数：AVG・MIN・MAXを使用して平均値、最小値、最大値を取得する

はじめに

平均値などは関数で簡単に出せますが、求める表の形次第では作りが難しくなります。ウィンドウ関数を使用すれば簡単にできることも有るので紹介します。

環境

MacOSX 10.10.5 Yosemite
Redshift 1.0.1125

テーブルの準備

販売情報：fruit_sales_data

作成するカラムの意味は下記です。
id：販売順
group_id：商品グループ
name：商品名
price：価格
quantity：販売数

CREATE SCHEMA IF NOT EXISTS blog;

CREATE TABLE blog.fruit_sales_data (
	id INTEGER,
	group_id INTEGER,
	name VARCHAR(8),
	price INTEGER,
	quantity INTEGER
);

INSERT INTO blog.fruit_sales_data
VALUES
 (1, 1, 'apple', 300, 5)
 ,(2, 1, 'apple', 300, 10)
 ,(3, 2, 'orange', 200, 7)
 ,(4, 2, 'orange', 200, 15)
 ,(5, 3, 'banana', 100, 2)
 ,(6, 3, 'banana', 100, 9)
 ,(8, 3, 'banana', 100, 11)
 ,(7, 2, 'orange', 200, 10);

testdb=# select * from blog.fruit_sales_data order by id;
 id | group_id |  name  | price | quantity 
----+----------+--------+-------+----------
  1 |        1 | apple  |   300 |        5
  2 |        1 | apple  |   300 |       10
  3 |        2 | orange |   200 |        7
  4 |        2 | orange |   200 |       15
  5 |        3 | banana |   100 |        2
  6 |        3 | banana |   100 |        9
  7 |        2 | orange |   200 |       10
  8 |        3 | banana |   100 |       11
(8 rows)

ウィンドウ関数

AVG

平均を求めます。
Amazon Redshift | AVG ウィンドウ関数

AVG ([ ALL ] expression) OVER ([ PARTITION BY expr_list ]
                               [ ORDER BY order_list frame_clause ])

MIN/MAX

最小値/最大値を求めます。
どちらも使い方は同じです。
Amazon Redshift |MIN ウィンドウ関数
Amazon Redshift |Max ウィンドウ関数

MAX ([ ALL ] expression) OVER ([ PARTITION BY expr_list ]
                               [ ORDER BY order_list frame_clause ])

frame_clause に関してはこちらを参照してください。
Amazon Redshift |ウィンドウ関数の構文の概要

使い方１

AVGを例としますが、MIN/MAXも同じ使い方です。

SELECT 
  name
  , quantity
  , AVG(quantity) OVER(PARTITION BY name) AS quantity_avg
FROM blog.fruit_sales_data 
ORDER BY name;

--------+----------+--------------
  name  | quantity | quantity_avg 
--------+----------+--------------
 apple  |       10 |            7
 apple  |        5 |            7
 banana |        9 |            7
 banana |        2 |            7
 banana |       11 |            7
 orange |       15 |           10
 orange |       10 |           10
 orange |        7 |           10
(8 rows)

商品名ごとの売り上げの平均が表示されています。

使い方２

SELECT
  name
  , quantity
  , AVG(quantity) OVER(PARTITION BY name 
                       ORDER BY quantity ROWS 1 PRECEDING) AS "avg"
FROM blog.fruit_sales_data
ORDER BY name, quantity;

--------+----------+-----
  name  | quantity | avg 
--------+----------+-----
 apple  |        5 |   5
 apple  |       10 |   7
 banana |        2 |   2
 banana |        9 |   5
 banana |       11 |  10
 orange |        7 |   7
 orange |       10 |   8
 orange |       15 |  12
(8 rows)

今度は ORDER BY を付けました。ROWS〜 はセットです。
1 PRECEDING は、一つ前からを指定しています。
カラム「avg」には、項目ごとの一つ上との平均が表示されています。
ちなみに、ORDER BY を記述しないと、
「ORDER BY quantity ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING」
と同じ意味になり、簡単に言えば全てを範囲に含むという意味になります。

関数とウィンドウ関数の違い

SELECT
  name
  , AVG(quantity) 
FROM blog.fruit_sales_data 
GROUP BY name;

--------+-----
  name  | avg 
--------+-----
 apple  |   7
 orange |  10
 banana |   7
(3 rows)

商品名と平均を出そうとすると GROUP BY が必要になるので上記の様になり、ウィンドウ関数の様には簡単にできません。

応用

目的

商品ごとの売り上げ数の平均値、最小値、最大値、最小値と最大値の幅を出し、尚且つ平均値以上の場合を表示する。

SQL

SELECT * 
FROM 
(
  SELECT
    *
    , CASE WHEN quantity >= "avg" THEN 1
           ELSE 0 
           END avg_up
    , "max" - "min" AS quantity_diff
  FROM 
  (
    SELECT
      id
      , name
      , price
      , quantity
      , AVG(CAST(quantity AS DECIMAL(10,2)))
            OVER(PARTITION BY name) AS "avg"
      , MIN(quantity)
            OVER(PARTITION BY name) AS "min"
      , MAX(quantity)
            OVER(PARTITION BY name) AS "max"
    FROM 
      blog.fruit_sales_data
    ORDER BY
      name
  )
)
WHERE avg_up > 0;

----+--------+-------+----------+-------+-----+-----+--------+---------------
 id |  name  | price | quantity |  avg  | min | max | avg_up | quantity_diff 
----+--------+-------+----------+-------+-----+-----+--------+---------------
  2 | apple  |   300 |       10 |  7.50 |   5 |  10 |      1 |             5
  6 | banana |   100 |        9 |  7.33 |   2 |  11 |      1 |             9
  8 | banana |   100 |       11 |  7.33 |   2 |  11 |      1 |             9
  4 | orange |   200 |       15 | 10.66 |   7 |  15 |      1 |             8
(4 rows)

さいごに

いかがでしたでしょうか。使い分けには慣れが必要かもしれませんが、色々試してみて下さい。