sql - 计算顺序事件和序列 SQL 的计数

Question

我有一个使用找到的答案构建的查询 here这非常有帮助。我已经添加了一些东西来满足我的需要。我添加的其中一件事是 ROW_NUMBER() 以计算某人在 30 天内在任何时间长度内被重新接纳的次数。我已按照第一个答案和问题 that was posted here 的建议将 cte 结果插入到临时表中.这并没有解决思想、序列长度和序列计数问题。

这是查询:

-- CREATE TABLE TO STORE CTE RESULTS
DECLARE @PPR TABLE(
    VISIT1      VARCHAR(20) -- THIS IS A UNIQUE VALUE
    , READMIT   VARCHAR(20) -- THIS IS A UNIQUE VALUE
    , MRN       VARCHAR(10) -- THIS IS UNIQUE TO A PERSON
    , INIT_DISC DATETIME
    , RA_ADM    DATETIME
    , R1        INT
    , R2        INT
    , INTERIM1  VARCHAR(20)
    , RA_COUNT  INT
    , FLAG      VARCHAR(2)
);

-- THE CTE THAT WILL GET USED TO POPULATE THE ABOVE TABLE
WITH cte AS (
  SELECT PTNO_NUM
    , Med_Rec_No
    , Dsch_Date
    , Adm_Date
    , ROW_NUMBER() OVER (
                         PARTITION BY MED_REC_NO 
                         ORDER BY PtNo_Num
                         ) AS r

  FROM smsdss.BMH_PLM_PtAcct_V

  WHERE Plm_Pt_Acct_Type = 'I'
  AND PtNo_Num < '20000000' 
  )

-- INSERT CTE RESULTS INTO PPR TABLE
INSERT INTO @PPR
SELECT
c1.PtNo_Num                                AS [INDEX]
, c2.PtNo_Num                              AS [READMIT]
, c1.Med_Rec_No                            AS [MRN]
, c1.Dsch_Date                             AS [INITIAL DISCHARGE]
, c2.Adm_Date                              AS [READMIT DATE]
, C1.r
, C2.r
, DATEDIFF(DAY, c1.Dsch_Date, c2.Adm_Date) AS INTERIM1
, ROW_NUMBER() OVER (
                    PARTITION BY C1.MED_REC_NO 
                    ORDER BY C1.PTNO_NUM ASC
                    ) AS [RA COUNT]

, CASE 
    WHEN DATEDIFF(DAY, c1.Dsch_Date, c2.Adm_Date) <= 30 
    THEN 1 
    ELSE 0
  END [FLAG]

FROM cte       C1
INNER JOIN cte C2
ON C1.Med_Rec_No = C2.Med_Rec_No

WHERE C1.Adm_Date <> C2.Adm_Date
AND C1.r + 1 = C2.r

ORDER BY C1.Med_Rec_No, C1.Dsch_Date

-- MANIPULATE PPR TABLE
SELECT PPR.VISIT1
, PPR.READMIT
, PPR.MRN
, PPR.INIT_DISC
, PPR.RA_ADM
--, PPR.R1
--, PPR.R2
, PPR.INTERIM1
--, PPR.RA_COUNT
, PPR.FLAG
-- THE BELOW DOES NOT WORK AT ALL
, CASE
    WHILE (SELECT PPR.INTERIM1 FROM @PPR PPR) <= 30
    BEGIN
        ROW_NUMBER() OVER (PARTITION BY PPR.MRN, PPR.VISIT1
                           ORDER BY PPR.VISIT1
                           )
        IF (SELECT PPR.INTERIM1 FROM @PPR PPR) > 30
            BREAK
    END
  END


FROM @PPR PPR

WHERE PPR.MRN = 'A NUMBER'

当前输出示例:

INDEX | READMIT | MRN | INIT DISCHARGE | RA DATE   | INTERIM | RACOUNT | FLAG | FLAG_2
12345 | 12349   | 123 | 2005-07-05     | 2005-07-09| 4       | 1       | 1    | 0
12349 | 12351   | 123 | 2005-07-11     | 2005-07-15| 4       | 2       | 1    | 0

所以第三行显然不是 30 天后的再入院，而只是患者回到医院的时间点，因此 RA_Count 回到 1 并且标志变为 0，因为这不是 30 天的再入院。

我应该创建一个表而不是使用 cte 吗？

我想添加的是链长和链数。以下是一些定义:

链长:某人在后续访问后的 30 天内连续重新入院的次数。

例如

