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




쿼리 (10)

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

  • 필자는 Postgres 버전 8.3-10을 사용하여 테스트를 수행했습니다. 그러나이 기능은 버전 7.1부터 기본적으로 항상 기본적으로 제공되고 있습니다.

색인

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

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

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

효율성 / 성능 최적화

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

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

기준

나는 여기에서 구식 인 간단한 벤치 마크를 보았다. 나는 이 개별 답변에서 상세한 벤치 마크로 대체했다.

제목에서 알 수 있듯이 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

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

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'

첫 번째와 마지막 기능을 제공하는 포스트 그레스 확장 기능입니다. 분명히 위의 방법보다 빠릅니다.

편집 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);

당연히 집합체 내에서 적합하다고 판단되면 주문하고 필터링 할 수 있습니다. 그것은 매우 강력한 구문입니다.


기준

Postgres 9.49.5 로 가장 흥미로운 후보를 테스트하면서 purchases 200,000 개의 행10,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에 대한 두 번째 테스트 에서 동일한 설정을 사용했지만 customer_id 를 생성하기 위해 random() * 100000 을 사용하여 customer_id 당 몇 개의 행만 가져 random() * 100000 .

테이블 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. CTE의 row_number() ( 기타 답변 참조 )

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 하위 쿼리가있는 LATERAL ( 여기 참조 )

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. LATERAL customer 테이블 ( 여기 참조 )

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;

6. ORDER BY array_agg() ( 다른 답변 참조 )

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 에서 색인 스캔 만 사용 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.와 동일하지만 ~ 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

2011 년의 기존 (구형) 벤치 마크

PostgreSQL 9.1 을 사용하여 65579 개의 행과 1 열의 btree 인덱스로 구성된 실제 테이블에서 세 가지 테스트를 실행했으며 각각의 세 개의 열에 대해 5 개의 실행 시간 이 가장 좋습니다.
@OMGPonies의 첫 번째 쿼리 ( A )와 위의 DISTINCT ON 솔루션 ( B ) 비교 :

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

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

    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

  • 집계 된 행 집합에서 임의의 특정 조건에 따라 행을 선택하려는 경우

  • max/min 외에도 다른 ( sum/avg ) 집계 함수를 사용하려는 경우 따라서 DISTINCT ON 과 함께 단서를 사용할 수 없습니다.

다음 하위 쿼리를 사용할 수 있습니다.

SELECT  
    (  
       SELECT **id** FROM t2   
       WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )   
    ) id,  
    name,   
    MAX(amount) ma,  
    SUM( ratio )  
FROM t2  tf  
GROUP BY name

amount = MAX( tf.amount ) 를 한 가지 제약 조건으로 원하는 조건으로 바꿀 수 있습니다.이 하위 쿼리는 두 개 이상의 행을 반환하면 안됩니다

하지만 그런 일을하고 싶다면 아마도 윈도우 함수를 찾고있을 것입니다.


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 를 유지할 수 있습니다.

오라클 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

허용 된 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에서 작동합니다.


쿼리 :

SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p 
ON 
  p.customer = purchases.customer 
  AND 
  purchases.total < p.total
WHERE p.total IS NULL

어떻게 작동합니까! (나 거기 가봤 어)

우리는 매 구매마다 가장 높은 총액만을 보유하고 싶습니다.

일부 이론 자료 (쿼리를 이해하려는 경우이 부분은 건너 뜁니다)

합계가 이름과 ID가 주어진 값을 반환하는 함수 T (customer, id)라고합시다. 주어진 합계 (T (고객, id))가 증명해야하는 가장 높은 것을 증명하기 위해 우리는

  • ∀x T (고객, id)> T (고객, x) (이 합계는 해당 고객의 다른 모든 합계보다 높음)

또는

  • ¬∃x T (고객, id) <T (고객, x) (해당 고객에 대해 더 많은 합계가 존재하지 않음)

첫 번째 접근법은 우리가 정말로 좋아하지 않는 그 이름에 대한 모든 기록을 가져와야합니다.

두 번째 것은 이보다 더 높은 기록이 없을 수 있다고 말하는 현명한 방법이 필요합니다.

SQL로 돌아 가기

우리가 조인 테이블에 이름과 합계가 조인 된 테이블보다 작 으면 :

      LEFT JOIN purchases as p 
      ON 
      p.customer = purchases.customer 
      AND 
      purchases.total < p.total

동일한 사용자에 대해 합계가 높은 다른 레코드가있는 모든 레코드를 조인해야합니다.

purchases.id, purchases.customer, purchases.total, p.id, p.customer, p.total
1           , Tom           , 200             , 2   , Tom   , 300
2           , Tom           , 300
3           , Bob           , 400             , 4   , Bob   , 500
4           , Bob           , 500
5           , Alice         , 600             , 6   , Alice   , 700
6           , Alice         , 700

그러면 그룹화가 필요하지 않은 각 구매에 대해 가장 높은 합계를 필터링 할 수 있습니다.

WHERE p.total IS NULL

purchases.id, purchases.name, purchases.total, p.id, p.name, p.total
2           , Tom           , 300
4           , Bob           , 500
6           , Alice         , 700

이것이 우리가 필요로하는 해답입니다.


나는 다음에 대한 로빈의 탁월한 대답 을 수정할 것이다.

UPDATE Table
SET Table.col1 = other_table.col1,
 Table.col2 = other_table.col2
FROM
    Table
INNER JOIN other_table ON Table.id = other_table.id
WHERE
    Table.col1 != other_table.col1
OR Table.col2 != other_table.col2
OR (
    other_table.col1 IS NOT NULL
    AND Table.col1 IS NULL
)
OR (
    other_table.col2 IS NOT NULL
    AND Table.col2 IS NULL
)

WHERE 절이 없으면 실제로 영향을받지 않아야하는 인덱스 재 계산 또는 실행 트리거를 유발할 수있는 영향을받지 않는 행까지 영향을 미칩니다.





sql sqlite postgresql group-by greatest-n-per-group