この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
平均値などは関数で簡単に出せますが、求める表の形次第では作りが難しくなります。ウィンドウ関数を使用すれば簡単にできることも有るので紹介します。
環境
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 |ウィンドウ関数の構文の概要
使い方1
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)
商品名ごとの売り上げの平均が表示されています。
使い方2
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)
さいごに
いかがでしたでしょうか。使い分けには慣れが必要かもしれませんが、色々試してみて下さい。