Chọn hàng đầu tiên trong mỗi nhóm GROUP BY?

1441
David Wolever 2010-09-27 15:23.

Như tiêu đề gợi ý, tôi muốn chọn hàng đầu tiên của mỗi nhóm hàng được nhóm với a GROUP BY.

Cụ thể, nếu tôi có một purchasesbảng trông như thế này:

SELECT * FROM purchases;

Đầu ra của tôi:

id | khách hàng | toàn bộ
--- + ---------- + ------
 1 | Joe | 5
 2 | Sally | 3
 3 | Joe | 2
 4 | Sally | 1

Tôi muốn truy vấn idmua hàng lớn nhất ( total) được thực hiện bởi mỗi người customer. Một cái gì đó như thế này:

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

Đầu ra mong đợi:

FIRST (id) | khách hàng | FIRST (tổng số)
---------- + ---------- + -------------
        1 | Joe | 5
        2 | Sally | 3

17 answers

1212
OMG Ponies 2010-09-27 15:27.

Trên Oracle 9.2+ (không phải 8i + như đã nêu ban đầu), 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

Được hỗ trợ bởi bất kỳ cơ sở dữ liệu nào:

Nhưng bạn cần thêm logic để phá vỡ mối quan hệ:

  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
1232
Erwin Brandstetter 2011-10-03 16:21.

Trong PostgreSQL, điều này thường đơn giản hơn và nhanh hơn (tối ưu hóa hiệu suất hơn bên dưới):

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

Hoặc ngắn hơn (nếu không rõ ràng) với số thứ tự của các cột đầu ra:

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

Nếu totalcó thể là NULL (không ảnh hưởng gì đến cả hai cách, nhưng bạn sẽ muốn khớp với các chỉ mục hiện có ):

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

Những điểm chính

DISTINCT ONlà một phần mở rộng PostgreSQL của tiêu chuẩn (chỉ DISTINCTtrong toàn bộ SELECTdanh sách được xác định).

Liệt kê bất kỳ số biểu thức nào trong DISTINCT ONmệnh đề, giá trị hàng kết hợp xác định các bản sao. Hướng dẫn sử dụng:

Rõ ràng, hai hàng được coi là khác biệt nếu chúng khác nhau ở ít nhất một giá trị cột. Giá trị rỗng được coi là bằng nhau trong so sánh này.

Tôi nhấn mạnh đậm.

DISTINCT ONcó thể được kết hợp với ORDER BY. Biểu thức hàng đầu trong ORDER BYphải nằm trong tập hợp các biểu thức trong DISTINCT ON, nhưng bạn có thể sắp xếp lại thứ tự giữa các biểu thức đó một cách tự do. Thí dụ.
Bạn có thể thêm các biểu thức bổ sungORDER BY để chọn một hàng cụ thể từ mỗi nhóm ngang hàng. Hoặc, như sách hướng dẫn đưa ra :

(Các) DISTINCT ONbiểu thức phải khớp với ORDER BY(các) biểu thức ngoài cùng bên trái . Các ORDER BYđiều khoản thông thường sẽ chứa thêm biểu thức (s) để xác định ưu tiên mong muốn các hàng trong mỗi DISTINCT ONnhóm.

Tôi đã thêm vào idmục cuối cùng để phá vỡ mối quan hệ:
"Chọn hàng có hàng nhỏ nhất idtừ mỗi nhóm chia sẻ hàng cao nhất total."

Để sắp xếp kết quả theo cách không đồng ý với thứ tự sắp xếp xác định đầu tiên cho mỗi nhóm, bạn có thể lồng truy vấn phía trên trong một truy vấn bên ngoài với một truy vấn khác ORDER BY. Thí dụ.

Nếu totalcó thể là NULL, bạn có thể muốn hàng có giá trị khác rỗng lớn nhất. Thêm NULLS LASTnhư đã chứng minh. Xem:

  • Sắp xếp theo cột ASC, nhưng giá trị NULL đầu tiên?

Các SELECTdanh sách không bị hạn chế bởi các biểu thức trong DISTINCT ONhoặc ORDER BYtrong bất kỳ cách nào. (Không cần thiết trong trường hợp đơn giản ở trên):

  • Bạn không cần phải bao gồm bất kỳ biểu thức nào trong DISTINCT ONhoặc ORDER BY.

  • Bạn có thể bao gồm bất kỳ biểu thức nào khác trong SELECTdanh sách. Đây là công cụ để thay thế các truy vấn phức tạp hơn nhiều bằng các truy vấn con và các hàm tổng hợp / cửa sổ.

