如何编写BigQuery SQL脚本按自定义优先级返回数据？

样本数据 #

需求 #

按自定义优先级（Token 7 优先，Token 1 次之，Token 2 最后），为每个ID筛选对应数据，最终输出如下：

解决方案 #

核心思路是用CASE语句给自定义优先级赋予可排序的数值，再结合窗口函数对每个ID分组后按优先级排序，取排名第一的行。

完整SQL语句 #

WITH ranked_data AS (
  SELECT
    ID,
    MetaData,
    Data,
    ROW_NUMBER() OVER (
      PARTITION BY ID
      ORDER BY 
        CASE MetaData
          WHEN 'Token 7' THEN 1
          WHEN 'Token 1' THEN 2
          WHEN 'Token 2' THEN 3
          ELSE 4 -- 处理未定义的其他Token
        END ASC
    ) AS rn
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的实际表路径
)
SELECT
  ID,
  MetaData,
  Data
FROM
  ranked_data
WHERE
  rn = 1;

语句说明 #

1. CTE ranked_data：为每条数据生成排名rn
   • PARTITION BY ID：按ID分组，保证每个ID独立计算排名
   • ORDER BY CASE ...：自定义优先级映射，优先级越高的Token对应数值越小，排序时越靠前
2. 主查询：筛选每个ID中排名为1的行，即该ID下优先级最高的数据

如果同一ID下存在多条同最高优先级的记录，ROW_NUMBER()会随机选取其中一行；若需保留所有同优先级记录，可将ROW_NUMBER()替换为RANK()，并筛选rn <= 1。

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