仅基于最新时间戳统计设备操作次数的SQL查询问题求助

需要统计数据表中各设备的最新操作，并按指定操作统计对应数量：

• 查询shutdown时返回0
• 查询running时返回2
• 查询starting时返回1

数据表结构及数据如下：

原尝试SQL：

SELECT
    count(device)
FROM
    table
WHERE
    action='shutdown'
    AND
    timestamp=(SELECT max(timestamp) FROM table)
;

原SQL错误原因：全局的max(timestamp)仅能获取整个表的最新时间，无法匹配每个设备自身的最新操作时间，导致统计结果不符合预期。

方法1：子查询获取每个设备最新时间后关联统计 #

先筛选出每个设备的最新操作时间，再关联原表定位到对应操作，最后统计数量：

SELECT COUNT(t.device)
FROM (
    SELECT device, MAX(timestamp) AS latest_time
    FROM table
    GROUP BY device
) AS latest
JOIN table t ON t.device = latest.device AND t.timestamp = latest.latest_time
WHERE t.action = 'shutdown'; -- 替换为目标操作：'running'/'starting'

方法2：窗口函数筛选最新操作 #

用ROW_NUMBER()窗口函数为每个设备的操作按时间倒序排序，取排序值为1的记录（即最新操作），再统计指定操作的数量：

SELECT COUNT(*)
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY device ORDER BY timestamp DESC) AS rn
    FROM table
) AS ranked
WHERE rn = 1 AND action = 'shutdown'; -- 替换为目标操作

替换WHERE子句中的action值即可得到对应结果：

• 替换为'shutdown'返回0
• 替换为'running'返回2
• 替换为'starting'返回1

内容的提问来源于stack exchange，提问作者Telmo