计算顺序事件和序列数SQL [英] Counting sequential events and counts of sequences SQL
问题描述
我有一个使用答案构建的查询此处，它非常有帮助。我添加了一些东西以满足我的需求。我添加的一件事是 ROW_NUMBER（），以便计算某人在30天内在任何时间范围内被重新接纳的次数。我已将 cte 结果插入到临时表中，如第一个答案中所建议的那样，并通过一个问题的insert-into-table-variable-cte>。这并不能解决序列长度和序列计数问题。
I have a query that I built using an answer found here and it was very helpful. I have added some things to it to suit my needs. One of the things that I added was a ROW_NUMBER() in order to count how many times someone has been readmitted within 30 days over any time length. I have inserted the cte results into a temp table as suggested in the first answer and by a question that was posted here. This does not solve thought, the sequence length and sequence count issue.
这是查询：
-- 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天的重新准入。
So the third line is obviously not a readmit in 30 days but just a point in time where the patient came back to the hospital so the RA_Count goes back to 1 and the flag goes to 0 because it is not a 30day readmit.
我应该创建表格而不是使用 cte ？
Should I create a table instead of using a cte?
我想添加的是链长和链数。以下是一些定义：
What I would like to add is a Chain Length and a Chain Count. Here are some definitions:
链长：某人在随后的访问中连续30天被重新接纳几次。
Chain Length: How many times in a row has someone been readmitted within 30 days of subsequent visits.
例如
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 语句来确定链长
Chain count would then be how many chains are there: so in the above table there would be 2. I am trying to use the case statement to make the chain length
这里是一个SQL Fiddle，带有一些示例数据将在执行 CTE 之前显示 SQL Fiddle
Here is an SQL Fiddle with some sample data as it will appear before the CTE is executed SQL Fiddle
谢谢
推荐答案
更新＃1：如果两个事件之间的最大时差为30天，则将两个事件链接在一起。 [COUNT]个值是每人生成的。
您可以改写以下使用递归公用表表达式：
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在（code> @EventsWithNum 上定义的（EventNum，PersonID）强制SQL Server创建（在这种情况下）具有复合键<$ c $的聚簇索引c> EventNum，PersonID 。
the execution plan contains, for the last statement, two Index Seek operators because this constraint PRIMARY KEY (EventNum, PersonID) defined on @EventsWithNum forces SQL Server to create (in this case) a clustered index with a compound key EventNum, PersonID.
此外，我们可以看到 INSERT @EventsWithNum的估计成本... 大于与CountingSequentiaEvents（...）SELECT ... FROM CountingSequentiaEvents ... 的估计成本。
Also, we can see that the estimate cost for INSERT @EventsWithNum ... is greater than the estimated cost for WITH CountingSequentiaEvents (...) SELECT ... FROM CountingSequentiaEvents ....
这篇关于计算顺序事件和序列数SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
