두 시스템간에 데이터를 변환해야합니다.
첫 번째 시스템은 일정을 일반 날짜 목록으로 저장합니다. 스케줄에 포함 된 각 날짜는 한 행입니다. 날짜 순서에 다양한 간격이있을 수 있습니다 (주말, 공휴일 및 더 이상 일시 중지, 일부 요일은 일정에서 제외 될 수 있음). 간격이 전혀 없으며 주말도 포함될 수 있습니다. 일정은 최대 2 년입니다. 보통 몇 주입니다.
주말을 제외하고 2 주간 진행되는 간단한 일정 예는 다음과 같습니다 (아래 스크립트에는 더 복잡한 예가 있음).
+----+------------+------------+---------+--------+
| ID | ContractID | dt | dowChar | dowInt |
+----+------------+------------+---------+--------+
| 10 | 1 | 2016-05-02 | Mon | 2 |
| 11 | 1 | 2016-05-03 | Tue | 3 |
| 12 | 1 | 2016-05-04 | Wed | 4 |
| 13 | 1 | 2016-05-05 | Thu | 5 |
| 14 | 1 | 2016-05-06 | Fri | 6 |
| 15 | 1 | 2016-05-09 | Mon | 2 |
| 16 | 1 | 2016-05-10 | Tue | 3 |
| 17 | 1 | 2016-05-11 | Wed | 4 |
| 18 | 1 | 2016-05-12 | Thu | 5 |
| 19 | 1 | 2016-05-13 | Fri | 6 |
+----+------------+------------+---------+--------+
ID
고유하지만 반드시 순차적 일 필요는 없습니다 (기본 키임). 날짜는 각 계약 내에서 고유합니다 (에 고유 인덱스가 있음 (ContractID, dt)
).
두 번째 시스템은 스케줄의 일부인 요일 목록과 함께 간격으로 스케줄을 저장합니다. 각 간격은 시작 및 종료 날짜 (포함)와 일정에 포함 된 요일 목록으로 정의됩니다. 이 형식을 사용하면 Mon-Wed와 같은 반복적 인 주별 패턴을 효율적으로 정의 할 수 있지만 공휴일 등의 패턴이 중단되면 고통이됩니다.
위의 간단한 예는 다음과 같습니다.
+------------+------------+------------+----------+----------------------+
| ContractID | StartDT | EndDT | DayCount | WeekDays |
+------------+------------+------------+----------+----------------------+
| 1 | 2016-05-02 | 2016-05-13 | 10 | Mon,Tue,Wed,Thu,Fri, |
+------------+------------+------------+----------+----------------------+
[StartDT;EndDT]
동일한 계약에 속하는 간격이 겹치지 않아야합니다.
첫 번째 시스템의 데이터를 두 번째 시스템에서 사용하는 형식으로 변환해야합니다. 현재 단일 계약의 C #에서 클라이언트 측 에서이 문제를 해결하고 있지만 서버 측의 대량 처리 및 내보내기 / 가져 오기를 위해 서버 측의 T-SQL 에서이 작업을 수행하고 싶습니다. 대부분 CLR UDF를 사용하여 수행 할 수 있지만이 단계에서는 SQLCLR을 사용할 수 없습니다.
여기서의 과제는 가능한 한 짧고 인간 친화적 인 간격 목록을 만드는 것입니다.
예를 들어이 일정은 다음과 같습니다.
+-----+------------+------------+---------+--------+
| ID | ContractID | dt | dowChar | dowInt |
+-----+------------+------------+---------+--------+
| 223 | 2 | 2016-05-05 | Thu | 5 |
| 224 | 2 | 2016-05-06 | Fri | 6 |
| 225 | 2 | 2016-05-09 | Mon | 2 |
| 226 | 2 | 2016-05-10 | Tue | 3 |
| 227 | 2 | 2016-05-11 | Wed | 4 |
| 228 | 2 | 2016-05-12 | Thu | 5 |
| 229 | 2 | 2016-05-13 | Fri | 6 |
| 230 | 2 | 2016-05-16 | Mon | 2 |
| 231 | 2 | 2016-05-17 | Tue | 3 |
+-----+------------+------------+---------+--------+
이되어야한다 :
+------------+------------+------------+----------+----------------------+
| ContractID | StartDT | EndDT | DayCount | WeekDays |
+------------+------------+------------+----------+----------------------+
| 2 | 2016-05-05 | 2016-05-17 | 9 | Mon,Tue,Wed,Thu,Fri, |
+------------+------------+------------+----------+----------------------+
,이거 말고:
+------------+------------+------------+----------+----------------------+
| ContractID | StartDT | EndDT | DayCount | WeekDays |
+------------+------------+------------+----------+----------------------+
| 2 | 2016-05-05 | 2016-05-06 | 2 | Thu,Fri, |
| 2 | 2016-05-09 | 2016-05-13 | 5 | Mon,Tue,Wed,Thu,Fri, |
| 2 | 2016-05-16 | 2016-05-17 | 2 | Mon,Tue, |
+------------+------------+------------+----------+----------------------+
gaps-and-islands
이 문제에 대한 접근 방식 을 적용하려고했습니다 . 나는 두 번에 그것을 시도했다. 첫 번째 패스에서 나는 간단한 연속 일의 섬을 발견합니다. 즉, 섬의 끝은 주말, 공휴일 또는 다른 일과 같이 일의 순서에 차이가 있습니다. 이렇게 발견 된 섬마다 쉼표로 구분 된 distinct 목록을 작성합니다 WeekDays
. 두 번째 패스에서 I 그룹은 일련의 주 번호 간격 또는 WeekDays
.
이 방법을 사용하면 주 번호가 연속적이지만 WeekDays
변경 사항 이 있기 때문에 각 부분주는 위에 표시된 것처럼 추가 간격으로 끝납니다 . 또한 일주일 내에 규칙적인 간격이있을 수 있으며 (에 대한 ContractID=3
데이터 만있는 샘플 데이터 참조 Mon,Wed,Fri,
)이 방식은 해당 일정에서 매일 별도의 간격을 생성합니다. 밝은면에서 일정에 간격이 전혀 없으면 ( ContractID=7
주말을 포함하는 샘플 데이터 참조) 한 번의 간격을 생성 하며이 경우 시작 또는 종료 주가 부분적인지 중요하지 않습니다.
내가 원하는 것을 더 잘 이해하려면 아래 스크립트의 다른 예제를 참조하십시오. 주말이 제외되는 경우가 많지만 다른 요일도 제외 될 수 있습니다. 예를 들면 3 단으로 Mon
, Wed
그리고 Fri
스케줄의 일부이다. 또한, 예 7에서와 같이 주말이 포함될 수 있습니다. 솔루션은 모든 요일을 동일하게 처리해야합니다. 요일은 일정에 포함하거나 제외 할 수 있습니다.
생성 된 간격 목록이 주어진 스케줄을 올바르게 설명하는지 확인하기 위해 다음 의사 코드를 사용할 수 있습니다.
- 모든 간격을 반복
- 각 간격에 대해 시작 날짜와 종료 날짜 사이의 모든 달력 날짜를 포함합니다 (포함).
- 각 날짜에 요일이에 표시되어 있는지 확인하십시오
WeekDays
. 그렇다면이 날짜가 일정에 포함됩니다.
바라건대, 어떤 경우에 새로운 간격을 만들어야하는지 명확하게 설명합니다. 예제 4와 5에서 한 월요일 ( 2016-05-09
)이 스케줄의 중간에서 제거되며 이러한 스케줄을 단일 간격으로 표시 할 수 없습니다. 예 6에서는 스케줄에 간격이 길기 때문에 두 개의 간격이 필요합니다.
간격은 일람표에서 주별 패턴을 나타내며 패턴이 중단 / 변경 될 때 새 간격을 추가해야합니다. 예 11에서 처음 3 주 동안 패턴 Tue
이 있으면이 패턴이로 바뀝니다 Thu
. 결과적으로 우리는 그러한 일정을 설명하기 위해 두 개의 간격이 필요합니다.
현재 SQL Server 2008을 사용하고 있으므로 솔루션이이 버전에서 작동해야합니다. 이후 버전의 기능을 사용하여 SQL Server 2008 용 솔루션을 단순화 / 개선 할 수 있다면 보너스이기도합니다.
나는이 Calendar
테이블 (날짜 목록)과 Numbers
이 필요한 경우,이를 사용하려면 확인하므로, 테이블 (1부터 정수 번호의 전체 목록을). 임시 테이블을 작성하고 여러 단계로 데이터를 처리하는 여러 쿼리를 갖는 것도 좋습니다. 알고리즘의 단계 수는 고정되어야하지만 커서와 명시 적 WHILE
루프는 정상이 아닙니다.
샘플 데이터 및 예상 결과를위한 스크립트
-- @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, 1, '2016-05-03', 'Tue', 3),
(112, 1, '2016-05-04', 'Wed', 4),
(113, 1, '2016-05-05', 'Thu', 5),
(114, 1, '2016-05-06', 'Fri', 6),
(115, 1, '2016-05-09', 'Mon', 2),
(116, 1, '2016-05-10', 'Tue', 3),
(117, 1, '2016-05-11', 'Wed', 4),
(118, 1, '2016-05-12', 'Thu', 5),
(119, 1, '2016-05-13', 'Fri', 6),
-- a partial end of the week, the whole week, partial start of the week (without weekends)
(223, 2, '2016-05-05', 'Thu', 5),
(224, 2, '2016-05-06', 'Fri', 6),
(225, 2, '2016-05-09', 'Mon', 2),
(226, 2, '2016-05-10', 'Tue', 3),
(227, 2, '2016-05-11', 'Wed', 4),
(228, 2, '2016-05-12', 'Thu', 5),
(229, 2, '2016-05-13', 'Fri', 6),
(230, 2, '2016-05-16', 'Mon', 2),
(231, 2, '2016-05-17', 'Tue', 3),
-- only Mon, Wed, Fri are included across two weeks plus partial third week
(310, 3, '2016-05-02', 'Mon', 2),
(311, 3, '2016-05-04', 'Wed', 4),
(314, 3, '2016-05-06', 'Fri', 6),
(315, 3, '2016-05-09', 'Mon', 2),
(317, 3, '2016-05-11', 'Wed', 4),
(319, 3, '2016-05-13', 'Fri', 6),
(330, 3, '2016-05-16', 'Mon', 2),
-- a whole week (without weekend), in the second week Mon is not included
(410, 4, '2016-05-02', 'Mon', 2),
(411, 4, '2016-05-03', 'Tue', 3),
(412, 4, '2016-05-04', 'Wed', 4),
(413, 4, '2016-05-05', 'Thu', 5),
(414, 4, '2016-05-06', 'Fri', 6),
(416, 4, '2016-05-10', 'Tue', 3),
(417, 4, '2016-05-11', 'Wed', 4),
(418, 4, '2016-05-12', 'Thu', 5),
(419, 4, '2016-05-13', 'Fri', 6),
-- three weeks, but without Mon in the second week (no weekends)
(510, 5, '2016-05-02', 'Mon', 2),
(511, 5, '2016-05-03', 'Tue', 3),
(512, 5, '2016-05-04', 'Wed', 4),
(513, 5, '2016-05-05', 'Thu', 5),
(514, 5, '2016-05-06', 'Fri', 6),
(516, 5, '2016-05-10', 'Tue', 3),
(517, 5, '2016-05-11', 'Wed', 4),
(518, 5, '2016-05-12', 'Thu', 5),
(519, 5, '2016-05-13', 'Fri', 6),
(520, 5, '2016-05-16', 'Mon', 2),
(521, 5, '2016-05-17', 'Tue', 3),
(522, 5, '2016-05-18', 'Wed', 4),
(523, 5, '2016-05-19', 'Thu', 5),
(524, 5, '2016-05-20', 'Fri', 6),
-- long gap between two intervals
(623, 6, '2016-05-05', 'Thu', 5),
(624, 6, '2016-05-06', 'Fri', 6),
(625, 6, '2016-05-09', 'Mon', 2),
(626, 6, '2016-05-10', 'Tue', 3),
(627, 6, '2016-05-11', 'Wed', 4),
(628, 6, '2016-05-12', 'Thu', 5),
(629, 6, '2016-05-13', 'Fri', 6),
(630, 6, '2016-05-16', 'Mon', 2),
(631, 6, '2016-05-17', 'Tue', 3),
(645, 6, '2016-06-06', 'Mon', 2),
(646, 6, '2016-06-07', 'Tue', 3),
(647, 6, '2016-06-08', 'Wed', 4),
(648, 6, '2016-06-09', 'Thu', 5),
(649, 6, '2016-06-10', 'Fri', 6),
(655, 6, '2016-06-13', 'Mon', 2),
(656, 6, '2016-06-14', 'Tue', 3),
(657, 6, '2016-06-15', 'Wed', 4),
(658, 6, '2016-06-16', 'Thu', 5),
(659, 6, '2016-06-17', 'Fri', 6),
-- two weeks, no gaps between days at all, even weekends are included
(710, 7, '2016-05-02', 'Mon', 2),
(711, 7, '2016-05-03', 'Tue', 3),
(712, 7, '2016-05-04', 'Wed', 4),
(713, 7, '2016-05-05', 'Thu', 5),
(714, 7, '2016-05-06', 'Fri', 6),
(715, 7, '2016-05-07', 'Sat', 7),
(716, 7, '2016-05-08', 'Sun', 1),
(725, 7, '2016-05-09', 'Mon', 2),
(726, 7, '2016-05-10', 'Tue', 3),
(727, 7, '2016-05-11', 'Wed', 4),
(728, 7, '2016-05-12', 'Thu', 5),
(729, 7, '2016-05-13', 'Fri', 6),
-- no gaps between days at all, even weekends are included, with partial weeks
(805, 8, '2016-04-30', 'Sat', 7),
(806, 8, '2016-05-01', 'Sun', 1),
(810, 8, '2016-05-02', 'Mon', 2),
(811, 8, '2016-05-03', 'Tue', 3),
(812, 8, '2016-05-04', 'Wed', 4),
(813, 8, '2016-05-05', 'Thu', 5),
(814, 8, '2016-05-06', 'Fri', 6),
(815, 8, '2016-05-07', 'Sat', 7),
(816, 8, '2016-05-08', 'Sun', 1),
(825, 8, '2016-05-09', 'Mon', 2),
(826, 8, '2016-05-10', 'Tue', 3),
(827, 8, '2016-05-11', 'Wed', 4),
(828, 8, '2016-05-12', 'Thu', 5),
(829, 8, '2016-05-13', 'Fri', 6),
(830, 8, '2016-05-14', 'Sat', 7),
-- only Mon-Wed included, two weeks plus partial third week
(910, 9, '2016-05-02', 'Mon', 2),
(911, 9, '2016-05-03', 'Tue', 3),
(912, 9, '2016-05-04', 'Wed', 4),
(915, 9, '2016-05-09', 'Mon', 2),
(916, 9, '2016-05-10', 'Tue', 3),
(917, 9, '2016-05-11', 'Wed', 4),
(930, 9, '2016-05-16', 'Mon', 2),
(931, 9, '2016-05-17', 'Tue', 3),
-- only Thu-Sun included, three weeks
(1013,10,'2016-05-05', 'Thu', 5),
(1014,10,'2016-05-06', 'Fri', 6),
(1015,10,'2016-05-07', 'Sat', 7),
(1016,10,'2016-05-08', 'Sun', 1),
(1018,10,'2016-05-12', 'Thu', 5),
(1019,10,'2016-05-13', 'Fri', 6),
(1020,10,'2016-05-14', 'Sat', 7),
(1021,10,'2016-05-15', 'Sun', 1),
(1023,10,'2016-05-19', 'Thu', 5),
(1024,10,'2016-05-20', 'Fri', 6),
(1025,10,'2016-05-21', 'Sat', 7),
(1026,10,'2016-05-22', 'Sun', 1),
-- only Tue for first three weeks, then only Thu for the next three weeks
(1111,11,'2016-05-03', 'Tue', 3),
(1116,11,'2016-05-10', 'Tue', 3),
(1131,11,'2016-05-17', 'Tue', 3),
(1123,11,'2016-05-19', 'Thu', 5),
(1124,11,'2016-05-26', 'Thu', 5),
(1125,11,'2016-06-02', 'Thu', 5),
-- one week, then one week gap, then one week
(1210,12,'2016-05-02', 'Mon', 2),
(1211,12,'2016-05-03', 'Tue', 3),
(1212,12,'2016-05-04', 'Wed', 4),
(1213,12,'2016-05-05', 'Thu', 5),
(1214,12,'2016-05-06', 'Fri', 6),
(1215,12,'2016-05-16', 'Mon', 2),
(1216,12,'2016-05-17', 'Tue', 3),
(1217,12,'2016-05-18', 'Wed', 4),
(1218,12,'2016-05-19', 'Thu', 5),
(1219,12,'2016-05-20', 'Fri', 6);
SELECT ID, ContractID, dt, dowChar, 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, '2016-05-02', '2016-05-13', 10, 'Mon,Tue,Wed,Thu,Fri,'),
(2, '2016-05-05', '2016-05-17', 9, 'Mon,Tue,Wed,Thu,Fri,'),
(3, '2016-05-02', '2016-05-16', 7, 'Mon,Wed,Fri,'),
(4, '2016-05-02', '2016-05-06', 5, 'Mon,Tue,Wed,Thu,Fri,'),
(4, '2016-05-10', '2016-05-13', 4, 'Tue,Wed,Thu,Fri,'),
(5, '2016-05-02', '2016-05-06', 5, 'Mon,Tue,Wed,Thu,Fri,'),
(5, '2016-05-10', '2016-05-20', 9, 'Mon,Tue,Wed,Thu,Fri,'),
(6, '2016-05-05', '2016-05-17', 9, 'Mon,Tue,Wed,Thu,Fri,'),
(6, '2016-06-06', '2016-06-17', 10, 'Mon,Tue,Wed,Thu,Fri,'),
(7, '2016-05-02', '2016-05-13', 12, 'Sun,Mon,Tue,Wed,Thu,Fri,Sat,'),
(8, '2016-04-30', '2016-05-14', 15, 'Sun,Mon,Tue,Wed,Thu,Fri,Sat,'),
(9, '2016-05-02', '2016-05-17', 8, 'Mon,Tue,Wed,'),
(10,'2016-05-05', '2016-05-22', 12, 'Sun,Thu,Fri,Sat,'),
(11,'2016-05-03', '2016-05-17', 3, 'Tue,'),
(11,'2016-05-19', '2016-06-02', 3, 'Thu,'),
(12,'2016-05-02', '2016-05-06', 5, 'Mon,Tue,Wed,Thu,Fri,'),
(12,'2016-05-16', '2016-05-20', 5, 'Mon,Tue,Wed,Thu,Fri,');
SELECT ContractID, StartDT, EndDT, DayCount, WeekDays
FROM @Dst
ORDER BY ContractID, StartDT;
답변 비교
실제 테이블 @Src
에는 distinct 403,555
행이 15,857
있습니다 ContractIDs
. 모든 답변은 (적어도 내 데이터에 대해서는) 올바른 결과를 생성하며 모두 빠르지 만 최적 성은 다릅니다. 간격이 적을수록 좋습니다. 나는 호기심을 위해 런타임을 포함시켰다. 주요 초점은 속도가 아니라 정확하고 최적의 결과입니다 (시간이 오래 걸리지 않는 한-Ziggy Crueltyfree Zeitgeister의 비 재귀 쿼리를 10 분 후에 중지했습니다).
+--------------------------------------------------------+-----------+---------+
| 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 | | |
+--------------------------------------------------------+-----------+---------+
답변
이것은 재귀 CTE를 사용합니다. 결과는 질문의 예와 동일합니다 . 생각해 낸 것은 악몽이었다 … 코드에는 복잡한 논리를 통해 쉽게 설명이 포함되어있다.
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, dt date, -- 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, ContractID, dt,
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, ContractID, dowBit, WeekNo, RowID AS StartRowID, WeekNo AS StartWN, dowBit AS WeekDays, dowBit AS StartWeekDays
FROM @Ranked
WHERE RowID = 1
UNION ALL
SELECT RowID, ContractID, dowBit, WeekNo, 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).
간단한 전략이며 일주일보다 짧거나 긴 시퀀스 (다른 숫자에 대해 상수 7이 발생 dowBit
하고 MODULUS x 에서 계산 된 값 DayNo
대신 DATEPART(wk)
32 개 ) 및 최대 32 개의 시퀀스에 사용될 수 있습니다 .
SET DATEFIRST 1 -- Make Monday weekday=1
-- Get the minimum information needed to calculate sequences
DECLARE @Days TABLE (ContractID int NOT NULL, dt date, DayNo int NOT NULL, dowBit int NOT NULL, PRIMARY KEY (ContractID, DayNo));
INSERT INTO @Days
SELECT ContractID, dt, CAST(CAST(dt AS datetime) AS int) AS DayNo, POWER(2, DATEPART(dw, dt)-1) AS dowBit
FROM @Src
DECLARE @RangeStartFirstPass TABLE (ContractID int NOT NULL, DayNo int NOT NULL, PRIMARY KEY (ContractID, DayNo))
-- Calculate, from the above list, which days are not present in the previous 7
INSERT INTO @RangeStartFirstPass
SELECT r.ContractID, r.DayNo
FROM @Days r
LEFT JOIN @Days pr ON (pr.ContractID = r.ContractID AND pr.DayNo BETWEEN r.DayNo-7 AND r.DayNo-1) -- Last 7 days
GROUP BY r.ContractID, r.DayNo, r.dowBit
HAVING r.dowBit & COALESCE(SUM(pr.dowBit), 0) = 0
-- Update the previous list with all days that occur right after a missing day
INSERT INTO @RangeStartFirstPass
SELECT *
FROM (
SELECT DISTINCT ContractID, (SELECT MIN(DayNo) FROM @Days WHERE ContractID = d.ContractID AND DayNo > d.DayNo + 7) AS DayNo
FROM @Days d
WHERE NOT EXISTS (SELECT 1 FROM @Days WHERE ContractID = d.ContractID AND DayNo = d.DayNo + 7)
) d
WHERE DayNo IS NOT NULL AND
NOT EXISTS (SELECT 1 FROM @RangeStartFirstPass WHERE ContractID = d.ContractID AND DayNo = d.DayNo)
DECLARE @RangeStart TABLE (ContractID int NOT NULL, DayNo int NOT NULL, PRIMARY KEY (ContractID, DayNo));
-- Fetch the first sequence for each contract
INSERT INTO @RangeStart
SELECT ContractID, MIN(DayNo)
FROM @RangeStartFirstPass
GROUP BY ContractID
-- Add to the list above the next sequence for each contract, until all are added
-- (ensure no sequence is added with less than 7 days)
WHILE @@ROWCOUNT > 0
INSERT INTO @RangeStart
SELECT f.ContractID, MIN(f.DayNo)
FROM (SELECT ContractID, MAX(DayNo) AS DayNo FROM @RangeStart GROUP BY ContractID) s
JOIN @RangeStartFirstPass f ON (f.ContractID = s.ContractID AND f.DayNo > s.DayNo + 7)
GROUP BY f.ContractID
-- Summarise results
SELECT ContractID, StartDT, EndDT, DayCount,
CASE WHEN WeekDays & 64 > 0 THEN 'Sun,' ELSE '' END +
CASE WHEN WeekDays & 1 > 0 THEN 'Mon,' ELSE '' END +
CASE WHEN WeekDays & 2 > 0 THEN 'Tue,' ELSE '' END +
CASE WHEN WeekDays & 4 > 0 THEN 'Wed,' ELSE '' END +
CASE WHEN WeekDays & 8 > 0 THEN 'Thu,' ELSE '' END +
CASE WHEN WeekDays & 16 > 0 THEN 'Fri,' ELSE '' END +
CASE WHEN WeekDays & 32 > 0 THEN 'Sat,' ELSE '' END AS WeekDays
FROM (
SELECT r.ContractID,
MIN(d.dt) AS StartDT,
MAX(d.dt) AS EndDT,
COUNT(*) AS DayCount,
SUM(DISTINCT d.dowBit) AS WeekDays
FROM (SELECT *, COALESCE((SELECT MIN(DayNo) FROM @RangeStart WHERE ContractID = rs.ContractID AND DayNo > rs.DayNo), 999999) AS DayEnd FROM @RangeStart rs) r
JOIN @Days d ON (d.ContractID = r.ContractID AND d.DayNo BETWEEN r.DayNo AND r.DayEnd-1)
GROUP BY r.ContractID, r.DayNo
) d
ORDER BY ContractID, StartDT
답변
정확히 당신이 찾고 있지만 관심이있을 수 있습니다.
쿼리는 각 주에 사용 된 요일에 쉼표로 구분 된 문자열로 주를 만듭니다. 그런 다음에서 동일한 패턴을 사용하는 연속 주 섬을 찾습니다 Weekdays
.
with Weeks as
(
select T.*,
row_number() over(partition by T.ContractID, T.WeekDays order by T.WeekNumber) as rn
from (
select S1.ContractID,
min(S1.dt) as StartDT,
max(S1.dt) as EndDT,
datediff(day, 0, S1.dt) / 7 as WeekNumber, -- Number of weeks since '1900-01-01 (a monday)'
count(*) as DayCount,
stuff((
select ','+S2.dowChar
from @Src as S2
where S2.ContractID = S1.ContractID and
S2.dt between min(S1.dt) and max(S1.dt)
order by S2.dt
for xml path('')
), 1, 1, '') as WeekDays
from @Src as S1
group by S1.ContractID,
datediff(day, 0, S1.dt) / 7
) as T
)
select W.ContractID,
min(W.StartDT) as StartDT,
max(W.EndDT) as EndDT,
count(*) * W.DayCount as DayCount,
W.WeekDays
from Weeks as W
group by W.ContractID,
W.WeekDays,
W.DayCount,
W.rn - W.WeekNumber
order by W.ContractID,
min(W.WeekNumber);
결과:
ContractID StartDT EndDT DayCount WeekDays
----------- ---------- ---------- ----------- -----------------------------
1 2016-05-02 2016-05-13 10 Mon,Tue,Wed,Thu,Fri
2 2016-05-05 2016-05-06 2 Thu,Fri
2 2016-05-09 2016-05-13 5 Mon,Tue,Wed,Thu,Fri
2 2016-05-16 2016-05-17 2 Mon,Tue
3 2016-05-02 2016-05-13 6 Mon,Wed,Fri
3 2016-05-16 2016-05-16 1 Mon
4 2016-05-02 2016-05-06 5 Mon,Tue,Wed,Thu,Fri
4 2016-05-10 2016-05-13 4 Tue,Wed,Thu,Fri
5 2016-05-02 2016-05-06 5 Mon,Tue,Wed,Thu,Fri
5 2016-05-10 2016-05-13 4 Tue,Wed,Thu,Fri
5 2016-05-16 2016-05-20 5 Mon,Tue,Wed,Thu,Fri
6 2016-05-05 2016-05-06 2 Thu,Fri
6 2016-05-09 2016-05-13 5 Mon,Tue,Wed,Thu,Fri
6 2016-05-16 2016-05-17 2 Mon,Tue
6 2016-06-06 2016-06-17 10 Mon,Tue,Wed,Thu,Fri
7 2016-05-02 2016-05-08 7 Mon,Tue,Wed,Thu,Fri,Sat,Sun
7 2016-05-09 2016-05-13 5 Mon,Tue,Wed,Thu,Fri
8 2016-04-30 2016-05-01 2 Sat,Sun
8 2016-05-02 2016-05-08 7 Mon,Tue,Wed,Thu,Fri,Sat,Sun
8 2016-05-09 2016-05-14 6 Mon,Tue,Wed,Thu,Fri,Sat
9 2016-05-02 2016-05-11 6 Mon,Tue,Wed
9 2016-05-16 2016-05-17 2 Mon,Tue
10 2016-05-05 2016-05-22 12 Thu,Fri,Sat,Sun
11 2016-05-03 2016-05-10 2 Tue
11 2016-05-17 2016-05-19 2 Tue,Thu
11 2016-05-26 2016-06-02 2 Thu
ContractID = 2
결과의 차이가 원하는 것과 비교되는 것을 보여줍니다. 첫 주와 마지막 주가 WeekDays
다르기 때문에 별도의 기간으로 취급됩니다 .
답변
나는이 경우 최적의 솔루션을 산출하는 접근 방식으로 끝났으며 일반적으로 잘 될 것이라고 생각합니다. 그러나 해결책은 상당히 길기 때문에 다른 사람이 더 간결한 다른 접근 방식을 가지고 있는지 확인하는 것이 흥미로울 것입니다.
알고리즘의 개요는 다음과 같습니다.
- 매주를 나타내는 단일 행이 있도록 데이터 세트를 피벗하십시오.
- 각 주 내의 섬을 계산
ContractId
- 동일
ContractId
하고 동일한 동일한 주를 병합WeekDays
- 이전 그룹이 같은 섬
WeekDays
에 있고 단일 주가WeekDays
이전 그룹의 주요 하위 집합과 일치하는 단일 주 (아직 병합되지 않은) 에 대해 이전 그룹으로 병합 - 다음 그룹이 같은 섬
WeekDays
에 있고 단일 그룹이 다음 그룹 의 후행 하위 집합과 일치하는 단일 주 (아직 병합되지 않은)WeekDays
에 대해 다음 그룹으로 병합 - 같은 섬에서 합병 된 곳이 아닌 두 주 동안, 두 주일이 합쳐질 수있는 부분적 주 (예 : “Mon, Tue, Wed, Thu”및 “Wed, Thu, Sat”) )
- 남은 1 주일 동안 (아직 병합되지 않은 경우) 가능하면 주를 두 부분으로 나누고 두 부분을 병합합니다. 첫 번째 부분은 같은 섬의 이전 그룹으로, 두 번째 부분은 같은 섬의 다음 그룹으로
답변
간격이있는 주 또는 주말이있는 주를 그룹화하는 논리를 이해할 수 없었습니다 (예 : 주말에 2 주 연속으로 주말이있는 주)?
다음 쿼리는 평일 연속으로 그룹화하고 월-일이 아닌 Sun-Sat 그룹으로 그룹화하는 것을 제외하고 원하는 출력을 생성합니다. 정확히 원하는 것은 아니지만 다른 전략에 대한 힌트를 얻을 수 있습니다. 일의 그룹화는 여기 에서 온다 . 사용 된 윈도우 기능은 SQLServer 2008에서 작동해야하지만 실제로 버전을 테스트 할 버전이 없습니다.
WITH
mysrc AS (
SELECT *, RANK() OVER (PARTITION BY ContractID ORDER BY DT) AS rank
FROM @Src
),
prepos AS (
SELECT s.*, pos.ID AS posid
FROM mysrc s
LEFT JOIN mysrc pos ON (pos.ContractID = s.ContractID AND pos.rank = s.rank+1 AND (pos.DowInt = s.DowInt+1 OR pos.DowInt = 2 AND s.DowInt=6))
),
grped AS (
SELECT TOP 100 *, (SELECT COUNT(CASE WHEN posid IS NULL THEN 1 END) FROM prepos WHERE contractid = p.contractid AND rank < p.rank) as grp
FROM prepos p
ORDER BY ContractID, DT
)
SELECT ContractID, min(dt) AS StartDT, max(dt) AS EndDT, count(*) AS DayCount,
STUFF( (SELECT ', ' + dowchar
FROM (
SELECT TOP 100 dowint, dowchar
FROM grped
WHERE ContractID = g.ContractID AND grp = g.grp
GROUP BY dowint, dowchar
ORDER BY 1
) a
FOR XML PATH(''), TYPE).value('.','varchar(max)'), 1, 2, '') AS WeekDays
FROM grped g
GROUP BY ContractID, grp
ORDER BY 1, 2
결과
+------------+------------+------------+----------+-----------------------------------+
| ContractID | StartDT | EndDT | DayCount | WeekDays |
+------------+------------+------------+----------+-----------------------------------+
| 1 | 2/05/2016 | 13/05/2016 | 10 | Mon, Tue, Wed, Thu, Fri |
| 2 | 5/05/2016 | 17/05/2016 | 9 | Mon, Tue, Wed, Thu, Fri |
| 3 | 2/05/2016 | 2/05/2016 | 1 | Mon |
| 3 | 4/05/2016 | 4/05/2016 | 1 | Wed |
| 3 | 6/05/2016 | 9/05/2016 | 2 | Mon, Fri |
| 3 | 11/05/2016 | 11/05/2016 | 1 | Wed |
| 3 | 13/05/2016 | 16/05/2016 | 2 | Mon, Fri |
| 4 | 2/05/2016 | 6/05/2016 | 5 | Mon, Tue, Wed, Thu, Fri |
| 4 | 10/05/2016 | 13/05/2016 | 4 | Tue, Wed, Thu, Fri |
| 5 | 2/05/2016 | 6/05/2016 | 5 | Mon, Tue, Wed, Thu, Fri |
| 5 | 10/05/2016 | 20/05/2016 | 9 | Mon, Tue, Wed, Thu, Fri |
| 6 | 5/05/2016 | 17/05/2016 | 9 | Mon, Tue, Wed, Thu, Fri |
| 6 | 6/06/2016 | 17/06/2016 | 10 | Mon, Tue, Wed, Thu, Fri |
| 7 | 2/05/2016 | 7/05/2016 | 6 | Mon, Tue, Wed, Thu, Fri, Sat |
| 7 | 8/05/2016 | 13/05/2016 | 6 | Sun, Mon, Tue, Wed, Thu, Fri |
| 8 | 30/04/2016 | 30/04/2016 | 1 | Sat |
| 8 | 1/05/2016 | 7/05/2016 | 7 | Sun, Mon, Tue, Wed, Thu, Fri, Sat |
| 8 | 8/05/2016 | 14/05/2016 | 7 | Sun, Mon, Tue, Wed, Thu, Fri, Sat |
| 9 | 2/05/2016 | 4/05/2016 | 3 | Mon, Tue, Wed |
| 9 | 9/05/2016 | 10/05/2016 | 2 | Mon, Tue |
+------------+------------+------------+----------+-----------------------------------+
답변
완성도 gaps-and-islands
를 높이기 위해이 질문을하기 전에 직접 시도한 2 단계 접근법이 있습니다.
실제 데이터에서 테스트 할 때 잘못된 결과를 생성하고 수정 한 경우는 거의 없었습니다.
알고리즘은 다음과 같습니다.
- 연속 날짜의 섬을 생성 (
CTE_ContractDays
,CTE_DailyRN
,CTE_DailyIslands
)와 섬의 각각의 시작 및 종료 날짜의 주 번호를 계산합니다. 여기서 주 번호는 월요일이주의 첫 번째 요일이라고 가정하여 계산됩니다. - 일정에 같은 주 내에 비 연속 날짜가있는 경우 (예 : 3) 이전 단계에서는 같은 주에 여러 행을 만듭니다. 일주일에 한 행만 갖도록 행을 그룹화하십시오 (
CTE_Weeks
). - 이전 단계의 각 행에 대해 쉼표로 구분 된 요일 목록 (
CTE_FirstResult
)을 작성하십시오. - 갭 및 아일랜드의 두 번째 패스는 동일한 주
WeekDays
(CTE_SecondRN
,CTE_Schedules
)로 연속 주를 그룹화 합니다.
주간 패턴 (1, 7, 8, 10, 12)에 중단이없는 경우를 잘 처리합니다. 패턴에 비연 속일이있는 경우를 잘 처리합니다 (3).
그러나 불행히도 부분 주 (2, 3, 5, 6, 9, 11) 동안 추가 간격을 생성합니다.
WITH
CTE_ContractDays
AS
(
SELECT
S.ContractID
,MIN(S.dt) OVER (PARTITION BY S.ContractID) AS ContractMinDT
,S.dt
,ROW_NUMBER() OVER (PARTITION BY S.ContractID ORDER BY S.dt) AS rn1
,DATEDIFF(day, '2001-01-01', S.dt) AS DayNumber
,S.dowChar
,S.dowInt
FROM
@Src AS S
)
,CTE_DailyRN
AS
(
SELECT
DayNumber - rn1 AS WeekGroupNumber
,ROW_NUMBER() OVER (
PARTITION BY
ContractID
,DayNumber - rn1
ORDER BY dt) AS rn2
,ContractID
,ContractMinDT
,dt
,rn1
,DayNumber
,dowChar
,dowInt
FROM CTE_ContractDays
)
,CTE_DailyIslands
AS
(
SELECT
ContractID
,ContractMinDT
,MIN(dt) AS MinDT
,MAX(dt) AS MaxDT
,COUNT(*) AS DayCount
-- '2001-01-01' is Monday
,DATEDIFF(day, '2001-01-01', MIN(dt)) / 7 AS WeekNumberMin
,DATEDIFF(day, '2001-01-01', MAX(dt)) / 7 AS WeekNumberMax
FROM CTE_DailyRN
GROUP BY
ContractID
,rn1-rn2
,ContractMinDT
)
,CTE_Weeks
AS
(
SELECT
ContractID
,ContractMinDT
,MIN(MinDT) AS MinDT
,MAX(MaxDT) AS MaxDT
,SUM(DayCount) AS DayCount
,WeekNumberMin
,WeekNumberMax
FROM CTE_DailyIslands
GROUP BY
ContractID
,ContractMinDT
,WeekNumberMin
,WeekNumberMax
)
,CTE_FirstResult
AS
(
SELECT
ContractID
,ContractMinDT
,MinDT
,MaxDT
,DayCount
,CA_Data.XML_Value AS DaysOfWeek
,WeekNumberMin AS WeekNumber
,ROW_NUMBER() OVER(PARTITION BY ContractID ORDER BY MinDT) AS rn1
FROM
CTE_Weeks
CROSS APPLY
(
SELECT CAST(CTE_ContractDays.dowChar AS varchar(8000)) + ',' AS dw
FROM CTE_ContractDays
WHERE
CTE_ContractDays.ContractID = CTE_Weeks.ContractID
AND CTE_ContractDays.dt >= CTE_Weeks.MinDT
AND CTE_ContractDays.dt <= CTE_Weeks.MaxDT
GROUP BY
CTE_ContractDays.dowChar
,CTE_ContractDays.dowInt
ORDER BY CTE_ContractDays.dowInt
FOR XML PATH(''), TYPE
) AS CA_XML(XML_Value)
CROSS APPLY
(
SELECT CA_XML.XML_Value.value('.', 'VARCHAR(8000)')
) AS CA_Data(XML_Value)
)
,CTE_SecondRN
AS
(
SELECT
ContractID
,ContractMinDT
,MinDT
,MaxDT
,DayCount
,DaysOfWeek
,WeekNumber
,rn1
,WeekNumber - rn1 AS SecondGroupNumber
,ROW_NUMBER() OVER (
PARTITION BY
ContractID
,DaysOfWeek
,DayCount
,WeekNumber - rn1
ORDER BY MinDT) AS rn2
FROM CTE_FirstResult
)
,CTE_Schedules
AS
(
SELECT
ContractID
,MIN(MinDT) AS StartDT
,MAX(MaxDT) AS EndDT
,SUM(DayCount) AS DayCount
,DaysOfWeek
FROM CTE_SecondRN
GROUP BY
ContractID
,DaysOfWeek
,rn1-rn2
)
SELECT
ContractID
,StartDT
,EndDT
,DayCount
,DaysOfWeek AS WeekDays
FROM CTE_Schedules
ORDER BY
ContractID
,StartDT
;
결과
+------------+------------+------------+----------+------------------------------+
| ContractID | StartDT | EndDT | DayCount | WeekDays |
+------------+------------+------------+----------+------------------------------+
| 1 | 2016-05-02 | 2016-05-13 | 10 | Mon,Tue,Wed,Thu,Fri, |
| 2 | 2016-05-05 | 2016-05-06 | 2 | Thu,Fri, |
| 2 | 2016-05-09 | 2016-05-13 | 5 | Mon,Tue,Wed,Thu,Fri, |
| 2 | 2016-05-16 | 2016-05-17 | 2 | Mon,Tue, |
| 3 | 2016-05-02 | 2016-05-13 | 6 | Mon,Wed,Fri, |
| 3 | 2016-05-16 | 2016-05-16 | 1 | Mon, |
| 4 | 2016-05-02 | 2016-05-06 | 5 | Mon,Tue,Wed,Thu,Fri, |
| 4 | 2016-05-10 | 2016-05-13 | 4 | Tue,Wed,Thu,Fri, |
| 5 | 2016-05-02 | 2016-05-06 | 5 | Mon,Tue,Wed,Thu,Fri, |
| 5 | 2016-05-10 | 2016-05-13 | 4 | Tue,Wed,Thu,Fri, |
| 5 | 2016-05-16 | 2016-05-20 | 5 | Mon,Tue,Wed,Thu,Fri, |
| 6 | 2016-05-05 | 2016-05-06 | 2 | Thu,Fri, |
| 6 | 2016-05-09 | 2016-05-13 | 5 | Mon,Tue,Wed,Thu,Fri, |
| 6 | 2016-05-16 | 2016-05-17 | 2 | Mon,Tue, |
| 6 | 2016-06-06 | 2016-06-17 | 10 | Mon,Tue,Wed,Thu,Fri, |
| 7 | 2016-05-02 | 2016-05-13 | 12 | Sun,Mon,Tue,Wed,Thu,Fri,Sat, |
| 8 | 2016-04-30 | 2016-05-14 | 15 | Sun,Mon,Tue,Wed,Thu,Fri,Sat, |
| 9 | 2016-05-02 | 2016-05-11 | 6 | Mon,Tue,Wed, |
| 9 | 2016-05-16 | 2016-05-17 | 2 | Mon,Tue, |
| 10 | 2016-05-05 | 2016-05-22 | 12 | Sun,Thu,Fri,Sat, |
| 11 | 2016-05-03 | 2016-05-10 | 2 | Tue, |
| 11 | 2016-05-17 | 2016-05-19 | 2 | Tue,Thu, |
| 11 | 2016-05-26 | 2016-06-02 | 2 | Thu, |
| 12 | 2016-05-02 | 2016-05-06 | 5 | Mon,Tue,Wed,Thu,Fri, |
| 12 | 2016-05-16 | 2016-05-20 | 5 | Mon,Tue,Wed,Thu,Fri, |
+------------+------------+------------+----------+------------------------------+
커서 기반 솔루션
C # 코드를 커서 기반 알고리즘으로 변환하여 실제 데이터의 다른 솔루션과 비교하는 방법을 확인했습니다. 다른 세트 기반 또는 재귀 접근법보다 훨씬 느리다는 것을 확인하지만 최적의 결과를 생성합니다.
CREATE TABLE #Dst_V2 (ContractID bigint, StartDT date, EndDT date, DayCount int, WeekDays varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS);
SET NOCOUNT ON;
DECLARE @VarOldDateFirst int = @@DATEFIRST;
SET DATEFIRST 7;
DECLARE @iFS int;
DECLARE @VarCursor CURSOR;
SET @VarCursor = CURSOR FAST_FORWARD
FOR
SELECT
ContractID
,dt
,dowChar
,dowInt
FROM #Src AS S
;
OPEN @VarCursor;
DECLARE @CurrContractID bigint = 0;
DECLARE @Currdt date;
DECLARE @CurrdowChar char(3);
DECLARE @CurrdowInt int;
DECLARE @VarCreateNewInterval bit = 0;
DECLARE @VarTempDT date;
DECLARE @VarTempdowInt int;
DECLARE @LastContractID bigint = 0;
DECLARE @LastStartDT date;
DECLARE @LastEndDT date;
DECLARE @LastDayCount int = 0;
DECLARE @LastWeekDays varchar(255);
DECLARE @LastMonCount int;
DECLARE @LastTueCount int;
DECLARE @LastWedCount int;
DECLARE @LastThuCount int;
DECLARE @LastFriCount int;
DECLARE @LastSatCount int;
DECLARE @LastSunCount int;
FETCH NEXT FROM @VarCursor INTO @CurrContractID, @Currdt, @CurrdowChar, @CurrdowInt;
SET @iFS = @@FETCH_STATUS;
IF @iFS = 0
BEGIN
SET @LastContractID = @CurrContractID;
SET @LastStartDT = @Currdt;
SET @LastEndDT = @Currdt;
SET @LastDayCount = 1;
SET @LastMonCount = 0;
SET @LastTueCount = 0;
SET @LastWedCount = 0;
SET @LastThuCount = 0;
SET @LastFriCount = 0;
SET @LastSatCount = 0;
SET @LastSunCount = 0;
IF @CurrdowInt = 1 SET @LastSunCount = @LastSunCount + 1;
IF @CurrdowInt = 2 SET @LastMonCount = @LastMonCount + 1;
IF @CurrdowInt = 3 SET @LastTueCount = @LastTueCount + 1;
IF @CurrdowInt = 4 SET @LastWedCount = @LastWedCount + 1;
IF @CurrdowInt = 5 SET @LastThuCount = @LastThuCount + 1;
IF @CurrdowInt = 6 SET @LastFriCount = @LastFriCount + 1;
IF @CurrdowInt = 7 SET @LastSatCount = @LastSatCount + 1;
END;
WHILE @iFS = 0
BEGIN
SET @VarCreateNewInterval = 0;
-- Contract changes -> start new interval
IF @LastContractID <> @CurrContractID
BEGIN
SET @VarCreateNewInterval = 1;
END;
IF @VarCreateNewInterval = 0
BEGIN
-- check days of week
-- are we still within the first week of the interval?
IF DATEDIFF(day, @LastStartDT, @Currdt) > 6
BEGIN
-- we are beyond the first week, check day of the week
-- have we seen @CurrdowInt before?
-- we should start a new interval if this is the new day of the week that didn't exist in the first week
IF @CurrdowInt = 1 AND @LastSunCount = 0 SET @VarCreateNewInterval = 1;
IF @CurrdowInt = 2 AND @LastMonCount = 0 SET @VarCreateNewInterval = 1;
IF @CurrdowInt = 3 AND @LastTueCount = 0 SET @VarCreateNewInterval = 1;
IF @CurrdowInt = 4 AND @LastWedCount = 0 SET @VarCreateNewInterval = 1;
IF @CurrdowInt = 5 AND @LastThuCount = 0 SET @VarCreateNewInterval = 1;
IF @CurrdowInt = 6 AND @LastFriCount = 0 SET @VarCreateNewInterval = 1;
IF @CurrdowInt = 7 AND @LastSatCount = 0 SET @VarCreateNewInterval = 1;
IF @VarCreateNewInterval = 0
BEGIN
-- check the gap between current day and last day of the interval
-- if the gap between current day and last day of the interval
-- contains a day of the week that was included in the interval before,
-- we should create new interval
SET @VarTempDT = DATEADD(day, 1, @LastEndDT);
WHILE @VarTempDT < @Currdt
BEGIN
SET @VarTempdowInt = DATEPART(WEEKDAY, @VarTempDT);
IF @VarTempdowInt = 1 AND @LastSunCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
IF @VarTempdowInt = 2 AND @LastMonCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
IF @VarTempdowInt = 3 AND @LastTueCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
IF @VarTempdowInt = 4 AND @LastWedCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
IF @VarTempdowInt = 5 AND @LastThuCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
IF @VarTempdowInt = 6 AND @LastFriCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
IF @VarTempdowInt = 7 AND @LastSatCount > 0 BEGIN SET @VarCreateNewInterval = 1; BREAK; END;
SET @VarTempDT = DATEADD(day, 1, @VarTempDT);
END;
END;
END;
-- else
-- we are still within the first week, so we can add this day to the interval
END;
IF @VarCreateNewInterval = 1
BEGIN
-- save the new interval into the final table
SET @LastWeekDays = '';
IF @LastSunCount > 0 SET @LastWeekDays = @LastWeekDays + 'Sun,';
IF @LastMonCount > 0 SET @LastWeekDays = @LastWeekDays + 'Mon,';
IF @LastTueCount > 0 SET @LastWeekDays = @LastWeekDays + 'Tue,';
IF @LastWedCount > 0 SET @LastWeekDays = @LastWeekDays + 'Wed,';
IF @LastThuCount > 0 SET @LastWeekDays = @LastWeekDays + 'Thu,';
IF @LastFriCount > 0 SET @LastWeekDays = @LastWeekDays + 'Fri,';
IF @LastSatCount > 0 SET @LastWeekDays = @LastWeekDays + 'Sat,';
INSERT INTO #Dst_V2
(ContractID
,StartDT
,EndDT
,DayCount
,WeekDays)
VALUES
(@LastContractID
,@LastStartDT
,@LastEndDT
,@LastDayCount
,@LastWeekDays);
-- init the new interval
SET @LastContractID = @CurrContractID;
SET @LastStartDT = @Currdt;
SET @LastEndDT = @Currdt;
SET @LastDayCount = 1;
SET @LastMonCount = 0;
SET @LastTueCount = 0;
SET @LastWedCount = 0;
SET @LastThuCount = 0;
SET @LastFriCount = 0;
SET @LastSatCount = 0;
SET @LastSunCount = 0;
IF @CurrdowInt = 1 SET @LastSunCount = @LastSunCount + 1;
IF @CurrdowInt = 2 SET @LastMonCount = @LastMonCount + 1;
IF @CurrdowInt = 3 SET @LastTueCount = @LastTueCount + 1;
IF @CurrdowInt = 4 SET @LastWedCount = @LastWedCount + 1;
IF @CurrdowInt = 5 SET @LastThuCount = @LastThuCount + 1;
IF @CurrdowInt = 6 SET @LastFriCount = @LastFriCount + 1;
IF @CurrdowInt = 7 SET @LastSatCount = @LastSatCount + 1;
END ELSE BEGIN
-- update last interval
SET @LastEndDT = @Currdt;
SET @LastDayCount = @LastDayCount + 1;
IF @CurrdowInt = 1 SET @LastSunCount = @LastSunCount + 1;
IF @CurrdowInt = 2 SET @LastMonCount = @LastMonCount + 1;
IF @CurrdowInt = 3 SET @LastTueCount = @LastTueCount + 1;
IF @CurrdowInt = 4 SET @LastWedCount = @LastWedCount + 1;
IF @CurrdowInt = 5 SET @LastThuCount = @LastThuCount + 1;
IF @CurrdowInt = 6 SET @LastFriCount = @LastFriCount + 1;
IF @CurrdowInt = 7 SET @LastSatCount = @LastSatCount + 1;
END;
FETCH NEXT FROM @VarCursor INTO @CurrContractID, @Currdt, @CurrdowChar, @CurrdowInt;
SET @iFS = @@FETCH_STATUS;
END;
-- save the last interval into the final table
IF @LastDayCount > 0
BEGIN
SET @LastWeekDays = '';
IF @LastSunCount > 0 SET @LastWeekDays = @LastWeekDays + 'Sun,';
IF @LastMonCount > 0 SET @LastWeekDays = @LastWeekDays + 'Mon,';
IF @LastTueCount > 0 SET @LastWeekDays = @LastWeekDays + 'Tue,';
IF @LastWedCount > 0 SET @LastWeekDays = @LastWeekDays + 'Wed,';
IF @LastThuCount > 0 SET @LastWeekDays = @LastWeekDays + 'Thu,';
IF @LastFriCount > 0 SET @LastWeekDays = @LastWeekDays + 'Fri,';
IF @LastSatCount > 0 SET @LastWeekDays = @LastWeekDays + 'Sat,';
INSERT INTO #Dst_V2
(ContractID
,StartDT
,EndDT
,DayCount
,WeekDays)
VALUES
(@LastContractID
,@LastStartDT
,@LastEndDT
,@LastDayCount
,@LastWeekDays);
END;
CLOSE @VarCursor;
DEALLOCATE @VarCursor;
SET DATEFIRST @VarOldDateFirst;
DROP TABLE #Dst_V2;
답변
나는 Vladimir의 커서 솔루션 이 너무 느리다는 것에 약간 놀랐습니다 . 그래서 나는 그 버전을 최적화하려고 시도했습니다. 커서 사용이 매우 느리다는 것을 확인했습니다.
그러나 행 집합을 처리하는 동안 변수에 추가하여 SQL Server에서 문서화되지 않은 기능을 사용하는 대신,이 논리의 단순화 된 버전을 만들어서 최적의 결과를 생성하고 커서와 원래 솔루션보다 훨씬 빠르게 실행할 수있었습니다. . 따라서 귀하의 책임하에 사용하되 관심이있는 경우 솔루션을 제시하겠습니다. WHILE
루프를 반복 할 때마다 다음 행 번호를 찾기 위해 1에서 최대 행 번호까지 루프 를 사용하도록 솔루션을 업데이트 할 수도 있습니다 . 이것은 완전히 문서화되고 신뢰할 수있는 기능을 고수하지만 WHILE
루프가 허용되지 않는 문제에 대해 (인위적인) 명시된 제약 조건을 위반합니다 .
SQL 2014 사용이 허용 된 경우 행 번호를 반복하고 메모리 최적화 테이블의 각 행 번호에 액세스 하는 기본적으로 컴파일 된 저장 프로 시저 가보다 빠르게 실행되는 동일한 논리를 구현할 수 있습니다.
시험 데이터 세트를 약 50 만 행으로 확장하는 것을 포함 하여 전체 솔루션이 있습니다. 새로운 솔루션은 약 3 초 안에 완료되며 제 의견으로는 이전에 제공 한 솔루션보다 훨씬 간결하고 읽기 쉽습니다. 여기에 관련된 세 단계를 설명하겠습니다.
1 단계 : 전처리
먼저 데이터를 처리하는 순서대로 데이터 세트에 행 번호를 추가합니다. 그렇게하는 동안 우리는 또한 각 dowInt를 2의 거듭 제곱으로 변환하여 비트 맵을 사용하여 주어진 그룹에서 어떤 요일이 관찰되었는지 나타낼 수 있습니다
IF OBJECT_ID('tempdb..#srcWithRn') IS NOT NULL
DROP TABLE #srcWithRn
GO
SELECT rn = IDENTITY(INT, 1, 1), ContractId, dt, dowInt,
POWER(2, dowInt) AS dowPower, dowChar
INTO #srcWithRn
FROM #src
ORDER BY ContractId, dt
GO
ALTER TABLE #srcWithRn
ADD PRIMARY KEY (rn)
GO
2 단계 : 새로운 그룹화를 식별하기 위해 계약일 반복
다음으로 행 번호 순으로 데이터를 반복합니다. 새 그룹의 경계를 구성하는 행 번호 목록 만 계산 한 다음 해당 행 번호를 테이블로 출력합니다.
DECLARE @ContractId INT, @RnList VARCHAR(MAX), @NewGrouping BIT = 0, @DowBitmap INT = 0, @startDt DATE
SELECT TOP 1 @ContractId = ContractId, @startDt = dt, @RnList = ',' + CONVERT(VARCHAR(MAX), rn), @DowBitmap = DowPower
FROM #srcWithRn
WHERE rn = 1
SELECT
-- New grouping if new contract, or if we're observing a new day that we did
-- not observe within the first 7 days of the grouping
@NewGrouping = CASE
WHEN ContractId <> @ContractId THEN 1
WHEN DATEDIFF(DAY, @startDt, dt) > 6
AND @DowBitmap & dowPower <> dowPower THEN 1
ELSE 0
END,
@ContractId = ContractId,
-- If this is a newly observed day in an existing grouping, add it to the bitmap
@DowBitmap = CASE WHEN @NewGrouping = 0 THEN @DowBitmap | DowPower ELSE DowPower END,
-- If this is a new grouping, reset the start date of the grouping
@startDt = CASE WHEN @NewGrouping = 0 THEN @startDt ELSE dt END,
-- If this is a new grouping, add this rn to the list of row numbers that delineate the boundary of a new grouping
@RnList = CASE WHEN @NewGrouping = 0 THEN @RnList ELSE @RnList + ',' + CONVERT(VARCHAR(MAX), rn) END
FROM #srcWithRn
WHERE rn >= 2
ORDER BY rn
OPTION (MAXDOP 1)
-- Split the list of grouping boundaries into a table
IF OBJECT_ID('tempdb..#newGroupingRns') IS NOT NULL
DROP TABLE #newGroupingRns
SELECT splitListId AS rn
INTO #newGroupingRns
FROM dbo.f_delimitedIntListSplitter(SUBSTRING(@RnList, 2, 1000000000), DEFAULT)
GO
ALTER TABLE #newGroupingRns
ADD PRIMARY KEY (rn)
GO
3 단계 : 각 그룹 경계의 행 번호를 기반으로 최종 결과 계산
그런 다음 위 루프에서 식별 된 경계를 사용하여 각 그룹에 해당하는 모든 날짜를 집계하여 최종 그룹을 계산합니다.
IF OBJECT_ID('tempdb..#finalGroupings') IS NOT NULL
DROP TABLE #finalGroupings
GO
SELECT MIN(s.ContractId) AS ContractId,
MIN(dt) AS StartDT,
MAX(dt) AS EndDT,
COUNT(*) AS DayCount,
CASE WHEN MAX(CASE WHEN dowChar = 'Sun' THEN 1 ELSE 0 END) = 1 THEN 'Sun,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Mon' THEN 1 ELSE 0 END) = 1 THEN 'Mon,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Tue' THEN 1 ELSE 0 END) = 1 THEN 'Tue,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Wed' THEN 1 ELSE 0 END) = 1 THEN 'Wed,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Thu' THEN 1 ELSE 0 END) = 1 THEN 'Thu,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Fri' THEN 1 ELSE 0 END) = 1 THEN 'Fri,' ELSE '' END +
CASE WHEN MAX(CASE WHEN dowChar = 'Sat' THEN 1 ELSE 0 END) = 1 THEN 'Sat,' ELSE '' END AS WeekDays
INTO #finalGroupings
FROM #srcWithRn s
CROSS APPLY (
-- For any row, its grouping is the largest boundary row number that occurs at or before this row
SELECT TOP 1 rn AS groupingRn
FROM #newGroupingRns grp
WHERE grp.rn <= s.rn
ORDER BY grp.rn DESC
) g
GROUP BY g.groupingRn
ORDER BY g.groupingRn
GO
답변
토론은 코드를 따릅니다.
declare @Helper table(
rn tinyint,
dowInt tinyint,
dowChar char(3));
insert @Helper
values ( 1,1,'Sun'),
( 2,2,'Mon'),
( 3,3,'Tue'),
( 4,4,'Wed'),
( 5,5,'Thu'),
( 6,6,'Fri'),
( 7,7,'Sat'),
( 8,1,'Sun'),
( 9,2,'Mon'),
(10,3,'Tue'),
(11,4,'Wed'),
(12,5,'Thu'),
(13,6,'Fri'),
(14,7,'Sat');
with MissingDays as
(
select
h1.rn as rn1,
h1.dowChar as StartDay,
h2.rn as rn2,
h2.dowInt as FollowingDayInt,
h2.dowChar as FollowingDayChar
from @Helper as h1
inner join @Helper as h2
on h2.rn > h1.rn
where h1.rn < 8
and h2.rn < h1.rn + 8
)
,Numbered as
(
select
a.*,
ROW_NUMBER() over (partition by a.ContractID order by a.dt) as rn
from #Src as a
)
,Incremented as
(
select
b.*,
convert(varchar(max), b.dowChar)+',' as WeekDays,
b.dt as IntervalStart
from Numbered as b
where b.rn = 1
union all
select
c.*,
case
when
(DATEDIFF(day, d.IntervalStart, c.dt) > 6) -- interval goes beyond 7 days
and (
(d.WeekDays not like '%'+c.dowChar+'%') -- the new week day has not been seen before
or
(DATEDIFF(day, d.dt, c.dt) > 7)
or
(
(DATEDIFF(day, d.dt, c.dt) > 1)
and
(
exists( select
e.FollowingDayChar
from MissingDays as e
where e.StartDay = d.dowChar
and rn2 < (select f.rn2 from MissingDays as f
where f.StartDay = d.dowChar
and f.FollowingDayInt = c.dowInt)
and d.WeekDays like '%'+e.FollowingDayChar+'%'
)
)
)
)
then convert(varchar(max),c.dowChar)+','
else
case
when d.WeekDays like '%'+c.dowChar+'%'
then d.WeekDays
else d.WeekDays+convert(varchar(max),c.dowChar)+','
end
end,
case
when
(DATEDIFF(day, d.IntervalStart, c.dt) > 6) -- interval goes beyond 7 days
and (
(d.WeekDays not like '%'+c.dowChar+'%') -- the new week day has not been seen before
or
(DATEDIFF(day, d.dt, c.dt) > 7) -- there is a one week gap
or
(
(DATEDIFF(day, d.dt, c.dt) > 1) -- there is a gap..
and
(
exists( select -- .. and the omitted days are in the preceeding interval
e.FollowingDayChar
from MissingDays as e
where e.StartDay = d.dowChar
and rn2 < (select f.rn2 from MissingDays as f
where f.StartDay = d.dowChar
and f.FollowingDayInt = c.dowInt)
and d.WeekDays like '%'+e.FollowingDayChar+'%'
)
)
)
)
then c.dt
else d.IntervalStart
end
from Numbered as c
inner join Incremented as d
on d.ContractID = c.ContractID
and d.rn = c.rn - 1
)
select
g.ContractID,
g.IntervalStart as StartDT,
MAX(g.dt) as EndDT,
COUNT(*) as DayCount,
MAX(g.WeekDays) as WeekDays
from Incremented as g
group by
g.ContractID,
g.IntervalStart
order by
ContractID,
StartDT;
@Helper
이 규칙에 대처하는 것입니다.
현재 요일과 마지막 요일 사이의 간격에 이전 간격에 포함 된 요일이 포함되어 있으면 새 간격을 만들어야합니다
주어진 날 사이에 요일 이름을 일 번호 순서로 나열 할 수 있습니다. 새 간격을 시작할지 여부를 결정할 때 사용됩니다. 주말에 코드를 쉽게 묶을 수 있도록 2 주 분량의 값으로 채 웁니다.
이것을 구현하는 더 깨끗한 방법이 있습니다. 완전한 “날짜”테이블은 하나입니다. 일 수와 모듈로 산술도 영리한 방법이 있습니다.
CTE MissingDays
는 주어진 2 일 사이의 요일 이름 목록을 생성하는 것입니다. 재귀 CTE (다음)는 집계, TOP () 또는 다른 연산자를 허용하지 않기 때문에이 어수선한 방식으로 처리됩니다. 이것은 우아하지 않지만 작동합니다.
CTE Numbered
는 데이터에 알려진 갭이없는 시퀀스를 적용하는 것입니다. 나중에 많은 비교를 피합니다.
CTE Incremented
는 행동이 일어나는 곳입니다. 본질적으로 재귀 CTE를 사용하여 데이터를 단계별로 실행하고 규칙을 시행합니다. Numbered
(위) 에서 생성 된 행 번호 는 재귀 처리를 구동하는 데 사용됩니다.
재귀 CTE의 시드는 단순히 각 ContractID의 첫 번째 날짜를 가져오고 새 간격이 필요한지 결정하는 데 사용될 값을 초기화합니다.
새 간격을 시작할지 여부를 결정하려면 현재 간격의 시작 날짜, 요일 목록 및 달력 날짜의 간격 길이가 필요합니다. 이들은 결정에 따라 재설정되거나 이월 될 수 있습니다. 따라서 재귀 부분은 두 개 이상의 열 값에 대해 새 간격을 시작할지 여부를 결정해야하기 때문에 장황하고 약간 반복적입니다.
열에 대한 결정 로직 WeekDays
과 IntervalStart
같은 결정 로직을 가져야한다 – 그것은 그들 사이에 컷 앤 붙여 넣을 수 있습니다. 새 간격을 시작하는 논리가 변경되면 변경해야 할 코드입니다. 그러므로 이상적으로 추상화 될 것이다. 재귀 CTE에서이 작업을 수행하는 것은 어려울 수 있습니다.
이 EXISTS()
절은 재귀 CTE에서 집계 함수를 사용할 수 없다는 결과입니다. 간격에 해당하는 날이 현재 간격에 있는지 확인하면됩니다.
논리 절의 중첩에 대한 마술은 없습니다. 다른 형태 나 중첩 된 CASE를 사용하는 것이 더 명확하다면, 이런 식으로 유지할 이유가 없다고 말합니다.
마지막 SELECT
으로 출력을 원하는 형식으로 제공합니다.
PK를 켜는 Src.ID
것은이 방법에 유용하지 않습니다. 클러스터 된 인덱스가 (ContractID,dt)
좋을 것이라고 생각합니다.
거친 가장자리가 몇 개 있습니다. 날짜는 dow 순서로 반환되지 않지만 달력 순서에서는 소스 데이터에 나타납니다. @Helper와 관련된 모든 것은 klunky이며 부드럽게 할 수 있습니다. 나는 하루에 1 비트를 사용하고 대신 이진 함수를 사용하는 아이디어를 좋아합니다 LIKE
. 적절한 보조 인덱스를 사용하여 보조 CTE 중 일부를 임시 테이블로 분리하면 의심 할 여지없이 도움이 될 것입니다.
이에 대한 문제 중 하나는 “주”가 표준 달력과 일치하지 않고 데이터에 의해 구동되고 새로운 간격이 시작되어야하는 것으로 결정되면 재설정된다는 것입니다. “주”또는 적어도 간격은 하루부터 전체 데이터 세트에 걸쳐있을 수 있습니다.
이해를 돕기 위해 다음은 다양한 변경 후 Geoff의 샘플 데이터에 대한 예상 비용입니다 (감사합니다!).
estimated cost
My submission as is w/ CTEs, Geoff's data: 791682
Geoff's data, cluster key on (ContractID, dt): 21156.2
Real table for MissingDays: 21156.2
Numbered as table UCI=(ContractID, rn): 16.6115 26s elapsed.
UCI=(rn, ContractID): 41.9845 26s elapsed.
MissingDays as refactored to simple lookup 16.6477 22s elapsed.
Weekdays as varchar(30) 13.4013 30s elapsed.
예상 행 수와 실제 행 수는 크게 다릅니다.
재귀 CTE의 결과로 계획에 테이블 스 푸프가 있습니다. 대부분의 작업은 다음과 같은 작업 테이블에서 이루어집니다.
Table 'Worktable'. Scan count 2, logical reads 4 196 269, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MissingDays'. Scan count 464 116, logical reads 928 232, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Numbered'. Scan count 484 122, logical reads 1 475 467, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
재귀가 구현 된 방식이라고 생각합니다!