[sql] 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입니다.

답변

예, 이것은 일반적인 집계 문제입니다. 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이 기능을 지원하려면 서버 구성 에서이 옵션 ( )을 수동으로 활성화해야합니다 …

답변

Postgres에서는 특수 DISTINCT ON (expression)구문을 사용할 수도 있습니다 .

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

답변

group by선택 에서 그룹화되지 않은 필드와 집계되지 않은 필드를 지정할 때의 문제점 은 엔진이이 경우 어떤 레코드의 필드를 리턴해야하는지 알 수 없다는 것입니다. 처음인가요? 마지막인가요? 자연스럽게 집계 결과에 해당하는 (아무 기록 일반적으로 없습니다 min및 max예외가).

그러나 해결 방법이 있습니다. 필수 필드도 집계하십시오. posgres에서는 다음과 같이 작동합니다.

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

이것은 avg 순으로 모든 wname의 배열을 생성하고 첫 번째 요소를 반환합니다 (postgres의 배열은 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;

노트

둘 중 하나는 그룹당 여러 개의 최대 값을 유지합니다. 평균이 max와 동일한 레코드가 두 개 이상인 경우에도 그룹당 단일 레코드 만 원하는 경우 @ypercube의 답변을 확인해야합니다.

답변

저에게는 “일반적인 집계 문제”가 아니라 잘못된 SQL 쿼리에 관한 것입니다. “각 cname에 대한 최대 평균 선택 …”에 대한 정답은

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

결과는 다음과 같습니다.

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

이 결과는 일반적으로 “각 그룹에 가장 적합한 결과는 무엇입니까?”라는 질문에 대답합니다. . 스페인의 경우 최상의 결과는 5이고 캐나다의 경우 최상의 결과는 2입니다. 사실이며 오류가 없습니다. wmname 도 표시해야하는 경우 , ” 결과 세트에서 wmname을 선택 하는 규칙 은 무엇입니까 ?”라는 질문에 대답해야합니다. 실수를 명확히하기 위해 입력 데이터를 약간 변경해 봅시다 :

  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 인터프리터가 오류를 반환하는 이유입니다. 쿼리가 올바르지 않습니다.

다시 말해, ” spain그룹 에서 누가 최고 입니까?”라는 질문에 대한 정답은 없습니다. . 루피는 usopp보다 낫지 않습니다. 왜냐하면 usopp는 같은 “점수”를 가지고 있기 때문입니다.

답변

이것은 잘 작동하는 것 같습니다

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

답변

최근에을 사용하여 계산하려고 할 때이 문제가 발생 case when하여 whichand 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