SQLでヒストグラムを作成してみよう

この記事の概要

---

この記事ではSQLを使ったヒストグラム作成の方法を説明しています。
使用しているDBはRedshiftです。
CSVデータを用意していますので、ご自身のDBに入れて試してみてください。

ヒストグラムを作成することで、データがどのように分散しているか一目でわかります。

ヒストグラム作成手順の概要

---

表.度数分布表

バケット数	下限	上限	度数
1	0	1000	34
2	1000	2000	134
3	2000	3000	46
4	3000	4000	34
5	4000	5000	75
....	....	....	....

上記のような表を作るために以下の3つの手順を踏みます。

1. 対象データの最大値、最小値、範囲（最大値 - 最小値）、階級数を求めます。
2. 対象データの階級判定を行います。
3. 各階級に入るデータを集計し、表にします。

1.対象データの最大値、最小値、範囲（最大値 - 最小値）、階級数を求めます。

---

以下のクエリで最大値（+1）、最小値、階級数を決めます。

WITH stats AS
  (SELECT MAX(price) + 1 AS max_price,
           MIN(price) AS min_price,
           MAX(price) + 1 - MIN(price) AS range_price,
           0 AS bucket_num
   FROM practice)
SELECT *
FROM stats;

上記のクエリ結果

max_price	min_price	range_price	bucket_num
5001	0	5001	0

2.対象データの階級判定を行います。

---

以下のクエリで正規化金額、階級範囲、階級の判定を行います。
正規化金額：対象金額 - 最小金額
階級範囲：範囲 / 階級数
階級の判定：FLOOR(正規化金額 / 階級範囲)
※ 最大値（+1）とすることで全てのレコードが階級上限未満に含まれます。

WITH stats AS
  (SELECT MAX(price) + 1 AS max_price,
           MIN(price) AS min_price,
           MAX(price) + 1 - MIN(price) AS range_price,
           10 AS bucket_num
   FROM practice)
SELECT price,
    min_price,
    price - min_price AS diff,
    1.0 * range_price / bucket_num AS bucket_range,
    FLOOR(1.0 * (price - min_price) / (1.0 * range_price / bucket_num)) + 1 AS bucket
    FROM practice, stats

上記のクエリ結果

price	min_price	diff	bucket_range	bucket
4000	0	4000	500.10	8
5000	0	5000	500.10	10
...	...	...	...	...

3.各階級に入るデータを集計し、表にします。

---

以下のクエリで階級毎に集計し、金額範囲、度数などを求めます。

WITH stats AS
  (SELECT MAX(price) + 1 AS max_price,
           MIN(price) AS min_price,
           MAX(price) + 1 - MIN(price) AS range_price,
           10 AS bucket_num
   FROM practice),
     price_bucket AS
  (SELECT price, min_price, price - min_price AS diff, 
          1.0*range_price / bucket_num AS bucket_range,
          floor(1.0*(price - min_price) / (1.0*range_price / bucket_num)) + 1 AS bucket
   FROM practice, stats)
SELECT bucket,
        min_price + bucket_range * (bucket-1) AS lower_limit,
        min_price + bucket_range * bucket AS upper_limit,
        count(price) AS num_purcase,
        sum(price) AS total_amount
FROM price_bucket
GROUP BY bucket,
         min_price,
         bucket_range
ORDER BY bucket

上記のクエリ結果

bucket	lower_limit	upper_limit	num_purcase	total_amount
1	0.00	500.10	7396	1735955
2	500.10	1000.20	8063	6449010
3	1000.20	1500.30	5965	7816609
4	1500.30	2000.40	5114	9261628
5	2000.40	2500.50	3719	8674364
6	2500.50	3000.60	3617	10222082
7	3000.60	3500.70	2582	8623447
8	3500.70	4000.80	2680	10281398
9	4000.80	4500.90	1910	8314304
10	4500.90	5001.00	2049	9940058

そして、上記の表をグラフ化するとヒストグラムを作成することができます。

ヒストグラム

まとめ

以上のように簡単にSQLでヒストグラムを作成することができます。 また、最大値や最小値を指定した任意の階級幅でヒストグラムを作成することも可能です。