如何识别在特定时间范围内发生的行? [英] How to identify rows that occur within a specific time frame?
问题描述
我有一张桌子,其中包含病人去医院就诊的情况。我正在尝试标记上次访问后90天内发生的访问。但是,需要注意的是,一旦将某个访问标记为重复访问,就不应将该访问用于评估与另一个访问的重复。让我用一个例子来解释。
I have a table that contains hospital visits for patients. I am trying to flag visits that occur within 90 days of the previous visit. However, the caveat to this is that once a visit is flagged as an overlap visit, that visit should not be used to assess an overlap with another visit. Let me explain with an example.
visitID patientid visit_date
1 23 1/12/2018
2 23 1/30/2018
3 23 4/20/2018
4 23 5/02/2018
在上面的示例中,患者进行了4次就诊。访问2在访问1的90天内,因此应将访问2标记为重叠。标记访问2后,该行就不应在分析中用于以后的任何访问的重叠标识。从概念上讲,这就像删除访问2并再次开始分析。
In the example above, the patient had 4 visits. Visit 2 was within 90 days of visit 1, so visit 2 should be flagged as an overlap. Once visit 2 is flagged, that row should not be used in the analysis for overlap identification for any future visits. Conceptually, it would be like removing visit 2 and beginning the analysis again.
过渡阶段(删除访问2,然后再次开始分析)
interim stage (visit 2 is removed, and analysis begins again)
visitID patientid visit_date
1 23 1/12/2018
3 23 4/20/2018
4 23 5/02/2018
因此,即使访问3与访问2重叠,由于访问2已从分析中删除,因此访问3不会标记为上次访问(第1次访问)距离我们有90天了。最后,应标记4次访问,因为它与未标记的访问重叠。
So even though visit 3 overlaps with visit 2, since visit 2 has been removed from the analysis, visit 3 will not be flagged as the previous visit (visit 1) is farther than 90 days away. Lastly, the 4 visit should be flagged as it overlaps with a visit that was not flagged.
visitID patientid visit_date flag
1 23 1/12/2018 0
2 23 1/30/2018 1
3 23 4/20/2018 0
4 23 5/02/2018 1
我试图解决这个难题的方法:
My attempt to solve this puzzle:
WITH overlaps AS
(SELECT DISTINCT T2.visit
FROM visits_table AS T1
INNER JOIN visits_table AS T2
ON T1.visit != T2.visit
AND T2.visit_date BETWEEN T1.visit_date AND DATEADD(DAY, 89, T1.visit_date))
SELECT T3.visit, T3.patientid, T3.visit_date,
CASE WHEN EXISTS (SELECT 1 FROM overlaps
WHERE overlaps.visit = T3.visit)
THEN 1
ELSE 0
END flag
FROM visits_table T3
我的代码正在执行的操作是检查每一行,而不管是否应在分析中使用它。我不知道如何使它动态化,以便忽略应该忽略的行。
What my code is doing is checking each row regardless of whether it should be used in the analysis. I don't know how to make it dynamic so that it ignores rows that should be ignored.
create table visits_table (visit int,patientid int,visit_date date);
INSERT INTO visits_table (visit, patientid, visit_date) VALUES (1,23,'1/12/2018')
INSERT INTO visits_table (visit, patientid, visit_date) VALUES (2,23,'1/30/2018')
INSERT INTO visits_table (visit, patientid, visit_date) VALUES (3,23,'4/20/2018')
INSERT INTO visits_table (visit, patientid, visit_date) VALUES (4,23,'5/02/2018')
推荐答案
我相信您必须使用递归CTE来完成此操作:
I believe you have to do this with a recursive CTE:
with vt as (
select vt.*, row_number() over (partition by patientid order by visit_date) as seqnum
from visits_table vt
),
cte as (
select vt.visit, vt.patientid, vt.visit_date, vt.visit_date as first_visit_date, seqnum
from vt
where seqnum = 1
union all
select vt.visit, vt.patientid, vt.visit_date,
(case when vt.visit_date > dateadd(day, 90, cte.first_visit_date) then vt.visit_date else cte.first_visit_date end),
vt.seqnum
from cte join
vt
on vt.seqnum = cte.seqnum + 1 and vt.patientid = cte.patientid
)
select cte.visit, cte.patientid, cte.visit_date,
(case when first_visit_date = visit_date then 0 else 1 end) as flag
from cte
order by cte.patientid, cte.visit_date;
这里是db<>小提琴。
Here is a db<>fiddle.
这篇关于如何识别在特定时间范围内发生的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!