BigQuery中基于条件判断home_icon-Terdekat是否为最高order

需求说明 #

给定如下原始数据表：

name    type                        order
Hokben  6_image-Resto Siap Santap   3
Hokben  Hokben                      2
Hokben  home_icon-Terdekat          4
Hokben  home_icon-Terlaris          2
Jelly   Jelly                       2
Jelly   home_icon-Terlaris          1
Aqua    Resto                       3
Aqua    home_icon-Terdekat          5

需要按name分组，仅当记录的type为home_icon-Terdekat且其order值是该分组下的最大值时，新增result列为TRUE，其余所有情况result均为FALSE，最终得到符合要求的结果表。

解决方案代码 #

使用BigQuery窗口函数实现逻辑，SQL语句如下：

SELECT
  name,
  type,
  `order`,
  CASE
    WHEN type = 'home_icon-Terdekat' AND `order` = MAX(`order`) OVER (PARTITION BY name) THEN TRUE
    ELSE FALSE
  END AS result
FROM
  `your-project.your-dataset.your-table`
ORDER BY
  name, `order` DESC;

代码解释 #

1. 分组最大值计算：MAX(order) OVER (PARTITION BY name) 通过窗口函数，为每条记录计算其所属name分组下的最大order值，无需提前聚合分组数据。
2. 结果列生成：通过CASE语句做双重判断——仅当记录类型是home_icon-Terdekat，同时自身order等于分组最大值时，返回TRUE，其余情况统一返回FALSE。
3. 保留字处理：order是BigQuery的保留关键字，必须用反引号`包裹，避免语法错误。

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