Azure Synapse Serverless Pool外部表取每组最新记录的高性能查询咨询

以下方案均基于T-SQL实现，无需依赖Spark，针对Serverless SQL Pool的分布式计算特性做了优化：

方案一：窗口函数法（优先推荐，性能最优） #

用ROW_NUMBER()窗口函数做分组排序取第一条，是官方推荐的Top N per group实现方式，仅需一次全表扫描：

SELECT col_key, col_a, col_b, col_date
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY col_key ORDER BY col_date DESC) AS row_num
    FROM 你的外部表名称
    -- 若外部表有分区字段，此处添加分区裁剪条件，例如：WHERE dt >= '2023-01-01'，可大幅减少扫描数据量
) AS t
WHERE row_num = 1

优势： #

• 天然去重：即使同一个col_key存在多条相同col_date的记录，也可以通过在ORDER BY后追加其他排序字段保证仅返回一条
• 自动列裁剪：如果外部表是Parquet/Delta等列存格式，仅会扫描查询用到的4个字段，无需读取全表所有列
• 分布式优化：Serverless Pool对窗口函数做了专门的下推优化，比关联写法性能高30%以上

方案二：分组关联法（适合分组基数较小的场景） #

如果col_key的唯一值数量低于1000，该方案性能和窗口函数相当：

SELECT a.*
FROM 你的外部表名称 a
INNER JOIN (
    SELECT col_key, MAX(col_date) AS max_col_date
    FROM 你的外部表名称
    -- 同样可添加分区裁剪条件
    GROUP BY col_key
) AS b
ON a.col_key = b.col_key AND a.col_date = b.max_col_date

注意事项： #

• 同一个col_key存在多条相同col_date的记录时，会返回全部匹配项，无法自动去重
• 分组基数较大时会触发两次全表扫描，性能远低于窗口函数方案

通用性能优化建议 #

• 外部表优先使用Parquet/Delta Lake格式存储，比CSV格式查询效率高5~10倍
• 重复查询时可开启结果缓存：执行查询前先运行SET RESULT_SET_CACHING ON;，后续相同查询会直接返回缓存结果
• 查询频率极高的场景可以创建物化视图预计算结果，查询时直接读取物化视图即可，无需每次扫描原始文件

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