Tôi đã thử nghiệm với Postgres phiên bản 8.3 - 13. Nhưng tính năng này đã có ít nhất kể từ phiên bản 7.1, vì vậy về cơ bản luôn luôn.

Mục lục

Các hoàn hảo chỉ mục cho các truy vấn trên sẽ là một chỉ số đa cột kéo dài cả ba cột trong phù hợp với trình tự và với phù hợp với thứ tự sắp xếp:

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

Có thể quá chuyên biệt. Nhưng hãy sử dụng nó nếu hiệu suất đọc cho truy vấn cụ thể là rất quan trọng. Nếu bạn có DESC NULLS LASTtrong truy vấn, hãy sử dụng cùng một chỉ mục để thứ tự sắp xếp khớp và chỉ mục có thể áp dụng được.

Hiệu quả / Tối ưu hóa hiệu suất

Cân nhắc chi phí và lợi ích trước khi tạo các chỉ mục phù hợp cho từng truy vấn. Tiềm năng của chỉ số trên phần lớn phụ thuộc vào phân phối dữ liệu .

Chỉ mục được sử dụng vì nó cung cấp dữ liệu được sắp xếp trước. Trong Postgres 9.2 trở lên, truy vấn cũng có thể được hưởng lợi từ việc quét chỉ chỉ mục nếu chỉ mục nhỏ hơn bảng bên dưới. Tuy nhiên, chỉ mục phải được quét toàn bộ.

Đối với một vài hàng cho mỗi khách hàng (số lượng trong cột cao customer), điều này rất hiệu quả. Thậm chí nhiều hơn như vậy nếu bạn vẫn cần đầu ra được sắp xếp. Lợi ích thu hẹp với số lượng hàng ngày càng tăng trên mỗi khách hàng.
Lý tưởng nhất là bạn có đủ work_memđể xử lý bước sắp xếp liên quan trong RAM và không bị tràn ra đĩa. Nhưng nói chung, đặt work_mem quá cao có thể có tác dụng phụ. Cân nhắc SET LOCALcho các truy vấn đặc biệt lớn. Tìm bao nhiêu bạn cần với EXPLAIN ANALYZE. Đề cập đến " Đĩa: " trong bước sắp xếp cho thấy cần thêm:

  • Tham số cấu hình work_mem trong PostgreSQL trên Linux
  • Tối ưu hóa truy vấn đơn giản bằng cách sử dụng ĐẶT HÀNG THEO ngày tháng và văn bản

Đối với nhiều hàng cho mỗi khách hàng (số lượng ít trong cột customer), quét chỉ mục lỏng lẻo (còn gọi là "quét bỏ qua") sẽ hiệu quả hơn (nhiều), nhưng điều đó không được triển khai cho đến Postgres 13. (Một triển khai cho quét chỉ chỉ mục có trong phát triển cho Postgres 14. Xem tại đây và đây .)
Hiện tại, có các kỹ thuật truy vấn nhanh hơn để thay thế cho điều này. Đặc biệt nếu bạn có một bàn riêng chứa những khách hàng duy nhất, đó là trường hợp sử dụng điển hình. Nhưng cũng có thể nếu bạn không:

  • Tối ưu hóa truy vấn GROUP BY để truy xuất hàng mới nhất cho mỗi người dùng
  • Tối ưu hóa truy vấn tối đa theo nhóm
  • Truy vấn N hàng liên quan cuối cùng trên mỗi hàng

Điểm chuẩn

Tôi đã có một điểm chuẩn đơn giản ở đây mà bây giờ đã lỗi thời. Tôi đã thay thế nó bằng một điểm chuẩn chi tiết trong câu trả lời riêng này .

145
Erwin Brandstetter 2016-01-11 20:05.

Điểm chuẩn

Kiểm tra các ứng cử viên thú vị nhất với Postgres 9.49.5 với một bảng nửa thực tế gồm 200k hàng trong purchases10k khác biệtcustomer_id ( trung bình 20 hàng cho mỗi khách hàng ).

