如何在Azure Synapse的MERGE语句中使用窗口函数？

错误原因 #

MERGE语句的VALUES子句不支持窗口函数（比如ROW_NUMBER()），窗口函数仅允许在SELECT查询或子查询中使用，这就是你遇到窗口函数仅可用于SELECT语句错误的核心原因。

解决方案 #

针对SCD Type 1的维度数据同步场景，提供两种可行的处理方式：

方式一：在USING子句中预先计算新增行的station_key #

先获取目标表当前最大的station_key，再给源表中不存在于目标表的行分配递增的新键，避免在INSERT的VALUES段直接使用窗口函数：

DECLARE @max_key INT;
SELECT @max_key = ISNULL(MAX(station_key), 0) FROM prd.dim_stations;

MERGE INTO prd.dim_stations AS p 
USING (
    SELECT 
        s.station_id,
        s.station_name,
        s.station_latitude,
        s.sattion_longitude,
        -- 为新增行计算连续递增的station_key
        ROW_NUMBER() OVER(ORDER BY s.station_id, s.station_name, s.station_latitude, s.sattion_longitude ASC) + @max_key AS new_station_key
    FROM stg.stations AS s
) AS s
ON s.station_id = p.station_id
WHEN MATCHED
THEN UPDATE SET
    p.latitude = s.station_latitude,
    p.longitude = s.sattion_longitude,
    p.name = s.station_name
WHEN NOT MATCHED BY TARGET THEN
INSERT (station_key, station_id, name, latitude, longitude)
    VALUES
    (
        s.new_station_key,
        s.station_id,
        s.station_name,
        s.station_latitude,
        s.sattion_longitude
    )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OPTION(LABEL= 'MERGE Stations');

方式二：改用IDENTITY列自动生成station_key #

更简洁可靠的方式是将station_key设为IDENTITY列，让系统自动维护递增键值，彻底避免手动计算的麻烦：

1. 重建目标表（若允许重置表结构）：

IF NOT EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = 'prd' and t.name = 'dim_stations')
    CREATE TABLE prd.dim_stations
    (
        station_key INT IDENTITY(1,1) NOT NULL,
        station_id VARCHAR(50) NOT NULL, -- 替换为实际数据类型
        name VARCHAR(100) NOT NULL, -- 替换为实际数据类型
        latitude DECIMAL(10,8) NULL, -- 替换为实际数据类型
        longitude DECIMAL(11,8) NULL, -- 替换为实际数据类型
        CONSTRAINT PK_dim_stations PRIMARY KEY NONCLUSTERED (station_key)
    )
    WITH
    (
        DISTRIBUTION = HASH(station_key),
        CLUSTERED COLUMNSTORE INDEX
    );
GO

2. 简化后的MERGE语句：

MERGE INTO prd.dim_stations AS p 
USING stg.stations AS s
ON s.station_id = p.station_id
WHEN MATCHED
THEN UPDATE SET
    p.latitude = s.station_latitude,
    p.longitude = s.sattion_longitude,
    p.name = s.station_name
WHEN NOT MATCHED BY TARGET THEN
INSERT (station_id, name, latitude, longitude)
    VALUES
    (
        s.station_id,
        s.station_name,
        s.station_latitude,
        s.sattion_longitude
    )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OPTION(LABEL= 'MERGE Stations');

额外提示 #

• 方式一中，若目标表为空，@max_key会被设为0，新增行的键会从1开始，与你最初的建表逻辑保持一致。
• 注意原代码中的拼写错误：sattion_longitude应为station_longitude，建议修正以避免数据同步错误。

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