如何按model_instance_id获取CPU和MEM的最新更新时间记录

问题 #

现有如下数据集：

model_instance_id | metric_name |    metric_value    |      updateddatetime

           1059971 | CPU         | 3                  | 2023-03-24 12:58:38.461286
           1059971 | CPU         | 3                  | 2023-03-24 12:38:55.77642
           1059971 | MEM         | 3                  | 2023-03-24 12:03:33.411069
           1059971 | MEM         | 3                  | 2023-03-24 11:43:44.102632
           1059990 | CPU         | 3                  | 2023-03-24 11:23:54.731535
           1059990 | CPU         | 3                  | 2023-03-24 11:04:11.673082
           1059990 | CPU         | 3                  | 2023-03-24 10:44:30.661532
           1059990 | CPU         | 3                  | 2023-03-24 10:24:44.181107
           1059990 | CPU         | 3                  | 2023-03-24 10:04:49.792651
           1059990 | MEM         | 0.8048095703125    | 2023-03-24 12:58:38.492705
           1059990 | MEM         | 0.8048095703125    | 2023-03-24 12:38:55.823345
           1059990 | MEM         | 0.8048095703125    | 2023-03-24 12:03:33.473615
           1059990 | MEM         | 0.8048095703125    | 2023-03-24 11:43:44.118206
           1059990 | MEM         | 0.8048095703125    | 2023-03-24 11:23:54.794035

需求是为每个model_instance_id分别获取CPU和MEM类型下最新updateddatetime对应的记录。

尝试了以下SQL语句：

select DISTINCT ON ("model_instance_id")model_instance_id, model_manager_id,metric_name,metric_value,mim.updateddatetime 
from criml.model_instance_metrics 
where mim.metric_name in ('CPU','MEM') 
order by model_instance_id,metric_name,mim.updateddatetime DESC NULLS LAST;

但由于DISTINCT ON仅以model_instance_id去重，每个model_instance_id只能得到CPU或MEM中的一条记录，无法满足需求。

期望得到的结果：

model_instance_id | metric_name |    metric_value    |      updateddatetime
-------------------+-------------+--------------------+----------------------------
           1059971 | CPU         | 3                  | 2023-03-24 12:58:38.461286
           1059971 | MEM         | 3                  | 2023-03-24 12:38:55.776426
           1059990 | CPU         | 3                  | 2023-03-24 11:43:44.102632
           1059990 | MEM         | 3                  | 2023-03-24 11:23:54.731535

解决方案 #

方案一：调整DISTINCT ON的分组键（PostgreSQL专属） #

PostgreSQL的DISTINCT ON支持指定多个字段作为去重依据，只需把model_instance_id和metric_name一起作为参数，就能保证每个实例的每种指标都保留最新记录：

SELECT DISTINCT ON (model_instance_id, metric_name)
       model_instance_id, 
       metric_name, 
       metric_value, 
       updateddatetime
FROM criml.model_instance_metrics
WHERE metric_name IN ('CPU', 'MEM')
ORDER BY model_instance_id, metric_name, updateddatetime DESC NULLS LAST;

说明：DISTINCT ON会按指定的字段组合去重，排序时先按实例ID、指标名称分组，再按时间倒序，每组的第一条就是该实例对应指标的最新记录。

方案二：使用窗口函数（通用兼容方案） #

如果需要兼容其他不支持DISTINCT ON的数据库，可以用ROW_NUMBER()窗口函数实现：

WITH ranked_metrics AS (
    SELECT 
        model_instance_id, 
        metric_name, 
        metric_value, 
        updateddatetime,
        ROW_NUMBER() OVER (
            PARTITION BY model_instance_id, metric_name 
            ORDER BY updateddatetime DESC NULLS LAST
        ) AS rn
    FROM criml.model_instance_metrics
    WHERE metric_name IN ('CPU', 'MEM')
)
SELECT 
    model_instance_id, 
    metric_name, 
    metric_value, 
    updateddatetime
FROM ranked_metrics
WHERE rn = 1;

说明：通过PARTITION BY按实例ID和指标名称分组，每组内按时间倒序编号，筛选出编号为1的记录就是每组的最新数据。

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