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

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

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

SELECT * FROM purchases;

내 결과 :

아이디 | 고객 | 합계
--- + ---------- + ------
 1 | 조 | 5
 2 | 샐리 | 삼
 3 | 조 | 2
 4 | 샐리 | 1

에 의해 이루어진 id최대 구매 금액 ( total) 을 문의하고 싶습니다 customer. 이 같은:

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

예상 출력 :

먼저 (id) | 고객 | 첫 (총)
---------- + ---------- + ---------------
        1 | 조 | 5
        2 | 샐리 | 삼



답변

Oracle 9.2+ (원래 언급 된 8i + 아님)에서 SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 3.0+, Teradata, Sybase, Vertica :

WITH summary AS (
    SELECT p.id,
           p.customer,
           p.total,
           ROW_NUMBER() OVER(PARTITION BY p.customer
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 1

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

그러나 관계를 끊기 위해 논리를 추가해야합니다.

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer,
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total


답변

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;

totalNULL 일 수있는 경우 (어느 쪽도 다 치지 않지만 기존 색인과 일치 시키려는 경우 ) :

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

주요 포인트

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

  • DISTINCT ON절 에 여러 표현식을 나열 하고 결합 된 행 값은 중복을 정의합니다. 매뉴얼 :

    분명히, 하나 이상의 열 값이 다른 두 행은 서로 다른 것으로 간주됩니다. 이 비교에서 널값은 동일한 것으로 간주됩니다.

    대담한 강조 광산.

  • DISTINCT ON와 결합 할 수 있습니다 ORDER BY. 앞에 오는 표현식은 ORDER BY의 표현식 세트 에 있어야 DISTINCT ON하지만 자유롭게 순서를 다시 정렬 할 수 있습니다. 예. 각 피어 그룹에서 특정 행을 선택 하기 위해 식을 더 추가 할 수 있습니다 ORDER BY. 또는 매뉴얼에 따르면 :

    DISTINCT ON표현 (들)은 왼쪽 일치해야합니다 ORDER BY
    표현 (들). 이 ORDER BY절에는 일반적으로 각 DISTINCT ON그룹 내에서 원하는 행의 우선 순위를 결정하는 추가식이 포함됩니다 .

    id관계를 끊기 위해 마지막 항목으로 추가 했습니다.
    id각 그룹 에서 가장 작은 행을 선택 하여 가장 높은 행을 공유하십시오 total.”

    그룹당 첫 번째를 결정하는 정렬 순서에 동의하지 않는 방식으로 결과를 정렬하려면 외부 쿼리에서 다른 쿼리와 함께 위의 쿼리를 중첩 할 수 있습니다 ORDER BY. 예.

  • totalNULL 일 수있는 경우 가장 null이 아닌 값을 가진 행을 원할 것입니다 . 설명 된대로 추가하십시오 NULLS LAST. 보다:

  • SELECT목록 의 표현에 의해 제한되지 않는다 DISTINCT ON또는 ORDER BY어떤 방법이다. (위의 간단한 경우에는 필요하지 않음) :

    • 당신은 필요가 없습니다 에 표현 중 하나를 포함 DISTINCT ONORDER BY.

    • 목록에 다른 식을 포함시킬 수 있습니다SELECT . 이는 훨씬 복잡한 쿼리를 하위 쿼리 및 집계 / 창 함수로 대체하는 데 유용합니다.

  • Postgres 버전 8.3 – 12로 테스트했습니다. 그러나이 기능은 최소한 버전 7.1 이후 기본적으로 항상 사용되었습니다.

인덱스

완벽한 위의 쿼리에 대한 인덱스는 것입니다 멀티 컬럼 인덱스 순서를 일치와 일치하는 정렬 순서로 세 개의 열을 걸친 :

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

너무 전문적 일 수 있습니다. 그러나 특정 쿼리에 대한 읽기 성능이 중요한 경우 사용하십시오. DESC NULLS LAST쿼리에있는 경우 정렬 순서가 일치하고 인덱스가 적용되도록 인덱스에서 동일하게 사용하십시오.

효과 / 성능 최적화

각 쿼리마다 맞춤형 인덱스를 생성하기 전에 비용과 이점을 측정하십시오. 위의 색인의 가능성은 데이터 분포 에 크게 좌우됩니다 .

인덱스는 미리 정렬 된 데이터를 전달하기 때문에 사용됩니다. Postgres 9.2 이상 에서는 인덱스가 기본 테이블보다 작은 경우 인덱스 만 스캔을 통해 쿼리를 활용할 수도 있습니다 . 그러나 인덱스는 전체적으로 스캔해야합니다.

기준

나는 여기에 오래된 벤치 마크가 있습니다. 이 별도의 답변에서 자세한 벤치 마크로 대체했습니다 .


답변

기준

포스트 그레스와 함께 가장 흥미로운 후보 테스트 9.49.5 의 중간 현실적인 테이블과 200K 행purchases10,000 별개의customer_id ( 평균. 고객 당 20 행 ).

Postgres 9.5의 경우 효과적으로 86446 명의 개별 고객과의 2 차 테스트를 실시했습니다. 아래를 참조하십시오 ( 고객 당 평균 2.3 행 ).

설정

메인 테이블

CREATE TABLE purchases (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int  -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);

내가 사용 serial(아래 추가 PK 제약)과 정수를 customer_id그보다 일반적인 설정이기 때문에. 또한 some_column일반적으로 더 많은 열을 보충하기 위해 추가되었습니다 .

더미 데이터, PK, 인덱스-일반적인 테이블에는 죽은 튜플이 있습니다.

INSERT INTO purchases (customer_id, total, some_column)    -- insert 200k rows
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,200000) g;

ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);

