この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
関数SUMはよく使いますが、ウィンドウ関数にもSUMが存在します。実際に使ってみて合計値と累積値を作成してみます。
環境
Mac OSX 10.10.5 Redshift 1.0.1125
テーブルの準備
商品の販売データ：fruit_sales_data
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)
,(7,2,'orange',200,10)
,(8,3,'banana',100,11);.
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)左から、レコードID、商品ID、商品名、価格、販売数となっています。
構文
SUM ( [ ALL ] expression ) OVER ([ PARTITION BY expr_list ]
[ ORDER BY order_list frame_clause ])| 句・引数 | 説明 |
|---|---|
| expression | 関数の対象となる列または式。 |
| ALL | 引数 ALL を指定すると、この関数は式から重複する値をすべて保持します。ALL がデフォルト。 |
| OVER | 集計関数に使用するウィンドウ句を指定します。OVER 句は、ウィンドウ集計関数を標準セット集計関数と区別します。 |
| PARTITION BY expr_list | 1 つ以上の式で SUM 関数のウィンドウを定義します。 |
| ORDER BY order_list | 各パーティション内の行をソートします。PARTITION BY が指定されていない場合、ORDER BY はテーブル全体を使用します。 |
| frame_clause | ORDER BY 句が集計関数に使用される場合、明示的なフレーム句が必要です。フレーム句は順序付けた結果内の行のセットを含めるか除外して、関数のウィンドウの行のセットを絞り込みます。フレーム句は ROWS キーワードおよび関連する指定子で構成されます。Amazon Redshift | ウィンドウ関数の構文の概要 |
使い方
使い方１：商品グループごとの合計値のカラムを追加
SQL
SELECT
name
, quantity
, SUM(quantity) OVER(PARTITION BY name)
FROM blog.fruit_sales_data
GROUP BY name, quantity
ORDER BY name;実行結果
.
name | quantity | sum
--------+----------+-----
apple | 5 | 15
apple | 10 | 15
banana | 2 | 22
banana | 11 | 22
banana | 9 | 22
orange | 10 | 32
orange | 7 | 32
orange | 15 | 32
(8 rows)使い方２：行ごとに販売額と総販売額のカラムを追加
SQL
SELECT
name
, price
, quantity
, price * quantity AS group_price
, SUM(price * quantity) OVER() AS total
FROM blog.fruit_sales_data
GROUP BY name, quantity, price
ORDER BY name, quantity;実行結果
.
name | price | quantity | group_price | total
--------+-------+----------+-------------+-------
apple | 300 | 5 | 1500 | 13100
apple | 300 | 10 | 3000 | 13100
banana | 100 | 2 | 200 | 13100
banana | 100 | 9 | 900 | 13100
banana | 100 | 11 | 1100 | 13100
orange | 200 | 7 | 1400 | 13100
orange | 200 | 10 | 2000 | 13100
orange | 200 | 15 | 3000 | 13100
(8 rows)使い方３：商品グループごとの累積値のカラムを追加
使い方１と似ていますが、ORDER BY句などのオプションの設定で実現しています。 「UNBOUNDED PRECEDING」は、前にある行を全て」という意味です。 他にも色々指定ができます。
SQL
SELECT
name
, quantity
, SUM(quantity) OVER(PARTITION BY name ORDER BY quantity ROWS UNBOUNDED PRECEDING)
FROM blog.fruit_sales_data
ORDER BY name;実行結果
.
name | quantity | sum
--------+----------+-----
apple | 5 | 5
apple | 10 | 15
banana | 2 | 2
banana | 9 | 11
banana | 11 | 22
orange | 7 | 7
orange | 10 | 17
orange | 15 | 32
(8 rows)さいごに
オプションの指定で柔軟に対応できそうなので、覚えておくと良さそうです。
3
