從大表中檢索最新的 3 個值

引擎 MySQL 5.6.37

我有一個不斷增長的表（大約 1000 萬行），它保存來自設備的值以及時間戳。下面是表結構：

CREATE TABLE `meterreadings` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`meterid` varchar(16) DEFAULT NULL,
`metervalue` int(11) DEFAULT NULL,
`date_time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `meterid` (`meterid`,`date_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

我想知道過去 3 個儀表值之一是否已經過零（對於所有儀表 ID）。我嘗試使用以下查詢並使用 PHP 遍歷所有設備 ID，但是當我要掃描大約 500 個 Meterid 時，這種方法相當慢：

SELECT COUNT(CASE WHEN metervalue>0 THEN 1 END) AS present 
FROM (SELECT * 
     FROM meterreadings 
     where meterid= ? 
     order by Id desc 
     LIMIT 3) AS a

有沒有更好的方法可以完成這項工作，最好是使用一個查詢而不進行迭代？

查看：

SELECT meterid /* , IF(SUM(metervalue>0),1,0) flag */
FROM ( SELECT t.*,
             IF(@meterid=t.meterid, 
                @rownum:=@rownum+1, 
                @rownum:=1+least(0,@meterid:=t.meterid)) rown 
      FROM meterreadings t, 
           (SELECT @rownum:=1, @meterid:=0) vars
      /* WHERE t.date_time > @some_date_time */
      ORDER BY meterid, date_time DESC
    ) nums
WHERE rown < 4
GROUP BY meterid
HAVING SUM(metervalue>0) > 0 

結果集是meterid最後 3 個值中至少有 1 個正值的 ’s 列表。

評論 WHERE 允許通過設置一些“起點”來減少處理的記錄數量。

如果您需要meterid具有正值存在的“標誌”的完整列表 - 刪除輸出列表中的HAVING條件和取消註釋flag欄位。

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