如何用Union All填充所有字段？双CTE数据合并异常排查

方法1：通过JOIN关联两个CTE #

因为第一个CTE仅返回一行数据，我们可以将第二个CTE的所有行与第一个CTE的行进行关联，从而将firmware值填充到每一行：

WITH 
software_production AS (
    SELECT DISTINCT
        f.client_id,
        m.firmware,
        f.created_at
    FROM `production-us`.`dev_warehouse`.`dev_dataset` AS f, UNNEST(motion_trackers) AS m
    WHERE f.client_id IS NOT NULL
        AND f.client_id = 'US-211111'
        AND f.client_id != '-2'
),
next_created_at AS (
    SELECT
        *,
        LEAD(created_at) OVER (PARTITION BY client_id ORDER BY created_at ASC) AS created_at_lead
    FROM software_production
),
next_created_at_final AS (
    SELECT
        * EXCEPT(created_at_lead),
        CASE
            WHEN created_at_lead IS NULL THEN TIMESTAMP('9999-12-31 00:00:00')
            ELSE created_at_lead
        END AS created_at_lead
    FROM next_created_at
    WHERE created_at_lead IS NULL OR (created_at != created_at_lead)
),
first_dt_versions AS (
    SELECT
        client_id,
        system_created_at AS created_at,
        device.dt_semantic_version AS device_dt_version,
        configuration_change_timestamp
    FROM `production-us`.`dev_warehouse`.`dev_client_system`
    WHERE device.dt_semantic_version IS NOT NULL
        AND client_id = 'US-211111' 
),
dt_conversion AS (
    SELECT DISTINCT
        client_id,
        created_at,
        device_dt_version,
        configuration_change_timestamp AS dt_version_configuration_start_date,
        LEAD(configuration_change_timestamp) OVER(PARTITION BY client_id ORDER BY configuration_change_timestamp ASC) AS dt_version_configuration_end_date
    FROM first_dt_versions 
    GROUP BY client_id, created_at, device_dt_version, configuration_change_timestamp
)
SELECT
    d.client_id,
    d.device_dt_version,
    n.firmware,
    d.dt_version_configuration_start_date,
    d.dt_version_configuration_end_date
FROM dt_conversion d
INNER JOIN next_created_at_final n 
    ON d.client_id = n.client_id;

方法2：使用子查询直接获取firmware值 #

如果只需要firmware字段，不需要第一个CTE的其他字段，可以直接在第二个CTE中嵌入子查询获取对应client_id的firmware值，代码更简洁：

WITH 
first_dt_versions AS (
    SELECT
        client_id,
        system_created_at AS created_at,
        device.dt_semantic_version AS device_dt_version,
        configuration_change_timestamp
    FROM `production-us`.`dev_warehouse`.`dev_client_system`
    WHERE device.dt_semantic_version IS NOT NULL
        AND client_id = 'US-211111' 
),
dt_conversion AS (
    SELECT DISTINCT
        client_id,
        created_at,
        device_dt_version,
        configuration_change_timestamp AS dt_version_configuration_start_date,
        LEAD(configuration_change_timestamp) OVER(PARTITION BY client_id ORDER BY configuration_change_timestamp ASC) AS dt_version_configuration_end_date,
        -- 子查询获取当前client_id对应的firmware值
        (SELECT DISTINCT m.firmware
         FROM `production-us`.`dev_warehouse`.`dev_dataset` AS f, UNNEST(motion_trackers) AS m
         WHERE f.client_id = d.client_id
             AND f.client_id IS NOT NULL
             AND f.client_id != '-2') AS firmware
    FROM first_dt_versions d
    GROUP BY client_id, created_at, device_dt_version, configuration_change_timestamp
)
SELECT
    client_id,
    device_dt_version,
    firmware,
    dt_version_configuration_start_date,
    dt_version_configuration_end_date
FROM dt_conversion;

为什么Union All无法满足需求 #

Union All的作用是将两个结果集纵向拼接，即把第一个CTE的行和第二个CTE的行放在同一个结果集中，但不会横向填充字段。你需要的是将第一个CTE的firmware值附加到第二个CTE的每一行，这属于横向关联的需求，因此应该使用JOIN或子查询，而非Union All。

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