Đối với Postgres 9.5, tôi đã chạy thử nghiệm thứ 2 với hiệu quả là 86446 khách hàng khác biệt. Xem bên dưới ( trung bình 2,3 hàng cho mỗi khách hàng ).

Thiết lập

Bảng chính

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

Tôi sử dụng một serial(ràng buộc PK được thêm vào bên dưới) và một số nguyên customer_idvì đó là một thiết lập điển hình hơn. Cũng được thêm vào some_columnđể tạo ra nhiều cột hơn.

Dữ liệu giả, PK, chỉ mục - một bảng điển hình cũng có một số bộ giá trị chết:

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 bảng - cho truy vấn cao cấp:

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;

Trong thử nghiệm thứ hai của tôi cho 9.5, tôi đã sử dụng thiết lập tương tự, nhưng với random() * 100000cách tạo customer_idđể chỉ nhận được vài hàng mỗi customer_id.

Kích thước đối tượng cho bảng purchases

Được tạo bằng một truy vấn được lấy từ câu trả lời có liên quan này:

  • Đo kích thước của một hàng trong bảng PostgreSQL
               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 |              |

Truy vấn

1. row_number()trong CTE, ( xem câu trả lời khác )

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;

  1. row_number() trong truy vấn con (tối ưu hóa của tôi)
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( xem câu trả lời khác )

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

4. rCTE với LATERALtruy vấn con ( xem tại đây )

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. customerbảng với LATERAL( xem tại đây )

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. array_agg()với ORDER BY( xem câu trả lời khác )

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

Các kết quả

Thời gian thực thi cho các truy vấn trên với EXPLAIN ANALYZE(và tắt tất cả các tùy chọn ), tốt nhất là 5 lần chạy .

Tất cả các truy vấn đã sử dụng một Index Chỉ Quét trên purchases2_3c_idx(trong số những bước khác). Một số trong số đó chỉ dành cho kích thước chỉ mục nhỏ hơn, những người khác hiệu quả hơn.

A. Postgres 9,4 với 200k hàng và ~ 20 hàng mỗi 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. Tương tự với 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. Tương tự như B., nhưng với ~ 2,3 hàng mỗi 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

Điểm chuẩn liên quan

Đây là một sản phẩm mới do thử nghiệm "ogr" với 10 triệu hàng và 60 nghìn "khách hàng" duy nhất trên Postgres 11.5 (hiện tại tính đến tháng 9 năm 2019). Kết quả vẫn phù hợp với những gì chúng ta đã thấy cho đến nay:

  • Cách thích hợp để truy cập hàng mới nhất cho từng số nhận dạng riêng lẻ?

Điểm chuẩn gốc (lỗi thời) từ năm 2011

Tôi đã chạy ba bài kiểm tra với PostgreSQL 9.1 trên một bảng đời thực gồm 65579 hàng và chỉ mục btree cột đơn trên mỗi cột trong số ba cột liên quan và mất thời gian thực thi tốt nhất trong 5 lần chạy.
So sánh truy vấn đầu tiên của @OMGPonies ( A) với giải pháp trênDISTINCT ON ( B):

  1. Chọn toàn bộ bảng, kết quả là 5958 hàng trong trường hợp này.
A: 567.218 ms
B: 386.673 ms
  1. Sử dụng điều kiện WHERE customer BETWEEN x AND ydẫn đến 1000 hàng.
A: 249.136 ms
B:  55.111 ms
  1. Chọn một khách hàng duy nhất với WHERE customer = x.
A:   0.143 ms
B:   0.072 ms

Thử nghiệm tương tự được lặp lại với chỉ số được mô tả trong câu trả lời khác

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
59
TMS 2013-06-27 22:38.

Đây là vấn đề phổ biến nhất-n-mỗi nhóm , đã có các giải pháp được kiểm tra tốt và được tối ưu hóa cao . Cá nhân tôi thích giải pháp kết hợp bên trái của Bill Karwin ( bài đăng gốc với rất nhiều giải pháp khác ).

Lưu ý rằng rất nhiều giải pháp cho vấn đề phổ biến này có thể được tìm thấy trong một trong những nguồn chính thức nhất, hướng dẫn sử dụng MySQL ! Xem Ví dụ về các Truy vấn Phổ biến :: Các Hàng có Số lượng Tối đa theo Nhóm của một Cột Nhất định .

