BigQuery中如何基于另一字段取值筛选符合条件用户的全量数据

你的需求是筛选出至少有1条action='1'的用户，并保留这些用户的所有原始行，下面给出两种更高效简洁的实现方式：

方案1：窗口函数实现（性能最优） #

仅需要对全表扫描1次，不需要聚合、拆分操作，数据量越大性能优势越显著，适合大规模数据集场景：

WITH table_1 AS (
    SELECT '1' AS user, '0' AS action
    UNION ALL SELECT '1' AS user, '1' AS action
    UNION ALL SELECT '2' AS user, '0' AS action
    UNION ALL SELECT '2' AS user, '0' AS action
    UNION ALL SELECT '3' AS user, '1' AS action
    UNION ALL SELECT '3' AS user, '0' AS action
)
SELECT user, action
FROM (
    SELECT 
        *,
        MAX(action) OVER(PARTITION BY user) AS max_action
    FROM table_1
)
WHERE max_action = '1'

逻辑说明：按用户分组取最大的action值，只要用户有过action=1的记录，最大值就为1，直接过滤即可保留该用户所有行。如果action是数值类型，直接把字符串的'1'改成数值1即可。

方案2：IN子查询实现（写法最简洁易读） #

逻辑直观易懂，适合快速写查询、数据集规模中等的场景：

WITH table_1 AS (
    SELECT '1' AS user, '0' AS action
    UNION ALL SELECT '1' AS user, '1' AS action
    UNION ALL SELECT '2' AS user, '0' AS action
    UNION ALL SELECT '2' AS user, '0' AS action
    UNION ALL SELECT '3' AS user, '1' AS action
    UNION ALL SELECT '3' AS user, '0' AS action
)
SELECT * FROM table_1
WHERE user IN (
    SELECT DISTINCT user FROM table_1 WHERE action = '1'
)

优化点说明 #

对比你原来的实现方案，以上两种方案的优势：

• 省去了ARRAY_AGG聚合和UNNEST展开的步骤，大幅降低CPU和内存开销
• 不需要额外处理字段映射，如果原始表有更多字段，直接查询即可，不需要修改聚合逻辑
• 逻辑可读性更高，后续维护调整更方便

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