根據時間戳獲取每個組的最近 2 個條目

我正在使用具有以下列的 MySQL 5.7 數據庫

• item_symbol
• 源日期
• 價格

我有復合主鍵，它們是item_symbol 和的組合source_date。我在表中有大約 100k 個條目。

我的問題是，對於每個item_symbol，我想從表中選擇item_symbol最近price的（基於源日期）2 個條目。

我的表的一個例子：

我在這裡訪問了我最喜歡的 MySQL“提示和技巧”站點，並訪問了常見查詢連結並查找了每組前 N 個部分。這個站點的好處在於它告訴你如何在 MySQL 中為所有版本做一些事情——嗯，至少可以追溯到 MySQL 5.5——如果你還在執行它，那麼……

我想出了從上面改編的以下內容（下面的所有 DDL、DML 和 SQL 都可以在此處的小提琴中找到）：

我使用了來自@nbk 的 DDL 和 DML，向他致敬（和 +1）：

CREATE TABLE item
(
 item_symbol CHAR(1), 
 price       DECIMAL(10,2), 
 source_date DATE
);

並填充它：

INSERT INTO item
(item_symbol, price, source_date)
VALUES
('A', 20.1, '2021-06-10'),
('A', 18.2, '2021-06-11'),
('A', 10.9, '2021-06-13'),
('A', 21.0, '2021-06-15'),
('B', 88.2, '2021-06-10'),
('B', 60.9, '2021-06-11'),
('B', 78.16, '2021-06-13'),
('B', 79.0, '2021-06-15');

當您沒有諸如ROW_NUMBER()視窗函式之類的功能時，MySQL 允許使用使用者變數，這是天賜之物，這會使該查詢變得微不足道。我強烈建議您升級到版本 8，它還有許多其他優點 - CTE、CHECK 約束……

無論如何，我將展示這些步驟，一部分是向您解釋，一部分是向我自己解釋！:-)

SELECT
 item_symbol, price, source_date,
 IF 
 (
   @prev <> item_symbol,
   @row_num := 1,
   @row_num := @row_num + 1
 ) AS my_rank,
 @prev := item_symbol
FROM item
JOIN 
 (
   SELECT @row_num := NULL, @prev := 0
 ) AS r
ORDER BY item_symbol, source_date DESC, price DESC;

結果：

item_symbol     price   source_date     my_rank     @prev := item_symbol
         A     21.00   2021-06-15            1     A
         A     10.90   2021-06-13            2     A
         A     18.20   2021-06-11            3     A
         A     20.10   2021-06-10            4     A
         B     79.00   2021-06-15            1     B
         B     78.16   2021-06-13            2     B
         B     60.90   2021-06-11            3     B
         B     88.20   2021-06-10            4     B
8 rows

所以，我們有項目（‘A’，‘B’）按日期排序DESC（最近的第一個）和價格。請注意，該查詢的 12 行可以替換為一個ROW_NUMBER()函式行！

所以，現在我們將它包裝在一個查詢中，取出那些my_rank值為 <= 2 的結果——這給了我們兩個最近的日期！

SELECT item_symbol, price, source_date, my_rank  -- this last one is not 
FROM                                             -- required - for clarity...
(
 SELECT
   item_symbol, price, source_date,
   IF 
   (
     @prev <> item_symbol,
     @row_num := 1,
     @row_num := @row_num + 1
   ) AS my_rank,
   @prev := item_symbol
   FROM item
   JOIN (SELECT @row_num := NULL, @prev := 0) AS r
   ORDER BY item_symbol, source_date DESC, price DESC  -- in case of ties!
) AS t
WHERE t.my_rank <= 2
ORDER BY item_symbol, source_date DESC; -- change this as required

結果：

item_symbol     price   source_date     my_rank
         A     21.00   2021-06-15            1
         A     10.90   2021-06-13            2
         B     79.00   2021-06-15            1
         B     78.16   2021-06-13            2

我建議您花一些時間瀏覽巧妙的軟體網站！

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