体重ヒストグラムのSQL文

Question

以下のテーブルを基に体重ヒストグラムを作成したいです

tbl_student
組 名前 体重
1 a 38
1 b 52
1 c 56
1 d 62
2 e 45
2 f 65
2 g 78

このテーブルを集計し、以下の形にしたいです
tbl_weight_histogram
組 体重レンジ 人数
1 30 1
1 40 0
1 50 2
1 60 1
2 30 0
2 40 1
2 50 0
2 60 2

・体重レンジは30台、40台、50台、60台以上とする
・人数0人の場合も0件としてカウントする

特に2つ目の条件で困っております
外部テーブルを用意するのは構いません
どなたかご助言よろしくお願いします。

Comments

[SVC34]

DBMSは何を使用していますか

Answer 1

図でイメージするOracleのSQL全集 第1回 さまざまな結合
https://blogs.oracle.com/otnjp/oracle-sql-image-001
の「Partitioned Outer Join」を見ながら
SQLFiddleのOracle11gR2環境で作ってみました :-)

with RangeTable(体重レンジ,下限,上限) as(
select 30, 0, 39 from dual union
select 40,40, 49 from dual union
select 50,50, 59 from dual union
select 60,60,999 from dual),
tbl_student(組,名前,体重) as(
select 1,'a',38 from dual union
select 1,'b',52 from dual union
select 1,'c',56 from dual union
select 1,'d',62 from dual union
select 2,'e',45 from dual union
select 2,'f',65 from dual union
select 2,'g',78 from dual)
select b.組,a.体重レンジ,count(b.体重) as 人数
  from RangeTable a
  Left Join tbl_student b
partition by(b.組)
   on b.体重 between a.下限 and a.上限
group by b.組,a.体重レンジ
order by b.組,a.体重レンジ;

| 組 | 体重レンジ | 人数 |
|----|------------|------|
| 1  |         30 |    1 |
| 1  |         40 |    0 |
| 1  |         50 |    2 |
| 1  |         60 |    1 |
| 2  |         30 |    0 |
| 2  |         40 |    1 |
| 2  |         50 |    0 |
| 2  |         60 |    2 |

Answer 2

図でイメージするOracleのSQL全集 第8回 PivotとUnPivot
https://blogs.oracle.com/otnjp/oracle-sql-image-008
を見ながら、SQLFiddleのOracle11gR2環境で作ってみました :-)

with tbl_student(組,名前,体重) as(
select 1,'a',38 from dual union
select 1,'b',52 from dual union
select 1,'c',56 from dual union
select 1,'d',62 from dual union
select 2,'e',45 from dual union
select 2,'f',65 from dual union
select 2,'g',78 from dual),
tmp as(
select 組,
count(case when 体重 between  0 and  39 then 1 end) as cnt1,
count(case when 体重 between 40 and  49 then 1 end) as cnt2,
count(case when 体重 between 50 and  59 then 1 end) as cnt3,
count(case when 体重 between 60 and 999 then 1 end) as cnt4
  from tbl_student
group by 組)
select *
  from tmp
UnPivot(人数 for 体重レンジ
        in(cnt1 as 30,
           cnt2 as 40,
           cnt3 as 50,
           cnt4 as 60))
order by 組,体重レンジ;

| 組 | 体重レンジ | 人数 |
|----|------------|------|
| 1  |         30 |    1 |
| 1  |         40 |    0 |
| 1  |         50 |    2 |
| 1  |         60 |    1 |
| 2  |         30 |    0 |
| 2  |         40 |    1 |
| 2  |         50 |    0 |
| 2  |         60 |    2 |

Answer 3

この手の処理はレンジをテーブルに持ったほうがよいです
とくに「2 g 78 」のように70超を60に押し込めようとしているのでしたらなおさらです
一応学生にとって59.5kgと60.0kgは大きな違いなのでweightはdoubleにしておきました

• 元データ

create table weight(weight_range int,weight_min double,weight_max double);
insert into weight values
(30,0,39.99), /*30を下回ってもレンジは30*/
(40,40,49.99),
(50,50,59.99),
(60,60,999.99); /*70を超えてもレンジは60*/

create table student(class int,name varchar(20),weight double);
insert into student values
(1,'a',38),
(1,'b',52),
(1,'c',56),
(1,'d',62),
(2,'e',45),
(2,'f',65),
(2,'g',78);

ホントはクラスもテーブルを使うほうがいいのですが、今回はdistinctで処理します

select t1.class,t1.weight_range,coalesce(t4.count,0) as count
from (select class,weight_range from weight
inner join (select distinct class from student) as sub on 1) as t1
left join (
select class,weight_range,count(*) as count
from weight as t2
inner join student as t3 on t3.weight between t2.weight_min and t2.weight_max
group by class,weight_range
) as t4 on t1.class=t4.class and t1.weight_range=t4.weight_range
order by class,weight_range

Comments

[SVC34]

> この手の処理はレンジをテーブルに持ったほうがよいです
> とくに「2 g 78 」のように70超を60に押し込めようとしているのでしたらなおさらです

なるほど、参考になります

[shiono]

回答ありがとうございます
レンジテーブルが汎用的でとても参考になりました
ベストアンサーにさせていただきます

Answer 4

以下は共通表式 (CTE) を用いたPostgreSQLでの例。一時的な表をSQL中に記述することが出来ます。

体重レンジのCTEとクラス一覧のCTEをクロス結合して集計パターンを網羅した中間テーブルを作り、そこにtbl_studentを外部結合して集計しています。

共通表式はMySQLでは8.0からでしか利用できませんが、他の主要なDBMSでは以前からサポートされています。

WITH cte_w (
  weight_range
) AS (
  SELECT 30 UNION ALL
  SELECT 40 UNION ALL
  SELECT 50 UNION ALL
  SELECT 60
),
cte_c (
  class
) AS (
  SELECT 1 UNION ALL
  SELECT 2
)
SELECT
  tmp.class
  ,tmp.weight_range
  ,COUNT(tbl_student.weight)
FROM
  (SELECT * FROM cte_c, cte_w) AS tmp
  LEFT OUTER JOIN tbl_student
    ON tmp.class = tbl_student.class
    AND (
      tmp.weight_range = TRUNC(tbl_student.weight, -1)
      OR (
        tmp.weight_range = 60
        AND tbl_student.weight >= 60
      )
    )
GROUP BY
  tmp.class
  ,tmp.weight_range
ORDER BY
  tmp.class
  ,tmp.weight_range
;

Comments

[shiono]

迅速な回答ありがとうございます
共通表式を初めて知り、大変勉強になりました！
参考にさせていただきます

Answer 5

まず、体重を10単位に丸めるには TRUNCATE関数 を使います。（テーブルを削除する TRUNCATE文 とは違うので注意）

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.6.2 数学関数

SELECT TRUNCATE(38, -1);
-> 30

つぎに、レコードが無い体重も0人と取得したい件は

SQLで集約した時系列データの値が0となる行を補完する - Qiita

などSQLで無理矢理実装する方法もありますが、存在しないデータを扱うのはDBのお仕事では無いので、できればアプリ側で作るのがよいと思います。

Comments

[shiono]

迅速な回答ありがとうございます
参考にさせていただきます