使用SQL查找给定时间范围内的事件

Question

我的大脑现在一定是一团糟，因为我被困在这上面了。我有一个表格，上面有给药的日期，还有他们服药的天数。一个人可以开出n数量的药物，所以下面查询的CTE是找到最大的时间范围(药物的填充日期+供应的天数)。然后我想找到同时服用至少七种不同药物的人。我很难找到人们在特定时间内服用的药物。这需要限制在一个人一次至少服用seven药物的时间段内。直到最后一部分之前，一切都运行得很好。

来自CTE的样本数据(fillDate + longestscript = endingDate)

​

​

并发脚本由查询中的count(distinct rx.drugname)确定

​

​

  ;with cte 
as
(
      select rx.patid
        ,rx.fillDate
        ,MAX(rx.dayssup) as longestScript
        ,DATEADD(day,cast(rx.dayssup as int),rx.filldate) as endingDate
        from rx
        group by rx.patid, rx.fillDate,rx.daysSup
 ),
 startends as (
         select patid, FillDate as thedate, 1 as isstart 0 as isend
         from CTE union all
         select patid, EndingDate as thedate, 0 as isstart, 1 as isend
         from CTE
     ),
     cums as (
         select se.*,
                (select min(thedate) from startends se2 where se2.filledate > se.filldate) as nextdate,
                (select SUM(isstart) from startends se2 where se2.filldate <= se.filldate) as cumstarts,
                (select SUM(isend) from startends se2 where se2.filldate <= se.filldate) as cumends
         from startends se
     )
select *
from cums
where sumstarts - cumends >= 7

您可以在第二个查询中看到，每个患者出现的次数在fillDate和endingDate之间的时间跨度不同。我如何编写一个查询，让9种药物在第二个屏幕的第一行重复出现？SQL Server08 r2是我的数据库管理系统。

Answer 1

我不是很确定你的模式，所以我猜有点，但是我注意到的第一件事是在你的CTE x中，你选择了MAX(DaysSup)，但也按dayssup分组，使最大值变得多余。

但是，我真的不认为这与您的问题相关。我个人会采取一种不同的方法来解决这个问题。我假设您有一个类似于以下内容的表：

CREATE TABLE rx
(       PatID           INT,
        FillDate        DATE,
        Dayssup         INT,
        DrugName        VARCHAR(50)
)

因此，您可以按照以下方式做一些事情：

SELECT  rx.PatID,
        rx.FillDate,
        rx.DrugName,
        [DateTaken] = DATEADD(DAY, v.Number, FillDate)
FROM    RX
        INNER JOIN master..spt_values v
            ON v.Number BETWEEN 0 AND rx.DaysSup
            AND v.Type = 'P'

这将给出每个患者服用药物的所有日期的列表，而不是一个范围，因此您可以使用以下内容：

WITH x AS
(   SELECT  rx.PatID,
            rx.FillDate,
            rx.DrugName,
            [DateTaken] = DATEADD(DAY, v.Number, FillDate) 
    FROM    rx
            INNER JOIN master..spt_values v
                ON v.Number BETWEEN 0 AND rx.DaysSup
                AND v.Type = 'P'
), y AS
(   SELECT  x.PatID,
            x.DateTaken,
            DrugsTaken = COUNT(DISTINCT x.DrugName)
    FROM    x
    GROUP BY x.PatID, x.DateTaken
    HAVING COUNT(DISTINCT x.DrugName) >= 7
), z AS
(   SELECT  *,
            GroupID = DATEDIFF(DAY, - ROW_NUMBER() OVER(PARTITION BY PatID ORDER BY DateTaken DESC), DateTaken)
    FROM    y
)
SELECT  z.PatID, 
        [MostConccurent] = MAX(z.DrugsTaken),
        [DateStarted] = MIN(z.DateTaken),
        [DateEnded] = MAX(z.DateTaken)
FROM    z
GROUP BY z.PatID, z.GroupID;

我已经介绍了第一部分，第二部分只是简单地将结果限制在7种或更多药物的所有日期。第三个CTE按连续日期对每个患者进行分组，最后一个获得每个日期的最小和最大值。

如果您需要在这些日期服用的药物列表，您可以重新加入cte x。

SELECT  z.PatID, 
        x.DrugName,
        [MostConccurent] = MAX(z.DrugsTaken),
        [DateStarted] = MIN(z.DateTaken),
        [DateEnded] = MAX(z.DateTaken)
FROM    z
        INNER JOIN x
            ON x.PatID = z.PatID
            AND x.DateTaken = z.DateTaken
GROUP BY z.PatID, z.GroupID, x.DrugName;

Answer 2

您是否正在使用SQL Server 2012？在该数据库中，解决方案要简单得多，因为Microsoft扩展了窗口函数以包括部分和。

这个想法是计算每个可能的日期的累积填充和结束的数量--无论是填充日期还是结束日期。那么不同之处在于累积脚本的数量。以下是每个日期的信息：

with cte as (<your query>),
     startends as (
         select patid, FillDate as thedate, 1 as isstart 0 as isend
         from CTE union all
         select patid, EndingDate as thedate, 0 as isstart, 1 as isend
         from CTE
     ),
     cums as (
         select se.*,
                (select min(thedate) from startends se2 where se2.filledate > se.filldate) as nextdate,
                (select SUM(isstart) from startends se2 where se2.filldate <= se.filldate) as cumstarts,
                (select SUM(isend) from startends se2 where se2.filldate <= se.filldate) as cumends
         from startends se
     )
select *
from cums
where cumstarts - cumends >= 7

结果集中的每一行都有一个"nextdate“，它定义了条件成立的时间段。你可能会在一个周期内得到多个记录，因为患者会同时开7到8到9到8到9到7张处方。

如果您有大量数据，这将是一个相当低效的查询，因为它执行的是相当昂贵的连接操作。但是，正如我所说的，这在SQL Server 2012中将非常高效。