我的任务是通过查看现有值来增加预期标志列。我使用 while 循环解决了这个问题,并且可以在 Azure synapse-Sql 数据仓库中使用 while 循环更新预期值。但我想知道使用任何排名函数来执行此操作的直接方法?
我的样本数据
CREATE TABLE [dbo].[work_table](
[work_order_key] [int] NULL,
[modification_date] [datetime2](7) NULL,
[from_status] [varchar](1000) NULL,
[to_status] [varchar](100) NULL,
[count] [int] NULL,
[sequence] [int] NULL,
[count_VCR] [int] NULL,
[flag] [int] NULL,
[flag_expected] [int] NULL
)
INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-14T11:53:05.1630000' AS DateTime2), N'Not Reviewed', N'Planned', 1, 1, 1, 1, NULL)
INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-19T03:49:25.7370000' AS DateTime2), N'Planned', N'In Progress', 1, 2, 1, 1, NULL)
INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-19T04:22:33.0630000' AS DateTime2), N'In Progress', N'Awaiting Parts', 1, 3, 1, 1, NULL)
INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-27T02:58:54.7570000' AS DateTime2), N'Awaiting Parts', N'Parts Ordered', 1, 4, 0, 1, NULL)
INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-27T02:59:00.8530000' AS DateTime2), N'Parts Ordered', N'Parts Received', 1, 5, 0, 1, NULL)
INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-27T02:59:09.9000000' AS DateTime2), N'Parts Received', N'Planned', 1, 6, 0, 1, NULL)
INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-28T07:59:58.1130000' AS DateTime2), N'Planned', N'In Progress', 1, 7, 1, 1, NULL)
INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-29T07:53:30.9030000' AS DateTime2), N'In Progress', N'Work Completed', 1, 8, 1, 1, NULL)
INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-05-05T06:15:34.4300000' AS DateTime2), N'Work Completed', N'Completed', 1, 9, 0, 1, NULL)
分区列是 [work_order_key] 以及列表中的任何列(按其合适的方式)。while循环可以得到这个答案。我使用 work_order_key 和 count_VCR 列在 while 循环中获取此值。但我想与小组核实是否可以使用任何 SQL 窗口或排名函数进行直接查询。
我无法添加我的解决方案,因为它是生产解决方案。这只是示例数据集。感谢您的支持。
这是一个典型的缺口与岛屿问题。有很多解决方案,但典型的解决方案是使用
LAG
窗口化的COUNT
.作为一条
UPDATE
语句(请注意,SQL Server 中不需要联接,因为您可以直接更新 CTE)。数据库<>小提琴