31
Paul A Jungwirth 2014-08-28 08:14.

Trong Postgres, bạn có thể sử dụng array_aggnhư thế này:

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

Điều này sẽ cung cấp cho bạn idmua hàng lớn nhất của mỗi khách hàng.

Một số điều cần lưu ý:

  • array_agglà một hàm tổng hợp, vì vậy nó hoạt động với GROUP BY.
  • array_aggcho phép bạn chỉ định một phạm vi thứ tự cho riêng chính nó, vì vậy nó không hạn chế cấu trúc của toàn bộ truy vấn. Ngoài ra còn có cú pháp cho cách bạn sắp xếp NULL, nếu bạn cần làm điều gì đó khác với mặc định.
  • Khi chúng ta xây dựng mảng, chúng ta lấy phần tử đầu tiên. (Mảng Postgres được lập chỉ mục 1, không được lập chỉ mục 0).
  • Bạn có thể sử dụng array_aggtheo cách tương tự cho cột đầu ra thứ ba, nhưng max(total)đơn giản hơn.
  • Không giống như DISTINCT ON, việc sử dụng array_aggcho phép bạn giữ lại tài khoản của mình GROUP BY, trong trường hợp bạn muốn điều đó vì những lý do khác.
15
gyan 2013-06-18 08:02.

Giải pháp không hiệu quả lắm như Erwin đã chỉ ra, vì sự hiện diện của SubQs

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;
12
khaled_gomaa 2018-03-25 06:11.

Truy vấn:

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

LÀM THẾ NÀO ĐỂ LÀM ĐƯỢC! (Tôi đã từng ở đó)

Chúng tôi muốn đảm bảo rằng chúng tôi chỉ có tổng số tiền cao nhất cho mỗi lần mua hàng.


Một số nội dung lý thuyết (bỏ qua phần này nếu bạn chỉ muốn hiểu truy vấn)

Gọi Tổng là một hàm T (khách hàng, id) trong đó nó trả về một giá trị cho tên và id Để chứng minh rằng tổng cho trước (T (khách hàng, id)) là cao nhất, chúng ta phải chứng minh rằng chúng ta muốn chứng minh

  • ∀x T (khách hàng, id)> T (khách hàng, x) (tổng số này cao hơn tất cả các tổng số khác cho khách hàng đó)

HOẶC LÀ

  • ¬∃x T (khách hàng, id) <T (khách hàng, x) (không tồn tại tổng số cao hơn cho khách hàng đó)

Cách tiếp cận đầu tiên sẽ cần chúng tôi lấy tất cả các bản ghi cho tên mà tôi không thực sự thích.

Cái thứ hai sẽ cần một cách thông minh để nói rằng không thể có kỷ lục nào cao hơn kỷ lục này.


Quay lại SQL

Nếu chúng ta vẫn tham gia bảng trên tên và tổng số ít hơn bảng đã tham gia:

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

chúng tôi đảm bảo rằng tất cả các bản ghi có một bản ghi khác với tổng số cao hơn cho cùng một người dùng sẽ được tham gia:

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

Điều đó sẽ giúp chúng tôi lọc để có tổng số tiền cao nhất cho mỗi lần mua hàng mà không cần phân nhóm:

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

Và đó là câu trả lời mà chúng tôi cần.

10
matiu 2015-03-11 05:19.

Tôi sử dụng cách này (chỉ 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
);

Sau đó, ví dụ của bạn sẽ hoạt động gần như như sau:

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

CAVEAT: Nó bỏ qua các hàng NULL của


Chỉnh sửa 1 - Sử dụng tiện ích mở rộng postgres thay thế

Bây giờ tôi sử dụng cách này: http://pgxn.org/dist/first_last_agg/

Để cài đặt trên ubuntu 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'

Đó là một tiện ích mở rộng postgres cung cấp cho bạn các chức năng đầu tiên và cuối cùng; rõ ràng là nhanh hơn so với cách trên.


Chỉnh sửa 2 - Sắp xếp và lọc

Nếu bạn sử dụng các hàm tổng hợp (như các hàm này), bạn có thể sắp xếp các kết quả mà không cần dữ liệu đã được sắp xếp theo thứ tự:

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

