递归 CTE 还是分区上的ROW_NUMBER?

Question

嗨，下面是一个小样本，我正在尝试找到一种根据ATTENDED_OR_DID_NOT_ATTEND重置 COUNT 的方法 CDO_OP_REFERRAL_UNIQUE_ID， 最后一列是我似乎无法在查询中应用的预期结果，每次我在分区上使用ROW_NUMBER时，我都会得到错误的结果，每次ATTENDED_OR_DID_NOT_ATTEND更改时它都不会再次重新启动

DECLARE @CDO_OP_APPOINTMENT TABLE (CDO_OP_REFERRAL_UNIQUE_ID int,LOCAL_PATIENT_NUMBER varchar(10),APPOINTMENT_START_DATE_TIME datetime,ATTENDED_OR_DID_NOT_ATTEND varchar(10),DESIRED_OUTCOME varchar(10))
INSERT INTO @CDO_OP_APPOINTMENT
VALUES
('480805568',   'HEY1030785',   '05/11/2013 10:00', '2',    '1'),
('480805568',   'HEY1030785',   '12/11/2013 10:00', '5',    '1'),
('480805568',   'HEY1030785',   '22/11/2013 09:30', '5',    '2'),
('480805568',   'HEY1030785',   '03/12/2013 13:00', '3',    '1'),
('480805568',   'HEY1030785',   '30/12/2013 10:15', '5',    '1'),
('480805568',   'HEY1030785',   '24/02/2014 09:15', '4',    '1'),
('480805568',   'HEY1030785',   '24/02/2014 14:15', '5',    '1'),
('480805568',   'HEY1030785',   '17/03/2014 15:25', '4',    '1'),
('480805568',   'HEY1030785',   '20/03/2014 18:50', '5',    '1'),
('480805568',   'HEY1030785',   '23/09/2014 15:55', '5',    '2'),
('480805568',   'HEY1030785',   '14/04/2015 16:30', '5',    '3'),
('480805568',   'HEY1030785',   '14/04/2015 17:30', '4',    '1'),
('480805568',   'HEY1030785',   '15/05/2015 14:15', '5',    '1')
SELECT * from @CDO_OP_APPOINTMENT

Answer 1

这是一个差距和孤岛问题。 最简单的解决方案是行号的差异：

select a.*,
row_number() over (partition by CDO_OP_REFERRAL_UNIQUE_ID, ATTENDED_OR_DID_NOT_ATTEND , seqnum - seqnum_2 order by APPOINTMENT_START_DATE_TIME) as desired_result
from (select a.*,
row_number() over (partition by CDO_OP_REFERRAL_UNIQUE_ID order by APPOINTMENT_START_DATE_TIME) as seqnum,
row_number() over (partition by CDO_OP_REFERRAL_UNIQUE_ID, ATTENDED_OR_DID_NOT_ATTEND order by APPOINTMENT_START_DATE_TIME) as seqnum_2,
from @CDO_OP_APPOINTMENT a
) a;

Answer 2

设法解决与上面的答案非常相似的问题

set dateformat dmy
go
DECLARE @CDO_OP_APPOINTMENT TABLE (CDO_OP_REFERRAL_UNIQUE_ID int,LOCAL_PATIENT_NUMBER varchar(10),APPOINTMENT_START_DATE_TIME datetime,ATTENDED_OR_DID_NOT_ATTEND varchar(10),DESIRED_OUTCOME varchar(10))
INSERT INTO @CDO_OP_APPOINTMENT
VALUES
('480805568',   'HEY1030785',   '05/11/2013 10:00', '2',    '1'),
('480805568',   'HEY1030785',   '12/11/2013 10:00', '5',    '1'),
('480805568',   'HEY1030785',   '22/11/2013 09:30', '5',    '2'),
('480805568',   'HEY1030785',   '03/12/2013 13:00', '3',    '1'),
('480805568',   'HEY1030785',   '30/12/2013 10:15', '5',    '1'),
('480805568',   'HEY1030785',   '24/02/2014 09:15', '4',    '1'),
('480805568',   'HEY1030785',   '24/02/2014 14:15', '5',    '1'),
('480805568',   'HEY1030785',   '17/03/2014 15:25', '4',    '1'),
('480805568',   'HEY1030785',   '20/03/2014 18:50', '5',    '1'),
('480805568',   'HEY1030785',   '23/09/2014 15:55', '5',    '2'),
('480805568',   'HEY1030785',   '14/04/2015 16:30', '5',    '3'),
('480805568',   'HEY1030785',   '14/04/2015 17:30', '4',    '1'),
('480805568',   'HEY1030785',   '15/05/2015 14:15', '5',    '1')

SELECT CDO_OP_REFERRAL_UNIQUE_ID ,LOCAL_PATIENT_NUMBER ,APPOINTMENT_START_DATE_TIME ,ATTENDED_OR_DID_NOT_ATTEND 
,ROW_NUMBER() OVER (PARTITION BY CDO_OP_REFERRAL_UNIQUE_ID,Seq1-Seq2,ATTENDED_OR_DID_NOT_ATTEND ORDER BY APPOINTMENT_START_DATE_TIME) AS DESIRED_OUTCOME 
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY CDO_OP_REFERRAL_UNIQUE_ID ORDER BY APPOINTMENT_START_DATE_TIME) AS Seq1,
ROW_NUMBER() OVER (PARTITION BY CDO_OP_REFERRAL_UNIQUE_ID,ATTENDED_OR_DID_NOT_ATTEND ORDER BY APPOINTMENT_START_DATE_TIME) AS Seq2
from @CDO_OP_APPOINTMENT
)t
ORDER BY APPOINTMENT_START_DATE_TIME