Pertanyaan Pilih baris pertama di setiap grup GROUP BY?


Seperti judulnya, saya ingin memilih baris pertama dari setiap set baris yang dikelompokkan dengan GROUP BY.

Khususnya, jika saya punya purchases tabel yang terlihat seperti ini:

SELECT * FROM purchases;

Keluaran saya:

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

Saya ingin bertanya tentang id pembelian terbesar (total) dibuat oleh masing-masing customer. Sesuatu seperti ini:

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

Output yang diharapkan:

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

896
2017-09-27 01:23


asal


Jawaban:


Pada Oracle 9.2+ (bukan 8i + asalnya dinyatakan), 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

Didukung oleh database apa pun:

Tetapi Anda perlu menambahkan logika untuk memutuskan ikatan:

  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

800
2017-09-27 01:27



Di PostgreSQL ini biasanya lebih sederhana dan lebih cepat (optimasi kinerja lebih lanjut di bawah):

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

Atau lebih pendek (jika tidak jelas) dengan nomor ordinal kolom output:

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

Jika total dapat NULL (tidak akan merugikan, namun Anda ingin mencocokkan indeks yang ada):

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

Poin utama

  • DISTINCT ON adalah ekstensi PostgreSQL dari standar (hanya di mana DISTINCT secara keseluruhan SELECT daftar didefinisikan).

  • Cantumkan sejumlah ekspresi dalam DISTINCT ON klausa, nilai baris gabungan mendefinisikan duplikat. Manual:

    Jelas, dua baris dianggap berbeda jika mereka berbeda setidaknya   satu nilai kolom. Nilai nol dianggap sama dalam perbandingan ini.

    Tambang penekanan saya.

  • DISTINCT ON dapat dikombinasikan dengan ORDER BY. Memimpin ekspresi harus sesuai dengan yang utama DISTINCT ON ekspresi dalam urutan yang sama. Anda dapat menambahkan tambahan ekspresi untuk ORDER BY untuk memilih baris tertentu dari setiap kelompok teman sebaya. saya tambahkan id sebagai barang terakhir untuk memutuskan ikatan:

    "Pilih baris dengan yang terkecil id dari masing-masing kelompok berbagi yang tertinggi total. "

    Jika total bisa NULL, kamu paling mungkin ingin baris dengan nilai non-null terbesar. Menambahkan NULLS LAST seperti yang ditunjukkan. Detail:

  • Itu SELECT daftar tidak dibatasi oleh ekspresi dalam DISTINCT ON atau ORDER BY dengan cara apapun. (Tidak diperlukan dalam kasus sederhana di atas):

    • Kamu tidak perlu termasuk salah satu ekspresi di DISTINCT ON atau ORDER BY.

    • Kamu bisa sertakan ekspresi lainnya di SELECT daftar. Ini berperan untuk menggantikan kueri yang jauh lebih rumit dengan subkueri dan fungsi agregat / jendela.

  • Saya menguji dengan Postgres versi 8.3 - 10. Namun fitur ini sudah ada setidaknya sejak versi 7.1, jadi pada dasarnya selalu.

Indeks

Itu sempurna indeks untuk kueri di atas akan menjadi a indeks multi-kolom mencakup ketiga kolom dalam urutan yang cocok dan dengan urutan sortir yang cocok:

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

Mungkin terlalu khusus untuk aplikasi dunia nyata. Tetapi gunakan jika membaca kinerja sangat penting. Jika Anda memiliki DESC NULLS LAST dalam kueri, gunakan yang sama dalam indeks sehingga Postgres tahu urutan sortir cocok.

Efektivitas / Pengoptimalan kinerja

Anda harus mempertimbangkan biaya dan manfaat sebelum Anda membuat indeks yang disesuaikan untuk setiap kueri. Potensi indeks di atas sangat bergantung pada distribusi data.

Indeks ini digunakan karena memberikan data yang telah diurutkan sebelumnya, dan dalam Postgres 9.2 atau yang lebih baru, kueri juga dapat memperoleh manfaat dari indeks hanya memindai jika indeks lebih kecil dari tabel yang mendasari. Namun, indeks harus dipindai secara keseluruhan.

Benchmark

Saya memiliki patokan sederhana di sini yang sudah ketinggalan zaman sekarang. Saya menggantinya dengan patokan rinci dalam jawaban terpisah ini.


809
2017-10-03 02:21



Benchmark

