복붙노트

[SQL] 각 GROUP BY 그룹에서 첫 번째 행을 선택?

SQL

각 GROUP BY 그룹에서 첫 번째 행을 선택?

제목에서 알 수 있듯이, 나는 그룹 BY와 함께 그룹화 행의 각 세트의 첫 번째 행을 선택하고 싶습니다.

특히, 내가 구매 테이블을 가지고 있다면 그 다음과 같다 :

SELECT * FROM purchases;

내 출력 :

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

나는 가장 큰 구매 (총)의 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

해결법

  1. ==============================

    1.

    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
  2. ==============================

    2.PostgreSQL의에서이 일반적으로 간단하고 빠른 (더 성능 최적화 아래)입니다 :

    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;

    총 NULL이 될 수 있다면 (어느 쪽이든을 해치지 않을 것입니다,하지만 당신은 기존의 인덱스를 일치시킬 수 있습니다)

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

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

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

    너무 전문화 될 수 있습니다. 특정 쿼리에 대한 읽기 성능이 매우 중요하지만 그것을 사용할 수 있습니다. 쿼리에 DESC NULLS LAST이있는 경우, 그 정렬 순서와 일치하도록 인덱스에 동일한을 사용하여 인덱스가 적용됩니다.

    각 쿼리에 맞게 조정 인덱스를 작성하기 전에 비용 및 이익 무게. 위의 인덱스의 잠재력은 크게 데이터 분포에 따라 달라집니다.

    이 사전 정렬 된 데이터를 제공하기 때문에 인덱스가 사용됩니다. 포스트 그레스 9.2 이상 쿼리에서 또한 인덱스가 기본 테이블보다 작은 경우에만 스캔 인덱스 혜택을 누릴 수 있습니다. 인덱스는하지만, 전체를 스캔해야합니다.

    이제 구식이되어 여기에 간단한 벤치 마크를했다. 나는이 별도의 답안에 대한 자세한 벤치 마크를 교체했다.

  3. ==============================

    3.구매에서 20 만 행과 10,000 별개의 CUSTOMER_ID (평균. 고객 당 20 행)의 중간 현실적인 테이블과 포스트 그레스 9.4과 9.5로 가장 흥미로운 후보를 테스트.

    구매에서 20 만 행과 10,000 별개의 CUSTOMER_ID (평균. 고객 당 20 행)의 중간 현실적인 테이블과 포스트 그레스 9.4과 9.5로 가장 흥미로운 후보를 테스트.

    포스트 그레스 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
);

    즉,보다 일반적인 설치 이후 나는 직렬 (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;

    고객 테이블 - 우수한 쿼리에 대한

    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에 대한 내 두 번째 테스트에서 나는 같은 설정을 사용하지만, 무작위로 () * 100000 CUSTOMER_ID 당 몇 행을 얻을 CUSTOMER_ID을 생성 할 수 있습니다.

    이 쿼리 생성됩니다.

                   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 |              |

    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;

    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;

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

    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;

    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;

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

    5 개 실행의 가장 위의 ANALYZE EXPLAIN와 쿼리 (모든 옵션을 해제)에 대한 실행 시간.

    (다른 단계들) purchases2_3c_idx에 인덱스 만 스캔 사용되는 모든 쿼리. 단지 인덱스의 작은 크기에 대한 그들 중 일부는 다른 사람보다 효율적으로.

    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

    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  

    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

    여기에 (2019년 9월 일 현재) 포스트 그레스 11.5에 "OGR"10M 행으로 테스트하고 60K 독특한 "고객"에 의해 새로운 하나입니다. 결과는 우리가 지금까지 본 것을에 부합 여전히 :

    나는 포함 된 세 개의 열 각각에 65,579 행과 단일 열 BTREE 인덱스의 실제 테이블에 PostgreSQL의 9.1 세 테스트를 실행 5 개 실행의 최선의 실행 시간이 걸렸습니다. 상기 DISTINCT ON 용액 (B)에 @OMGPonies 첫 질의 (A)를 비교 :

    인덱스 반복 동일한 테스트는 다른 대답에 설명

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

  4. ==============================

    4.이것은 이미 잘 테스트에는 일반적인 큰-N 당 그룹 문제, 그리고 고도로 최적화 된 솔루션이다. 개인적으로 나는 빌 Karwin (다른 솔루션의 많은 원래의 게시물)에 의해 왼쪽 가입 솔루션을 선호합니다.

    이것은 이미 잘 테스트에는 일반적인 큰-N 당 그룹 문제, 그리고 고도로 최적화 된 솔루션이다. 개인적으로 나는 빌 Karwin (다른 솔루션의 많은 원래의 게시물)에 의해 왼쪽 가입 솔루션을 선호합니다.

    이 일반적인 문제에 대한 해결의 무리가 놀라 울 정도로 대부분의 공식 소스 MySQL의 설명서의 하나에서 찾을 수 있습니다! 일반적인 쿼리의 예 : 특정 컬럼의 그룹 현명한 최대를 들고 행을 참조하십시오.

  5. ==============================

    5.포스트 그레스에서는이 같은 array_agg 사용할 수 있습니다 :

    포스트 그레스에서는이 같은 array_agg 사용할 수 있습니다 :

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

    이렇게하면 각 고객의 가장 큰 구매의 ID를 제공 할 것입니다.

    참고로 몇 가지 :

  6. ==============================

    6.이 솔루션은 때문에 SubQs의 존재, 어윈 가리키는 매우 효율적이지 않다

    이 솔루션은 때문에 SubQs의 존재, 어윈 가리키는 매우 효율적이지 않다

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

    7.내가 (단지 PostgreSQL을)이 방법을 사용 https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

    내가 (단지 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 행을 무시

    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'

    그것은 첫 번째와 마지막 기능을 제공하는 포스트 그레스 확장이다; 분명히 더 빨리 위의 방법보다.

    당신이 (이 같은) 집계 함수를 사용하는 경우, 당신은 이미 주문 데이터를 필요없이 결과를 주문할 수 있습니다 :

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

    당신은 집계에서 맞는하다고 판단 물론 당신은 주문 및 필터 할 수 있습니다; 그것은 매우 강력한 구문이다.

  8. ==============================

    8.매우 빠른 솔루션

    매우 빠른 솔루션

    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);
  9. ==============================

    9.쿼리 :

    쿼리 :

    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 (고객 ID)하자 주어진 총 (T는 (고객이 ID가)) 우리가 있음을 증명해야 가장 높은 것을 증명하려면 우리는 하나 입증 할

    또는

    첫 번째 방법은 내가 정말 좋아하지 않는 그 이름에 대한 모든 기록을 얻기 위해 우리를 필요로합니다.

    두 번째는이보다 더 레코드 이상있을 수 없다 말할 수있는 현명한 방법이 필요합니다.

    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

    그리고 그것은 우리가 필요로하는 대답.

  10. ==============================

    10.SQL 서버에서는이 작업을 수행 할 수 있습니다

    SQL 서버에서는이 작업을 수행 할 수 있습니다

    SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY customer
ORDER BY total DESC) AS StRank, *
FROM Purchases) n
WHERE StRank = 1

    설명 : 고객에 근거하여 수행하여 여기에 그룹 한 다음 문자열로 총 각 같은 그룹이 주어진다 일련 번호로 주문 우리는 그 StRank가 1 첫번째 1 개 고객을 복용

  11. ==============================

    11.PostgreSQL을, U-SQL, IBM DB2, 구글 BigQuery의 SQL에 대한 사용 ARRAY_AGG 기능 :

    PostgreSQL을, U-SQL, IBM DB2, 구글 BigQuery의 SQL에 대한 사용 ARRAY_AGG 기능 :

    SELECT customer, (ARRAY_AGG(id ORDER BY total DESC))[1], MAX(total)
