一个记录中的每个最小和最大日期的SQL Server窗口函数
问题描述:
我有一个场景,我试图让每个分区对应机会的所需的一切。一个记录中的每个最小和最大日期的SQL Server窗口函数
我有下面的例子,我只想返回一列中Service_Appointment
列中每个值的最小值和最大值,所以我可以将其作为另一个数据集的一部分进行查询。我需要它在一行这就是为什么我使用row_number
函数来查询where RN = 1
有很多行返回一个机会,我想要做的是所有的最小值和最大值为每个约会类型使用Createdon
。
下面是示例数据:
CREATE TABLE #temptable
(
[ActivityId] int,
[OpportunityId] int,
[Appointment_Service] nvarchar(255),
[CreatedOn] datetime,
[Appt_Booked_Date] datetime,
[Appointment_State] nvarchar(255),
[Appointment_Status] nvarchar(255),
[RN] bigint,
[LASTEST_First_Appointment] datetime,
[LASTEST_Second_Appointment] datetime
)
INSERT INTO #temptable
VALUES
(4176537, 105994, N'1st Appointment', N'2015-01-27T00:00:00', N'2015-01-27T00:00:00', N'Closed', N'Attended', 1, N'2015-01-27T00:00:00', N'2017-11-21T11:25:46'),
(4176536, 105994, N'2nd Appointment', N'2015-01-28T00:00:00', N'2015-01-28T00:00:00', N'Closed', N'Attended', 2, N'2015-01-28T00:00:00', N'2017-11-21T11:25:46'),
(19656494, 105994, N'Other Appointment', N'2017-03-31T15:33:26', N'2017-03-31T15:33:26', N'Scheduled', N'In Progress', 3, N'2017-11-21T11:25:46', N'2017-11-21T11:25:46'),
(9394803, 105994, N'Other Appointment', N'2017-04-28T06:38:56', N'2017-04-28T06:38:56', N'Scheduled', N'In Progress', 4, N'2017-11-21T11:25:46', N'2017-11-21T11:25:46'),
(3774963, 105994, N'Other Appointment', N'2017-04-28T17:20:06', N'2017-04-28T17:20:06', N'Scheduled', N'In Progress', 5, N'2017-11-21T11:25:46', N'2017-11-21T11:25:46')
这是我的查询
SELECT
ActivityId, OpportunityId,
Appointment_Service, CreatedOn,
Appt_Booked_Date,
Appointment_State, Appointment_Status,
ROW_NUMBER() OVER (PARTITION BY OpportunityId ORDER BY CreatedOn, ActivityId) AS RN,
LAST_VALUE(CreatedOn) OVER (PARTITION BY Appointment_Service ORDER BY CreatedOn, ActivityId ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LASTEST_First_Appointment
FROM
dbo.DIM_APPOINTMENT
有3种不同的约会,所以我只是想首先第一个任命,最后1日任命(如果有的话),第一次约会和第二次约会,与其他约会一样。当我有我需要的所有列时,我可以只查询row_number字段来带回RN = 1,这将带来我需要的一切,至少这是计划哈哈,提前谢谢
答
我去了简单的数据为我的测试设置:
DECLARE @test TABLE
(
Id INT
,category INT
,x INT
);
INSERT INTO @test
(
Id
,category
,x
)
VALUES
(1, 1, 1)
,(1, 1, 2)
,(1, 1, 3)
,(1, 1, 4)
,(1, 2, 5)
,(1, 2, 6)
,(1, 2, 7)
,(1, 2, 8);
当我跑到你的代码就好像你在哪里上错列(即或我只是没有保持你的例子直在我的头)划分。在这个数据集类别代表预约类型。
然后,我们只需要得到我们的拳头,最后的正确设置:
SELECT DISTINCT
t.Id
,t.category
,firstX = FIRST_VALUE(x) OVER (PARTITION BY Id, category ORDER BY x)
,lastX = LAST_VALUE(x) OVER (PARTITION BY Id
,category
ORDER BY x
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
FROM @test AS t;
下面是结果:
Id category firstX lastX
1 1 1 4
1 2 5 8
然后转动的结果集:
WITH cte_last
AS (
SELECT DISTINCT
t.Id
,t.category
,firstX = FIRST_VALUE(x) OVER (PARTITION BY Id, category ORDER BY x)
,lastX = LAST_VALUE(x) OVER (PARTITION BY Id
,category
ORDER BY x
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
FROM @test AS t
)
SELECT l.Id
,cat1First = SUM(CASE l.category WHEN 1 THEN l.firstX ELSE 0 END)
,cat1Last = SUM(CASE l.category WHEN 1 THEN l.lastX ELSE 0 END)
,cat2First = SUM(CASE l.category WHEN 2 THEN l.firstX ELSE 0 END)
,cat2Last = SUM(CASE l.category WHEN 2 THEN l.lastX ELSE 0 END)
FROM cte_last AS l
GROUP BY Id;
你得到这个:
Id cat1First cat1Last cat2First cat2Last
1 1 4 5 8
请您提供样品输出吗? – lucky
您的表格没有'service_appointment'列。 –