Vì vậy, ví dụ tương đương, với đặt hàng sẽ là một cái gì đó như:

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

Tất nhiên, bạn có thể đặt hàng và lọc khi bạn cho là phù hợp với tổng thể; đó là cú pháp rất mạnh mẽ.

8
Alejandro Salamanca Mazuelo 2014-04-09 06:13.

Giải pháp rất nhanh

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

và thực sự rất nhanh nếu bảng được lập chỉ mục bởi id:

create index purchases_id on purchases (id);
8
Diwas Poudel 2018-12-30 06:12.

Trong SQL Server, bạn có thể thực hiện việc này:

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

Giải thích: Ở đây Group by được thực hiện trên cơ sở khách hàng và sau đó đặt hàng theo tổng số, sau đó mỗi nhóm như vậy được cấp số thứ tự là StRank và chúng tôi sẽ lấy ra 1 khách hàng đầu tiên có StRank là 1

8
Valentin Podkamennyi 2019-04-05 10:54.

Sử dụng ARRAY_AGGhàm cho PostgreSQL , U-SQL , IBM DB2 và Google BigQuery SQL :

SELECT customer, (ARRAY_AGG(id ORDER BY total DESC))[1], MAX(total)
FROM purchases
GROUP BY customer
3
Johnny Wong 2017-01-05 05:47.

Giải pháp "Được hỗ trợ bởi mọi cơ sở dữ liệu" của OMG Ponies được chấp nhận có tốc độ tốt từ thử nghiệm của tôi.

Ở đây tôi cung cấp một cách tiếp cận tương tự, nhưng hoàn thiện hơn và giải pháp cơ sở dữ liệu sạch hơn. Mối quan hệ được xem xét (giả sử mong muốn chỉ nhận được một hàng cho mỗi khách hàng, thậm chí nhiều bản ghi cho tổng số tối đa cho mỗi khách hàng) và các trường mua hàng khác (ví dụ: buy_payment_id) sẽ được chọn cho các hàng khớp thực sự trong bảng mua hàng.

Được hỗ trợ bởi bất kỳ cơ sở dữ liệu nào:

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

Truy vấn này khá nhanh, đặc biệt là khi có một chỉ số tổng hợp như (khách hàng, tổng số) trên bảng mua hàng.

Nhận xét:

  1. t1, t2 là bí danh truy vấn con có thể bị xóa tùy thuộc vào cơ sở dữ liệu.

  2. Lưu ý : using (...)mệnh đề hiện không được hỗ trợ trong MS-SQL và Oracle db kể từ lần chỉnh sửa này vào tháng 1 năm 2017. Bạn phải tự mở rộng nó thành ví dụ, on t2.id = purchase.idv.v. Cú pháp SỬ DỤNG hoạt động trong SQLite, MySQL và PostgreSQL.

3
Lukasz Szozda 2019-11-18 11:19.

Snowflake / Teradata hỗ trợ QUALIFYmệnh đề hoạt động giống như HAVINGđối với các hàm cửa sổ:

SELECT id, customer, total
FROM PURCHASES
QUALIFY ROW_NUMBER() OVER(PARTITION BY p.customer ORDER BY p.total DESC) = 1
2
pbillen 2019-12-10 02:49.

Trong PostgreSQL, một khả năng khác là sử dụng first_valuehàm cửa sổ kết hợp với SELECT DISTINCT:

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

Tôi đã tạo một tổng hợp (id, total), vì vậy cả hai giá trị đều được trả về bởi cùng một tổng hợp. Tất nhiên bạn luôn có thể áp dụng first_value()hai lần.

1
Eugen Konkov 2018-09-29 03:50.
  • Nếu bạn muốn chọn bất kỳ hàng nào (theo một số điều kiện cụ thể của bạn) từ tập hợp các hàng tổng hợp.

  • Nếu bạn muốn sử dụng một sum/avghàm tổng hợp ( ) khác ngoài max/min. Vì vậy, bạn không thể sử dụng manh mối vớiDISTINCT ON

Bạn có thể sử dụng truy vấn con tiếp theo:

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

Bạn có thể thay thế amount = MAX( tf.amount )bằng bất kỳ điều kiện nào bạn muốn với một hạn chế: Truy vấn con này không được trả về nhiều hơn một hàng

