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

huangapple
go评论101阅读模式
英文:

How to use Window Function in MERGE statement?

问题

我正在在Azure Synapse中创建一个SQL脚本，将维度数据从暂存表[Source]转换到存储表[Target]。

目标表使用ROW_NUMBER()函数创建一个键：

  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')
  2.     CREATE TABLE prd.dim_stations
  3.     WITH
  4.     (
  5.         DISTRIBUTION = HASH(station_key),
  6.         CLUSTERED COLUMNSTORE INDEX
  7.     )
  8.     AS  SELECT 
  9.             ROW_NUMBER() OVER(ORDER BY [station_id],[station_name],[station_latitude],[sattion_longitude] ASC) AS station_key, 
  10.             [station_id],
  11.             [station_name]          AS name,
  12.             [station_latitude]      AS latitude,
  13.             [sattion_longitude]     AS longitude
  14.         FROM
  15.             stg.stations;
  16. GO

我正在尝试在表中使用MERGE来处理SCD Type 1，但在Insert语句中无法处理键，出现错误，意味着窗口函数只能与SELECT语句一起使用。

MERGE语句如下：

  1. MERGE INTO prd.dim_stations AS p 
  2. USING stg.stations AS s
  3. ON s.station_id = p.station_id
  4. WHEN MATCHED
  5. THEN UPDATE SET
  6.     p.latitude = s.station_latitude,
  7.     p.longitude = s.sattion_longitude,
  8.     p.name = s.station_name
  9. WHEN NOT MATCHED BY TARGET THEN
  10. INSERT (station_key, station_id, name, latitude, longitude)
  11.     VALUES
  12.     (
  13.         ROW_NUMBER() OVER(ORDER BY [station_id],[station_name],[station_latitude],[sattion_longitude] ASC),
  14.         s.station_id,
  15.         s.station_name,
  16.         s.station_latitude,
  17.         s.sattion_longitude
  18.     )
  19. WHEN NOT MATCHED BY SOURCE THEN
  20.     DELETE
  21. OPTION(LABEL= 'MERGE Stations')

我非常感谢您提供的任何帮助。

英文:

I'm creating a SQL Script in Azure Synapse to transform dimension data from a Staging table [Source] to its Storage table [Target].

The target is created with a key using ROW_NUMBER() function:

  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')
  2.     CREATE TABLE prd.dim_stations
  3.     WITH
  4.     (
  5.         DISTRIBUTION = HASH(station_key),
  6.         CLUSTERED COLUMNSTORE INDEX
  7.     )
  8.     AS  SELECT 
  9.             ROW_NUMBER() OVER(ORDER BY [station_id],[station_name],[station_latitude],[sattion_longitude] ASC) AS station_key, 
  10.             [station_id],
  11.             [station_name]          AS name,
  12.             [station_latitude]      AS latitude,
  13.             [sattion_longitude]     AS longitude
  14.         FROM
  15.             stg.stations;
  16. GO

I'm trying to handle SCD Type 1 in the table using MERGE but I can't handle the key in the Insert Statement with an error that means Window Functions Could Only be Used with SELECT Statement.

The Merge Statement:

  1. MERGE INTO prd.dim_stations AS p 
  2. USING stg.stations AS s
  3. ON s.station_id = p.station_id
  4. WHEN MATCHED
  5. THEN UPDATE SET
  6.     p.latitude = s.station_latitude,
  7.     p.longitude = s.sattion_longitude,
  8.     p.name = s.station_name
  9. WHEN NOT MATCHED BY TARGET THEN
  10. INSERT (station_key, station_id, name, latitude, longitude)
  11.     VALUES
  12.     (
  13.         ROW_NUMBER() OVER(ORDER BY [station_id],[station_name],[station_latitude],[sattion_longitude] ASC),
  14.         s.station_id,
  15.         s.station_name,
  16.         s.station_latitude,
  17.         s.sattion_longitude
  18.     )
  19. WHEN NOT MATCHED BY SOURCE THEN
  20.     DELETE
  21. OPTION(LABEL= 'MERGE Stations')

I really appreciate any help you can provide.

答案1

得分: 0

我尝试过在合并中不使用SELECT语句来尝试这种方法。

例如，我创建了两个表，分别称为Source01和Target01，以实现SCD Type 01，该类型将执行更新和插入。

