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.対象データの最大値、最小値、範囲(最大値 - 最小値)、階級数を求めます。
以下のクエリで最大値(+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でヒストグラムを作成することができます。 また、最大値や最小値を指定した任意の階級幅でヒストグラムを作成することも可能です。