DELETE FROM purchases WHERE random() > 0.9; -- some dead rows

INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,20000) g;  -- add 20k to make it ~ 200k

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

VACUUM ANALYZE purchases;

customer 테이블-우수한 쿼리

CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM   purchases
GROUP  BY 1
ORDER  BY 1;

ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);

VACUUM ANALYZE customer;

내에서 두 번째 테스트 9.5 나는 같은 설정을 사용할 수 있지만 함께하면 random() * 100000생성하는 customer_id당 몇 행을 얻을 customer_id.

테이블의 객체 크기 purchases

이 쿼리로 생성됩니다 .

               what                | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 20496384 | 20 MB        |           102
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    24576 | 24 kB        |             0
 table_size_incl_toast             | 20529152 | 20 MB        |           102
 indexes_size                      | 10977280 | 10 MB        |            54
 total_size_incl_toast_and_indexes | 31506432 | 30 MB        |           157
 live_rows_in_text_representation  | 13729802 | 13 MB        |            68
 ------------------------------    |          |              |
 row_count                         |   200045 |              |
 live_tuples                       |   200045 |              |
 dead_tuples                       |    19955 |              |

쿼리

1. row_number()CTE에서 ( 다른 답변 참조 )

WITH cte AS (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   )
SELECT id, customer_id, total
FROM   cte
WHERE  rn = 1;

2. row_number()하위 쿼리에서 (내 최적화)

SELECT id, customer_id, total
FROM   (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   ) sub
WHERE  rn = 1;

3. DISTINCT ON( 다른 답변 참조 )

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

4. LATERAL하위 쿼리가있는 rCTE ( 여기 참조 )

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, customer_id, total
   FROM   purchases
   ORDER  BY customer_id, total DESC
   LIMIT  1
   )
   UNION ALL
   SELECT u.*
   FROM   cte c
   ,      LATERAL (
      SELECT id, customer_id, total
      FROM   purchases
      WHERE  customer_id > c.customer_id  -- lateral reference
      ORDER  BY customer_id, total DESC
      LIMIT  1
      ) u
   )
SELECT id, customer_id, total
FROM   cte
ORDER  BY customer_id;

5. customer테이블 LATERAL( 여기 참조 )

SELECT l.*
FROM   customer c
,      LATERAL (
   SELECT id, customer_id, total
   FROM   purchases
   WHERE  customer_id = c.customer_id  -- lateral reference
   ORDER  BY total DESC
   LIMIT  1
   ) l;

array_agg()ORDER BY( 다른 답을 참조 )

SELECT (array_agg(id ORDER BY total DESC))[1] AS id
     , customer_id
     , max(total) AS total
FROM   purchases
GROUP  BY customer_id;

결과

위의 쿼리에 대한 실행 시간 EXPLAIN ANALYZE(및 모든 옵션이 꺼져 있음 ), 최대 5 회 실행 .

모든 쿼리는 다른 단계 중에서 인덱스 전용 스캔 을 사용했습니다 purchases2_3c_idx. 그들 중 일부는 단지 더 작은 크기의 색인을 위해, 다른 일부는 더 효과적으로.

A. Postgres 9.4 (200k 행 및 ~ 20 개) customer_id

1. 273.274 ms
2. 194.572 ms
3. 111.067 ms
4.  92.922 ms
5.  37.679 ms  -- winner
6. 189.495 ms

B. Postgres 9.5와 동일

1. 288.006 ms
2. 223.032 ms
3. 107.074 ms
4.  78.032 ms
5.  33.944 ms  -- winner
6. 211.540 ms  

