如何在BigQuery中按指定条件将用户交易状态合并至单行

要实现按用户、日期、交易维度合并交易状态，核心是利用状态优先级规则（Completed > In Progress > Hold），通过分组聚合判断各状态的存在性，直接返回最高优先级的状态。

方法1：基于存在性判断的CASE语句 #

这是最直观的写法，通过统计各状态的出现次数，按优先级返回结果（只需扫描一次表，效率较高）：

SELECT
    user_id,
    transaction_date,
    transaction_dimension,
    CASE
        -- 只要存在Completed交易，直接返回Completed
        WHEN COUNT(CASE WHEN status = 'Completed' THEN 1 END) > 0 THEN 'Completed'
        -- 无Completed但存在In Progress，返回In Progress
        WHEN COUNT(CASE WHEN status = 'In Progress' THEN 1 END) > 0 THEN 'In Progress'
        -- 其余情况统一返回Hold
        ELSE 'Hold'
    END AS consolidated_status
FROM MyTable
-- 针对特定用户筛选，可移除该条件处理全量数据
WHERE user_id = 'A1'
GROUP BY user_id, transaction_date, transaction_dimension;

方法2：利用优先级排序取最高状态 #

给每个状态分配数值优先级，分组后取优先级最高的状态：

SELECT DISTINCT
    user_id,
    transaction_date,
    transaction_dimension,
    -- 按优先级倒序，取分组内第一条状态
    FIRST_VALUE(status) OVER (
        PARTITION BY user_id, transaction_date, transaction_dimension
        ORDER BY CASE status
            WHEN 'Completed' THEN 3
            WHEN 'In Progress' THEN 2
            WHEN 'Hold' THEN 1
        END DESC
    ) AS consolidated_status
FROM MyTable
WHERE user_id = 'A1';

你的查询返回重复结果的原因 #

你之前的查询没有按user_id、transaction_date、transaction_dimension三个维度做分组，或者分组后未对status做聚合处理，而是直接返回每笔交易的原始状态，导致同一个用户-日期-维度组合下出现多条重复记录。

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