MySQL: Dapatkan entri terkini dalam setiap kumpulan

Question

Terdapat jadualmessages yang mengandungi data seperti ini:

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 select * from messages group by name, hasil yang saya akan dapat ialah:

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

Apakah pertanyaan yang akan mengembalikan hasil berikut?

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

Maksudnya, rekod terakhir dalam setiap kumpulan hendaklah dikembalikan.

Pada masa ini, ini adalah pertanyaan yang saya gunakan:

SELECT
  *
FROM (SELECT
  *
FROM messages
ORDER BY id DESC) AS x
GROUP BY name

Tetapi ini nampaknya sangat tidak cekap. Adakah terdapat cara lain untuk mencapai hasil yang sama?

Answer 1

UPD: 31-03-2017, versi 5.7.5 MySQL mendayakan suis ONLY_FULL_GROUP_BY secara lalai (jadi pertanyaan GROUP BY bukan deterministik dilumpuhkan). Selain itu, mereka mengemas kini pelaksanaan GROUP BY dan penyelesaiannya mungkin tidak berfungsi seperti yang diharapkan walaupun dengan suis dilumpuhkan. Perlu menyemaknya.

Penyelesaian Bill Karwin di atas berfungsi dengan baik apabila bilangan item dalam kumpulan agak kecil, tetapi apabila kumpulan agak besar prestasi pertanyaan menjadi lemah kerana penyelesaian memerlukan lebih kurang n*n/2 + n/2 Hanya bandingkan IS NULLIS NULL.

Saya menguji pada jadual InnoDB yang mengandungi 1868444618684446 行和 1182 组的 InnoDB 表上进行了测试。该表包含功能测试的测试结果，并以 (test_id, request_id) 作为主键。因此，test_id 是一个组，我正在为每个test_id 搜索最后一个request_id baris dan 1182

kumpulan. Jadual ini mengandungi keputusan ujian untuk ujian berfungsi dan mempunyai (test_id, request_id)

sebagai kunci utama. Jadi, test_id

ialah kumpulan dan saya sedang mencari request_id

terakhir untuk setiap test_id

.

• Penyelesaian Bill telah berjalan pada Dell e4310 saya selama beberapa jam sekarang, dan walaupun ia berjalan pada indeks penutup (oleh itu menggunakan indeks dalam EXPLAIN), saya tidak tahu bila ia akan lengkap. (group_id, item_value) 对是每个 group_id 中的最后一个值，即如果我们按降序遍历索引，则为每个 group_id
Saya mempunyai beberapa penyelesaian lain berdasarkan idea yang sama:
• Jika indeks pendasar ialah indeks BTREE (yang selalunya berlaku), maka pasangan (group_id, item_value)
terbesar ialah nilai terakhir dalam setiap group_id
• , iaitu jika kita melintasi indeks dalam menurun pesanan, Maka ia adalah yang pertama daripada setiap id_kumpulan
;

Jika kita membaca nilai yang diliputi oleh indeks, nilai dibaca dalam susunan indeks Setiap indeks secara tersirat mengandungi lajur kunci utama yang dilampirkan pada indeks tersebut (iaitu kunci utama berada dalam indeks penutup). Dalam penyelesaian di bawah saya beroperasi secara langsung pada kunci utama, dalam kes anda, anda hanya perlu menambah lajur kunci utama kepada hasilnya.

Dalam banyak kes, adalah lebih murah untuk mengumpul ID baris yang diperlukan dalam susunan yang dikehendaki dalam subkueri dan menggabungkan hasil subkueri kepada ID. Oleh kerana untuk setiap baris dalam hasil subquery, MySQL perlu melakukan pengambilan berdasarkan kunci utama, subquery akan dimasukkan ke dalam join dahulu, dan baris akan dikeluarkan mengikut susunan id dalam subquery (jika kita tinggalkan ORDER BY yang jelas untuk menyertai )

3 Cara MySQL Menggunakan Indeks

ialah artikel yang bagus untuk membantu anda memahami beberapa butiran.

Penyelesaian 1

Ini sangat pantas, mengambil masa kira-kira 0.8 saat pada baris 18J+ saya:

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

Jika anda ingin menukar susunan kepada ASC, masukkannya dalam subkueri yang hanya mengembalikan id dan gunakannya sebagai subkueri untuk menyertai lajur yang 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;

Ini mengambil masa kira-kira 1.2 saat untuk data saya. 🎜 🎜🎜Penyelesaian 2🎜🎜 🎜Ini satu lagi penyelesaian yang mengambil masa kira-kira 19 saat untuk jam tangan saya: 🎜

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 ujian dalam susunan menurun. Ia jauh lebih perlahan kerana ia melakukan imbasan indeks penuh, tetapi ia memberi anda idea tentang cara mengeluarkan N baris maksimum untuk setiap kumpulan.

Kelemahan pertanyaan ini ialah cache pertanyaan tidak boleh menyimpan hasil cariannya.

Answer 2

MySQL 8.0 kini menyokong fungsi tetingkap, seperti hampir semua pelaksanaan SQL yang popular. Menggunakan sintaks standard ini, kita boleh menulis sehingga n pertanyaan bagi setiap 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;

Ini dan kaedah mencari yang lain bilangan maksimum baris yang dikumpulkan diterangkan dalam manual MySQL.

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 
 Berkenaan prestasi, satu penyelesaian mungkin lebih baik bergantung pada sifat data. Oleh itu, anda harus menguji kedua-dua pertanyaan dan menggunakan pertanyaan yang mempunyai prestasi yang lebih baik berdasarkan pangkalan data anda. 
Sebagai contoh, saya mempunyai salinan StackOverflow Ogos pembuangan data. Saya akan menggunakannya untuk tujuan penanda aras. Posts Terdapat 1,114,357 baris dalam jadual. Ia berjalan pada MySQL 5.0.75 pada Macbook Pro 2.40GHz saya. 
Saya akan menulis pertanyaan untuk mencari siaran terkini untuk ID pengguna yang diberikan (saya). 
Mula-mula gunakan teknik  ditunjukkan  oleh @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 row in set (1 min 17.89 sec)
Malah EXPLAINanalisis mengambil masa lebih 16 saat: 
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     |  | 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 rows in set (16.09 sec)
Sekarang gunakan Petua saya untuk menggunakan LEFT JOIN: 
SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid 
EXPLAIN Analisis 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 rows in set (0.00 sec)

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. Stack Overflow ada di sini untuk menunjukkan kepada anda cara melakukan kerja pembangunan perisian, bukan untuk melakukan semua kerja untuk anda.