この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
分析について学習していてデシル分析に出会いました。とても有用に感じたので学習結果を記しておきます。
環境
Mac OSX 10.10.5 Redshift 1.0.1125
デシル分析とは
対象データを10等分（ラテン語でデシル）にして売上の割合などを分析する手法の様です。
準備
CREATE SCHEMA IF NOT EXISTS blog;
DROP TABLE IF EXISTS blog.pre_decil;
CREATE TABLE blog.pre_decil (
id integer
, user_id integer
, item_id integer
, sales_date date
, price integer
, quantity integer
);
INSERT INTO blog.pre_decil
VALUES
( 1,333, 3,'2016-12-01',2000,5)
,( 2,222, 2,'2016-10-01',1500,6)
,( 3,111, 7,'2016-06-01',5000,1)
,( 4,222, 5,'2016-03-01',3000,3)
,( 5,333, 6,'2015-11-01',4000,4)
,( 6,111, 4,'2015-07-01',2500,2)
,( 7,111, 1,'2015-02-01',1000,3)
,( 8,111, 9,'2014-10-01',6000,1)
,( 9,333, 3,'2014-09-01',2000,2)
,(10,111, 1,'2014-03-01',1000,2)
,(11,111, 1,'2013-10-01',1000,2)
,(12,111, 8,'2013-03-01',5500,1)
,(13,333, 1,'2012-12-01',1000,3)
,(14,222, 4,'2012-10-01',2500,1)
,(15,333,10,'2012-09-01',7000,2)
,(16,111, 2,'2012-06-01',1500,2)
,(17,333, 6,'2011-11-01',4000,5)
,(18,333, 3,'2011-09-01',2000,6)
,(19,111, 4,'2011-07-01',2500,7)
,(20,222, 5,'2011-03-01',3000,1)
,(21,111, 7,'2011-02-01',5000,2)
,(22,111, 9,'2011-02-01',6000,1)
,(23,111, 3,'2010-10-01',2000,3)
,(24,111, 8,'2010-07-01',5500,2)
,(25,111, 5,'2010-03-01',3000,5);testdb=# select * from blog.pre_decil order by id;
id | user_id | item_id | sales_date | price | quantity
----+---------+---------+------------+-------+----------
1 | 333 | 3 | 2016-12-01 | 2000 | 5
2 | 222 | 2 | 2016-10-01 | 1500 | 6
3 | 111 | 7 | 2016-06-01 | 5000 | 1
4 | 222 | 5 | 2016-03-01 | 3000 | 3
5 | 333 | 6 | 2015-11-01 | 4000 | 4
6 | 111 | 4 | 2015-07-01 | 2500 | 2
7 | 111 | 1 | 2015-02-01 | 1000 | 3
8 | 111 | 9 | 2014-10-01 | 6000 | 1
9 | 333 | 3 | 2014-09-01 | 2000 | 2
10 | 111 | 1 | 2014-03-01 | 1000 | 2
11 | 111 | 1 | 2013-10-01 | 1000 | 2
12 | 111 | 8 | 2013-03-01 | 5500 | 1
13 | 333 | 1 | 2012-12-01 | 1000 | 3
14 | 222 | 4 | 2012-10-01 | 2500 | 1
15 | 333 | 10 | 2012-09-01 | 7000 | 2
16 | 111 | 2 | 2012-06-01 | 1500 | 2
17 | 333 | 6 | 2011-11-01 | 4000 | 5
18 | 333 | 3 | 2011-09-01 | 2000 | 6
19 | 111 | 4 | 2011-07-01 | 2500 | 7
20 | 222 | 5 | 2011-03-01 | 3000 | 1
21 | 111 | 7 | 2011-02-01 | 5000 | 2
22 | 111 | 9 | 2011-02-01 | 6000 | 1
23 | 111 | 3 | 2010-10-01 | 2000 | 3
24 | 111 | 8 | 2010-07-01 | 5500 | 2
25 | 111 | 5 | 2010-03-01 | 3000 | 5
(25 rows)カラムについて。 id = レコードのid user_id = ユーザーid item_id = 商品ID sales_date = 販売日 price = 単価 quantity = 売上数
構文（ウィンドウ関数：NTILE）
Amazon Redshift | NTILE ウィンドウ関数
NTILE (expr) OVER ([ PARTITION BY expression_list ]
[ ORDER BY order_list ])expr に何等分するかを指定。 PARTITION BY でグループを指定。 ORDER BY で対象カラムの昇順・降順を指定。
使い方
目的
単価と売上数からを売上金額の高い順に10分割する。
SQL
SELECT
user_id
, item_id
, price
, quantity
, price * quantity AS amount
, NTILE(10) OVER(PARTITION BY user_id ORDER BY (price * quantity) DESC) AS decil
FROM
blog.pre_decil
ORDER BY
user_id;実行結果
.
user_id | item_id | price | quantity | amount | decil
---------+---------+-------+----------+--------+-------
111 | 4 | 2500 | 7 | 17500 | 1
111 | 5 | 3000 | 5 | 15000 | 1
111 | 8 | 5500 | 2 | 11000 | 2
111 | 7 | 5000 | 2 | 10000 | 2
111 | 9 | 6000 | 1 | 6000 | 3
111 | 9 | 6000 | 1 | 6000 | 3
111 | 3 | 2000 | 3 | 6000 | 4
111 | 8 | 5500 | 1 | 5500 | 4
111 | 4 | 2500 | 2 | 5000 | 5
111 | 7 | 5000 | 1 | 5000 | 6
111 | 2 | 1500 | 2 | 3000 | 7
111 | 1 | 1000 | 3 | 3000 | 8
111 | 1 | 1000 | 2 | 2000 | 9
111 | 1 | 1000 | 2 | 2000 | 10
222 | 2 | 1500 | 6 | 9000 | 1
222 | 5 | 3000 | 3 | 9000 | 2
222 | 5 | 3000 | 1 | 3000 | 3
222 | 4 | 2500 | 1 | 2500 | 4
333 | 6 | 4000 | 5 | 20000 | 1
333 | 6 | 4000 | 4 | 16000 | 2
333 | 10 | 7000 | 2 | 14000 | 3
333 | 3 | 2000 | 6 | 12000 | 4
333 | 3 | 2000 | 5 | 10000 | 5
333 | 3 | 2000 | 2 | 4000 | 6
333 | 1 | 1000 | 3 | 3000 | 7
(25 rows)ORDER BYにuser_idを指定しただけですが、それ以下はウィンドウ関数のORDER BYで指定した順に並び替えられて表示されてます。
デシル分析を行う
目的
商品別の売上金額の比率を作成してデシル分析する。
SQL
-- 5,売上金額（amount）と比率（percentage）の累積（amount_cum, percent_cum）を作成。
-- 全カラム順を整える。
SELECT
item_id
, price
, amount
, SUM(amount) OVER(ORDER BY decil
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS amount_cum
, amount_total
, percentage
, SUM(percentage) OVER(ORDER BY decil
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS percent_cum
, decil
FROM
(
-- 4,売上金額の比率でデシル分析。
SELECT
*
, NTILE(10) OVER(ORDER BY percentage DESC) AS decil
FROM
(
-- 3,売上金額（amount）と総売上金額（amount_total）から売上金額の比率を作成。
SELECT
*
, CAST((CAST(amount AS FLOAT) / CAST(amount_total AS FLOAT) * 100)
AS DECIMAL(5,2)
) AS percentage
FROM
(
-- 2,単価の数から販売数（quantity）、商品別の売上金額（amount）、総売上金額（amount_total）を作成。
SELECT
item_id
, price
, quantity
, price * quantity AS amount
, SUM(price * quantity)
OVER(ORDER BY (price * quantity)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS amount_total
FROM
(
-- 1,商品ごとに単価と売上数を集計。
SELECT
item_id
, SUM(price) AS price
, SUM(quantity) AS quantity
FROM blog.pre_decil
GROUP BY item_id
)
GROUP BY item_id, price, quantity
)
)
);1〜5までを順に処理しています。 何をしているか詳細を知りたければ、段階ごとに実行して確認してみてください。
実行結果
.
item_id | price | amount | amount_cum | amount_total | percentage | percent_cum | decil
---------+-------+--------+------------+--------------+------------+-------------+-------
3 | 8000 | 128000 | 128000 | 521000 | 24.57 | 24.57 | 1
5 | 9000 | 81000 | 209000 | 521000 | 15.55 | 40.12 | 2
4 | 7500 | 75000 | 284000 | 521000 | 14.40 | 54.52 | 3
6 | 8000 | 72000 | 356000 | 521000 | 13.82 | 68.34 | 4
1 | 4000 | 40000 | 396000 | 521000 | 7.68 | 76.02 | 5
8 | 11000 | 33000 | 429000 | 521000 | 6.33 | 82.35 | 6
7 | 10000 | 30000 | 459000 | 521000 | 5.76 | 88.11 | 7
9 | 12000 | 24000 | 483000 | 521000 | 4.61 | 92.72 | 8
2 | 3000 | 24000 | 507000 | 521000 | 4.61 | 97.33 | 9
10 | 7000 | 14000 | 521000 | 521000 | 2.69 | 100.02 | 10
(10 rows)上位３項目で50%を超える売上となっている事が分かりました。
さいごに
SQLの階層が深くなりがちですが、分析には必須ですね。 用意したテーブルを使用して日付別など、他の条件でも試してみると良いと思います。
3
