查找员工首次非0 Direct_Reports对应最小AS_OF日期的SQL优化方案

优化方案 #

原SQL效率低的核心原因是需要先过滤所有Direct_Reports>0的记录再做分组聚合，表数据量级较大时全表扫描成本过高。你可以使用窗口函数方案实现更低的资源消耗，两种兼容不同SQL引擎的写法如下：

方案1：支持QUALIFY语法的引擎（Snowflake、BigQuery、Spark SQL等） #

SELECT DISTINCT
    Employee_ID,
    FIRST_VALUE(As_Of) OVER (
        PARTITION BY Employee_ID 
        ORDER BY As_Of
    ) AS manager_start_date
FROM table
WHERE Direct_Reports > 0
QUALIFY ROW_NUMBER() OVER (PARTITION BY Employee_ID ORDER BY As_Of) = 1

该写法仅保留每个员工第一个Direct_Reports>0的记录，不需要二次分组聚合，计算链路更短。

方案2：兼容全SQL引擎写法 #

SELECT
    Employee_ID,
    MIN(As_Of) AS manager_start_date
FROM (
    SELECT
        Employee_ID,
        As_Of,
        ROW_NUMBER() OVER (
            PARTITION BY Employee_ID 
            ORDER BY As_Of
        ) AS first_qualified_rn
    FROM table
    WHERE Direct_Reports > 0
) t
WHERE first_qualified_rn = 1
GROUP BY 1

额外性能优化建议 #

如果你的表支持二级索引，给(Employee_ID, Direct_Reports, As_Of)创建联合索引，可避免全表扫描，两种写法的性能均可提升50%以上。

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