問題について
- 最頻値とは、テーブルのある列において最も個数の多い値のこと。
- 給料名簿テーブルの中から、給与額の最頻値をレコード数を求める。
chiakiさんの答え
※chiakiさんはゼミ準備で大学に来れなかったので、以下のSQLは検証してません。次の見出しで検証を行います。
SQL文
SELECT P1.check_amt, count(*)
FROM Payroll P1
WHERE P1.check_amt =
( SELECT P2.check_amt
FROM Payroll P2
WHERE count(*)>= ALL ( SELECT count(*)
FROM Payroll P3
GROUP BY P3.check_amt ))
考え方
- P3をcheck_amtでグループ化して、check_amtの値が等しいもので、それぞれレコード数を数える。
- 1.で求めた全ての個数よりレコード数の多いcheck_amtを求める。(頻度の最も高いcheck_amtが求まる)
- check_amtが、P2.check_amt(最頻値)と等しいことを条件にして、最頻値check_amtとレコード数count(*)を出力。
検証
- まず集約演算をWHERE節に書けないので、HAVING節に書きましょう。
- 外側の問合せ文のWHERE節でP1.check_amt = (副問合せの結果)となっていますが、
単一の値=集合値はできないので、=をIN句に直しましょう。
- 外側の問合せ文のcount(*)はcheck_amt毎に計算するのでGROUP BY check_amtをつけましょう。
というわけで修正したSQL文で実行
mysql> SELECT P1.check_amt,count(*)
FROM Payroll P1
WHERE P1.check_amt IN (
SELECT P2.check_amt
FROM Payroll P2
GROUP BY check_amt
HAVING count(*)>=ALL(SELECT count(*) FROM Payroll P3
GROUP BY P3.check_amt)) GROUP BY check_amt;
+-----------+----------+
| check_amt | count(*) |
+-----------+----------+
| 100.00 | 3 |
| 300.00 | 3 |
+-----------+----------+
2 rows in set (0.00 sec)
- うん。できました。しかし何か冗長な気がする…
- WHERE節内の副問合せはわざわざ用意しなくてもよさそう。
SELECT P2.check_amt, count(*)
FROM Payroll P2
GROUP BY check_amt
HAVING count(*)>=ALL(SELECT count(*) FROM Payroll P3
GROUP BY P3.check_amt;
これでOKでしょう。これで書籍の解3と同じになりました。
kunさんの答え
2008年04月08日12時10分17秒.pdf
検証
そのまま実行するとエラーになりました。
ERROR 1248 (42000): Every derived table must have its own alias
なので、いちばん内側の副問合せにP1というリレーション名をつけたら動いた!
SELECT check_amt as mode, count(*) as rec_nbr
FROM Payroll
GROUP BY check_amt HAVING count(*)=
(SELECT max(count1)
FROM (SELECT count(*) as count1
FROM Payroll
GROUP BY check_amt) P1);
結果
結果
mode | rec_nbr
--------+---------
100.00 | 3
300.00 | 3
(2 行)
☆おさらい☆
★SQL89では、サブクエリが返す値は一つでなければならない。気になる用語の一つは「直交性??」。SQL-89には、SQL-92のような直交性がない。
★エラー文の
ERROR 1248 (42000): Every derived table must have its own alias
これは、from句に入れるサブクエリをリレーション名を付けると直ります!
★解3で使われるOLAPの「over」がちょっと理解できない。。。
最終更新:2008年04月16日 14:11