Dapatkan rekod terakhir setiap kumpulan menggunakan MySQL

Question

Terdapat jadual yang dipanggil mesej

Id Name Other_Columns
--------------------------
1 A A_data_1
2 A A_data_2
3 A A_data_3
4 B B_data_1
5 B B_data_2
6 C C_data_1

Jika saya menjalankan pertanyaan pilih * daripada kumpulan mesej mengikut nama, saya mendapat keputusan berikut:

1 A A_data_1
4 B B_data_1
6 C C_data_1

Pertanyaan yang manakah akan mengembalikan hasil berikut?

3 A A_data_3
5 B B_data_2
6 C C_data_1

Iaitu, rekod terakhir dalam setiap kumpulan hendaklah dikembalikan.

Pada masa ini, ini ialah pertanyaan yang saya gunakan:

SELECT
  *
DARI (PILIH
  *
DARIPADA mesej
PESANAN OLEH id DESC) SEBAGAI x
KUMPULAN MENGIKUT nama
Tetapi ini nampaknya tidak cekap. Adakah terdapat cara lain untuk mencapai hasil yang sama? 

Answer 1

UPD: 31-03-2017, versi MySQL 5.7.5 mempunyai suis ONLY_FULL_GROUP_BY didayakan secara lalai (oleh itu, pertanyaan GROUP BY bukan deterministik dilumpuhkan). Selain itu, mereka mengemas kini pelaksanaan GROUP BY dan penyelesaian mungkin tidak lagi berfungsi seperti yang diharapkan walaupun dengan suis dilumpuhkan. Pemeriksaan diperlukan.

Penyelesaian Bill Karwin berfungsi dengan baik apabila bilangan item dalam kumpulan adalah kecil, tetapi prestasi pertanyaan menjadi lebih teruk apabila kumpulan lebih besar kerana penyelesaian memerlukan kira-kira n*n/2 + n/2次IS NULLperbandingan.

Saya termasuk dalam 18684446行和1182个组的InnoDB表上进行了测试。该表包含功能测试的测试结果，并且(test_id, request_id)是主键。因此，test_id是一个组，我正在寻找每个test_id的最后一个request_id.

Penyelesaian Bill telah berjalan pada Dell e4310 saya selama beberapa jam sekarang, saya tidak tahu bila ia akan lengkap, walaupun ia beroperasi pada indeks tertutup (oleh itu EXPLAIN ditunjukkan using index).

Saya ada beberapa lagi penyelesaian berdasarkan idea yang sama:

• Jika indeks asas ialah indeks BTREE (iaitu kes biasa), nilai pertama setiap group_id中的最大(group_id, item_value)对就是每个group_id的最后一个值，如果我们按降序遍历索引，则是每个group_id
• Jika kita membaca nilai yang diliputi oleh indeks, nilai akan dibaca dalam susunan indeks
• Setiap indeks secara tersirat mengandungi lajur kunci utama tambahan (iaitu kunci utama berada dalam indeks penutup). Dalam penyelesaian di bawah saya memanipulasi kunci utama secara langsung, dalam kes anda, anda hanya perlu menambah lajur kunci utama dalam hasilnya.
• Dalam kebanyakan kes, pendekatan yang lebih murah ialah mengumpul ID baris yang diperlukan dalam susunan yang dikehendaki dalam subkueri dan menggabungkan hasil subkueri dengan ID. Memandangkan MySQL memerlukan satu pengambilan berdasarkan kunci utama untuk setiap baris dalam hasil subkueri, subkueri akan diletakkan terlebih dahulu dalam gabungan dan baris akan dikeluarkan mengikut urutan ID dalam subkueri (jika kita meninggalkan ORDER BY eksplisit daripada menyertainya)

3 cara MySQL menggunakan indeks ialah artikel yang bagus untuk memahami beberapa butiran.

Penyelesaian 1

Penyelesaian ini sangat pantas, mengambil masa kira-kira 0.8 saat untuk 18 juta+ baris data saya:

SELECT test_id, MAX(request_id) AS request_id
FROM testresults
GROUP BY test_id DESC;

Jika anda ingin menukar susunan kepada menaik, masukkannya dalam subkueri, pulangkan hanya ID dan sertainya sebagai subkueri dengan lajur lain:

SELECT test_id, request_id
FROM (
    SELECT test_id, MAX(request_id) AS request_id
    FROM testresults
    GROUP BY test_id DESC) as ids
ORDER BY test_id;

Untuk data saya, penyelesaian ini mengambil masa kira-kira 1.2 saat.

Penyelesaian 2

Ini adalah penyelesaian lain, untuk jadual saya ia mengambil masa kira-kira 19 saat:

SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