Nhưng nếu bạn muốn làm những điều như vậy, bạn có thể tìm kiếm các chức năng cửa sổ

1
BazSTR 2019-01-19 00:59.

Đối với SQl Server, cách hiệu quả nhất là:

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

và đừng quên tạo chỉ mục nhóm cho các cột đã sử dụng

1
uncle bob 2020-07-17 17:40.

Theo cách này, nó hoạt động đối với tôi:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article
              GROUP BY s2.article)
ORDER BY article;

Chọn giá cao nhất trên mỗi bài viết

Related questions

MORE COOL STUFF

Cate Blanchett chia tay chồng sau 3 ngày bên nhau và vẫn kết hôn với anh ấy 25 năm sau

Cate Blanchett chia tay chồng sau 3 ngày bên nhau và vẫn kết hôn với anh ấy 25 năm sau

Cate Blanchett đã bất chấp những lời khuyên hẹn hò điển hình khi cô gặp chồng mình.

Tại sao Michael Sheen là một diễn viên phi lợi nhuận

Tại sao Michael Sheen là một diễn viên phi lợi nhuận

Michael Sheen là một diễn viên phi lợi nhuận nhưng chính xác thì điều đó có nghĩa là gì?

Hallmark Star Colin Egglesfield Các món ăn gây xúc động mạnh đối với người hâm mộ tại RomaDrama Live! [Loại trừ]

Hallmark Star Colin Egglesfield Các món ăn gây xúc động mạnh đối với người hâm mộ tại RomaDrama Live! [Loại trừ]

Ngôi sao của Hallmark Colin Egglesfield chia sẻ về những cuộc gặp gỡ với người hâm mộ ly kỳ tại RomaDrama Live! cộng với chương trình INSPIRE của anh ấy tại đại hội.

Tại sao bạn không thể phát trực tuyến 'chương trình truyền hình phía Bắc'

Tại sao bạn không thể phát trực tuyến 'chương trình truyền hình phía Bắc'

Bạn sẽ phải phủi sạch đầu đĩa Blu-ray hoặc DVD để xem tại sao Northern Exposure trở thành một trong những chương trình nổi tiếng nhất của thập niên 90.

Where in the World Are You? Take our GeoGuesser Quiz

Where in the World Are You? Take our GeoGuesser Quiz

The world is a huge place, yet some GeoGuessr players know locations in mere seconds. Are you one of GeoGuessr's gifted elite? Take our quiz to find out!

8 công dụng tuyệt vời của Baking Soda và Giấm

8 công dụng tuyệt vời của Baking Soda và Giấm

Bạn biết đấy, hai sản phẩm này là nguồn điện để làm sạch, riêng chúng. Nhưng cùng với nhau, chúng có một loạt công dụng hoàn toàn khác.

Hạn hán, biến đổi khí hậu đe dọa tương lai của thủy điện Hoa Kỳ

Hạn hán, biến đổi khí hậu đe dọa tương lai của thủy điện Hoa Kỳ

Thủy điện rất cần thiết cho lưới điện của Hoa Kỳ, nhưng nó chỉ tạo ra năng lượng khi có nước di chuyển. Bao nhiêu nhà máy thủy điện có thể gặp nguy hiểm khi các hồ và sông cạn kiệt?

Quyên góp tóc của bạn để giúp giữ nước sạch của chúng tôi

Quyên góp tóc của bạn để giúp giữ nước sạch của chúng tôi

Tóc tỉa từ các tiệm và các khoản quyên góp cá nhân có thể được tái sử dụng như những tấm thảm thấm dầu và giúp bảo vệ môi trường.

Làm thế nào để xây dựng một quả địa cầu từ Scratch

Làm thế nào để xây dựng một quả địa cầu từ Scratch

Thời gian, sự kiên nhẫn, thời gian, sự cống hiến và thời gian chỉ là một vài trong số những thứ mà Peter Bellerby cần để thành lập và sau đó điều hành Bellerby & Co. Globemakers, một trong những công ty duy nhất trên Trái đất vẫn sản xuất các quả địa cầu bằng tay.

Năm giai đoạn đau buồn sau khi mất việc làm

Năm giai đoạn đau buồn sau khi mất việc làm

