如何在Snowflake中无需子查询生成标识最大order_status的布尔列

可以实现该需求，不需要嵌套子查询，直接使用原生窗口函数即可完成计算。

实现逻辑 #

• 需求本质是判断当前行的created_at是否为指定分组下的最大值，直接用MAX() OVER()窗口函数即可在当前查询层级完成聚合计算，不需要额外嵌套子查询
• 等值判断结果直接返回Snowflake原生布尔类型，结果为TRUE/FALSE，符合输出要求
• 若按id维度判断每个id对应的最新状态行，窗口函数增加PARTITION BY id子句；若判断全局数据的最新行，去掉PARTITION BY子句即可

完整可执行代码 #

WITH t1 AS (
SELECT 'A' AS id, 'created' AS status, '2021-05-18 18:30:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'created' AS status, '2021-05-19 11:30:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'pending' AS status, '2021-05-19 12:00:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'successful' AS status, '2021-05-20 18:30:00'::timestamp AS created_at
)
SELECT 
    *,
    created_at = MAX(created_at) OVER (PARTITION BY id) AS is_latest_status
FROM t1

输出说明 #

针对示例数据的执行结果如下：

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