【SQL】postgreSQLでFROOR関数を用いたヒストグラムを作成するための集計方法

postgreSQLでFROOR関数を用いたヒストグラムを作成するための集計方法を紹介します。

以下のようなテーブルを考えていきます。

member_id character varying(2)	total_price integer
1	1500
2	3200
3	1820
4	2680
5	1020
6	3800
7	4100
8	1660
9	4500
10	3260
11	2010
12	1960

member_idは会員番号、total_priceはある期間における購入金額の合計と考えてください。

階級幅は1000円とし、度数を集計して以下のようなヒストグラム作成を目指します。

ヒストグラム

FROOR関数

FROOR(数値)とすると引数の数値データの小数点以下を切り捨てます。

total_priceの値を階級幅(1000)で割り、小数点を切り捨て、階級幅を再びかけることで千の位のみを求めることができます。

たとえばtotal_priceが1960の場合だと
1000で割ると1.96になり
小数点以下を切り捨て再び階級幅1000をかけ集計する。 このようにして階級ごとの度数を集計していく。

度数分布表作成のためのSQL

WITH sub1 AS
(
SELECT 
    fre_table.member_id
    , FLOOR(total_price / 1000) * 1000 AS total_price_by1000
FROM 
    fre_table AS fre_table
)
SELECT 
    total_price_by1000
    , COUNT(*) AS cnt
FROM 
    sub1
GROUP BY
    total_price_by1000
;

上クエリの結果

total_price by1000	cnt
1000	5
2000	2
3000	3
4000	2

以上のように簡単に度数分布表をつくることができます。