[解決済み] [GROUP BY句に含まれるか、集約関数で使用される必要があります。

質問事項

次のようなテーブルがあります。 呼び出し元 'makerar'

 cname  | wmname |          avg           
--------+-------------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     5.0000000000000000

そして、各cnameの最大avgを選択したい。

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

が、エラーになります。

ERROR:  column "makerar.wmname" must appear in the GROUP BY clause or be used in an   aggregate function 
LINE 1: SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname;

というわけで、こんな感じです。

SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname, wmname;

しかし、これでは意図した結果が得られず、以下のような不正な出力が表示されます。

 cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     5.0000000000000000

実際の結果は次のとおりです。

 cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

注：このテーブルは、以前の操作で作成されたVIEWです。

どのように解決するのですか？

はい、これはよくある集計の問題です。以前は SQL3 (1999年) の場合、選択されたフィールドは GROUP BY 節[*]を使用します。

この問題を回避するには、サブクエリで集約を計算し、それを自分自身と結合して、表示する必要のある追加の列を取得する必要があります。

SELECT m.cname, m.wmname, t.mx
FROM (
    SELECT cname, MAX(avg) AS mx
    FROM makerar
    GROUP BY cname
    ) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

しかし、ウィンドウ関数を使うこともでき、その方がシンプルに見えます。

SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;

この方法の唯一の点は、すべてのレコードを表示することです（ウィンドウ関数はグループ化しません）。しかし、正しい（つまり、最大で cname レベル) MAX をそれぞれの行の国名にするのは、あなた次第です。

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  |     5.0000000000000000
 spain  | usopp  |     5.0000000000000000

解決策は、間違いなく、よりエレガントではない、唯一の (cname, wmname) のタプルは、最大値にマッチします。

SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */
    m.cname, m.wmname, t.avg AS mx
FROM (
    SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn 
    FROM makerar
) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1
;


 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

[*]: 興味深いことに、グループ化されていないフィールドを選択することは仕様上可能ですが、主要なエンジンはそれを好まないようです。OracleとSQLServerはこれをまったく許可していません。Mysqlは以前はデフォルトで許可していましたが、5.7以降では管理者がこのオプションを有効にする必要があります( ONLY_FULL_GROUP_BY この機能をサポートするために、サーバーの設定に手動で追加してください。