【PostgreSQL】顧客をランク分けするデシル分析をおこなうNTILEウィンドウ関数

今回は、顧客をランク分けすることで優良顧客を探すデシル分析を、SQLのNTILEウィンドウ関数を使っておこなう方法を紹介する。

デシル分析とは

購買金額順に顧客を人数ベースで10等分する分析手法をデシル分析という。

マーケティング用語でパレートの法則と呼ばれるものがあり、一般的に全体の2割の顧客が総売上の8割を占めることが多い。

デシル分析をすることにより顧客をランク分けし、優良顧客を探すことができるのだ。

NTILEウィンドウ関数を使ったデシル分析

PostgreSQLではNTILEウィンドウ関数を使って、デシル分析を簡単におこなうことができる。

早速クエリを見ていこう。

SELECT
	order_year
	, customer_id
	, order_amount
	, NTILE(10) OVER (
		partition by order_year
		order by order_amount desc
	) as decile
FROM
	yearly_orders
;

yearly_ordersテーブルには、年ごと・顧客ごとの購買金額が記録されているものとする。

まず、6行目で年ごとにグループ化する。
次に7行目で、グループ化したものを購買金額の高い順にソートする。

最後にNTILEウィンドウ関数の引数に指定された数（今回は10）で全体を分割し、各行に1〜10の値を与える。

この結果をもとに、各ランクごとの購買金額の平均値や中央値を取ったりして分析を進めていく。