FROM purchases
GROUP BY customer
  12. ==============================

    12.PostgreSQL을, 또 다른 가능성은 SELECT DISTINCT와 조합 FIRST_VALUE 윈도우 함수를 사용하는 것이다 :

    PostgreSQL을, 또 다른 가능성은 SELECT DISTINCT와 조합 FIRST_VALUE 윈도우 함수를 사용하는 것이다 :

    select distinct customer_id,
                first_value(row(id, total)) over(partition by customer_id order by total desc, id)
from            purchases;

    두 값이 동일한 집합에 의해 반환하므로 I는 복합 (ID, 총)을 만들었다. 당신은 물론 항상 ()를 두 번 FIRST_VALUE 적용 할 수 있습니다.

  13. ==============================

    13.솔루션 "데이터베이스에서 지원하는"허용 된 OMG 조랑말 '내 테스트에서 좋은 속도를 가지고있다.

    솔루션 "데이터베이스에서 지원하는"허용 된 OMG 조랑말 '내 테스트에서 좋은 속도를 가지고있다.

    여기에는 동일 접근 방식을 제공하지만, 더 완전하고 모든 데이터베이스 솔루션을 청소합니다. 넥타이는 (각 고객에 대해 하나의 행을 얻기 위해 고객 당 최대 총도 여러 레코드를 욕망을 가정)으로 간주하고, 다른 구매 분야 (예를 들면 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

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

    말:

  14. ==============================

    14.눈송이 / 테라 데이타 지원은 윈도 윙 기능 HAVING처럼 작동 절 자격 :

    눈송이 / 테라 데이타 지원은 윈도 윙 기능 HAVING처럼 작동 절 자격 :

    SELECT id, customer, total
FROM PURCHASES
QUALIFY ROW_NUMBER() OVER(PARTITION BY p.customer ORDER BY p.total DESC) = 1
  15. ==============================

    15.당신은 다음 하위 쿼리를 사용할 수 있습니다 :

    당신은 다음 하위 쿼리를 사용할 수 있습니다 :

    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

    이 하위 쿼리가 하나의 행보다 더 많은 반환하지 않아야합니다 : 당신은 당신이 한 제한 원하는 조건과 금액 = MAX (tf.amount를) 대체 할 수있다

    당신이 그런 일을하고 싶어한다면 당신은 아마 윈도우 함수를 찾고

  16. ==============================

    16.SQL Server의 경우 가장 효율적인 방법은 다음과 같습니다

    SQL Server의 경우 가장 효율적인 방법은 다음과 같습니다

    with
ids as ( --condition for split table into groups
    select i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i) 
) 
,src as ( 
    select * from yourTable where  <condition> --use this as filter for other conditions
)
,joined as (
    select tops.* from ids 
    cross apply --it`s like for each rows
    (
        select top(1) * 
        from src
        where CommodityId = ids.i 
    ) as tops
)
select * from joined

    및 사용되는 열에 대한 클러스터 된 인덱스를 생성하는 것을 잊지 마세요

    17. from https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 SQL 서버에서 하나의 텍스트 문자열에 여러 행의 텍스트를 연결하는?  (0) 2020.03.04
[SQL] 각 그룹의 마지막 레코드를 가져 - MySQL의  (0) 2020.03.04
[SQL] 2005 마이크로 소프트 SQL 서버에서 GROUP_CONCAT MySQL의 기능을 시뮬레이션?  (0) 2020.03.04
[SQL] 열에 최대 값 행만에서 SQL [중복]  (0) 2020.03.04
[SQL] 때 MySQL의에서 작은 따옴표, 큰 따옴표, 및 역 따옴표를 사용하는  (0) 2020.03.04
,

티스토리툴바