如何快速设置flag标记：检测variable从1→0及2→1的异常变更

一、标记存在variable从1→0变化的组织 #

当目标组织的variable出现从1到0的变化时，将该组织所有行的flag设为1，否则为0。

实现代码（SQL） #

WITH org_change_detection AS (
    SELECT
        organisation,
        time,
        variable,
        -- 标记当前行是否为1→0的变化点
        CASE 
            WHEN LAG(variable) OVER (PARTITION BY organisation ORDER BY time) = 1 
                 AND variable = 0 
            THEN 1 
            ELSE 0 
        END AS is_drop_event
    FROM your_table_name
)
SELECT
    organisation,
    time,
    variable,
    -- 组织内只要有一次变化，所有行flag设为1
    MAX(is_drop_event) OVER (PARTITION BY organisation) AS flag
FROM org_change_detection;

核心逻辑 #

1. 使用LAG()窗口函数，按组织分组、时间排序，获取当前行的上一行variable值。
2. 识别出所有发生1→0变化的行，标记为is_drop_event=1。
3. 对每个组织取is_drop_event的最大值，实现“只要有一次变化，全组织标记”的效果。

二、标记存在variable从2→1变化的组织 #

需求逻辑一致，仅调整变化的判断条件，即可实现对2→1变化的检测：

实现代码（SQL） #

WITH org_change_detection AS (
    SELECT
        organisation,
        time,
        variable,
        -- 标记当前行是否为2→1的变化点
        CASE 
            WHEN LAG(variable) OVER (PARTITION BY organisation ORDER BY time) = 2 
                 AND variable = 1 
            THEN 1 
            ELSE 0 
        END AS is_drop_event
    FROM your_table_name
)
SELECT
    organisation,
    time,
    variable,
    MAX(is_drop_event) OVER (PARTITION BY organisation) AS flag
FROM org_change_detection;

核心逻辑 #

与上一方案完全一致，仅将变化判断条件替换为“上一行值为2，当前行值为1”，最终通过聚合实现全组织标记。

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