SQLでヒストグラムを作成する

SQLでヒストグラムを作成する仕様があり、パズルのように頭を悩ませたので、忘れないように書き残しておきます。

仕様

こういう階級テーブル(bin)が存在して...

max_score
10
20
:
90
100

こういう結果テーブル(record)があるときに...

id	score
1	45
2	12
3	98
4	68
:	:

こういうヒストグラムを作成したい。frequenceは区間に含まれるrecordの件数。

bin	frequency
10	4
20	13
:
90	4
100	2

テーブル定義とデータ挿入スクリプト

階級テーブル(bin)

drop table if exists bin;
create table bin (max_score integer);
insert into bin(max_score)
select (c1 + 1) * 10
from
(select 0 as c1 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d1

結果テーブル(record)

drop table if exists record;
create table record(id integer, score integer);
insert into record (id, score)
select c10 * 10 + c1 + 1, FLOOR(1 + RAND() * 10) * FLOOR(1 + RAND() * 10)
from
(select 0 as c10 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d10,
(select 0 as c1 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d1

答え

-- 4. recordが1件もない区分が0として表示されるよう、区分マスタを外部結合する
select bin.max_score as bin, ifnull(z.frequency, 0) frequency from (
    -- 3. bin.max_scoreでグルーピングして、ヒストグラムを作成する
    select y.max_score as bin, count(*) as frequency from (
        -- 2. 1. の結果からbin.max_scoreに含まれるrecordレコードを抽出する
        select x.id, x.score, min(x.max_score) as max_score from (
            -- 1. 直積でbin.max_score以上のrecordレコードを抽出する
            select bin.max_score as max_score, record.id as id, record.score as score
            from bin, record
            where record.score <= bin.max_score
        ) as x
        group by x.id, x.score
    ) as y
    group by y.max_score
) z
right outer join bin on bin.max_score = z.bin
order by bin.max_score

結果

+------+-----------+
| bin  | frequency |
+------+-----------+
|   10 |        25 |
|   20 |        18 |
|   30 |        15 |
|   40 |        14 |
|   50 |        10 |
|   60 |         9 |
|   70 |         2 |
|   80 |         2 |
|   90 |         2 |
|  100 |         3 |
+------+-----------+
10 rows in set (0.00 sec)

解説

SQLにつけたコメントで解説していますが、要望があれば詳しく書きます...