Dapatkan rekod terakhir dalam setiap kumpulan - MySQL

Question

Terdapat jadual 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 akan mendapat keputusan sebagai:

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

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 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 penyelesaian mungkin tidak berfungsi seperti yang diharapkan walaupun dengan suis dilumpuhkan. Perlu menyemaknya.

Penyelesaian Bill Karwin di atas berfungsi dengan baik apabila kiraan item dalam kumpulan agak kecil, tetapi prestasi pertanyaan menjadi buruk apabila kumpulan agak besar, kerana penyelesaiannya memerlukan kira-kira n*n/2 + n/2 of only IS NULL perbandingan.

Saya membuat ujian saya pada jadual InnoDB sebanyak 18684446 rows with 1182 groups. The table contains testresults for functional tests and has the (test_id, request_id) as the primary key. Thus, test_id is a group and I was searching for the last request_id for each test_id.

Penyelesaian Bill telah berjalan selama beberapa jam pada dell e4310 saya dan saya tidak tahu bila ia akan selesai walaupun ia beroperasi pada indeks liputan (oleh itu using index dalam EXPLAIN).

Saya mempunyai beberapa penyelesaian lain berdasarkan idea yang sama:

• jika indeks pendasar ialah indeks BTREE (yang selalunya berlaku), yang terbesar (group_id, item_value) pair is the last value within each group_id, that is the first for each group_id jika kita berjalan melalui indeks dalam tertib menurun;
• 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 jauh lebih murah untuk mengumpul ID baris yang diperlukan dalam susunan yang diperlukan 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 untuk 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.

Inilah 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, 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 August dump Saya akan menggunakannya untuk menanda aras Terdapat 1,114,357 baris dalam jadual Posts Ini berjalan pada MySQL 5.0.75 GHz saya. .

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

Mula-mula menggunakan teknik ditunjukkan oleh @Eric dengan 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 EXPLAIN analisis mengambil masa lebih 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 rows in set (16.09 sec)

Kini hasilkan hasil pertanyaan yang sama menggunakan teknik saya dengan LEFT JOIN:

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 row in set (0.28 sec)

Analisis EXPLAIN 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)

---

Berikut ialah DDL untuk 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.