INDEX | READMIT | MRN  | INITIAL DISCHARGE    | READMIT DATE | CHAIN LEN | Count
123   | 133     | 1236 | 2009-05-13           | 2009-06-12   | 1         | 1
133   | 145     | 1236 | 2009-06-16           | 2009-07-04   | 2         | 1
145   | 157     | 1236 | 2009-07-06           | 2009-07-15   | 3         | 1
165   | 189     | 1236 | 2011-01-01           | 2011-01-12   | 1         | 2
189   | 195     | 1236 | 2011-02-06           | 2011-03-01   | 2         | 2 

链数就是链的数量:所以在上表中会有 2 个。我正在尝试使用 case 语句来计算链的长度

这是一个带有一些示例数据的 SQL Fiddle，因为它会在执行 CTE 之前出现 SQL Fiddle

谢谢，

Answer 1

更新 #1:如果两个事件之间的最大差异为 30 天，则两个事件相关联。每个人生成 [COUNT] 个值。

您可以改编以下使用 recursive common table expression 的示例:

CREATE TABLE dbo.Events (
    EventID INT IDENTITY(1,1) PRIMARY KEY,
    EventDate DATE NOT NULL,
    PersonID INT NOT NULL
);
GO
INSERT dbo.Events (EventDate, PersonID)
VALUES 
    ('2014-01-01', 1), ('2014-01-05', 1), ('2014-02-02', 1), ('2014-03-30', 1), ('2014-04-04', 1), 
    ('2014-01-11', 2), ('2014-02-02', 2),
    ('2014-01-03', 3), ('2014-03-03', 3);
GO

DECLARE @EventsWithNum TABLE (
    EventID INT NOT NULL,
    EventDate DATE NOT NULL,
    PersonID INT NOT NULL,
    EventNum INT NOT NULL,
    PRIMARY KEY (EventNum, PersonID)
);
INSERT  @EventsWithNum
SELECT  crt.EventID, crt.EventDate, crt.PersonID,
        ROW_NUMBER() OVER(PARTITION BY crt.PersonID ORDER BY crt.EventDate, crt.EventID) AS EventNum
FROM    dbo.Events crt;

WITH CountingSequentiaEvents
AS (
    SELECT  crt.EventID, crt.EventDate, crt.PersonID, crt.EventNum,
            1 AS GroupNum,
            1 AS GroupEventNum
    FROM    @EventsWithNum crt
    WHERE   crt.EventNum = 1

    UNION ALL 

    SELECT  crt.EventID, crt.EventDate, crt.PersonID, crt.EventNum,
            CASE 
                WHEN DATEDIFF(DAY, prev.EventDate, crt.EventDate) <= 30 THEN prev.GroupNum
                ELSE prev.GroupNum + 1 
            END AS GroupNum,
            CASE 
                WHEN DATEDIFF(DAY, prev.EventDate, crt.EventDate) <= 30 THEN prev.GroupEventNum + 1
                ELSE 1 
            END AS GroupEventNum
    FROM    @EventsWithNum crt JOIN CountingSequentiaEvents prev ON crt.PersonID = prev.PersonID
    AND     crt.EventNum = prev.EventNum + 1
)
SELECT  x.EventID, x.EventDate, x.PersonID,
        x.GroupEventNum AS [CHAIN LEN],
        x.GroupNum AS [Count]
FROM    CountingSequentiaEvents x
ORDER BY x.PersonID, x.EventDate
-- 1000 means 1000 + 1 = maximum 1001 events / person
OPTION (MAXRECURSION 1000); -- Please read http://msdn.microsoft.com/en-us/library/ms175972.aspx (section Guidelines for Defining and Using Recursive Common Table Expressions)

输出:

EventID EventDate  PersonID CHAIN LEN Count
------- ---------- -------- --------- -----
1       2014-01-01 1        1         1
2       2014-01-05 1        2         1
3       2014-02-02 1        3         1
------- ---------- -------- --------- -----
4       2014-03-30 1        1         2
5       2014-04-04 1        2         2
------- ---------- -------- --------- -----
6       2014-01-11 2        1         1
7       2014-02-02 2        2         1
------- ---------- -------- --------- -----
8       2014-01-03 3        1         1
------- ---------- -------- --------- -----
9       2014-03-03 3        1         2
------- ---------- -------- --------- -----

如你所见

对于最后一条语句，执行计划包含两个 Index Seek运营商因为这个约束 PRIMARY KEY (EventNum, PersonID)定义于 @EventsWithNum强制 SQL Server 创建(在本例中)具有复合键的聚簇索引 EventNum, PersonID .

此外，我们还可以看到 INSERT @EventsWithNum ... 的估算成本大于 WITH CountingSequentiaEvents (...) SELECT ... FROM CountingSequentiaEvents ... 的估计成本.