如何按账户分组，基于前序行值实现条件标记列生成？

现有如下结构的用户数据表格：

需要新增been_bad列，规则为：按账户分组，若当前月份及之前的记录中出现过bad='y'，则标记为y，否则为空，预期结果如下：

核心思路是按account分组，对每个账户的记录按month排序后，计算累计状态标记——一旦出现过bad='y'，后续所有行都保持y。无需循环，用窗口函数或变量即可实现，不同SQL方言的具体写法如下：

通用SQL（适用于PostgreSQL、BigQuery、SQL Server等支持标准窗口函数的数据库） #

利用MAX()窗口函数，对每个账户的bad列从第一条记录到当前行取最大值：

SELECT
    account,
    month,
    bad,
    CASE WHEN MAX(bad) OVER (PARTITION BY account ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) = 'y' THEN 'y' ELSE '' END AS been_bad
FROM your_table_name
ORDER BY account, month;

MySQL 8.0+ #

支持标准窗口函数，写法和上述通用SQL一致。如果是MySQL 5.x版本（不支持窗口函数），可以用变量实现：

SELECT
    account,
    month,
    bad,
    @been_bad := CASE
        WHEN account != @prev_account THEN IF(bad = 'y', 'y', '')
        ELSE IF(@been_bad = 'y' OR bad = 'y', 'y', '')
    END AS been_bad,
    @prev_account := account
FROM your_table_name,
     (SELECT @prev_account := '', @been_bad := '') AS vars
ORDER BY account, month;

逻辑解释 #

• 窗口函数写法：MAX(bad) OVER (...)会按账户分组、月份排序，计算从第一行到当前行的bad最大值。因为'y'比空字符串优先级高，只要之前出现过y，最大值就是y，否则为空。
• 变量写法：通过@prev_account跟踪当前账户，@been_bad记录累计状态，切换账户时重置状态，否则继承之前的状态或更新为y。

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