如何按条件为ID打标记？识别无pending/info_required状态的顺畅订单流程ID

实现逻辑 #

• 标记规则：同一ID下所有订单状态都不存在pending或info_required时，is_straight为true，否则为false
• 提供两种实现方式：保留原始全量行的窗口函数版本、仅输出ID和对应标记的聚合版本

全量行保留版本（窗口函数实现） #

适合需要保留原始订单状态、时间等所有字段的场景：

WITH t1 AS (
SELECT 'a' AS id, 'created' AS status, '2021-11-02 15:04:07'::timestamp AS created_at UNION ALL
SELECT 'a' AS id, 'created' AS status, '2021-11-03 13:23:34'::timestamp AS created_at UNION ALL
SELECT 'a' AS id, 'pending' AS status, '2021-11-07 04:04:46'::timestamp AS created_at UNION ALL
SELECT 'a' AS id, 'successful' AS status, '2021-11-07 13:25:05'::timestamp AS created_at UNION ALL
SELECT 'b' AS id, 'created' AS status, '2021-11-11 16:19:07'::timestamp AS created_at UNION ALL
SELECT 'b' AS id, 'successful' AS status, '2021-11-13 17:57:55'::timestamp AS created_at UNION ALL
SELECT 'c' AS id, 'created' AS status, '2021-11-15 01:09:23'::timestamp AS created_at UNION ALL
SELECT 'c' AS id, 'info_required' AS status, '2021-11-17 11:06:00'::timestamp AS created_at UNION ALL
SELECT 'c' AS id, 'successful' AS status, '2021-11-21 23:35:46'::timestamp AS created_at
)
SELECT 
    *,
    -- 通用SQL写法，兼容所有支持窗口函数的SQL引擎
    CASE WHEN COUNT(CASE WHEN status IN ('pending', 'info_required') THEN 1 END) OVER (PARTITION BY id) = 0 
         THEN true 
         ELSE false 
    END AS is_straight
    -- 简洁写法，兼容PostgreSQL、BigQuery等支持布尔聚合函数的引擎
    -- NOT bool_or(status IN ('pending', 'info_required')) OVER (PARTITION BY id) AS is_straight
FROM t1;

ID维度聚合版本 #

适合只需要获取每个ID对应标记结果的场景，性能更优：

WITH t1 AS (
SELECT 'a' AS id, 'created' AS status, '2021-11-02 15:04:07'::timestamp AS created_at UNION ALL
SELECT 'a' AS id, 'created' AS status, '2021-11-03 13:23:34'::timestamp AS created_at UNION ALL
SELECT 'a' AS id, 'pending' AS status, '2021-11-07 04:04:46'::timestamp AS created_at UNION ALL
SELECT 'a' AS id, 'successful' AS status, '2021-11-07 13:25:05'::timestamp AS created_at UNION ALL
SELECT 'b' AS id, 'created' AS status, '2021-11-11 16:19:07'::timestamp AS created_at UNION ALL
SELECT 'b' AS id, 'successful' AS status, '2021-11-13 17:57:55'::timestamp AS created_at UNION ALL
SELECT 'c' AS id, 'created' AS status, '2021-11-15 01:09:23'::timestamp AS created_at UNION ALL
SELECT 'c' AS id, 'info_required' AS status, '2021-11-17 11:06:00'::timestamp AS created_at UNION ALL
SELECT 'c' AS id, 'successful' AS status, '2021-11-21 23:35:46'::timestamp AS created_at
)
SELECT 
    id,
    CASE WHEN COUNT(CASE WHEN status IN ('pending', 'info_required') THEN 1 END) = 0 
         THEN true 
         ELSE false 
    END AS is_straight
FROM t1
GROUP BY id;

输出结果 #

聚合版本运行后结果如下：

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