sql-server – 将每日时间表分组为[开始日期;结束日期]与工作日

样本数据和预期结果的脚本

-- @Src is sample data
-- @Dst is expected result

DECLARE @Src TABLE (ID int PRIMARY KEY,ContractID int,dt date,dowChar char(3),dowInt int);
INSERT INTO @Src (ID,ContractID,dt,dowChar,dowInt) VALUES

-- simple two weeks (without weekend)
(110,1,'2016-05-02','Mon',2),(111,'2016-05-03','Tue',3),(112,'2016-05-04','Wed',4),(113,'2016-05-05','Thu',5),(114,'2016-05-06','Fri',6),(115,'2016-05-09',(116,'2016-05-10',(117,'2016-05-11',(118,'2016-05-12',(119,'2016-05-13',-- a partial end of the week,the whole week,partial start of the week (without weekends)
(223,2,(224,(225,(226,(227,(228,(229,(230,'2016-05-16',(231,'2016-05-17',-- only Mon,Fri are included across two weeks plus partial third week
(310,3,(311,(314,(315,(317,(319,(330,-- a whole week (without weekend),in the second week Mon is not included
(410,4,(411,(412,(413,(414,(416,(417,(418,(419,-- three weeks,but without Mon in the second week (no weekends)
(510,5,(511,(512,(513,(514,(516,(517,(518,(519,(520,(521,(522,'2016-05-18',(523,'2016-05-19',(524,'2016-05-20',-- long gap between two intervals
(623,6,(624,(625,(626,(627,(628,(629,(630,(631,(645,'2016-06-06',(646,'2016-06-07',(647,'2016-06-08',(648,'2016-06-09',(649,'2016-06-10',(655,'2016-06-13',(656,'2016-06-14',(657,'2016-06-15',(658,'2016-06-16',(659,'2016-06-17',-- two weeks,no gaps between days at all,even weekends are included
(710,7,(711,(712,(713,(714,(715,'2016-05-07','Sat',7),(716,'2016-05-08','Sun',1),(725,(726,(727,(728,(729,-- no gaps between days at all,even weekends are included,with partial weeks
(805,8,'2016-04-30',(806,'2016-05-01',(810,(811,(812,(813,(814,(815,(816,(825,(826,(827,(828,(829,(830,'2016-05-14',-- only Mon-Wed included,two weeks plus partial third week
(910,9,(911,(912,(915,(916,(917,(930,(931,-- only Thu-Sun included,three weeks
(1013,10,(1014,(1015,(1016,(1018,(1019,(1020,(1021,'2016-05-15',(1023,(1024,(1025,'2016-05-21',(1026,'2016-05-22',-- only Tue for first three weeks,then only Thu for the next three weeks
(1111,11,(1116,(1131,(1123,(1124,'2016-05-26',(1125,'2016-06-02',-- one week,then one week gap,then one week
(1210,12,(1211,(1212,(1213,(1214,(1215,(1216,(1217,(1218,(1219,6);

SELECT ID,dowInt
FROM @Src
ORDER BY ContractID,dt;


DECLARE @Dst TABLE (ContractID int,StartDT date,EndDT date,DayCount int,WeekDays varchar(255));
INSERT INTO @Dst (ContractID,StartDT,EndDT,DayCount,WeekDays) VALUES
(1,'Mon,'),(2,(3,(4,'Tue,(5,(6,(7,'Sun,Mon,Sat,(8,15,(9,(10,(11,'Thu,(12,');

SELECT ContractID,WeekDays
FROM @Dst
ORDER BY ContractID,StartDT;

答案比较

真实的表@Src有403,555行,有15,857个不同的ContractID.
所有答案都会产生正确的结果(至少对我的数据而言)并且所有答案都相当快,但它们的最优性不同.生成的间隔越少越好.为了好奇,我把运行时间包括在内.主要关注的是正确和最佳的结果,而不是速度(除非需要太长时间 – 我在10分钟后停止了Ziggy Crueltyfree Zeitgeister的非递归查询).

+--------------------------------------------------------+-----------+---------+
|                         Answer                         | Intervals | Seconds |
+--------------------------------------------------------+-----------+---------+
| Ziggy Crueltyfree Zeitgeister                          |     25751 |    7.88 |
| While loop                                             |           |         |
|                                                        |           |         |
| Ziggy Crueltyfree Zeitgeister                          |     25751 |    8.27 |
| Recursive                                              |           |         |
|                                                        |           |         |
| Michael Green                                          |     25751 |   22.63 |
| Recursive                                              |           |         |
|                                                        |           |         |
| Geoff Patterson                                        |     26670 |    4.79 |
| Weekly gaps-and-islands with merging of partial weeks  |           |         |
|                                                        |           |         |
| Vladimir Baranov                                       |     34560 |    4.03 |
| Daily,then weekly gaps-and-islands                    |           |         |
|                                                        |           |         |
| Mikael Eriksson                                        |     35840 |    0.65 |
| Weekly gaps-and-islands                                |           |         |
+--------------------------------------------------------+-----------+---------+
| Vladimir Baranov                                       |     25751 |  121.51 |
| Cursor                                                 |           |         |
+--------------------------------------------------------+-----------+---------+

解决方法

SET DATEFIRST 1 -- Make Monday weekday=1

DECLARE @Ranked TABLE (RowID int NOT NULL IDENTITY PRIMARY KEY,-- Incremental uninterrupted sequence in the right order
                       ID int NOT NULL UNIQUE,ContractID int NOT NULL,-- Original relevant values (ID is not really necessary)
                       WeekNo int NOT NULL,dowBit int NOT NULL);                 -- Useful to find gaps in days or weeks
INSERT INTO @Ranked
SELECT ID,DATEDIFF(WEEK,'1900-01-01',DATEADD(DAY,1-DATEPART(dw,dt),dt)) AS WeekNo,POWER(2,DATEPART(dw,dt)-1) AS dowBit
FROM @Src
ORDER BY ContractID,WeekNo,dowBit

/*
Each evaluated date makes part of the carried sequence if:
  - this is not a new contract,and
    - sequence started this week,or
    - same day last week was part of the sequence,or
    - sequence started last week and today is a lower day than the accumulated weekdays list
  - and there are no sequence gaps since previous day
(otherwise it does not make part of the old sequence,so it starts a new one) */

DECLARE @RankedRanges TABLE (RowID int NOT NULL PRIMARY KEY,WeekDays int NOT NULL,StartRowID int NULL);

WITH WeeksCTE AS -- Needed for building the sequence gradually,and comparing the carried sequence (and previous day) with a current evaluated day
( 
    SELECT RowID,dowBit,RowID AS StartRowID,WeekNo AS StartWN,dowBit AS WeekDays,dowBit AS StartWeekDays
    FROM @Ranked
    WHERE RowID = 1 
    UNION ALL
    SELECT RowID,StartRowID,CASE WHEN StartRowID IS NULL THEN StartWN ELSE WeekNo END AS WeekNo,CASE WHEN StartRowID IS NULL THEN WeekDays | dowBit ELSE dowBit END AS WeekDays,CASE WHEN StartRowID IS NOT NULL THEN dowBit WHEN WeekNo = StartWN THEN StartWeekDays | dowBit ELSE StartWeekDays END AS StartWeekDays
    FROM (
        SELECT w.*,pre.StartWN,pre.WeekDays,pre.StartWeekDays,CASE WHEN w.ContractID <> pre.ContractID OR     -- New contract always break the sequence
                         NOT (w.WeekNo = pre.StartWN OR        -- Same week as a new sequence always keeps the sequence
                              w.dowBit & pre.WeekDays > 0 OR   -- Days in the sequence keep the sequence (provided there are no gaps,checked later)
                              (w.WeekNo = pre.StartWN+1 AND (w.dowBit-1) & pre.StartWeekDays = 0)) OR -- Days in the second week when less than a week passed since the sequence started remain in sequence
                         (w.WeekNo > pre.StartWN AND -- look for gap after initial week
                          w.WeekNo > pre.WeekNo+1 OR -- look for full-week gaps
                          (w.WeekNo = pre.WeekNo AND                            -- when same week as previous day,((w.dowBit-1) ^ (pre.dowBit*2-1)) & pre.WeekDays > 0 -- days between this and previous weekdays,compared to current series
                          ) OR
                          (w.WeekNo > pre.WeekNo AND                                   -- when following week of previous day,((-1 ^ (pre.dowBit*2-1)) | (w.dowBit-1)) & pre.WeekDays > 0 -- days between this and previous weekdays,compared to current series
                          )) THEN w.RowID END AS StartRowID
        FROM WeeksCTE pre
        JOIN @Ranked w ON (w.RowID = pre.RowID + 1)
        ) w
) 
INSERT INTO @RankedRanges -- days sequence and starting point of each sequence
SELECT RowID,WeekDays,StartRowID
--SELECT *
FROM WeeksCTE
OPTION (MAXRECURSION 0)

--SELECT * FROM @RankedRanges

DECLARE @Ranges TABLE (RowNo int NOT NULL IDENTITY PRIMARY KEY,RowID int NOT NULL);

INSERT INTO @Ranges       -- @RankedRanges filtered only by start of each range,with numbered rows to easily find the end of each range
SELECT StartRowID
FROM @RankedRanges
WHERE StartRowID IS NOT NULL
ORDER BY 1

-- Final result putting everything together
SELECT rs.ContractID,rs.dt AS StartDT,re.dt AS EndDT,re.RowID-rs.RowID+1 AS DayCount,CASE WHEN rr.WeekDays & 64 > 0 THEN 'Sun,' ELSE '' END +
       CASE WHEN rr.WeekDays & 1 > 0 THEN 'Mon,' ELSE '' END +
       CASE WHEN rr.WeekDays & 2 > 0 THEN 'Tue,' ELSE '' END +
       CASE WHEN rr.WeekDays & 4 > 0 THEN 'Wed,' ELSE '' END +
       CASE WHEN rr.WeekDays & 8 > 0 THEN 'Thu,' ELSE '' END +
       CASE WHEN rr.WeekDays & 16 > 0 THEN 'Fri,' ELSE '' END +
       CASE WHEN rr.WeekDays & 32 > 0 THEN 'Sat,' ELSE '' END AS WeekDays
FROM (
    SELECT r.RowID AS StartRowID,COALESCE(pos.RowID-1,(SELECT MAX(RowID) FROM @Ranked)) AS EndRowID
    FROM @Ranges r
    LEFT JOIN @Ranges pos ON (pos.RowNo = r.RowNo + 1)
    ) g
JOIN @Ranked rs ON (rs.RowID = g.StartRowID)
JOIN @Ranked re ON (re.RowID = g.EndRowID)
JOIN @RankedRanges rr ON (rr.RowID = re.RowID)

另一种策略

这个应该比前一个快得多,因为它不依赖于SQL Server 2008中缓慢有限的递归CTE,尽管它实现了或多或少相同的策略.

有一个WHILE循环(我无法设法避免它),但是减少了迭代次数(任何给定契约的最大序列数(减1)).

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!