C. B와 동일하지만 ~ 2.3 행당 customer_id

1. 381.573 ms
2. 311.976 ms
3. 124.074 ms  -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms

관련 벤치 마크

다음은 Postgres 11.5 (2019 년 9 월 현재) 에서 10M 개의 행과 60k의 고유 한 “고객” 을 사용한 “ogr”테스트에 의한 새로운 것 입니다. 결과는 여전히 우리가 지금까지 본 것과 일치합니다.

2011 년의 원래 (오래된) 벤치 마크

PostgreSQL 9.1 을 사용하여 65579 행의 실제 테이블과 관련된 세 개의 열 각각에 대한 단일 열 btree 인덱스에서 세 가지 테스트를 실행 했으며 5 번의 실행 시간 을 가장 잘 수행했습니다 .
비교 @OMGPonies ‘ 첫 번째 쿼리를 ( A받는 사람) 위의 DISTINCT ON솔루션 ( B) :

  1. 이 경우 전체 테이블을 선택하면 결과는 5958 행입니다.

    A: 567.218 ms
    B: 386.673 ms
  2. WHERE customer BETWEEN x AND y1000 행의 결과 조건을 사용하십시오 .

    A: 249.136 ms
    B:  55.111 ms
  3. 로 단일 고객을 선택하십시오 WHERE customer = x.

    A:   0.143 ms
    B:   0.072 ms

다른 답변에 설명 된 색인으로 동일한 테스트를 반복했습니다.

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

1A: 277.953 ms
1B: 193.547 ms

2A: 249.796 ms -- special index not used  
2B:  28.679 ms

3A:   0.120 ms
3B:   0.048 ms


답변

이것은 일반적입니다 이미 잘 테스트되고 최적화 된 솔루션을 가지고있는 문제 . 개인적 으로 Bill Karwin왼쪽 조인 솔루션 ( 다른 솔루션이 많은 원본 게시물)을 선호합니다 .

이 일반적인 문제에 대한 많은 솔루션은 놀랍게도 가장 공식적인 소스 중 하나 인 MySQL 매뉴얼 에서 찾을 수 있습니다 ! 일반적인 쿼리 예 :: 특정 열의 그룹 별 최대 값을 유지하는 행을 참조하십시오 .


답변

Postgres에서는 다음 array_agg과 같이 사용할 수 있습니다 .

SELECT  customer,
        (array_agg(id ORDER BY total DESC))[1],
        max(total)
FROM purchases
GROUP BY customer

그러면 id각 고객의 최대 구매 금액이 제공됩니다 .

참고할 사항 :

  • array_agg은 집계 함수이므로와 함께 작동합니다 GROUP BY.
  • array_agg자체적으로 범위가 지정된 순서를 지정할 수 있으므로 전체 쿼리의 구조를 제한하지 않습니다. 기본값과 다른 것을 수행 해야하는 경우 NULL을 정렬하는 방법에 대한 구문도 있습니다.
  • 배열을 빌드하면 첫 번째 요소를 가져옵니다. (Postgres 어레이는 0 인덱스가 아닌 1 인덱스입니다.)
  • array_agg세 번째 출력 열과 비슷한 방식으로 사용할 수 있지만 max(total)더 간단합니다.
  • 와 달리 DISTINCT ON,를 array_agg사용하면 GROUP BY다른 이유로 원하는 경우를 사용할 수 있습니다 .

답변

SubQ가 존재하기 때문에 Erwin이 지적한 것처럼 솔루션이 그리 효율적이지 않습니다.

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


답변

이 방법을 사용합니다 (postgresql 만 해당) : https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

그런 다음 예제는 거의 그대로 작동해야합니다 .

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

주의 사항 : NULL 행을 무시합니다.


편집 1-대신 postgres 확장을 사용하십시오

이제이 방법을 사용합니다 : http://pgxn.org/dist/first_last_agg/

우분투 14.04에 설치하려면 :

apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'

첫 번째 기능과 마지막 기능을 제공하는 postgres 확장입니다. 분명히 위의 방법보다 빠릅니다.


편집 2-주문 및 필터링

이와 같은 집계 함수를 사용하는 경우 데이터를 이미 주문하지 않아도 결과를 주문할 수 있습니다.

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

따라서 순서가있는 동등한 예는 다음과 같습니다.

SELECT first(id order by id), customer, first(total order by id)
  FROM purchases
 GROUP BY customer
 ORDER BY first(total);

물론 골재 내에 맞는 것으로 주문하고 필터링 할 수 있습니다. 매우 강력한 구문입니다.