如何为PostgreSQL表中同资产同时间的重复指标组分配唯一UUID

我正在处理一张存储资产指标数据的PostgreSQL表，数据量已超10亿条。每条记录对应单个指标，字段包括asset_id、timestamp（秒级分辨率）、metric_name和metric_value，一次设备更新会生成多个指标行（如speed、distance等）。

需求规则：

• 同一asset_id和timestamp下的同一次更新指标组，需分配唯一UUID（update_id）
• 若同一asset_id和timestamp下出现重复的metric_name，说明是设备在同一秒内发送了多次更新，此时重复指标所在的新批次需要生成新的update_id

示例输入 #

create table example_input(
  asset_id uuid
 ,timestamp timestamp(0)
 ,metric_name text
 ,metric_value int);

insert into example_input values
 ('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:14','distance',100)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:14','speed',60)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:14','elevation',15)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:14','distance',120)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:14','speed',80)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:19','distance',140)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:19','elevation',20);

预期输出 #

解决方案SQL #

-- 需提前安装uuid-ossp扩展（若未安装）
-- CREATE EXTENSION IF NOT EXISTS uuid-ossp;

SELECT
  asset_id,
  timestamp,
  metric_name,
  metric_value,
  uuid_generate_v4() OVER (PARTITION BY asset_id, timestamp, batch_num) AS update_id
FROM (
  SELECT
    *,
    -- 按资产、时间戳、指标名分组，为每组内的记录分配批次号
    ROW_NUMBER() OVER (PARTITION BY asset_id, timestamp, metric_name ORDER BY metric_value) AS batch_num
  FROM example_input
) AS grouped_data;

逻辑说明 #

1. 子查询中，使用ROW_NUMBER()窗口函数为每个(asset_id, timestamp, metric_name)组合内的记录分配批次号batch_num：同一指标在同一秒内的第一次更新批次号为1，第二次为2，以此类推。
2. 外层查询中，基于asset_id、timestamp和batch_num分组生成UUID，确保同一批次的所有指标（即同一次设备更新）共享同一个update_id，不同批次则生成新的UUID。

注：若表中有记录插入顺序的字段（如自增ID、插入时间戳），建议将ORDER BY metric_value替换为该字段，以保证批次分配的准确性。

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