azure 如何在MERGE语句中使用Window函数？

wnavrhmk 于 2023-06-24 发布在其他

关注(0)|答案(1)|浏览(103)

我正在Azure Synapse中创建一个SQL脚本，以将维度数据从Staging表[Source]转换到其Storage表[Target]。
使用ROW_NUMBER（）函数创建带有键的目标：

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
    WITH
    (
        DISTRIBUTION = HASH(station_key),
        CLUSTERED COLUMNSTORE INDEX
    )
    AS  SELECT 
            ROW_NUMBER() OVER(ORDER BY [station_id],[station_name],[station_latitude],[sattion_longitude] ASC) AS station_key, 
            [station_id],
            [station_name]          AS name,
            [station_latitude]      AS latitude,
            [sattion_longitude]     AS longitude
        FROM
            stg.stations;
GO

我试图使用MERGE处理表中的SCD类型1，但我无法处理Insert语句中的键，错误表示Window Functions Could Only Be Used with SELECT语句。
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_key, station_id, name, latitude, longitude)
    VALUES
    (
        ROW_NUMBER() OVER(ORDER BY [station_id],[station_name],[station_latitude],[sattion_longitude] ASC),
        s.station_id,
        s.station_name,
        s.station_latitude,
        s.sattion_longitude
    )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OPTION(LABEL= 'MERGE Stations')

我真的很感激你能提供的任何帮助。

Azure

来源：https://stackoverflow.com/questions/76412582/how-to-use-window-function-in-merge-statement

1条答案

按热度按时间

f8rj6qna1#

我已经尝试过这种方法，但没有在Merge中使用SELECT语句。
例如，我创建了两个表，分别称为Source01和Target01，以实现SCD Type 01，它将执行更新和插入。来源：

CREATE  TABLE Source01 (
id INT,

name VARCHAR(50),

age INT,

city VARCHAR(50)

);

Target Create语句：

CREATE  TABLE Target01 (
id INT,
name VARCHAR(50),
age INT,
city VARCHAR(50),
row_num INT,
CONSTRAINT PK_Target01 PRIMARY  KEY  NONCLUSTERED (id) NOT ENFORCED
) WITH (
DISTRIBUTION = HASH(id),
CLUSTERED COLUMNSTORE INDEX
);  
INSERT  INTO Target01 (id, name, age, city, row_num)SELECT
id,

name,

age,

city,

ROW_NUMBER() OVER (ORDER  BY id) AS row_num

FROM Source01;

我在ID列上使用ROW_NUMBER（）函数。在执行Merge语句之前，行计数为14。

现在在source01我正在更新2条记录。插入1行。

-- Update age and city for John Doe
UPDATE Source01
SET age = 26,
city = 'Chicago'
WHERE id = 1;
-- Update age and city for Jane Smith
UPDATE Source01
SET age = 33,
city = 'San Francisco'
WHERE id = 2;
-- Insert a new record in the Source table
INSERT  INTO Source01 VALUES(16, 'Samantha Green', 31, 'Austin');

在更新Source表之前

使用MERGE语句。更新目标表中2条记录，插入新行。

合并声明：

MERGE  INTO Target01 AS T
USING Source01 AS S
ON T.id = S.id
WHEN  MATCHED  THEN
UPDATE  SET
T.name = S.name,
T.age = S.age,
T.city = S.city
WHEN  NOT  MATCHED  THEN
INSERT (id, name, age, city)
VALUES (S.id, S.name, S.age, S.city);

更新后

赞(0）回复(0）举报 2023-06-24

我来回答

azure 如何在MERGE语句中使用Window函数？

1条答案

相关问题

热门标签

最新问答