☰

GROUP BY句に出現するか、集計関数で使用される必要があります。質問する

tag-icon • tag-icon sql group-by aggregate-functions postgresql-9.1
GROUP BY句に出現するか、集計関数で使用される必要があります。質問する

私は、この呼び出し元「makerar」のようなテーブルを持っています

ユーザー名 wmname 平均
カナダ ゾロ 2.0000000000000000
スペイン ルフィ 1.00000000000000000000
スペイン ウソップ 5.0000000000000000

そして、各 cname の最大平均を選択したいと思います。

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;

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

ユーザー名 wmname 最大
カナダ ゾロ 2.0000000000000000
スペイン ルフィ 1.00000000000000000000
スペイン ウソップ 5.0000000000000000

実際の結果は

ユーザー名 wmname 最大
カナダ ゾロ 2.0000000000000000
スペイン ウソップ 5.0000000000000000

この問題を解決するにはどうすればいいでしょうか?

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

ベストアンサー1

はい、これは一般的な集約の問題です。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この機能をサポートするには、管理者がサーバー構成でこのオプション ( ) を手動で有効にする必要があります...

おすすめ記事