[sql] 각 GROUP BY 그룹의 첫 번째 행을 선택 하시겠습니까?


4 Answers

PostgreSQL에서 이것은 일반적으로 보다 간단하고 빠릅니다 (아래의 더 많은 성능 최적화).

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;

또는 출력 열의 서수 번호가 더 짧으면 (분명하지 않은 경우)

SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

total 이 NULL 일 수 있다면 (어느 쪽이라도 다 치지는 않지만 기존 인덱스와 일치 시키길 원할 것입니다) :

...
ORDER  BY customer, total DESC NULLS LAST, id;

주요 사항

  • DISTINCT ON 은 표준의 PostgreSQL 확장입니다 (전체 SELECT 목록에서 DISTINCT 만 정의 됨).

  • DISTINCT ON 절에있는 수식을 나열하면 조합 된 행 값이 중복을 정의합니다. 매뉴얼 :

    분명히 두 행은 적어도 하나의 열 값이 다른 경우 별개로 간주됩니다. 이 비교에서는 Null 값이 동일한 것으로 간주됩니다.

    대담한 강조.

  • DISTINCT ONORDER BY 와 결합 할 수 있습니다. 선행 표현식은 같은 순서로 선행 DISTINCT ON 표현식과 일치해야합니다. ORDER BY 에 표현식을 추가하여 각 피어 그룹에서 특정 행을 선택할 수 있습니다. 나는 동점을 맺을 마지막 항목으로 id 를 추가했다 :

    "각 그룹에서 가장 작은 total 가진 행을 선택하십시오."

    total 이 NULL 일 수 있다면 가장 큰 널이 아닌 값을 가진 행을 원할 것입니다 . 시연처럼 NULLS LAST 추가하십시오. 세부:

  • SELECT 목록 은 어떤 식 으로든 DISTINCT ON 또는 ORDER BY 식의 제약을받지 않습니다. (위의 간단한 경우에는 필요하지 않음) :

    • DISTINCT ON 또는 ORDER BY 포함시킬 필요는 없습니다 .

    • SELECT 목록에 다른 표현식을 포함 할 수 있습니다 . 이것은 훨씬 복잡한 쿼리를 서브 쿼리 및 집계 / 창 함수로 대체하는 데 유용합니다.

  • 필자는 버전 8.3-10으로 테스트를 실시했지만, 버전 7.1 이후에는 기능이 기본적으로 제공되었습니다.

색인

위의 쿼리에 대한 완벽한 인덱스는 일치하는 시퀀스와 일치하는 정렬 순서로 세 열 모두를 스패닝하는 다중 열 인덱스입니다 .

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

실제 응용 프로그램에 너무 전문화되어있을 수 있습니다. 그러나 읽기 성능이 중요 할 경우 사용하십시오. 쿼리에 DESC NULLS LAST 가 있으면 인덱스에서 동일하게 사용하여 Postgres가 정렬 순서가 일치하도록합니다.

효율성 / 성능 최적화

모든 쿼리에 대해 맞춤형 색인을 만들기 전에 비용과 이점을 고려해야합니다. 위의 지수의 잠재력은 데이터 배포 에 크게 의존합니다.

인덱스는 미리 정렬 된 데이터를 제공하기 때문에 사용되며 Postgres 9.2 이상에서는 인덱스가 기본 테이블보다 작 으면 인덱스 만 스캔 하여 쿼리를 활용할 수도 있습니다. 그러나 색인 전체를 스캔해야합니다.

기준

나는 Postgres 9.1에 대해 2016 년까지 구식 인 간단한 벤치 마크를 보냈다. 그래서 나는 Postgres 9.4와 9.5를 위한 더 나은, 재현성있는 설정으로 새로운 것을 실행 시켰고 상세한 결과를 또 다른 대답으로 추가했다.

Question

제목에서 알 수 있듯이 GROUP BY 그룹화 된 각 행 세트의 첫 번째 행을 선택하고 싶습니다.

특히, 다음과 같은 purchases 테이블이 있다면 :

SELECT * FROM purchases;

내 출력 :

id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1

customer 만든 가장 큰 구매 ( total ) id 를 쿼리하고 싶습니다. 이 같은:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

예상 출력 :

FIRST(id) | customer | FIRST(total)
----------+----------+-------------
        1 | Joe      | 5
        2 | Sally    | 3



이는 이미 잘 테스트되고 고도로 최적화 된 솔루션을 갖춘 일반적인 greatest-n-per-group 문제입니다. 개인적으로 필자는 Bill Karwin ( 다른 솔루션이 많은 원본 게시물) 의 왼쪽 가입 솔루션을 선호합니다.

이 공통적 인 문제에 대한 해결책은 MySQL 공식 설명서 에서 찾을 수 있습니다 ! 일반적인 쿼리의 예 :: 특정 열의 그룹 별 최대 값을 유지하는 행을 참조하십시오.




매우 빠른 솔루션

SELECT a.* 
FROM
    purchases a 
    JOIN ( 
        SELECT customer, min( id ) as id 
        FROM purchases 
        GROUP BY customer 
    ) b USING ( id );

테이블이 id에 의해 인덱싱되면 정말 빠릅니다.

create index purchases_id on purchases (id);



Erwin이 지적한 바와 같이 SubQ가 있기 때문에 솔루션의 효율성이 떨어집니다.

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;



허용 된 OMG Ponies의 "모든 데이터베이스 지원"솔루션은 테스트에서 좋은 속도를 보입니다.

여기에서는 동일한 접근법을 제공하지만 모든 데이터베이스 솔루션을 더 완전하고 깨끗하게 정리합니다. 동점이 고려됩니다 (각 고객에 대해 한 행만 가져오고 고객 당 최대 합계에 대한 여러 레코드조차도 원한다고 가정). 구매 테이블의 실제 일치하는 행에 대해 다른 구매 필드 (예 : purchase_payment_id)가 선택됩니다.

모든 데이터베이스에서 지원 :

select * from purchase
join (
    select min(id) as id from purchase
    join (
        select customer, max(total) as total from purchase
        group by customer
    ) t1 using (customer, total)
    group by customer
) t2 using (id)
order by customer

이 쿼리는 특히 구매 테이블에 (고객, 전체)와 같은 복합 인덱스가있는 경우 상당히 빠릅니다.

말:

  1. t1, t2는 데이터베이스에 따라 제거 할 수있는 하위 쿼리 별칭입니다.

  2. 주의 사항 : using (...) 절은 현재 2017 년 1 월 MS-SQL과 Oracle db에서 지원되지 않습니다. on t2.id = purchase.idon t2.id = purchase.id 직접 확장해야합니다. USING 구문 SQLite, MySQL 및 PostgreSQL에서 작동합니다.






Related