顯示每個唯一值的前 X 個結果

我試圖弄清楚如何輸出任何最近 3 個備份大於 10GB 的機器。我的 SQL 表類似於下表：

ComputerID | BackupImageName | WindowsVolumeGUID | FileSize | TimeCreated
1234       | PCBackup.spi    | kjsdfai-dadad-ddd | 10400    | 05-22-20
1234       | PCBackup1.spi   | kjsdfai-dadad-ddd | 10500    | 05-23-20
1234       | PCBackup2.spi   | kjsdfai-dadad-ddd | 10300    | 05-24-20
1234       | PCBackup3.spi   | kjsdfai-dadad-ddd | 10900    | 05-25-20
1225       | PCBackup4.spi   | kjsdfa5-dadad-ddd | 10600    | 05-22-20
1225       | PCBackup5.spi   | kjsdfa5-dadad-ddd | 5000     | 05-23-20
1225       | PCBackup6.spi   | kjsdfa5-dadad-ddd | 10000    | 05-24-20
1225       | PCBackup7.spi   | kjsdfa5-dadad-ddd | 4588     | 05-25-20

我需要輸出列表中最新 3 個文件大於 10000 個文件大小的所有機器（computerID）或卷（WindowsVolumeGUID）。因此，從上表中它將輸出 1234 的電腦 ID，因為它的最新 3 個大於 10000 文件大小，即使電腦 ID 1225 有 2 個大於 10000 的文件，它也不會輸出結果，因為兩者之間存在中斷大文件。搜尋需要考慮 WindowsVolumeGUID，因為有些機器有不止一個卷，我需要考慮單個卷備份並據此計算最新條目。

或者，如果有一種方法可以為每個唯一的 WindowsVolumeGUID 輸出前 3 個結果，而不僅僅是全面輸出，我也可以完成同樣的事情。

任何幫助將不勝感激！

1. 傳入架構和測試數據

CREATE TABLE `db_info` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `ComputerID` int(10) unsigned NOT NULL,
 `BackupImageName` varchar(32) NOT NULL,
 `WindowsVolumeGUID` varchar(32) NOT NULL,
 `FileSize` int(10) unsigned NOT NULL,
 `TimeCreated` timestamp NOT NULL DEFAULT current_timestamp(),
 PRIMARY KEY (`id`),
 KEY `ComputerID` (`ComputerID`),
 KEY `WindowsVolumeGUID` (`WindowsVolumeGUID`),
 KEY `FileSize` (`FileSize`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `db_info` (`ComputerID`, `BackupImageName`, `WindowsVolumeGUID`, `FileSize`, `TimeCreated`) VALUES
(1234, 'PCBackup.spi ', 'kjsdfai-dadad-ddd', 10400, '2020-05-22'),
(1234, 'PCBackup1.spi', 'kjsdfai-dadad-ddd', 10500, '2020-05-23'),
(1234, 'PCBackup2.spi', 'kjsdfai-dadad-ddd', 10300, '2020-05-24'),
(1234, 'PCBackup3.spi', 'kjsdfai-dadad-ddd', 10900, '2020-05-25'),
(1225, 'PCBackup4.spi', 'kjsdfa5-dadad-ddd', 10600, '2020-05-22'),
(1225, 'PCBackup5.spi', 'kjsdfa5-dadad-ddd', 5000, '2020-05-23'),
(1225, 'PCBackup6.spi', 'kjsdfa5-dadad-ddd', 10000, '2020-05-24'),
(1225, 'PCBackup7.spi', 'kjsdfa5-dadad-ddd', 4588, '2020-05-25');

2. 請求的查詢

SET @n := 0, @lastComputerId := NULL;
SELECT ComputerId, SUM(IF(FileSize >= 10000, 1, 0)) AS large_backups_count
FROM (
   SELECT ComputerId, FileSize
   FROM `db_info`
   WHERE (@n:=(@n * IF(@lastComputerId <=> ComputerID, 1, (@lastComputerId := ComputerID) AND 0) + 1)) < 4
   ORDER BY ComputerID, TimeCreated DESC
) AS tmp
GROUP BY ComputerId
HAVING large_backups_count >= 3;

結果：

+------------+---------------------+
| ComputerId | large_backups_count |
+------------+---------------------+
|       1234 |                   3 |
+------------+---------------------+

3. 基於最近三個備份的總大小的替代查詢：

SET @n := 0, @lastComputerId := NULL;
SELECT ComputerId, SUM(FileSize) AS sum_of_recent_three_backups
FROM (
   SELECT ComputerId, FileSize
   FROM `db_info`
   WHERE (@n:=(@n * IF(@lastComputerId <=> ComputerID, 1, (@lastComputerId := ComputerID) AND 0) + 1)) < 4
   ORDER BY ComputerID, TimeCreated DESC
) AS tmp
GROUP BY ComputerId
HAVING sum_of_recent_three_backups >= 30000;

結果：

+------------+-----------------------------+
| ComputerId | sum_of_recent_three_backups |
+------------+-----------------------------+
|       1234 |                       31200 |
+------------+-----------------------------+

引用自：https://dba.stackexchange.com/questions/268075