概要

p126 29 最頻値を求める　解法メモその２

Having句と極値関数を組み合わせて最頻値を求めています。
ALL述語使うよりもイメージしやすいかも

要件

支払い伝票に対して、金額の最頻値を求めたい。
※最頻値とは、母集団の中で最も多かった値

SQL

DDL

drop table if EXISTS Payroll;
create table Payroll(
    check_nbr Integer NOT NULL Primary Key,
    check_amt DECIMAL(8,2) NOT NULL
);

INSERT INTO Payroll VALUES(1, 100);
INSERT INTO Payroll VALUES(2, 100);
INSERT INTO Payroll VALUES(3, 200);
INSERT INTO Payroll VALUES(4, 150);
INSERT INTO Payroll VALUES(5, 300);
INSERT INTO Payroll VALUES(6, 150);
INSERT INTO Payroll VALUES(7, 300);
INSERT INTO Payroll VALUES(8, 300);
INSERT INTO Payroll VALUES(9, 100);

答えのSQL

Having句のサブクエリの中にサブクエリがある…
そこが理解するのに時間かかりましたね

各々分解してレコードを確認すれば済む話ですね。

-- p126 29 最頻値を求める　その２
SELECT check_amt,COUNT(*) AS check_cnt
FROM Payroll
GROUP BY check_amt
Having COUNT(*) = (SELECT MAX(check_cnt)
                    FROM (SELECT COUNT(*) AS check_cnt
                        FROM Payroll
                        GROUP BY check_amt) AS P1);

-- サブクエリ１ 
SELECT check_amt,COUNT(*) AS check_cnt
FROM Payroll
GROUP BY check_amt;


-- サブクエリ２ 
SELECT MAX(check_cnt)
FROM (SELECT COUNT(*) AS check_cnt
        FROM Payroll
        GROUP BY check_amt
        ) AS P1;

参考

SQLパズル　p126 29 最頻値を求める　その２

SQLパズル　サポートページ
https://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.html#LocalLink-p29

達人に学ぶSQL徹底指南書第２版 p113 Havingでサブクエリ　最頻値を求める　極値関数の利用

github