Source:

  1. CREATE TABLE Source01 (
  2. id INT,
  3. name VARCHAR(50),
  4. age INT,
  5. city VARCHAR(50)
  6. );

Target 创建语句:

  1. CREATE TABLE Target01 (
  2. id INT,
  3. name VARCHAR(50),
  4. age INT,
  5. city VARCHAR(50),
  6. row_num INT,
  7. CONSTRAINT PK_Target01 PRIMARY KEY NONCLUSTERED (id) NOT ENFORCED
  8. ) WITH (
  9. DISTRIBUTION = HASH(id),
  10. CLUSTERED COLUMNSTORE INDEX
  11. );  
  12. INSERT INTO Target01 (id, name, age, city, row_num)
  13. SELECT
  14. id,
  15. name,
  16. age,
  17. city,
  18. ROW_NUMBER() OVER (ORDER BY id) AS row_num
  19. FROM Source01;

在目标表中，我只是在ID列上使用ROW_NUMBER()函数。在执行合并语句之前，行数为14。

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

现在在Source01中，我更新了2条记录，并插入了1行。

  1. -- 更新 John Doe 的年龄和城市
  2. UPDATE Source01
  3. SET age = 26,
  4. city = 'Chicago'
  5. WHERE id = 1;
  6. -- 更新 Jane Smith 的年龄和城市
  7. UPDATE Source01
  8. SET age = 33,
  9. city = 'San Francisco'
  10. WHERE id = 2;
  11. -- 在源表中插入一条新记录
  12. INSERT INTO Source01 VALUES(16, 'Samantha Green', 31, 'Austin');

在更新Source表之前：

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

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

MERGE 语句:

  1. MERGE INTO Target01 AS T
  2. USING Source01 AS S
  3. ON T.id = S.id
  4. WHEN MATCHED THEN
  5. UPDATE SET
  6. T.name = S.name,
  7. T.age = S.age,
  8. T.city = S.city
  9. WHEN NOT MATCHED THEN
  10. INSERT (id, name, age, city)
  11. VALUES (S.id, S.name, S.age, S.city);

在更新后：

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

英文:

I have tried this approach with out using the SELECT statement in the Merge.

For example I have created 2 table Called the Source01 and Target01 to implement SCD Type 01 which will perform the update and Insert.
Source:

  1. CREATE  TABLE Source01 (
  2. id INT,
  4. name VARCHAR(50),
  6. age INT,
  8. city VARCHAR(50)
  10. );

Target Create statement:

  1. CREATE  TABLE Target01 (
  2. id INT,
  3. name VARCHAR(50),
  4. age INT,
  5. city VARCHAR(50),
  6. row_num INT,
  7. CONSTRAINT PK_Target01 PRIMARY  KEY  NONCLUSTERED (id) NOT ENFORCED
  8. ) WITH (
  9. DISTRIBUTION = HASH(id),
  10. CLUSTERED COLUMNSTORE INDEX
  11. );  
  12. INSERT  INTO Target01 (id, name, age, city, row_num)SELECT
  13. id,
  15. name,
  17. age,
  19. city,
  21. ROW_NUMBER() OVER (ORDER  BY id) AS row_num
  23. FROM Source01;

I am using the ROW_NUMBER() function just over ID column. in the target table.Before perform the Merge statement row count 14.
如何在MERGE语句中使用窗口函数？

Now at the source01 I am updating 2 records. and Inserting 1 row.

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

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

Before the Source table is updated
如何在MERGE语句中使用窗口函数？
如何在MERGE语句中使用窗口函数？
Using the MERGE statement. Update 2 records in the Target table and Inserting new row.

MERGE Statement:

  1. MERGE  INTO Target01 AS T
  2. USING Source01 AS S
  3. ON T.id = S.id
  4. WHEN  MATCHED  THEN
  5. UPDATE  SET
  6. T.name = S.name,
  7. T.age = S.age,
  8. T.city = S.city
  9. WHEN  NOT  MATCHED  THEN
  10. INSERT (id, name, age, city)
  11. VALUES (S.id, S.name, S.age, S.city);

如何在MERGE语句中使用窗口函数？
After Update
如何在MERGE语句中使用窗口函数？
如何在MERGE语句中使用窗口函数？

huangapple
  • 本文由 发表于 2023年6月6日 16:06:22
  • 转载请务必保留本文链接：https://go.coder-hub.com/76412582.html
  • azure
  • azure-synapse
  • sql
  • t-sql
  • window-functions
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定