Pilih baris pertama di masing-masing KELOMPOK DENGAN kelompok?
Seperti judulnya, saya'd suka pilih baris pertama dari setiap baris yang dikelompokkan dengan GROUP BY
.
Secara khusus, jika saya've punya pembelian
meja yang terlihat seperti ini:
SELECT * FROM purchases;
My Output:
id | pelanggan | total ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
I'd seperti untuk query untuk id
dari pembelian terbesar (total
) yang dibuat oleh masing-masing pelanggan
. Sesuatu seperti ini:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Output Yang Diharapkan:
PERTAMA(id) | pelanggan | PERTAMA(total) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3
1194
14
Di PostgreSQL ini adalah biasanya sederhana dan lebih cepat (lebih optimasi kinerja di bawah ini):
Atau lebih pendek (jika tidak jelas) dengan nomor urut dari kolom output:Jika
###Poin utama - [**`BERBEDA`**][1] adalah PostgreSQL ekstensi standar (di mana hanya `BERBEDA` secara keseluruhan `PILIH` daftar didefinisikan). - Daftar nomor ekspresi di `BERBEDA` klausa, gabungan turut mendefinisikan nilai duplikat. [Petunjuk:][2] > Jelas, dua baris yang dianggap berbeda jika mereka berbeda dalam setidaknya > satu kolom nilai. **Nilai-nilai Null dianggap sama dalam perbandingan ini.** Bold penekanan dari saya. - `BERBEDA` dapat dikombinasikan dengan **`ORDER BY`**. Terkemuka ekspresi harus sesuai terkemuka `BERBEDA` ekspresi dalam urutan yang sama. Anda dapat menambahkan *tambahan* ekspresi untuk `ORDER BY` untuk memilih baris tertentu dari masing-masing kelompok teman sebaya. Saya menambahkan `id` sebagai item terakhir untuk memecah ikatan: *"Memilih baris dengan terkecil `id` dari masing-masing kelompok sharing tertinggi `total`."* Untuk memesan hasil dengan cara yang tidak setuju dengan menentukan urutan pertama per kelompok, anda dapat menuliskan pertanyaan di atas di luar query dengan yang lain `ORDER BY`. Seperti: - https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104 - Jika `total` dapat NULL, anda *mungkin* ingin turut terbesar dengan nilai non-null. Tambahkan **`NULLS TERAKHIR`** seperti yang ditunjukkan. Rincian: - https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492 - **`PILIH` daftar** tidak dibatasi oleh ekspresi pada `BERBEDA` atau `ORDER BY` dengan cara apapun. (Tidak diperlukan dalam kasus sederhana di atas): - Anda *don't harus* termasuk salah satu ekspresi dalam `BERBEDA` atau `ORDER BY`. - Anda *bisa* memiliki ekspresi lain di `PILIH` daftar. Ini adalah berperan untuk menggantikan jauh lebih kompleks query dengan subqueries dan agregat / jendela fungsi. - Aku diuji dengan Postgres versi 8.3 – 12. Tetapi fitur ini telah ada setidaknya sejak versi 7.1, jadi pada dasarnya selalu. ##Indeks Yang *ideal* indeks untuk query di atas akan menjadi [multi-kolom index][3] yang mencakup semua tiga kolom dalam pencocokan berurutan dan sesuai dengan urutan:total
dapat NULL (tidak't menyakiti dengan cara baik, tapi anda'll ingin mencocokkan indeks yang ada):Mungkin terlalu khusus. Tapi menggunakannya jika baca kinerja untuk query tertentu adalah sangat penting. Jika anda memiliki
DESC NULLS TERAKHIR
dalam query, menggunakan yang sama dalam indeks sehingga urutan pertandingan dan indeks berlaku.Efektivitas / Kinerja optimasi
Menimbang biaya dan manfaat sebelum menciptakan disesuaikan indeks untuk setiap query. Potensi di atas indeks sebagian besar tergantung pada data distribusi. Indeks ini digunakan karena memberikan pre-data diurutkan. Di Postgres 9.2 atau lambat query juga bisa mendapatkan keuntungan dari aplikasi indeks hanya scan jika indeks lebih kecil dari tabel yang mendasari. Indeks harus dipindai secara keseluruhan, meskipun.
pelanggan
), ini sangat efisien. Bahkan lebih jadi jika anda perlu diurutkan keluaran pula. Manfaat menyusut dengan meningkatnya jumlah baris per pelanggan. Idealnya, anda memiliki cukupwork_mem
untuk proses yang terlibat mengurutkan langkah dalam RAM dan tidak tumpah ke disk. Tapi umumnya settingwork_mem
terlalu tinggi dapat memiliki efek yang merugikan. Pertimbangkan UNTUKLOKAL
untuk sangat besar pertanyaan. Menemukan berapa banyak yang anda butuhkan denganJELASKAN MENGANALISIS
. Menyebutkan "Disk:" dalam mengurutkan langkah menunjukkan kebutuhan untuk lebih lanjut:pelanggan
), sebuah longgar index scan (yang.k.a. "skip scan") akan menjadi (jauh) lebih efisien, tetapi yang's tidak dilaksanakan sampai dengan Postgres 12. (Sebuah implementasi untuk indeks-hanya memindai dalam pembangunan untuk Postgres 13. Lihat di sini dan di sini.) Untuk sekarang, ada lebih cepat query teknik untuk pengganti ini. Khususnya jika anda memiliki sebuah meja terpisah memegang pelanggan yang unik, yang khas use case. Tetapi juga jika anda don't:Acuan
Saya punya patokan sederhana berikut yang sudah usang sekarang. Saya menggantinya dengan acuan rinci ini yang terpisah jawaban.
Pada Oracle 9.2+ (tidak 8i+ sebagai awalnya dinyatakan), SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 3.0+, Teradata, Sybase, Vertica:
Didukung oleh database:
Tapi anda perlu untuk menambahkan logika untuk memecahkan ikatan:
Acuan
Pengujian yang paling menarik calon dengan Postgres 9.4 dan 9.5 dengan setengah realistis tabel 200k baris di
pembelian
dan 10k berbedacustomer_id
(avg. 20 baris per pelanggan).Untuk Postgres 9.5 aku berlari 2 tes dengan efektif 86446 berbeda pelanggan. Lihat di bawah ini (avg. 2.3 baris per pelanggan).
Setup
Tabel utama
Saya menggunakan
serial
(PK kendala ditambahkan di bawah) dan integercustomer_id
karena itu's yang lebih khas setup. Juga ditambahkansome_column
untuk menebus biasanya lebih kolom.Data Dummy, PK, indeks khas meja juga memiliki beberapa mati tupel:
pelanggan
table - untuk segala querySaya tes kedua untuk 9.5 saya menggunakan setup yang sama, tetapi dengan
random() * 100000
menghasilkancustomer_id
untuk mendapatkan hanya beberapa baris percustomer_id
.Objek ukuran untuk tabel
pembelian
Dihasilkan dengan query ini.
Pertanyaan
1.
row_number()
di CTE, (lihat jawaban lain)2.
row_number()
dalam subquery (saya optimasi)3.
BERBEDA
(lihat jawaban lain)4. rCTE dengan
LATERAL
subquery (lihat di sini)5.
pelanggan
tabel denganLATERAL
(lihat di sini)6.
array_agg()
denganORDER BY
(lihat jawaban lain)Hasil
Waktu eksekusi untuk di atas query dengan
JELASKAN MENGANALISIS
(dan semua pilihan off), terbaik dari 5 berjalan.Semua query yang digunakan Indeks Hanya Memindai pada
purchases2_3c_idx
(di antara langkah-langkah lain). Beberapa dari mereka hanya untuk ukuran yang lebih kecil dari index, yang lain lebih efektif.A. Postgres 9.4 dengan 200k baris dan ~ 20 per
customer_id
B. sama dengan Postgres 9.5
C. Sama seperti B., tapi dengan ~ 2.3 baris per
customer_id
Terkait tolok ukur
Berikut ini adalah baru dengan "ogr" pengujian dengan 10M baris dan 60k unik "pelanggan" di Postgres 11.5 (saat ini sebagai Sep. 2019). Hasil tersebut masih sejalan dengan apa yang telah kita lihat sejauh ini:
Asli (usang) acuan dari 2011
Aku berlari tiga tes dengan PostgreSQL 9.1 pada kehidupan nyata tabel 65579 baris dan satu kolom btree indeks pada masing-masing dari tiga kolom yang terlibat dan mengambil yang terbaik waktu pelaksanaan 5 berjalan. Membandingkan @OMGPonies' pertama query (
A
) untuk di atasBERBEDA
solusi (B
):A: ms 567.218 B: 386.673 ms
DIMANA pelanggan ANTARA x DAN y
yang mengakibatkan 1000 baris.A: ms 249.136 B: 55.111 ms
di MANA pelanggan = x
.A: ms 0.143 B: 0.072 ms
Tes yang sama berulang-ulang dengan indeks dijelaskan dalam jawaban lain
Ini adalah umum [tag:terbesar-n-per-group] masalah ini, yang telah diuji dengan baik dan sangat dioptimalkan solusi. Secara pribadi saya lebih suka kiri bergabung solusi oleh Bill Karwin (asli posting dengan banyak solusi lain).
Perhatikan bahwa banyak solusi untuk masalah umum ini dapat mengejutkan ditemukan di salah satu dari sebagian besar sumber-sumber resmi, MySQL manual! Lihat contoh-Contoh Pertanyaan Umum :: Baris Holding Kelompok-bijak Maksimum dari sebuah Kolom Tertentu.
Di Postgres anda dapat menggunakan
array_agg
seperti ini:Ini akan memberikan anda
id
masing-masing pelanggan's pembelian terbesar.Beberapa hal yang perlu diperhatikan:
array_agg
adalah sebuah fungsi agregat, sehingga bekerja denganGROUP BY
.array_agg
memungkinkan anda menentukan pemesanan scoped hanya itu sendiri, jadi itu tidak jadi't membatasi struktur keseluruhan query. Ada juga sintaks untuk cara mengurutkan NULLs, jika anda perlu untuk melakukan sesuatu yang berbeda dari default.array_agg
dengan cara yang sama untuk ketiga kolom output, tetapimax(total)
lebih sederhana.BERBEDA
, menggunakanarray_agg
memungkinkan anda tetapGROUP BY
, jika anda ingin bahwa untuk alasan lain.Solusi ini sangat tidak efisien seperti yang ditunjukkan oleh Erwin, karena kehadiran SubQs
Sangat cepat solusi
dan benar-benar sangat cepat jika tabel diindeks oleh id:
Saya menggunakan cara ini (postgresql saja): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29
Kemudian misalnya anda harus bekerja hampir seperti ini:
PERINGATAN: mengabaikan's NULL baris
Edit 1 - Menggunakan postgres ekstensi bukan
Sekarang saya menggunakan cara ini: http://pgxn.org/dist/first_last_agg/
Untuk install di ubuntu 14.04:
It's postgres ekstensi yang memberikan anda pertama dan terakhir fungsi; rupanya lebih cepat dari cara di atas.
Edit 2 - Pengurutan dan penyaringan
Jika anda menggunakan fungsi agregat (seperti ini), anda dapat memesan hasil, tanpa perlu memiliki data-data yang sudah terurut:
Sehingga setara dengan contoh, dengan memesan akan menjadi sesuatu seperti:
Tentu saja anda dapat memesan dan filter seperti yang anda anggap cocok dalam agregat; it's sangat kuat sintaks.
Gunakan
ARRAY_AGG
fungsi untuk PostgreSQL, U-SQL, IBM DB2, dan Google BigQuery SQL:Query:
BAGAIMANA ITU BEKERJA! (Saya've telah ada)
Kami ingin memastikan bahwa kami hanya memiliki total tertinggi untuk setiap pembelian.
Beberapa Teori Barang (melewatkan bagian ini jika anda hanya ingin memahami query)
Biarkan Total menjadi sebuah fungsi T(pelanggan,id) di mana ia mengembalikan nilai yang diberikan nama dan id Untuk membuktikan bahwa yang diberikan total (T(pelanggan,id)) adalah yang tertinggi yang kita miliki untuk membuktikan bahwa Kami ingin membuktikan baik
ATAU
Pendekatan pertama akan membutuhkan kami untuk mendapatkan semua catatan untuk nama itu yang saya tidak suka.
Yang kedua akan memerlukan sebuah cara cerdas untuk mengatakan tidak ada catatan lebih tinggi dari yang satu ini.
Kembali ke SQL
Jika kita meninggalkan bergabung pada tabel nama dan jumlah yang kurang dari bergabung dengan tabel:
kami memastikan bahwa semua catatan yang memiliki rekor lain dengan total lebih tinggi untuk pengguna yang sama untuk bergabung:
Yang akan membantu kita filter untuk total tertinggi untuk setiap pembelian dengan tidak ada pengelompokan yang dibutuhkan:
Dan yang's jawaban yang kita butuhkan.
Di SQL Server anda dapat melakukan ini:
Penjelasan:di Sini Group by dilakukan atas dasar pelanggan dan kemudian memesannya dengan total kemudian masing-masing kelompok tersebut diberikan nomor seri seperti StRank dan kami mengambil keluar pertama 1 pelanggan dan StRank 1
Diterima OMG Kuda', "Didukung oleh database" solusi memiliki kecepatan yang baik dari pengujian saya.
Di sini saya menyediakan satu pendekatan, tetapi lebih lengkap dan bersih-solusi database. Ikatan dianggap (misalnya keinginan untuk mendapatkan hanya satu baris untuk setiap pelanggan, bahkan beberapa catatan untuk max total per pelanggan), dan lain pembelian bidang (misalnya purchase_payment_id) akan dipilih untuk baris yang cocok dalam pembelian meja.
Didukung oleh database:
Permintaan ini cukup cepat terutama ketika ada composite seperti (pelanggan, total) pada pembelian meja.
Komentar:
t1, t2 adalah subquery alias yang bisa dilepas tergantung pada database.
Peringatan:
menggunakan (...)
klausul ini saat ini tidak didukung di MS-SQL dan Oracle db seperti ini edit pada Januari 2017. Anda harus memperluas diri untuk misalpada t2.id = pembelian.id
dll. MENGGUNAKAN sintaks bekerja di SQLite, MySQL dan PostgreSQL.Untuk SQl Server cara yang paling efisien adalah:
dan don't lupa untuk membuat indeks berkerumun untuk digunakan kolom
Jika anda ingin memilih salah (dengan beberapa syarat tertentu) baris dari set agregat baris.
Jika anda ingin menggunakan yang lain (
jumlah/rata
) agregasi fungsi selain untukmax/min
. Dengan demikian anda tidak dapat menggunakan petunjuk denganBERBEDA
Anda dapat menggunakan subquery:
Anda dapat mengganti
jumlah = MAX( tf.jumlah )
dengan kondisi apapun yang anda inginkan dengan satu batasan: Ini subquery tidak harus kembali lebih dari satu barisTetapi jika anda ingin untuk melakukan hal-hal seperti itu anda mungkin mencari fungsi window