質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.49%

体重ヒストグラムのSQL文

解決済

回答 5

投稿

  • 評価
  • クリップ 0
  • VIEW 2,747

score 7

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

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つ目の条件で困っております
外部テーブルを用意するのは構いません
どなたかご助言よろしくお願いします。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • SVC34

    2017/11/15 05:44

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

    キャンセル

回答 5

+16

図でイメージする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 |

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+13

図でイメージする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 |

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

checkベストアンサー

+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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/11/15 12:54

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

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

    キャンセル

  • 2017/11/16 13:23

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

    キャンセル

+1

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

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

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/11/16 13:14

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

    キャンセル

+1

以下は共通表式 (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
;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/11/16 13:20

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.49%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る