Big Query按组标记重复结果：按ID分组判断是否存在任意测试失败记录

需求说明 #

现有一组测试记录数据集，需按ID对数据分组，判断每组是否在任意时间点存在Test字段为FAIL的情况，为该组所有记录新增EVERFAIL字段标注对应结果。

原始数据样例 #

ID    Date     Test
A     1/2021   PASS
A     2/2021   PASS
A     3/2021   FAIL
A     4/2021   PASS
B     1/2021   PASS
B     2/2021   PASS
B     3/2021   PASS
B     4/2021   PASS

期望输出结果 #

ID    Date     Test    EVERFAIL
A     1/2021   PASS    TRUE
A     2/2021   PASS    TRUE
A     3/2021   FAIL    TRUE
A     4/2021   PASS    TRUE
B     1/2021   PASS    FALSE
B     2/2021   PASS    FALSE
B     3/2021   PASS    FALSE
B     4/2021   PASS    FALSE

常用实现方案 #

SQL实现 #

使用窗口函数即可在不聚合原表行的前提下完成分组判定：

SELECT 
    ID,
    Date,
    Test,
    CASE WHEN MAX(CASE WHEN Test = 'FAIL' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) = 1 
         THEN 'TRUE' ELSE 'FALSE' END AS EVERFAIL
FROM test_table;
-- 支持布尔类型的数据库（如PostgreSQL）可以简化写法：
-- SELECT *, bool_or(Test = 'FAIL') OVER (PARTITION BY ID) AS EVERFAIL FROM test_table;

Python Pandas实现 #

用groupby+transform完成分组判定，结果会自动匹配到分组内的每一行：

import pandas as pd
# 读取数据，此处以csv文件为例
df = pd.read_csv('test_data.csv')
# 新增EVERFAIL字段
df['EVERFAIL'] = df.groupby('ID')['Test'].transform(lambda x: (x == 'FAIL').any())

Excel实现 #

假设数据A列为ID、C列为Test，首行是表头，第二行开始为有效数据，在D2单元格输入以下公式后下拉填充即可：

=COUNTIFS(A:A,A2,C:C,"FAIL")>0

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