Pertanyaan SQL hanya memilih baris dengan nilai maksimal pada kolom


Saya memiliki tabel ini untuk dokumen (versi yang disederhanakan di sini):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

Bagaimana cara memilih satu baris per id dan hanya pembalikan terbesar?
Dengan data di atas, hasilnya harus berisi dua baris: [1, 3, ...] dan [2, 1, ..]. saya menggunakan MySQL.

Saat ini saya menggunakan pemeriksaan di while loop untuk mendeteksi dan menulis ulang revs lama dari resultset. Tetapi apakah ini satu-satunya metode untuk mencapai hasil? Apakah tidak ada SQL larutan?

Memperbarui
Seperti yang disarankan jawabannya, di sana aku s solusi SQL, dan di sini demo sqlfiddle.

Perbarui 2
Saya perhatikan setelah menambahkan di atas sqlfiddle, tingkat di mana pertanyaan itu diberi suara telah melampaui tingkat jawaban atas jawaban. Itu belum menjadi tujuan! Bias didasarkan pada jawaban, terutama jawaban yang diterima.


870
2017-10-12 19:42


asal


Jawaban:


Pada pandangan pertama...

Yang Anda butuhkan hanyalah a GROUP BY klausa dengan MAX fungsi agregat:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Tidak pernah sesederhana itu, kan?

Saya baru saja memperhatikan Anda membutuhkan content kolom juga.

Ini adalah pertanyaan yang sangat umum di SQL: temukan seluruh data untuk baris dengan beberapa nilai maks dalam kolom per beberapa pengenal grup. Saya mendengar banyak hal selama karir saya. Sebenarnya, itu adalah salah satu pertanyaan yang saya jawab dalam wawancara teknis pekerjaan saya saat ini.

Sebenarnya, sangat umum bahwa komunitas StackOverflow telah membuat satu tag hanya untuk menangani pertanyaan seperti itu: .

Pada dasarnya, Anda memiliki dua pendekatan untuk memecahkan masalah itu:

Bergabung dengan sederhana group-identifier, max-value-in-group Sub-kueri

Dalam pendekatan ini, Anda pertama kali menemukan group-identifier, max-value-in-group (sudah dipecahkan di atas) dalam sub-query. Kemudian Anda bergabung dengan tabel Anda ke sub-query dengan persamaan pada keduanya group-identifier dan max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Kiri Bergabung dengan diri sendiri, menyesuaikan kondisi dan filter

Dalam pendekatan ini, Anda meninggalkan bergabung dengan tabel itu sendiri. Kesetaraan, tentu saja, masuk dalam group-identifier. Kemudian, 2 langkah cerdas:

  1. Kondisi penggabungan kedua memiliki nilai sisi kiri kurang dari nilai yang benar
  2. Ketika Anda melakukan langkah 1, baris (s) yang benar-benar memiliki nilai maks akan memiliki NULL di sisi kanan (itu a LEFT JOIN, ingat?). Kemudian, kami menyaring hasil yang digabungkan, menunjukkan hanya baris di mana sisi kanan NULL.

Jadi Anda berakhir dengan:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Kesimpulan

Kedua pendekatan membawa hasil yang sama persis.

Jika Anda memiliki dua baris dengan max-value-in-group untuk group-identifier, kedua baris akan menghasilkan dua pendekatan.

Kedua pendekatan ini kompatibel dengan ANSI SQL, sehingga, akan bekerja dengan RDBMS favorit Anda, terlepas dari "rasa" nya.

Kedua pendekatan ini juga ramah terhadap kinerja, namun jarak tempuh Anda dapat bervariasi (RDBMS, Struktur DB, Indeks, dll.). Jadi ketika Anda memilih satu pendekatan di atas yang lain, patokan. Dan pastikan Anda memilih salah satu yang paling masuk akal bagi Anda.


1387
2017-10-12 19:43



Preferensi saya adalah menggunakan kode sesedikit mungkin ...

Anda dapat melakukannya menggunakan IN coba ini:

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

dalam pikiran saya itu kurang rumit ... lebih mudah dibaca dan dipelihara.


168
2017-10-12 19:47



Namun solusi lain adalah dengan menggunakan subquery yang terkorelasi:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

Memiliki indeks pada (id, rev) menjadikan subkueri hampir sebagai pencarian sederhana ...

Berikut ini adalah perbandingan untuk solusi dalam jawaban @AdrianCarneiro (subquery, leftjoin), berdasarkan pengukuran MySQL dengan tabel InnoDB dari ~ 1 juta catatan, ukuran grup menjadi: 1-3.

Sementara untuk scan tabel penuh subquery / leftjoin / timing terkorelasi berhubungan satu sama lain sebagai 6/8/9, ketika datang ke pencarian langsung atau batch (id in (1,2,3)), subquery jauh lebih lambat daripada yang lain (Karena rerunning the subquery). Namun saya tidak bisa membedakan antara leftjoin dan solusi yang terkorelasi dalam kecepatan.

