在SQL Server 2008中为Chain_builder脚本运行Group By

Question

下面是我的代码，运行需要花费大量时间。。。有什么改进的想法吗？当SRVDate和SRVDateTO连续时，它基本上会计算床位天数，然后将其相加并创建一个链来订购。这是每个memberid完成的。我想要的是，如果我们可以从tbl_bed_days_test一次捕获100个memberid记录，然后运行这个脚本。表tbl_bed_days_test有列Claim_id、MEMBERID、SVC_DATE和SVC_DATE_TO，基本上是源数据。

BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RESULTS_TABLE]') AND type IN (N'U'))
        DROP TABLE [RESULTS_TABLE]
    CREATE TABLE [RESULTS_TABLE](
    [CLAIM_ID] [nvarchar](15) NULL,
    [MEMBERID] [nvarchar](10) NULL,
    [SVC_DATE] [datetime] NULL,
    [SVC_DATE_TO] [datetime] NULL,
    [TOTAL_DAYS] [int] NULL,
    [CHAIN_COUNT] [int] NULL
    ) ON [PRIMARY]
    ;WITH chain_builder AS
    (
    SELECT ROW_NUMBER() OVER(ORDER BY s.MEMBERID, s.CLAIM_ID) as chain_ID,
      s.MEMBERID,
      s.SVC_DATE, s.SVC_DATE_TO, s.CLAIM_ID, 1 as chain_count
    FROM [tbl_bed_days_test] s
    WHERE s.SVC_DATE <> ALL 
      (
      SELECT DATEADD(d, 1, s2.SVC_DATE_TO)
      FROM [tbl_bed_days_test] s2
      WHERE s.MEMBERID = s2.MEMBERID
      )
    UNION ALL
    SELECT chain_ID, s.MEMBERID, s.SVC_DATE, s.SVC_DATE_TO,
      s.CLAIM_ID, chain_count + 1
      FROM [tbl_bed_days_test] s
    JOIN chain_builder as c
      ON s.MEMBERID = c.MEMBERID AND
      s.SVC_DATE = DATEADD(d, 1, c.SVC_DATE_TO)
    ),
    chains AS
    (
    SELECT chain_ID, MEMBERID, SVC_DATE, SVC_DATE_TO,
      CLAIM_ID, chain_count, ROW_NUMBER() OVER(PARTITION BY chain_ID, chain_count ORDER BY SVC_DATE_TO DESC) as link_row
    FROM chain_builder
    ),
    link_picker AS
    (
    SELECT chain_ID, MEMBERID, SVC_DATE, SVC_DATE_TO,
      CLAIM_ID, chain_count
    FROM chains
    WHERE link_row = 1
    ),
    diff AS
    (
    SELECT c.chain_ID, c.MEMBERID, c.SVC_DATE, c.SVC_DATE_TO,
      c.CLAIM_ID, c.chain_count,
      datediff(day,c.SVC_DATE,c.SVC_DATE_TO)+1 daysdiff
    FROM link_picker c
    ),
    diff_sum AS
    (
    SELECT chain_ID, MEMBERID, SVC_DATE, SVC_DATE_TO,
      CLAIM_ID, chain_count,
      SUM(daysdiff) OVER (PARTITION BY chain_ID) as total_diff
    FROM diff
    ),
    diff_comp AS
    (
    SELECT chain_ID, MEMBERID,
      MAX(total_diff) OVER (PARTITION BY MEMBERID) as total_diff
    FROM diff_sum
    )
    INSERT INTO [RESULTS_TABLE]
    SELECT DISTINCT ds.CLAIM_ID, ds.MEMBERID, ds.SVC_DATE,
      ds.SVC_DATE_TO, ds.total_diff as TOTAL_DAYS, ds.chain_count as CHAIN_COUNT
    FROM diff_sum ds
    JOIN diff_comp dc
    ON ds.chain_ID = dc.chain_ID AND ds.MEMBERID = dc.MEMBERID
      AND ds.total_diff = dc.total_diff
    OPTION (maxrecursion 0)
END
GO

Answer 1

好的，我已经看过你的执行计划了。我一直在想一种简化查询的方法。我认为它应该给出相同的结果，但你必须测试它，看看它是否能提高性能（并验证结果是否相同）。

;WITH chain_builder AS
(
SELECT ROW_NUMBER() OVER(ORDER BY s.MEMBER_ID, s.CLAIM_ID) as chain_ID,
  s.MEMBER_ID,
  s.SVC_DATE, s.SVC_DATE_TO, s.CLAIM_ID, 1 as chain_count
FROM [tbl_bed_days_test] s
WHERE s.SVC_DATE <> ALL 
  (
  SELECT DATEADD(d, 1, s2.SVC_DATE_TO)
  FROM [tbl_bed_days_test] s2
  WHERE s.MEMBER_ID = s2.MEMBER_ID
  )
UNION           ALL
SELECT chain_ID, s.MEMBER_ID, s.SVC_DATE, s.SVC_DATE_TO,
  s.CLAIM_ID, chain_count + 1
  FROM [tbl_bed_days_test] s
JOIN chain_builder as c
  ON s.MEMBER_ID = c.MEMBER_ID AND
  s.SVC_DATE = DATEADD(d, 1, c.SVC_DATE_TO)
),
chains AS
(
SELECT chain_ID, chain_count, MAX(SVC_DATE_TO) max_date
FROM chain_builder
GROUP BY chain_ID, chain_count
),
link_picker AS
(
SELECT chain_ID, MEMBER_ID, SVC_DATE, SVC_DATE_TO,
  CLAIM_ID, chain_count, datediff(day,c.SVC_DATE,c.SVC_DATE_TO)+1 daysdiff
FROM chain_builder b
JOIN chains c
  ON c.chain_ID=b.chain_ID
 AND c.chain_count=b.chain_count
 AND c.max_date=b.SVC_DATE_TO
),
diff_sum AS
(
SELECT chain_ID, MEMBER_ID, SVC_DATE, SVC_DATE_TO,
  CLAIM_ID, chain_count,
  SUM(daysdiff) OVER (PARTITION BY chain_ID) as total_diff
FROM diff
),
diff_comp AS
(
SELECT chain_ID, MEMBER_ID,
  MAX(total_diff) OVER (PARTITION BY MEMBER_ID) as total_diff
FROM diff_sum
)
INSERT INTO [RESULTS_TABLE]
SELECT DISTINCT ds.CLAIM_ID, ds.MEMBER_ID, ds.SVC_DATE,
  ds.SVC_DATE_TO, ds.total_diff as TOTAL_DAYS, ds.chain_count as CHAIN_COUNT
FROM diff_sum ds
JOIN diff_comp dc
ON ds.chain_ID = dc.chain_ID AND ds.MEMBER_ID = dc.MEMBER_ID
  AND ds.total_diff = dc.total_diff
OPTION (maxrecursion 0)

我觉得可以进一步简化它，但由于我没有表tbl_bed_days_test和其中的数据来测试它，所以更难做到这一点。