Snowflake中实现两列值不同时新增行的SQL方案咨询

需求 #

处理SOURCE_TABLE数据，规则如下：

• 若Date与Observed值相同：保留原行，新增Boolean_date和Boolean_observed列，两列值均等于原表的Boolean
• 若Date与Observed值不同：生成两行数据：
  1. 保留原Date、Observed，Boolean_date取原Boolean，Boolean_observed取原Boolean的反值
  2. 将Date替换为原Observed，Boolean_date取原Boolean的反值，Boolean_observed取原Boolean

原表数据 #

SELECT * FROM SOURCE_TABLE;

期望输出 #

-- 最终结果

替代CROSS APPLY的Snowflake实现方案 #

Snowflake不支持CROSS APPLY，用UNION ALL拆分场景即可实现需求：

-- 场景1：日期相等的行
SELECT
    Info,
    "Date",
    Observed,
    Boolean AS Boolean_date,
    Boolean AS Boolean_observed
FROM SOURCE_TABLE
WHERE "Date" = Observed

UNION ALL

-- 场景2：日期不等的第一行
SELECT
    Info,
    "Date",
    Observed,
    Boolean AS Boolean_date,
    NOT Boolean AS Boolean_observed
FROM SOURCE_TABLE
WHERE "Date" <> Observed

UNION ALL

-- 场景3：日期不等的第二行
SELECT
    Info,
    Observed AS "Date",
    Observed,
    NOT Boolean AS Boolean_date,
    Boolean AS Boolean_observed
FROM SOURCE_TABLE
WHERE "Date" <> Observed

ORDER BY Info, "Date"; -- 按需排序，可省略

方案解释 #

• 场景1：直接处理日期相同的行，两个布尔列复用原Boolean值
• 场景2+3：对日期不同的行拆分成两行，分别对应Date和Observed的布尔状态，通过NOT取反实现相反值
• UNION ALL合并三个场景的结果，最后用ORDER BY整理输出顺序（根据实际需求调整）

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