Đó là một ngày thứ Bảy, máy bay của tôi hạ cánh, và tôi đã sẵn sàng để thư giãn trong một kỳ nghỉ cuối tuần ngắn ngủi, khi một email đến trên điện thoại của tôi. Tôi đã mất việc.

Giữ an toàn cho danh tính của bạn với một cảnh báo đóng băng hoặc gian lận tín dụng

Giữ an toàn cho danh tính của bạn với một cảnh báo đóng băng hoặc gian lận tín dụng

Nếu bạn đã từng bị đánh cắp danh tính của mình, bạn biết đó là một trải nghiệm đáng sợ và căng thẳng. Một cách không phổ biến để ngăn chặn nó? Tín dụng bị đóng băng.

Buổi ra mắt phần 3 của The Good Place có một học sinh mới: Khán giả

Buổi ra mắt phần 3 của The Good Place có một học sinh mới: Khán giả

Eleanor (Kristen Bell) dường như đã tình nguyện cho chúng tôi làm vật tưởng nhớ. NBC's The Good Place là một chương trình thích thử thách tốt.

Nicky Hilton Forced to Borrow Paris' 'I Love Paris' Sweatshirt After 'Airline Loses All [My] Luggage'

Nicky Hilton Forced to Borrow Paris' 'I Love Paris' Sweatshirt After 'Airline Loses All [My] Luggage'

Nicky Hilton Rothschild's luggage got lost, but luckily she has an incredible closet to shop: Sister Paris Hilton's!

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa. Từ Hollywood đến New York và mọi nơi ở giữa, hãy xem các ngôi sao yêu thích của bạn đang làm gì!

17 tuổi bị đâm chết trong khi 4 người khác bị thương trong một cuộc tấn công bằng dao trên sông Wisconsin

17 tuổi bị đâm chết trong khi 4 người khác bị thương trong một cuộc tấn công bằng dao trên sông Wisconsin

Các nhà điều tra đang xem xét liệu nhóm và nghi phạm có biết nhau trước vụ tấn công hay không

Thanh thiếu niên, Gia đình Florida Hội đồng quản trị trường học về Luật 'Không nói đồng tính': 'Buộc chúng tôi tự kiểm duyệt'

Thanh thiếu niên, Gia đình Florida Hội đồng quản trị trường học về Luật 'Không nói đồng tính': 'Buộc chúng tôi tự kiểm duyệt'

Vụ kiện, nêu tên một số học khu, lập luận rằng dự luật "Không nói đồng tính" được ban hành gần đây của Florida "có hiệu quả im lặng và xóa bỏ học sinh và gia đình LGBTQ +"

Đường băng hạ cánh

Đường băng hạ cánh

Cuối hè đầu thu là mùa hoài niệm. Những chiếc đèn đường chiếu ánh sáng của chúng qua những con đường đẫm mưa, và những chiếc lá dưới chân - màu đỏ cam tắt trong bóng chạng vạng - là lời nhắc nhở về những ngày đã qua.

Hãy tưởng tượng tạo ra một chiến lược nội dung thực sự CHUYỂN ĐỔI. Nó có thể.

Hãy tưởng tượng tạo ra một chiến lược nội dung thực sự CHUYỂN ĐỔI. Nó có thể.

Vào năm 2021, tôi khuyến khích bạn suy nghĩ lại mọi thứ bạn biết về khách hàng mà bạn phục vụ và những câu chuyện bạn kể cho họ. Lùi lại.

Sự mất mát của voi ma mút đã mở ra trái tim tôi để yêu

Sự mất mát của voi ma mút đã mở ra trái tim tôi để yêu

Vào ngày sinh nhật thứ 9 của Felix The Cat, tôi nhớ về một trong những mất mát lớn nhất trong cuộc đời trưởng thành của tôi - Sophie của tôi vào năm 2013. Tôi đã viết bài luận này và chia sẻ nó trên nền tảng này một thời gian ngắn vào năm 2013.

Khi bạn không thể trở thành người mà Internet muốn bạn trở thành

Khi bạn không thể trở thành người mà Internet muốn bạn trở thành

Tôi ghét từ "tàu đắm". Mọi người cảm thấy thoải mái trong la bàn đạo đức của riêng mình, và khi làm như vậy, họ thấy mình vượt qua sự phán xét.

Language