SQL无需迭代/循环：按邮箱聚合取各字段最新非空值

需求说明 #

我有一个包含email、city、state、zip和email_effective_from（日期字段）的数据集，需要为每个邮箱生成一行数据，其中city、state、zip字段填充该邮箱对应的最新非空值。原查询通过拆分三个临时表再全连接的方式实现，但运行耗时极久，寻求更高效的方案。

原查询代码：

row_number()over(partition by Email_Addr order by email_effective_from desc) as rn1
into #d1 from data where zip is not null and email_addr is not null;
select Email_Addr,city,
row_number()over(partition by Email_Addr order by email_effective_from desc) as rn2 into #d2
from data where city is not null and email_addr is not null;
select Email_Addr,[state],
row_number()over(partition by Email_Addr order by email_effective_from desc) as rn3 into #d3
from data  where state is not null and email_addr is not null;
select a.email_addr,a.zip,b.city,c.[state] into #dff from #d1 a
full outer join #d2 b on a.email_addr=b.email_addr
full outer join #d3 c on a.email_addr=c.email_addr

优化方案 #

原方案的问题在于三次扫描原表，再进行两次全连接，IO和计算开销都很大。以下是几种更高效的实现方式，仅需扫描原表1~3次：

方案1：使用LAST_VALUE + IGNORE NULLS（支持数据库：SQL Server 2022+、PostgreSQL、Oracle等） #

利用窗口函数LAST_VALUE结合IGNORE NULLS，直接在每行获取该邮箱对应的最新非空字段值，最后去重即可：

WITH ranked_data AS (
    SELECT
        Email_Addr,
        LAST_VALUE(city) OVER (
            PARTITION BY Email_Addr 
            ORDER BY email_effective_from 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            IGNORE NULLS
        ) AS latest_city,
        LAST_VALUE([state]) OVER (
            PARTITION BY Email_Addr 
            ORDER BY email_effective_from 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            IGNORE NULLS
        ) AS latest_state,
        LAST_VALUE(zip) OVER (
            PARTITION BY Email_Addr 
            ORDER BY email_effective_from 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            IGNORE NULLS
        ) AS latest_zip,
        ROW_NUMBER() OVER (PARTITION BY Email_Addr ORDER BY email_effective_from DESC) AS rn
    FROM data
    WHERE Email_Addr IS NOT NULL
)
SELECT Email_Addr, latest_city, latest_state, latest_zip
INTO #dff
FROM ranked_data
WHERE rn = 1;

方案2：子查询聚合获取每个字段的最新值（兼容多数数据库） #

通过子查询分别获取每个邮箱下各字段的最新非空记录，再关联，避免全连接的性能损耗：

SELECT
    base.Email_Addr,
    city_latest.city,
    state_latest.[state],
    zip_latest.zip
INTO #dff
FROM (
    -- 获取所有不重复的有效邮箱
    SELECT DISTINCT Email_Addr
    FROM data
    WHERE Email_Addr IS NOT NULL
) base
LEFT JOIN (
    -- 获取每个邮箱最新的非空city
    SELECT Email_Addr, city
    FROM (
        SELECT
            Email_Addr,
            city,
            ROW_NUMBER() OVER (PARTITION BY Email_Addr ORDER BY email_effective_from DESC) AS rn
        FROM data
        WHERE city IS NOT NULL AND Email_Addr IS NOT NULL
    ) t
    WHERE rn = 1
) city_latest ON base.Email_Addr = city_latest.Email_Addr
LEFT JOIN (
    -- 获取每个邮箱最新的非空state
    SELECT Email_Addr, [state]
    FROM (
        SELECT
            Email_Addr,
            [state],
            ROW_NUMBER() OVER (PARTITION BY Email_Addr ORDER BY email_effective_from DESC) AS rn
        FROM data
        WHERE [state] IS NOT NULL AND Email_Addr IS NOT NULL
    ) t
    WHERE rn = 1
) state_latest ON base.Email_Addr = state_latest.Email_Addr
LEFT JOIN (
    -- 获取每个邮箱最新的非空zip
    SELECT Email_Addr, zip
    FROM (
        SELECT
            Email_Addr,
            zip,
            ROW_NUMBER() OVER (PARTITION BY Email_Addr ORDER BY email_effective_from DESC) AS rn
        FROM data
        WHERE zip IS NOT NULL AND Email_Addr IS NOT NULL
    ) t
    WHERE rn = 1
) zip_latest ON base.Email_Addr = zip_latest.Email_Addr;

方案3：一次扫描聚合所有字段（SQL Server兼容） #

利用MAX()函数配合CASE，仅扫描一次原表即可聚合出每个字段的最新非空值：

WITH latest_records AS (
    SELECT
        Email_Addr,
        -- 获取最新非空city
        MAX(CASE WHEN rn_city = 1 THEN city END) AS latest_city,
        -- 获取最新非空state
        MAX(CASE WHEN rn_state = 1 THEN [state] END) AS latest_state,
        -- 获取最新非空zip
        MAX(CASE WHEN rn_zip = 1 THEN zip END) AS latest_zip
    FROM (
        SELECT
            Email_Addr,
            city,
            [state],
            zip,
            ROW_NUMBER() OVER (PARTITION BY Email_Addr ORDER BY CASE WHEN city IS NOT NULL THEN email_effective_from END DESC) AS rn_city,
            ROW_NUMBER() OVER (PARTITION BY Email_Addr ORDER BY CASE WHEN [state] IS NOT NULL THEN email_effective_from END DESC) AS rn_state,
            ROW_NUMBER() OVER (PARTITION BY Email_Addr ORDER BY CASE WHEN zip IS NOT NULL THEN email_effective_from END DESC) AS rn_zip
        FROM data
        WHERE Email_Addr IS NOT NULL
    ) t
    GROUP BY Email_Addr
)
SELECT Email_Addr, latest_city, latest_state, latest_zip
INTO #dff
FROM latest_records;

优化说明 #

• 所有优化方案均减少了原表的扫描次数，避免了多次临时表创建和全连接操作，大幅降低IO开销。
• 优先选择方案1（如果数据库支持IGNORE NULLS），代码简洁且性能最优；方案2兼容性最好，适用于所有支持窗口函数的数据库；方案3适合需要一次扫描完成所有计算的场景。

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