SQLアタマアカデミー
第1回 連番の特性を利用してデータ操作をもっと自由に SQLで連番を扱う (3)メジアンを求める
メジアンを求める ~世界の中心を目指せ~
テーブルの行に連番を振れるようになれば,
例題として,
生徒の体重を表すテーブルがあるとします。メジアンの計算はデータ数が奇数と偶数の場合で分かれるので,
表5 Weights
student_ | Weight |
---|---|
A100 | 50 |
A101 | 55 |
A124 | 55 |
B343 | 60 |
B346 | 72 |
C563 | 72 |
C345 | 72 |
表6 Weights
student_ | Weight |
---|---|
A100 | 50 |
A101 | 55 |
A124 | 55 |
B343 | 60 |
B346 | 72 |
C563 | 72 |
C345 | 72 |
C478 | 90 |
集合指向的な解法
昔から知られている集合指向的な解法では,
リスト9 メジアンを求める
SELECT AVG(weight)
FROM (SELECT W1.weight
FROM Weights W1, Weights W2
GROUP BY W1.weight
--S1(下位集合)の条件
HAVING SUM(CASE WHEN W2.weight >= W1.weight THEN 1 ELSE 0 END)
>= COUNT(*) / 2
--S2(上位集合)の条件
AND SUM(CASE WHEN W2.weight <= W1.weight THEN 1 ELSE 0 END)
>= COUNT(*) / 2 ) TMP;
手続き型の解法
一方,
リスト10 メジアンを求める
SELECT AVG(weight) AS median
FROM (SELECT weight,
ROW_NUMBER() OVER (ORDER BY weight ASC, student_id ASC) AS hi,
ROW_NUMBER() OVER (ORDER BY weight DESC, student_id DESC) AS lo
FROM Weights) TMP
WHERE hi IN (lo, lo +1 , lo -1);
奇数の場合は,
この解法において,
- 注3)
- 図像的に言えば,
2人の歩く速度を常に同一かつ一定に保つということを意味します。
演習問題
集合指向と手続き型,
- 問題1
- リスト10の手続き的なクエリを,
実装非依存で動くようROW_ NUMBERを使わずに書き換えてください。 - 問題2
- リスト10の手続き的なクエリでは,
ソートキーに体重(weight) 列のほかに主キーの学生ID(student_ id)を含んでいます。このキーを除外すると, このクエリは正しく動作しません。一体なぜでしょう? - 問題3
- 問題1で使った,
クラスごとに分割されたテーブル (Weights2) から, クラス単位にメジアンを求めるコードを考えます。クラス1は55kg, クラス2は72. 5kg。これらを一度に求められるよう, リスト9と10の集合指向のクエリと手続き型のクエリをともに拡張してください。
バックナンバー
SQLアタマアカデミー
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (5)集合指向と手続き型
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (4)OLAP関数と集約関数を組み合わせる
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (3)OLAP専用関数
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (2)OLAP関数の基本構文
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (1)OLAP関数とは何か
- 第10回 結合大全 (5)非等値結合
- 第10回 結合大全 (4)自己結合
- 第10回 結合大全 (3)外部結合
- 第10回 結合大全 (2)内部結合
- 第10回 結合大全 (1)クロス結合