如何将T-SQL游标操作转换为基于集合的操作?

I have a medical reporting project that requires calculating "onset" dates from a list of diagnosis dates.

我有一个医疗报告项目，需要从诊断日期列表中计算“发作”日期。

The first time a patient presents with a certain diagnosis, that's onset date #1. For the next 30 days, any subsequent diagnosis date counts as part of that onset #1, so those subsequent dates can be dropped.

当病人第一次被诊断出某种疾病时，这是第一次。在接下来的30天里，任何后续的诊断日期都将作为第1个开始日期的一部分，因此这些后续日期可以被删除。

After 30 days have passed, the next diagnosis date counts as onset #2.

经过30天的治疗，下一个诊断日期是2号开始。

And so on..

等等. .

Example input

输入例子

2015-09-10 (this is onset #1)
2015-09-19 (within 30 days from onset #1, so drop)
2015-09-29 (within 30 days from onset #1, so drop)
2015-10-17 (>= 30 days from onset #1, so this is onset #2)
2015-10-19 (within 30 days from onset #2, so drop)
2015-11-13 (within 30 days from onset #2, so drop)
2015-11-29 (>= 30 days from onset #2, so this is onset #3)

Example output

示例输出

2015-09-10 (onset #1)
2015-10-17 (onset #2)
2015-11-29 (onset #3)

This can be done with a cursor, and a minimal example is included below.

这可以通过游标实现，下面包含了一个示例。

I've heard it said that any cursor operation can be expressed as a set-based one. But I can't figure out how one would approach this particular algorithm in a set-based way because calculations rely on previous ones. I can't see how it could be accomplished in one set-based "pass".

我听说过，任何游标操作都可以表示为基于集合的操作。但是我不知道如何用基于集合的方法来处理这个特定的算法，因为计算依赖于之前的方法。我不知道如何在一个基于集合的“pass”中完成它。

Can it be done? If so, how?

可以做到吗?如果是这样,如何?

Any solution should work in SQL Server 2012.

任何解决方案都应该在SQL Server 2012中工作。

DECLARE @dx_list TABLE(dx_dt date);
INSERT INTO @dx_list(dx_dt)
    VALUES ('2015-09-10') --this is onset #1
    , ('2015-09-19') 
    , ('2015-09-29')
    , ('2015-10-17') --date is >= 30 days from last onset, so this is onset #2
    , ('2015-10-19')
    , ('2015-11-13')
    , ('2015-11-29'); --date is >= 30 days from last onset, so this is onset #3

DECLARE @mycursor AS cursor;
SET @mycursor = CURSOR FOR
SELECT dx_dt
FROM @dx_list
ORDER BY dx_dt; --make sure dates are in order

DECLARE @possible_dt AS date;
DECLARE @onset_list TABLE(onset_dt date);

OPEN @mycursor;
FETCH NEXT FROM @mycursor INTO @possible_dt;
--First date is always an onset date
INSERT INTO @onset_list(onset_dt) VALUES (@possible_dt);

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM @mycursor INTO @possible_dt;
    --If this date is 30 days or more from last onset date, add it
    IF @possible_dt >= DATEADD(dd, 30, (SELECT MAX(onset_dt) FROM @onset_list))
    BEGIN
        INSERT INTO @onset_list(onset_dt) VALUES (@possible_dt);
    END
END

CLOSE @mycursor;
DEALLOCATE @mycursor;

--Show results
SELECT * FROM @onset_list;

1 个解决方案

#1

You can do it using a Recursive CTE :

你可以用递归的CTE来做:

;WITH OnsetDates AS (
   SELECT TOP 1 dx_dt
   FROM dx_list
   ORDER BY dx_dt

   UNION ALL

   SELECT dx_dt
   FROM (  
     SELECT d1.dx_dt,
            ROW_NUMBER() OVER (ORDER BY d1.dx_dt) AS rn
     FROM dx_list AS d1
     INNER JOIN OnsetDates AS d2 ON d1.dx_dt > d2.dx_dt
     WHERE DATEDIFF(d, d2.dx_dt, d1.dx_dt) >= 30 ) AS t
   WHERE t.rn = 1
)
SELECT *
FROM OnsetDates

The so-called anchor member of the CTE is just the top-level date. The recursive member fetches the next onset date: this is the first date that is past 30 days+ the date returned by the previous invocation of the recursive CTE.

CTE的所谓锚点成员只是*日期。递归成员获取下一个开始日期:这是第一个超过30天的日期，加上递归CTE先前调用返回的日期。

Note that in order to get this first date we have to use ROW_NUMBER and a sub-query, since TOP 1 and ORDER BY are not allowed in the recursive member of the CTE.

注意，为了获得第一个日期，我们必须使用ROW_NUMBER和子查询，因为在CTE的递归成员中不允许使用TOP 1和order BY。

Demo here

演示

#1