SQL实现：为窗口分区内的每两行生成唯一标识符(UID)

我来帮你解决这个按固定行数（每两行一组）给分区数据生成UID的问题～

问题核心分析 #

你需要基于firstIdentifier和secondIdentifier分区，每两行划分为一个组，给每个组分配唯一标识；如果分区最后剩余单行，则单独作为一组。之前用ntile()无法实现，是因为ntile()的逻辑是将分区内的数据均分为指定数量的组，而不是按固定行数分组——比如分区有5行时，ntile(2)会分成3行+2行的两组，而不是你需要的2+2+1三组。

解决方案思路 #

1. 先给每个分区内的行按timestamp排序，生成分区内行号；
2. 基于行号计算组ID：用CEIL(行号 / 2)，这样1、2行归为组1，3、4行归为组2，5行归为组3，以此类推；
3. 把分区标识（firstIdentifier、secondIdentifier）和组ID结合，用MD5()生成唯一UID，或者直接用组ID+分区标识作为可读标签。

完整SQL代码 #

SELECT
  rowId,
  firstIdentifier,
  secondIdentifier,
  timestamp,
  -- 生成唯一UID，用MD5拼接分区标识和组ID
  MD5(CONCAT(firstIdentifier, '_', secondIdentifier, '_', group_id)) AS expectedIdentifier
FROM (
  SELECT
    *,
    -- 每两行一组计算组ID
    CEIL(row_num / 2) AS group_id
  FROM (
    SELECT
      rowId,
      firstIdentifier,
      secondIdentifier,
      timestamp,
      -- 分区内按timestamp排序生成行号
      ROW_NUMBER() OVER (PARTITION BY firstIdentifier, secondIdentifier ORDER BY timestamp ASC) AS row_num
    FROM log
  ) t1
) t2;

可选：生成类似A/B/C的可读标签 #

如果不需要MD5哈希，想要类似示例中的A、B、C这种字母标签，可以用CHAR()函数转换组ID（注意组数量不能超过26，否则需要扩展逻辑）：

SELECT
  rowId,
  firstIdentifier,
  secondIdentifier,
  timestamp,
  -- 组1→A，组2→B，以此类推
  CHAR(64 + group_id) AS expectedIdentifier
FROM (
  SELECT
    *,
    CEIL(row_num / 2) AS group_id
  FROM (
    SELECT
      rowId,
      firstIdentifier,
      secondIdentifier,
      timestamp,
      ROW_NUMBER() OVER (PARTITION BY firstIdentifier, secondIdentifier ORDER BY timestamp ASC) AS row_num
    FROM log
  ) t1
) t2;

这个逻辑可以完美适配任意大小的分区：不管分区有2行、3行、5行还是更多，都能严格按每两行一组分配标识，剩余单行单独成组。

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