は、GROUP BY句に記述するか、集約関数で使用する必要があります。

このようなテーブルがあります；

 cname  | wmname |          avg           
--------+-------------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  | 1.00000000000000000000
 spain  | usopp  |     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;

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

 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です。

a_horse_with_no_name
編集された質問 1日 11月 2013 в 9:09
プログラミング
sql group-by aggregate-functions postgresql-9.1
26日 10月 2013 в 1:50
10 ビュー
質問へのコメント (1)
ソリューション

はい、これは一般的な集約の問題です。SQL3 (1999)]1以前では、選択されたフィールドは 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

MAX値に一致する (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
Sebas
編集した答え 17日 11月 2015 в 6:34
解説 (7)

Postgresでは、特別なDISTINCT ON (expression) 構文を使用することもできます：

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;
解説 (8)

「group by」選択でグループ化されていないフィールドと非集計フィールドを指定する際の問題は、この場合、エンジンはどのレコードのフィールドを返す必要があるかを知る方法がないことです。 最初ですか? 最後ですか? 通常、集計結果に自然に対応するレコードはありません(「min」と「max」は例外です)。

ただし、回避策があります。必要なフィールドも集計します。 雑踏では、これはうまくいくはずです:

SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;

これにより、avgで順序付けられたすべてのwnamesの配列が作成され、最初の要素が返されることに注意してください(postgresの配列は1ベースです)。

RonU
編集した答え 28日 5月 2019 в 12:19
解説 (1)
SELECT t1.cname, t1.wmname, t2.max
FROM makerar t1 JOIN (
    SELECT cname, MAX(avg) max
    FROM makerar
    GROUP BY cname ) t2
ON t1.cname = t2.cname AND t1.avg = t2.max;

rank()`ウィンドウ関数を使用しています：

SELECT cname, wmname, avg
FROM (
    SELECT cname, wmname, avg, rank() 
    OVER (PARTITION BY cname ORDER BY avg DESC)
    FROM makerar) t
WHERE rank = 1;

**注意

どちらを用いても、グループごとに複数の最大値が保持されます。avgがmaxに等しいレコードが複数あっても、グループごとに1つのレコードだけが欲しい場合は、@ypercube'さんの回答を参照してください。

zero323
編集した答え 1日 11月 2013 в 10:52
解説 (0)

私にとって、それは「一般的な集約の問題」ではなく、誤ったSQLクエリについてです。 「各cnameの最大平均を選択します。..」です。

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

結果は次のとおりです。

 cname  |      MAX(avg)
--------+---------------------
 canada | 2.0000000000000000
 spain  | 5.0000000000000000

この結果は、一般的に次の質問に答えます「各グループに最適な結果は何ですか?"。 スペインにとって最良の結果は5であり、カナダにとって最良の結果は2であることがわかります。 それは本当であり、エラーはありません。 wmname も表示する必要がある場合は、次の質問に答える必要があります。「結果のセットからwmnameを選択する RULE とは何ですか?「間違いを明確にするために、入力データを少し変更しましょう。

  cname | wmname |        avg           
--------+--------+-----------------------
 spain  | zoro   |  1.0000000000000000
 spain  | luffy  |  5.0000000000000000
 spain  | usopp  |  5.0000000000000000

このクエリを実行すると、どの結果が期待されますか: SELECT cname、wmname、MAX(avg)FROM makerar GROUP BY cname;? 「spain + luffy」または「spain + usopp」である必要があります? なぜ? クエリで「より良い」 wmname を選択する方法が決定されていないので、いくつかが適切である場合、結果も決定されません。 これが、SQLインタープリターがエラーを返す理由です-クエリは正しくありません。

言い換えれば、質問への正しい答えはありません「「スペイン」グループで誰が最高か?"。 usoppは同じ「スコア」を持っているので、Luffyはusoppに勝るものはありません。

解説 (0)

最近、「case when」を使用してカウントしようとしたときにこの問題に遭遇し、「which」および「count」ステートメントの順序を変更すると問題が解決することがわかりました。

SELECT date(dateday) as pick_day,
COUNT(CASE WHEN (apples = 'TRUE' OR oranges 'TRUE') THEN fruit END)  AS fruit_counter

FROM pickings

GROUP BY 1

使用する代わりに-後者では、リンゴとオレンジが集約関数に表示されるというエラーが発生しました。

CASE WHEN ((apples = 'TRUE' OR oranges 'TRUE') THEN COUNT(*) END) END AS fruit_counter
Paul Roub
編集した答え 9日 11月 2016 в 2:10
解説 (1)

これもうまくいくようです。

SELECT *
FROM makerar m1
WHERE m1.avg = (SELECT MAX(avg)
                FROM makerar m2
                WHERE m1.cname = m2.cname
               )
解説 (0)