寻求无游标实现新患者统计报表的高效SQL方案

我需要生成一份报表，统计特定月份的新患者就诊数量。新患者定义为两类：

• 从未就诊过的患者
• 曾就诊但上次就诊距今超3年的患者

目前我通过游标调用表值函数fnNewPatientsByVisitDate实现统计，该游标遍历近6年的就诊日期，执行时间超过35秒，效率极低。现寻求无游标的高效实现思路。

现有游标逻辑 #

DECLARE @BEGINYEAR  INT;
SET @BEGINYEAR = YEAR(GETDATE()) - 6;

DECLARE @TBLNewPts  TABLE(
VisitDate   SMALLDATETIME,
NewPtCount  INT
)

DECLARE @VisitDate  SMALLDATETIME

DECLARE TblCursor   CURSOR 
FAST_FORWARD

FOR
    SELECT DISTINCT VisitDate
    FROM dbo.Visits
    WHERE YEAR(VisitDate) > @BEGINYEAR 
    
OPEN TblCursor
FETCH NEXT FROM TblCursor INTO @VisitDate
WHILE @@FETCH_STATUS = 0

BEGIN

    INSERT @TBLNewPts 
    SELECT
    @VisitDate, 
    ISNULL(NewPts, 0)
            
    FROM dbo.fnNewPatientsByVisitDate(@VisitDate) 
    
FETCH NEXT FROM TblCursor INTO @VisitDate

END

CLOSE   TblCursor
DEALLOCATE TblCursor;

表值函数fnNewPatientsByVisitDate定义 #

CREATE FUNCTION [dbo].[fnNewPatientsByVisitDate]
(
@VisitDate  SMALLDATETIME
)
RETURNS 
@tbl TABLE 
(
NewPts  INT
)
WITH SCHEMABINDING

AS
BEGIN
    DECLARE @TBLNewPts  TABLE(
    VisitDate   SMALLDATETIME,
    PtID    INT
    )

    INSERT @TBLNewPts 
    --从未在@VISITDATE之前就诊过的患者
    SELECT 
    @VisitDate,
    PtID
    FROM dbo.vwKeptVisits 
    
    WHERE (VisitDate = @VisitDate)
          AND
          (PtID NOT IN 
                (SELECT PtID
                FROM dbo.vwKeptVisits
                WHERE VisitDate < @VisitDate)
                )   

    UNION

    --曾就诊但上次就诊距@VISITDATE超过3年的患者
    SELECT 
    @VisitDate,
    PtID    
    FROM dbo.vwKeptVisits 
    
    WHERE (VisitDate = @VisitDate)
          AND
          (PtID NOT IN 
                (SELECT PREVIOUS.PtID
                FROM dbo.vwKeptVisits AS PREVIOUS INNER JOIN
                    dbo.vwKeptVisits AS CURRNT ON
                    CURRNT.PtID = PREVIOUS.PtID AND
                    CURRNT.VisitNumber > PREVIOUS.VisitNumber 
                WHERE (PREVIOUS.VisitDate > DATEADD(YEAR,-3,@VisitDate)) 
                ));           

    INSERT @tbl
    SELECT
    COUNT(PtID)
    FROM @TBLNewPts ;

    RETURN 

END

索引视图vwKeptVisits定义及索引 #

/****** Object:  View [dbo].[vwKeptVisits]    Script Date: 6/18/2024 8:31:05 PM ******/

CREATE VIEW [dbo].[vwKeptVisits]
WITH SCHEMABINDING
AS
SELECT 
COUNT_BIG(*) AS ServiceCount,
V.VisitNumber,
V.PtID,
V.VisitDate,
V.MDID,
V.ApptID,
V.DX1,
V.DX2,
v.DX3,
v.DX4,
V.DX5,
V.POS 
FROM DBO.Visits V INNER JOIN
     dbo.[Visit Details] VD ON 
     V.VisitNumber = VD.VisitNumber
WHERE (V.Void = 0) AND 
(VD.[CPT CODE] <> 'No Show' AND
  VD.[CPT CODE] <> 'MD Cancel' AND
  VD.[CPT CODE] <> 'Pt Cancel') AND
  (V.ApptID IS NOT NULL)
GROUP BY V.VisitNumber, v.PtID , v.VisitDate, V.MDID, V.ApptID,
V.DX1, V.DX2, v.DX3, v.DX4, V.DX5, V.POS
GO

GRANT SELECT ON [dbo].[vwKeptVisits] TO [db_PracManUserRole] AS [dbo]
GO

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO

/****** Object:  Index [PK_vwKeptVisits]    Script Date: 6/18/2024 8:31:05 PM ******/
CREATE UNIQUE CLUSTERED INDEX [PK_vwKeptVisits] ON [dbo].[vwKeptVisits]
(
    [VisitNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [INDICESGROUP]
GO

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO

/****** Object:  Index [IX_vwKeptVisits_MDID]    Script Date: 6/18/2024 8:31:05 PM ******/
CREATE NONCLUSTERED INDEX [IX_vwKeptVisits_MDID] ON [dbo].[vwKeptVisits]
(
    [MDID] ASC
)
INCLUDE([PtID],[VisitDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [INDICESGROUP]
GO

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO

/****** Object:  Index [IX_vwKeptVisits_PtID_VisitDate]    Script Date: 6/18/2024 8:31:05 PM ******/
CREATE NONCLUSTERED INDEX [IX_vwKeptVisits_PtID_VisitDate] ON [dbo].[vwKeptVisits]
(
    [PtID] ASC,
    [VisitDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [INDICESGROUP]
GO

示例查询结果 #

VisitDate   NewPtCount
2019-01-02 00:00:00 0
2019-01-03 00:00:00 0
2019-01-04 00:00:00 0
2019-01-07 00:00:00 2
2019-01-08 00:00:00 3
2019-01-09 00:00:00 0
2019-01-10 00:00:00 4
2019-01-11 00:00:00 5
2019-01-14 00:00:00 1
2019-01-15 00:00:00 0
2019-01-16 00:00:00 0
2019-01-17 00:00:00 0
2019-01-18 00:00:00 0
2019-01-21 00:00:00 0
2019-01-22 00:00:00 1

当前查询耗时超过35秒，急需更高效的无游标实现方式。

内容的提问来源于stack exchange，提问作者JohnT