Menguji kandidat yang paling menarik dengan Postgres 9.4 dan 9.5 dengan meja realistis setengah 200 ribu baris di purchases dan 10 ribu berbeda customer_id (rata-rata 20 baris per pelanggan).

Untuk Postgres 9.5 Saya menjalankan tes kedua dengan efektif 86446 pelanggan yang berbeda. Lihat di bawah (rata-rata 2,3 baris per pelanggan).

Mempersiapkan

Meja utama

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

Saya menggunakan serial (Kendala PK ditambahkan di bawah) dan sebuah integer customer_id karena itu pengaturan yang lebih khas. Juga ditambahkan some_column untuk menebus kolom yang biasanya lebih banyak.

Data tiruan, PK, indeks - tabel khas juga memiliki beberapa tupel mati:

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 tabel - untuk kueri unggul

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;

Di dalam saya tes kedua untuk 9.5 saya menggunakan pengaturan yang sama, tetapi dengan random() * 100000 untuk menghasilkan customer_id untuk mendapatkan hanya beberapa baris per customer_id.

Ukuran objek untuk tabel purchases

Dihasilkan dengan pertanyaan ini.

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

Pertanyaan

1 row_number() di CTE, (lihat jawaban lainnya)

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() di subquery (pengoptimalan saya)

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 (lihat jawaban lainnya)

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

4. rCTE dengan LATERAL subquery (Lihat disini)

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 meja dengan LATERAL (Lihat disini)

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() dengan ORDER BY (lihat jawaban lainnya)

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

Hasil

Waktu eksekusi untuk kueri di atas dengan EXPLAIN ANALYZE (dan semua opsi mati), terbaik dari 5 berjalan.

Semua queries digunakan Indeks Hanya Pindai di purchases2_3c_idx (di antara langkah-langkah lain). Beberapa dari mereka hanya untuk ukuran indeks yang lebih kecil, yang lain lebih efektif.

A. Postgres 9.4 dengan 200 ribu baris dan ~ 20 per 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. Sama dengan 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. Sama seperti B., tetapi dengan ~ 2.3 baris per 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

Patokan asli (usang) dari 2011

Saya menjalankan tiga tes dengan PostgreSQL 9.1 pada tabel kehidupan nyata 65579 baris dan indeks btree satu kolom pada masing-masing dari tiga kolom yang terlibat dan mengambil yang terbaik waktu eksekusi dari 5 berjalan.
Perbandingan @OMGPonies ' pertanyaan pertama (A) ke atas DISTINCT ON larutan (B):

  1. Pilih seluruh tabel, menghasilkan 5958 baris dalam kasus ini.

    A: 567.218 ms
    B: 386.673 ms
    
  2. Gunakan kondisi WHERE customer BETWEEN x AND y menghasilkan 1000 baris.

    A: 249.136 ms
    B:  55.111 ms
    
  3. Pilih satu pelanggan dengan WHERE customer = x.

    A:   0.143 ms
    B:   0.072 ms
    

Tes yang sama diulang dengan indeks yang dijelaskan dalam jawaban lainnya

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


82
2018-01-11 06:05



Ini biasa terjadi  masalah, yang sudah teruji dan sangat baik solusi yang dioptimalkan. Secara pribadi saya lebih suka kiri bergabung dengan solusi oleh Bill Karwin (itu posting asli dengan banyak solusi lain).

Perhatikan bahwa banyak solusi untuk masalah umum ini dapat secara mengejutkan ditemukan di salah satu sumber resmi, Panduan MySQL! Lihat Contoh Pertanyaan Umum :: Baris Memegang Grup-bijaksana Maksimum dari Kolom Tertentu.


37
2018-06-27 08:38



Dalam Postgres yang dapat Anda gunakan array_agg seperti ini:

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

Ini akan memberi Anda id setiap pembelian terbesar pelanggan.

Beberapa hal yang perlu diperhatikan:

  • array_agg adalah fungsi agregat, sehingga berfungsi dengan GROUP BY.
  • array_agg memungkinkan Anda menentukan pemesanan yang mencakup hanya untuk dirinya sendiri, sehingga tidak membatasi struktur dari keseluruhan permintaan. Ada juga sintaks untuk bagaimana Anda menyortir NULL, jika Anda perlu melakukan sesuatu yang berbeda dari default.
  • Setelah kami membangun larik, kami mengambil elemen pertama. (Larik postgres adalah 1-indeks, bukan 0-indeks).
  • Anda bisa menggunakannya array_agg dengan cara yang sama untuk kolom output ketiga Anda, tetapi max(total) lebih sederhana.
  • Tidak seperti DISTINCT ON, menggunakan array_agg memungkinkan Anda mempertahankan GROUP BY, jika Anda menginginkannya karena alasan lain.

