查找设备最近一次状态变更时间及对应状态的SQL/Kusto实现方法

SQL 实现 #

• 方法1：窗口函数法
  用ROW_NUMBER()窗口函数按设备编号SerialNumber分区，按状态变更时间Transition_Date倒序排序，每个分区内排序为1的记录就是对应设备最近一次的状态变更记录。
  示例代码：

  SELECT SerialNumber, State, Transition_Date
  FROM (
      SELECT 
          SerialNumber,
          State,
          Transition_Date,
          -- 按设备分组，变更时间倒序排名
          ROW_NUMBER() OVER(PARTITION BY SerialNumber ORDER BY Transition_Date DESC) AS rank_num
      FROM device_state_table -- 替换为你的实际表名
  ) temp
  WHERE rank_num = 1
  

  若需保留同一设备同一最新时间下的多条变更记录，可将ROW_NUMBER()替换为RANK()。
• 方法2：子查询关联法
  先分组查询得到每个设备的最新变更时间，再将结果和原表关联，匹配设备编号和变更时间，就能得到对应状态值。
  示例代码：

  SELECT t1.SerialNumber, t1.State, t1.Transition_Date
  FROM device_state_table t1
  INNER JOIN (
      SELECT SerialNumber, MAX(Transition_Date) AS latest_date
      FROM device_state_table
      GROUP BY SerialNumber
  ) t2 ON t1.SerialNumber = t2.SerialNumber AND t1.Transition_Date = t2.latest_date
  

Kusto 实现 #

直接使用Kusto内置的arg_max()聚合函数，该函数可以返回指定列最大值对应的其他字段内容，一步得到结果。
示例代码：

device_state_table // 替换为你的实际表名
| summarize arg_max(Transition_Date, State) by SerialNumber
| project SerialNumber, State, Transition_Date

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