Puzzle29 - SQLパズル演習

問題について

• 最頻値とは、テーブルのある列において最も個数の多い値のこと。
• 給料名簿テーブルの中から、給与額の最頻値をレコード数を求める。

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 ))

考え方

1. P3をcheck_amtでグループ化して、check_amtの値が等しいもので、それぞれレコード数を数える。
2. 1.で求めた全ての個数よりレコード数の多いcheck_amtを求める。（頻度の最も高いcheck_amtが求まる）
3. 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」がちょっと理解できない。。。