20
2017-08-27 18:14



Solusinya tidak terlalu efisien seperti yang ditunjukkan oleh Erwin, karena adanya SubQs

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

11
2018-06-17 18:02



Saya menggunakan cara ini (hanya 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
);

Maka contoh Anda harus bekerja hampir dengan adanya:

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

CAVEAT: Ini mengabaikan baris NULL


Edit 1 - Gunakan ekstensi postgres sebagai gantinya

Sekarang saya menggunakan cara ini: http://pgxn.org/dist/first_last_agg/

Untuk menginstal di 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'

Ini adalah ekstensi postgres yang memberi Anda fungsi pertama dan terakhir; ternyata lebih cepat dari cara di atas.


Edit 2 - Memesan dan memfilter

Jika Anda menggunakan fungsi agregat (seperti ini), Anda dapat memesan hasil, tanpa harus memiliki data yang sudah dipesan:

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

Jadi contoh yang setara, dengan memesan akan menjadi sesuatu seperti:

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

Tentu saja Anda dapat memesan dan memfilter sebagaimana yang Anda anggap cocok dalam kelompok agregasi; itu sintaks yang sangat kuat.


6
2018-03-10 15:19



Solusi yang sangat cepat

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

dan sangat cepat jika tabel diindeks oleh id:

create index purchases_id on purchases (id);

5
2018-04-08 16:13



Kueri:

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

BAGAIMANA CARA KERJANYA! (Aku pernah disana)

Kami ingin memastikan bahwa kami hanya memiliki total tertinggi untuk setiap pembelian.


Beberapa Barang Teoretis (lewati bagian ini jika Anda hanya ingin memahami kueri)

Biarkan Total menjadi fungsi T (pelanggan, id) di mana ia mengembalikan nilai yang diberikan nama dan id Untuk membuktikan bahwa total yang diberikan (T (pelanggan, id)) adalah yang tertinggi yang harus kita buktikan Kami ingin membuktikannya

  • ∀x T (pelanggan, id)> T (pelanggan, x) (jumlah ini lebih tinggi dari yang lainnya total untuk pelanggan itu)

ATAU

  • ¬∃x T (pelanggan, id) <T (pelanggan, x) (tidak ada jumlah yang lebih tinggi untuk pelanggan itu)

Pendekatan pertama akan membutuhkan kita untuk mendapatkan semua catatan untuk nama itu yang sebenarnya tidak saya sukai.

Yang kedua akan membutuhkan cara cerdas untuk mengatakan tidak ada catatan yang lebih tinggi dari ini.


Kembali ke SQL

Jika kita meninggalkan bergabung dengan tabel pada nama dan total menjadi kurang dari tabel yang tergabung:

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

kami memastikan bahwa semua catatan yang memiliki catatan lain dengan jumlah yang lebih tinggi untuk pengguna yang sama untuk bergabung:

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

Itu akan membantu kami memfilter untuk total tertinggi untuk setiap pembelian tanpa pengelompokan yang diperlukan:

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

Dan itulah jawaban yang kami butuhkan.


5
2018-03-24 16:11



Solusi "Didukung oleh database" OMG Ponies yang diterima memiliki kecepatan yang baik dari pengujian saya.

Di sini saya memberikan solusi dengan pendekatan yang sama, tetapi lebih lengkap dan bersih dari basis data manapun. Hubungan dianggap (berasumsi bahwa keinginan untuk hanya mendapatkan satu baris untuk setiap pelanggan, bahkan beberapa catatan untuk jumlah maksimum per pelanggan), dan bidang pembelian lainnya (mis. Purchase_payment_id) akan dipilih untuk baris pencocokan nyata dalam tabel pembelian.

Didukung oleh database apa pun:

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

Kueri ini cukup cepat terutama ketika ada indeks gabungan seperti (pelanggan, total) pada tabel pembelian.

Ucapan:

  1. t1, t2 adalah alias subquery yang dapat dihapus tergantung pada database.

  2. Peringatan: the using (...) klausa saat ini tidak didukung di MS-SQL dan Oracle db pada hasil edit ini pada Jan 2017. Anda harus memperluasnya sendiri ke misalnya. on t2.id = purchase.id dll. Sintaks USING bekerja di SQLite, MySQL, dan PostgreSQL.


2
2018-01-04 15:47