Ia juga mengembalikan keputusan ujian dalam susunan menurun. Ia lebih perlahan kerana ia melakukan imbasan indeks penuh, tetapi ia boleh memberi anda idea tentang cara untuk mengeluarkan N baris maksimum untuk setiap kumpulan.

Kelemahan pertanyaan ini ialah keputusannya tidak boleh dicache oleh pertanyaan.

Answer 2

MySQL 8.0 kini menyokong Fungsi tetingkap, begitu juga dengan hampir semua pelaksanaan SQL yang popular. Menggunakan sintaks standard ini, kita boleh menulis pertanyaan maks-n-per-kumpulan:

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

Manual

MySQL menunjukkan kaedah ini dan kaedah lain untuk mencari baris terbesar terkumpul.

Berikut ialah jawapan asal yang saya tulis untuk soalan ini pada tahun 2009:

---

Saya menulis penyelesaian seperti ini:

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

Berkenaan prestasi, bergantung pada sifat data, salah satu penyelesaian mungkin lebih baik. Oleh itu, anda harus menguji kedua-dua pertanyaan dan memilih yang lebih baik berdasarkan prestasi pangkalan data anda.

Sebagai contoh, saya mempunyai salinan StackOverflow August Data Dump. Saya akan menggunakannya untuk penanda aras. Terdapat 1,114,357 baris data dalam jadual Posts. Ini sedang berjalan MySQL 5.0.75 pada Macbook Pro 2.40GHz saya.

Saya akan menulis pertanyaan untuk mencari siaran terkini untuk ID pengguna yang diberikan (saya).

Pertama digunakan teknik Eric menggunakan GROUP BY dalam subkueri:

SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
            FROM Posts pi GROUP BY pi.owneruserid) p2
  ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;

1行结果（1分17.89秒）

Malah EXPLAINanalisis mengambil masa lebih daripada 16 saat:

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                       | NULL        | NULL    | NULL         |   76756 |             | 
|  1 | PRIMARY     | p1         | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY     | 8       | p2.maxpostid |       1 | Using where | 
|  2 | DERIVED     | pi         | index  | NULL                       | OwnerUserId | 8       | NULL         | 1151268 | Using index | 
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
3行结果（16.09秒）

Kini menggunakan LEFT JOIN menggunakan teknik saya menghasilkan hasil pertanyaan yang sama:

SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;

1行结果（0.28秒）

EXPLAINAnalisis menunjukkan bahawa kedua-dua jadual boleh menggunakan indeksnya:

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| id | select_type | table | type | possible_keys              | key         | key_len | ref   | rows | Extra                                |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
|  1 | SIMPLE      | p1    | ref  | OwnerUserId                | OwnerUserId | 8       | const | 1384 | Using index                          | 
|  1 | SIMPLE      | p2    | ref  | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8       | const | 1384 | Using where; Using index; Not exists | 
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
2行结果（0.00秒）

---

Ini adalah DDL jadual Posts saya:

CREATE TABLE `posts` (
  `PostId` bigint(20) unsigned NOT NULL auto_increment,
  `PostTypeId` bigint(20) unsigned NOT NULL,
  `AcceptedAnswerId` bigint(20) unsigned default NULL,
  `ParentId` bigint(20) unsigned default NULL,
  `CreationDate` datetime NOT NULL,
  `Score` int(11) NOT NULL default '0',
  `ViewCount` int(11) NOT NULL default '0',
  `Body` text NOT NULL,
  `OwnerUserId` bigint(20) unsigned NOT NULL,
  `OwnerDisplayName` varchar(40) default NULL,
  `LastEditorUserId` bigint(20) unsigned default NULL,
  `LastEditDate` datetime default NULL,
  `LastActivityDate` datetime default NULL,
  `Title` varchar(250) NOT NULL default '',
  `Tags` varchar(150) NOT NULL default '',
  `AnswerCount` int(11) NOT NULL default '0',
  `CommentCount` int(11) NOT NULL default '0',
  `FavoriteCount` int(11) NOT NULL default '0',
  `ClosedDate` datetime default NULL,
  PRIMARY KEY  (`PostId`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `PostTypeId` (`PostTypeId`),
  KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  KEY `OwnerUserId` (`OwnerUserId`),
  KEY `LastEditorUserId` (`LastEditorUserId`),
  KEY `ParentId` (`ParentId`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
) ENGINE=InnoDB;

---

Nota kepada pengulas: Jika anda ingin menjalankan penanda aras lain menggunakan versi MySQL yang berbeza, set data yang berbeza atau reka bentuk jadual yang berbeza, sila lakukan sendiri. Saya telah menunjukkan teknik di atas. Tujuan Stack Overflow adalah untuk menunjukkan kepada anda cara melakukan kerja pembangunan perisian, bukan untuk melakukan semua kerja untuk anda.