Satu catatan terakhir, seperti leftjoin menciptakan n * (n + 1) / 2 bergabung dalam grup, kinerjanya dapat sangat dipengaruhi oleh ukuran grup ...


52
2018-01-23 14:16



Saya tidak dapat menjamin kinerja, tetapi inilah trik yang terinspirasi oleh keterbatasan Microsoft Excel. Ini memiliki beberapa fitur bagus

BARANG BAGUS

  • Ini harus memaksa pengembalian hanya satu "catatan max" bahkan jika ada dasi (kadang-kadang berguna)
  • Tidak perlu bergabung

PENDEKATAN

Ini sedikit jelek dan mengharuskan Anda mengetahui sesuatu tentang kisaran nilai valid dari putaran kolom. Mari kita berasumsi bahwa kita tahu putaran kolom adalah angka antara 0,00 dan 999 termasuk desimal tetapi hanya akan ada dua digit di sebelah kanan titik desimal (misalnya 34,17 akan menjadi nilai yang valid).

Inti masalahnya adalah Anda membuat kolom sintetis tunggal dengan merangkai string / pengemasan bidang perbandingan utama bersama dengan data yang Anda inginkan. Dengan cara ini, Anda dapat memaksa fungsi agregat SQL () untuk mengembalikan semua data (karena telah dikemas ke dalam satu kolom). Maka Anda harus membongkar data.

Begini tampilannya dengan contoh di atas, ditulis dalam SQL

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

Kemasan dimulai dengan memaksa putaran kolom menjadi sejumlah panjang karakter yang diketahui tanpa menghiraukan nilai putaran jadi misalnya

  • 3.2 menjadi 1003.201
  • 57 menjadi 1057.001
  • 923.88 menjadi 1923.881

Jika Anda melakukannya dengan benar, perbandingan string dua angka harus menghasilkan "max" yang sama dengan perbandingan numerik dari dua angka dan mudah untuk mengkonversi kembali ke nomor asli menggunakan fungsi substring (yang tersedia dalam satu bentuk atau lainnya cukup banyak dimana mana).


34
2018-06-30 06:02



Saya terperangah bahwa tidak ada jawaban yang ditawarkan solusi fungsi jendela SQL:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

Ditambahkan dalam SQL standar ANSI / ISO Standard SQL: 2003 dan kemudian diperpanjang dengan ANSI / ISO Standard SQL: 2008, jendela (atau windowing) fungsi tersedia dengan semua vendor utama sekarang. Ada lebih banyak jenis fungsi peringkat yang tersedia untuk menangani masalah dasi: RANK, DENSE_RANK, PERSENT_RANK.


27
2017-08-09 15:29



Saya pikir ini adalah solusi termudah:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • PILIH *: Kembalikan semua bidang.
  • FROM Employee: Table digeledah.
  • (SELECT * ...) subquery: Kembalikan semua orang, diurutkan berdasarkan Gaji.
  • GROUP BY employeeub.Salary:: Paksa baris Gaji yang diurutkan, atas setiap karyawan untuk menjadi hasil yang dikembalikan.

Jika Anda hanya membutuhkan satu baris, itu lebih mudah:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

Saya juga berpikir itu yang paling mudah untuk dipecahkan, dipahami, dan dimodifikasi untuk tujuan lain:

  • ORDER BY Employee.Salary DESC: Memesan hasil dengan gaji, dengan gaji tertinggi pertama.
  • BATAS 1: Kembalikan hanya satu hasil.

Memahami pendekatan ini, memecahkan masalah-masalah serupa ini menjadi sepele: dapatkan karyawan dengan gaji terendah (ubah DESC menjadi ASC), dapatkan sepuluh karyawan berpenghasilan teratas (ubah LIMIT 1 menjadi LIMIT 10), urutkan berdasarkan bidang lain (ubah ORDER BY Employee.Salary to ORDER BY Employee.Commission), dll.


20
2017-09-14 00:28



Sesuatu seperti ini?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

14
2017-10-12 19:48



Karena ini adalah pertanyaan paling populer yang berkaitan dengan masalah ini, saya akan mengeposkan lagi jawaban lain di sini juga:

Sepertinya ada cara yang lebih sederhana untuk melakukan ini (tapi hanya di MySQL):

select *
from (select * from mytable order by id, rev desc ) x
group by id

Harap jawab kredit pengguna Bohemian di pertanyaan ini untuk memberikan jawaban yang ringkas dan elegan untuk masalah ini.

EDIT: meskipun solusi ini bekerja untuk banyak orang mungkin tidak stabil dalam jangka panjang, karena MySQL tidak menjamin pernyataan GROUP BY akan mengembalikan nilai yang berarti untuk kolom yang tidak ada dalam daftar GROUP BY. Jadi gunakan solusi ini dengan resiko Anda sendiri


6
2017-07-03 14:33