18

Muszę przekonwertować dane między dwoma systemami.

Pierwszy system przechowuje harmonogramy jako zwykłą listę dat. Każda data uwzględniona w harmonogramie to jeden wiersz. W sekwencji dat mogą występować różne luki (weekendy, święta i dłuższe przerwy, niektóre dni tygodnia mogą zostać wyłączone z harmonogramu). Nie może być żadnych luk, nawet weekendy mogą być uwzględnione. Harmonogram może trwać do 2 lat. Zwykle trwa to kilka tygodni.

Oto prosty przykład harmonogramu obejmującego dwa tygodnie z wyłączeniem weekendów (w skrypcie poniżej znajdują się bardziej skomplikowane przykłady):

+----+------------+------------+---------+--------+
| 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 |
+----+------------+------------+---------+--------+

IDjest unikalny, ale niekoniecznie sekwencyjny (jest to klucz podstawowy). Daty są unikalne w ramach każdej umowy (istnieje niepowtarzalny indeks (ContractID, dt)).

Drugi system przechowuje harmonogramy jako interwały z listą dni tygodnia, które są częścią harmonogramu. Każdy interwał jest definiowany przez daty rozpoczęcia i zakończenia (włącznie) oraz listę dni tygodnia, które są uwzględnione w harmonogramie. W tym formacie możesz efektywnie definiować powtarzalne cotygodniowe wzorce, takie jak pon.-śr., Ale staje się to bolesne, gdy wzorzec jest zakłócany, na przykład w święta.

Oto jak będzie wyglądał prosty powyższy przykład:

+------------+------------+------------+----------+----------------------+
| ContractID |  StartDT   |   EndDT    | DayCount |       WeekDays       |
+------------+------------+------------+----------+----------------------+
|          1 | 2016-05-02 | 2016-05-13 |       10 | Mon,Tue,Wed,Thu,Fri, |
+------------+------------+------------+----------+----------------------+

[StartDT;EndDT] odstępy czasu należące do tej samej umowy nie powinny się pokrywać.

Muszę przekonwertować dane z pierwszego systemu do formatu używanego przez drugi system. W tej chwili rozwiązuję to po stronie klienta w C # dla pojedynczego danego kontraktu, ale chciałbym to zrobić w języku T-SQL po stronie serwera w celu masowego przetwarzania i eksportu / importu między serwerami. Najprawdopodobniej można to zrobić przy użyciu CLR UDF, ale na tym etapie nie mogę używać SQLCLR.

Wyzwanie polega na tym, aby lista interwałów była jak najkrótsza i przyjazna dla człowieka.

Na przykład ten harmonogram:

+-----+------------+------------+---------+--------+
| 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 |
+-----+------------+------------+---------+--------+

powinno stać się tak:

+------------+------------+------------+----------+----------------------+
| ContractID |  StartDT   |   EndDT    | DayCount |       WeekDays       |
+------------+------------+------------+----------+----------------------+
|          2 | 2016-05-05 | 2016-05-17 |        9 | Mon,Tue,Wed,Thu,Fri, |
+------------+------------+------------+----------+----------------------+

,nie to:

+------------+------------+------------+----------+----------------------+
| 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,             |
+------------+------------+------------+----------+----------------------+

Próbowałem zastosować gaps-and-islandspodejście do tego problemu. Próbowałem to zrobić w dwóch krokach. W pierwszym przejściu znajduję wyspy prostych kolejnych dni, tj. Koniec wyspy to jakakolwiek przerwa w sekwencji dni, czy to weekend, święto państwowe czy coś innego. Dla każdej takiej znalezionej wyspy tworzę listę oddzielonych przecinkami WeekDays. W drugim przejeździe grupa znalazła wyspy dalej, patrząc na lukę w sekwencji numerów tygodni lub zmianę w WeekDays.

Dzięki takiemu podejściu każdy częściowy tydzień kończy się jako dodatkowy interwał, jak pokazano powyżej, ponieważ chociaż liczby tygodni są następujące po sobie, WeekDayszmiana. Poza tym w ciągu tygodnia mogą występować regularne luki (patrz ContractID=3przykładowe dane, które mają dane tylko dla Mon,Wed,Fri,), a takie podejście wygenerowałoby osobne przedziały dla każdego dnia w takim harmonogramie. Z drugiej strony generuje jeden interwał, jeśli harmonogram nie ma żadnych luk (patrz ContractID=7przykładowe dane, które obejmują weekendy), w takim przypadku nie ma znaczenia, czy tydzień początkowy czy końcowy jest częściowy.

Zobacz inne przykłady w skrypcie poniżej, aby uzyskać lepszy obraz tego, czego szukam. Widać, że dość często weekendy są wykluczane, ale inne dni tygodnia mogą być również wykluczone. W przykładzie 3, tylko Mon, Wedi Frinależą do rozkładu. Poza tym można uwzględnić weekendy, jak w przykładzie 7. Rozwiązanie powinno traktować wszystkie dni tygodnia jednakowo. Każdy dzień tygodnia może zostać włączony lub wyłączony z harmonogramu.

Aby sprawdzić, czy wygenerowana lista przedziałów prawidłowo opisuje dany harmonogram, możesz użyć następującego pseudokodu:

zapętlić wszystkie przedziały
dla każdej pętli interwału przez wszystkie daty kalendarzowe między datami początkową i końcową (włącznie).
dla każdej daty sprawdź, czy jej dzień tygodnia jest wymieniony w WeekDays. Jeśli tak, data ta jest uwzględniona w harmonogramie.

Mamy nadzieję, że wyjaśnia to, w jakich przypadkach należy utworzyć nowy interwał. W przykładach 4 i 5 jeden poniedziałek ( 2016-05-09) jest usuwany ze środka harmonogramu i taki harmonogram nie może być reprezentowany przez pojedynczy przedział. W przykładzie 6 w harmonogramie jest długa przerwa, dlatego potrzebne są dwa odstępy.

Odstępy reprezentują tygodniowe wzorce w harmonogramie, a gdy wzorzec jest zakłócany / zmieniany, należy dodać nowy odstęp. W przykładzie 11 pierwsze trzy tygodnie mają wzór Tue, a następnie ten wzór zmienia się na Thu. W rezultacie potrzebujemy dwóch przedziałów, aby opisać taki harmonogram.

Obecnie używam SQL Server 2008, więc rozwiązanie powinno działać w tej wersji. Jeśli rozwiązanie dla SQL Server 2008 można uprościć / ulepszyć za pomocą funkcji z późniejszych wersji, jest to premia, proszę również to pokazać.

Mam Calendartabelę (listę dat) i Numberstabelę (listę liczb całkowitych rozpoczynających się od 1), więc w razie potrzeby można z nich korzystać. Można również tworzyć tabele tymczasowe i mieć kilka zapytań przetwarzających dane w kilku etapach. Liczba etapów w algorytmie musi być jednak ustalona, kursory i wyraźne WHILEpętle nie są w porządku.

Skrypt dla przykładowych danych i oczekiwanych wyników

-- @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;

Porównanie odpowiedzi

Prawdziwy stół @Srcma403,555 wiersze z 15,857wyraźnymi ContractIDs. Wszystkie odpowiedzi dają prawidłowe wyniki (przynajmniej dla moich danych) i wszystkie są dość szybkie, ale różnią się optymalizacją. Im mniej wygenerowanych interwałów, tym lepiej. Uwzględniłem czasy działania tylko dla ciekawości. Głównym celem jest poprawny i optymalny wynik, a nie szybkość (chyba że zajmuje to zbyt dużo czasu - zatrzymałem nierekurencyjne zapytanie Ziggy Crueltyfree Zeitgeister po 10 minutach).

+--------------------------------------------------------+-----------+---------+
|                         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                                                 |           |         |
+--------------------------------------------------------+-----------+---------+

sql-server sql-server-2008 gaps-and-islands

— Vladimir Baranov
źródło

Czy (11,'2016-05-03', '2016-05-17', 3, 'Tue,'), (11,'2016-05-19', '2016-06-02', 3, 'Thu,');w @Dst nie powinien znajdować się jeden wiersz Tue, Thu,?

— Kin Shah,

@Kin, przykład 11 musi mieć (co najmniej) dwa przedziały (dwa rzędy @Dst). Pierwsze dwa tygodnie harmonogramu mają tylko Tue, więc nie możesz mieć WeekDays=Tue,Thu,tych tygodni. Ostatnie dwa tygodnie harmonogramu mają tylko Thu, więc znowu nie możesz mieć WeekDays=Tue,Thu,na te tygodnie. Nieoptymalne rozwiązanie byłoby trzy rzędy: tylko Tuedla pierwszych dwóch tygodni, a następnie Tue,Thu,do trzeciego tygodnia, który ma zarówno Tuea Thu, a potem po prostu Thuw ciągu ostatnich dwóch tygodni.

— Vladimir Baranov,

1

Czy możesz wyjaśnić algorytm, według którego kontrakt 11 jest „optymalnie” podzielony na dwa przedziały. Czy osiągnąłeś to w aplikacji C #? W jaki sposób?

— Michael Green

@MichaelGreen, przepraszam, że nie mogłem odpowiedzieć wcześniej. Tak, kod C # dzieli kontrakt 11 na dwa przedziały. Zgrubny algorytm: przeglądam zaplanowane daty, jeden po drugim, notuję, które dni tygodnia napotkałem do tej pory od początku interwału i ustalam, czy powinienem zacząć nowy interwał: czy ContractIDzmiany, czy interwał przekracza 7 dni, a nowy dzień tygodnia nie był wcześniej widziany, jeśli na liście zaplanowanych dni jest luka.

— Vladimir Baranov

@MichaelGreen, przekonwertowałem mój kod C # na algorytm oparty na kursorach, aby zobaczyć, jak wypada w porównaniu z innymi rozwiązaniami na rzeczywistych danych. Dodałem kod źródłowy do mojej odpowiedzi i wyników do tabeli podsumowań w pytaniu.

— Vladimir Baranov

6

Ten używa rekurencyjnego CTE. Jego wynik jest identyczny z przykładem w pytaniu . Wymyślenie tego było koszmarem ... Kod zawiera komentarze ułatwiające jego skomplikowaną logikę.

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)

Kolejna strategia

Ten powinien być znacznie szybszy niż poprzedni, ponieważ nie opiera się na wolno ograniczonej rekurencyjnej CTE w SQL Server 2008, chociaż implementuje mniej więcej tę samą strategię.

Tam jest WHILE pętla (nie mogłem wymyślić sposobu jej uniknięcia), ale dotyczy ona mniejszej liczby iteracji (najwyższa liczba sekwencji (minus jedna) w danym kontrakcie).

Jest to prosta strategia i może być stosowana do sekwencji krótszych lub dłuższych niż tydzień (zastępując dowolne wystąpienie stałej 7 dla dowolnej innej liczby i dowBitobliczonej z MODULUSA x DayNozamiast DATEPART(wk)) i do 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

— Ziggy Crueltyfree Zeitgeister
źródło

@VladimirBaranov Dodałem nową strategię, która powinna być znacznie szybsza. Daj mi znać, jak ocenia się na podstawie twoich prawdziwych danych!

— Ziggy Crueltyfree Zeitgeister

2

@ZiggyCrueltyfreeZeitgeister, sprawdziłem twoje ostatnie rozwiązanie i dodałem je do listy wszystkich odpowiedzi w pytaniu. Daje prawidłowe wyniki i taką samą liczbę interwałów jak rekurencyjne CTE, a jego prędkość jest również bardzo bliska. Jak powiedziałem, prędkość nie jest krytyczna, dopóki jest rozsądna. 1 sekunda lub 10 sekund tak naprawdę nie ma dla mnie znaczenia.

— Vladimir Baranov

Inne odpowiedzi są również świetne i przydatne, i chciałbym przyznać nagrodę za więcej niż jedną odpowiedź. Wybrałem tę odpowiedź, ponieważ w momencie, gdy zaczynałem nagrodę, nie myślałem o rekurencyjnym CTE i ta odpowiedź była pierwszą, która ją zasugerowała i ma działające rozwiązanie. Ściśle mówiąc, rekurencyjne CTE nie jest rozwiązaniem opartym na zestawie, ale daje optymalne wyniki i jest dość szybkie. Odpowiedź przez @GeoffPatterson jest wielki, ale daje mniej optymalnych rezultatów i, szczerze mówiąc, zbyt skomplikowane.

— Vladimir Baranov

5

Nie do końca to, czego szukasz, ale być może zainteresuje Cię.

Kwerenda tworzy tygodnie z ciągiem oddzielonym przecinkami dla dni używanych w każdym tygodniu. Następnie znajduje wyspy kolejnych tygodni, w których stosuje się ten sam wzór 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);

Wynik:

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 = 2pokazuje różnicę wyniku w porównaniu z tym, czego chcesz. Pierwszy i ostatni tydzień będą traktowane jako osobne okresy, ponieważ WeekDayssą różne.

— Mikael Eriksson
źródło

Miałem ten pomysł, ale nie miałem okazji go wypróbować. Dziękujemy za podanie działającego zapytania. Podoba mi się, jak daje bardziej uporządkowany wynik. Przy grupowaniu danych w tygodniach wadą jest mniejsza elastyczność (w prostym codziennym podejściu do luk i wysp przykłady 7 i 8 byłyby zwinięte w jednym przedziale), ale jest to jednocześnie jasna strona - zmniejszamy złożoność problem. Zatem największym problemem związanym z tym podejściem są częściowe tygodnie na początku i na końcu harmonogramu. Takie częściowe tygodnie generują dodatkowy odstęp ...

— Vladimir Baranov

Czy możesz wymyślić sposób na dołączenie / grupowanie / scalenie tych częściowych tygodni w głównym harmonogramie? Na tym etapie mam tylko bardzo niejasny pomysł. Jeśli znajdziemy sposób na prawidłowe scalenie częściowych tygodni, wynik końcowy byłby bardzo bliski optymalnej.

— Vladimir Baranov

@VladimirBaranov Nie jestem pewien, jak by to zrobić. Zaktualizuję odpowiedź, jeśli coś mi przyjdzie na myśl.

— Mikael Eriksson,

Mój niejasny pomysł jest następujący: jest tylko 7 dni w tygodniu, podobnie WeekDaysjak liczba 7-bitowa. Tylko 128 kombinacji. Istnieje tylko 128 * 128 = 16384 możliwych par. Zbuduj tabelę temp ze wszystkimi możliwymi parami, a następnie wymyśl algorytm oparty na zestawie, który oznaczałby, które pary można scalić: wzorzec jednego tygodnia jest „pokryty” wzorcem z następnego tygodnia. Dołącz samodzielnie do bieżącego wyniku tygodniowego (ponieważ nie ma go LAGw 2008 r.) I użyj tej tabeli tymczasowej, aby zdecydować, które pary połączyć ... Nie jestem pewien, czy ten pomysł ma jakąkolwiek wartość.

— Vladimir Baranov

5

Skończyłem z podejściem, które daje optymalne rozwiązanie w tym przypadku i myślę, że ogólnie będzie dobrze. Rozwiązanie jest jednak dość długie, więc interesujące byłoby sprawdzenie, czy ktoś inny ma bardziej zwięzłe podejście.

Oto skrypt, który zawiera pełne rozwiązanie .

A oto zarys algorytmu:

Obróć zestaw danych, aby jeden tydzień reprezentował każdy tydzień
Obliczyć wyspy tygodni w każdej z nich ContractId
Scal wszystkie sąsiednie tygodnie, które mieszczą się w tym samym ContractIdi mają takie sameWeekDays
W przypadku pojedynczych tygodni (jeszcze nie połączonych), w których poprzednie zgrupowanie znajduje się na tej samej wyspie, a WeekDaystydzień z jednego tygodnia odpowiada wiodącemu podzbiorowi WeekDayspoprzedniego zgrupowania, połącz się z tym poprzednim zgrupowaniem
W przypadku każdego pojedynczego tygodnia (jeszcze nie połączonego), gdy następne zgrupowanie znajduje się na tej samej wyspie, a ten WeekDaysz jednego tygodnia odpowiada końcowemu podzbiorowi WeekDaysnastępnego zgrupowania, połącz się z następnym zgrupowaniem
W przypadku dowolnych dwóch sąsiednich tygodni na tej samej wyspie, na której żaden z nich nie został połączony, połącz je ze sobą, jeśli oba są częściowymi tygodniami, które można połączyć (np. „Pon, Wt, Śr, Czw” i „Śr, Czw, Sob”, )
W przypadku pozostałych pojedynczych tygodni (jeszcze nie połączonych), jeśli to możliwe, podziel tydzień na dwie części i połącz obie części, pierwszą część w poprzednią grupę na tej samej wyspie, a drugą część w następną grupę na tej samej wyspie

— Geoff Patterson
źródło

Dziękujemy, że dołożyłeś wszelkich starań, aby stworzyć działające rozwiązanie. Szczerze mówiąc, to trochę przytłaczające. Podejrzewałem, że połączenie częściowych tygodni nie będzie łatwe, ale nie spodziewałem się, że będzie to tak skomplikowane. Nadal mam nadzieję, że można to zrobić łatwiej, ale nie mam konkretnego pomysłu.

— Vladimir Baranov

Szybka kontrola potwierdza, że daje oczekiwany wynik dla przykładowych danych, co jest świetne, ale zauważyłem, że niektóre harmonogramy nie są obsługiwane w optymalny sposób. Najprostszy przykład: (1214,12,'2016-05-06', 'Fri', 6), (1225,12,'2016-05-09', 'Mon', 2),. Może być reprezentowany jako jeden przedział, ale twoje rozwiązanie daje dwa. Przyznaję, że tego przykładu nie było w przykładowych danych i nie ma on krytycznego znaczenia. Spróbuję uruchomić twoje rozwiązanie na prawdziwych danych.

— Vladimir Baranov

Doceniam twoją odpowiedź. W momencie, gdy zaczynałem nagrodę, nie myślałem o rekurencyjnym CTE, a Ziggy Crueltyfree Zeitgeister jako pierwszy zaproponował to i przedstawił działające rozwiązanie. Ściśle mówiąc, rekurencyjne CTE nie jest rozwiązaniem opartym na zestawie, ale daje optymalne wyniki, jest dość złożone i dość szybkie. Twoja odpowiedź jest oparta na zestawie, ale okazuje się zbyt skomplikowana, do tego stopnia, że nie jest praktyczna. Chciałbym móc podzielić nagrodę, ale niestety nie jest to dozwolone.

— Vladimir Baranov

@VladimirBaranov Nie ma problemu, nagroda jest w 100% do wykorzystania zgodnie z życzeniem. Powodem, dla którego lubię pytania o nagrodę, jest to, że osoba zadająca to pytanie jest zazwyczaj o wiele bardziej zaangażowana niż normalne pytanie. Nie przejmuj się zbytnio punktami. Całkowicie zgadzam się, że to rozwiązanie nie jest tym, którego użyłbym w moim kodzie produkcyjnym; była to eksploracja potencjalnego pomysłu, ale okazała się dość złożona.

— Geoff Patterson

3

Nie mogłem zrozumieć logiki grupowania tygodni z przerwami lub tygodni z weekendami (np. Kiedy są dwa kolejne tygodnie z weekendem, do którego tygodnia idzie weekend?).

Poniższe zapytanie generuje pożądane dane wyjściowe, z wyjątkiem tego, że grupuje tylko kolejne dni tygodnia i grupuje tygodnie od poniedziałku do soboty (zamiast od poniedziałku do niedzieli). Nie jest to dokładnie to, czego chcesz, ale może to dostarczyć wskazówek na temat innej strategii. Grupowanie dni pochodzi stąd . Użyte funkcje okienkowania powinny współpracować z SQLServer 2008, ale nie mam tej wersji do przetestowania, czy rzeczywiście działa.

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

Wynik

+------------+------------+------------+----------+-----------------------------------+
| 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                          |
+------------+------------+------------+----------+-----------------------------------+

— Ziggy Crueltyfree Zeitgeister
źródło

Dyskusja na temat tej odpowiedzi została przeniesiona na czat .

— Paul White przywraca Monikę

3

Dla kompletności, oto dwa przejścia gaps-and-islands podejście które wypróbowałem sam, zanim zadałem to pytanie.

Gdy testowałem to na rzeczywistych danych, znalazłem kilka przypadków, w których przynosiło to nieprawidłowe wyniki, i naprawiłem to.

Oto algorytm:

Generowanie wysp kolejnymi datami ( CTE_ContractDays, CTE_DailyRN,CTE_DailyIslands ) i obliczyć liczbę tygodni dla każdego rozpoczynającego i kończącego się dnia wyspie. Tutaj liczba tygodni jest obliczana przy założeniu, że poniedziałek jest pierwszym dniem tygodnia.
Jeśli harmonogram ma niesekwencyjne daty w tym samym tygodniu (jak w przykładzie 3), poprzedni etap utworzy kilka wierszy dla tego samego tygodnia. Grupuj wiersze, aby mieć tylko jeden wiersz na tydzień ( CTE_Weeks).
Dla każdego wiersza z poprzedniego etapu utwórz listę oddzieloną przecinkami dni tygodnia ( CTE_FirstResult).
Drugie przejście luk i wysp do grupowania kolejnych tygodni z tym samym WeekDays( CTE_SecondRN, CTE_Schedules).

Dobrze radzi sobie z przypadkami, gdy nie występują zakłócenia w cotygodniowych wzorcach (1, 7, 8, 10, 12). Dobrze radzi sobie z przypadkami, gdy wzór ma dni niesekwencyjne (3).

Ale niestety generuje dodatkowe odstępy dla częściowych tygodni (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
;

Wynik

+------------+------------+------------+----------+------------------------------+
| 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,         |
+------------+------------+------------+----------+------------------------------+

Rozwiązanie oparte na kursorze

Przekształciłem mój kod C # w algorytm oparty na kursorach, aby zobaczyć, jak się on ma do innych rozwiązań na rzeczywistych danych. Potwierdza, że jest znacznie wolniejszy niż inne podejścia oparte na zestawie lub rekurencyjne, ale generuje optymalny wynik.

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 Baranov
źródło

2

Byłem trochę zaskoczony, że rozwiązanie kursora Vladimira było tak wolne, więc próbowałem również zoptymalizować tę wersję. Potwierdziłem, że używanie kursora również było dla mnie bardzo wolne.

Jednak kosztem korzystania z nieudokumentowanej funkcjonalności w SQL Server przez dołączenie do zmiennej podczas przetwarzania zestawu wierszy byłem w stanie stworzyć uproszczoną wersję tej logiki, która daje optymalny wynik i działa znacznie szybciej niż kursor i moje oryginalne rozwiązanie . Więc skorzystaj na własne ryzyko, ale przedstawię rozwiązanie na wypadek, gdyby było to interesujące. Możliwe byłoby również zaktualizowanie rozwiązania w celu użycia WHILEpętli od jednego do maksymalnej liczby wierszy, szukając numeru następnego wiersza przy każdej iteracji pętli. Trzymałoby się to w pełni udokumentowanej i niezawodnej funkcjonalności, ale naruszyłoby (nieco sztuczne) stwierdzone ograniczenie problemu, że WHILEpętle nie są dozwolone.

Należy pamiętać, że jeśli korzystanie z SQL 2014 było dozwolone, prawdopodobnie jest to natywna kompilowana procedura składowana która zapętla numery wierszy i uzyskuje dostęp do każdego numeru wiersza w tabeli zoptymalizowanej pod kątem pamięci, byłaby implementacją tej samej logiki, która działałaby szybciej.

Oto pełne rozwiązanie , w tym rozszerzenie danych testowych do około pół miliona wierszy. Nowe rozwiązanie kończy się w ciągu około 3 sekund i moim zdaniem jest o wiele bardziej zwięzłe i czytelne niż poprzednie rozwiązanie, które zaoferowałem. Wytłumaczę tutaj trzy kroki:

Krok 1: przetwarzanie wstępne

Najpierw dodajemy numer wiersza do zestawu danych, w kolejności przetwarzania danych. Robiąc to, konwertujemy również każdy dowInt na potęgę 2, dzięki czemu możemy użyć mapy bitowej do przedstawienia dni, które zostały zaobserwowane w danej grupie:

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

Krok 2: Pętla między dniami umowy w celu zidentyfikowania nowych grup

Następnie zapętlamy dane, w kolejności według numeru wiersza. Obliczamy tylko listę numerów wierszy, które tworzą granicę nowego zgrupowania, a następnie wyprowadzamy te numery wierszy do tabeli:

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

Krok 3: Obliczanie wyników końcowych na podstawie numerów wierszy każdej granicy grupowania

Następnie obliczamy końcowe grupy, używając granic określonych w powyższej pętli, aby agregować wszystkie daty należące do każdej grupy:

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

— Geoff Patterson
źródło

Dziękuję Ci. Poprosiłem, aby nie używać kursorów ani WHILEpętli, ponieważ już wiedziałem, jak rozwiązać to za pomocą kursora i chciałem znaleźć rozwiązanie oparte na zestawie. Poza tym podejrzewałem, że kursor będzie wolny (szczególnie z zagnieżdżoną pętlą). Ta odpowiedź jest bardzo interesująca pod względem uczenia się nowych sztuczek i doceniam twój wysiłek.

— Vladimir Baranov

1

Dyskusja będzie przebiegać zgodnie z kodem.

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 ma poradzić sobie z tą zasadą:

Jeśli przerwa między bieżącym dniem a ostatnim dniem przedziału zawiera dzień tygodnia, który był uwzględniony w przedziale wcześniej, powinniśmy utworzyć nowy przedział

To pozwala mi wymienić nazwy dni w kolejności numerów dni między dowolnymi dwoma dniami. Jest to wykorzystywane przy podejmowaniu decyzji, czy należy rozpocząć nowy interwał. Wypełniam go wartościami z dwóch tygodni, aby ułatwić kodowanie w weekendy.

Istnieją czystsze sposoby na wdrożenie tego. Pełna tabela „dat” byłaby jedna. Prawdopodobnie jest też sprytny sposób na liczbę dni i arytmetykę modulo.

CTE MissingDays ma wygenerować listę nazw dni między dowolnymi dwoma danymi dniami. Jest obsługiwany w ten niezręczny sposób, ponieważ rekurencyjne CTE (następujące) nie zezwala na agregacje, TOP () lub inne operatory. To nieeleganckie, ale działa.

CTE Numbered polega na wymuszeniu znanej sekwencji bez przerw w danych. Pozwala to uniknąć wielu porównań później.

CTE Incrementedto miejsce akcji. Zasadniczo używam rekurencyjnego CTE do przechodzenia między danymi i egzekwowania reguł. Wygenerowany numer wierszaNumbered (powyżej) służy do sterowania przetwarzaniem rekurencyjnym.

Ziarno rekurencyjnej CTE po prostu pobiera pierwszą datę dla każdego ContractID i inicjuje wartości, które zostaną wykorzystane do podjęcia decyzji, czy wymagany jest nowy interwał.

Decyzja, czy nowy interwał powinien się rozpocząć, wymaga daty rozpoczęcia bieżącego interwału, listy dni i długości dowolnej przerwy w datach kalendarzowych. Mogą one zostać zresetowane lub przeniesione, w zależności od decyzji. Dlatego część rekurencyjna jest pełna i trochę powtarzalna, ponieważ musimy zdecydować, czy rozpocząć nowy interwał dla więcej niż jednej wartości kolumny.

Logika decyzyjna dla kolumn WeekDaysiIntervalStart powinna mieć tę samą logikę decyzyjną - można ją wycinać i wklejać między nimi. Jeśli logika rozpoczęcia nowego interwału miałaby ulec zmianie, należy zmienić kod. Idealnie byłoby zatem abstrakcyjne; robienie tego w rekurencyjnym CTE może być trudne.

EXISTS()Klauzula jest outfall nie jest w stanie korzystać z funkcji agregujących w rekurencyjnej CTE. Wszystko, co robi, to sprawdzić, czy dni mieszczące się w luce są już w bieżącym przedziale.

Nie ma nic magicznego w zagnieżdżaniu klauzul logicznych. Jeśli jest wyraźniejszy w innej konformacji lub przy użyciu zagnieżdżonych CASE, powiedzmy, że nie ma powodu, aby tak było.

Ostatnim SELECTjest podanie wyniku w pożądanym formacie.

Włączenie PK Src.IDnie jest przydatne dla tej metody. (ContractID,dt)Myślę, że indeks klastrowy byłby fajny.

Istnieje kilka szorstkich krawędzi. Dni nie są zwracane w sekwencji dow, ale w sekwencji kalendarza pojawiają się w danych źródłowych. Wszystko, co ma związek z @Helper, jest nieporadne i można je wygładzić. Podoba mi się pomysł używania jednego bitu dziennie i używania funkcji binarnych zamiast LIKE. Rozdzielenie niektórych pomocniczych CTE na tabelę temp z odpowiednimi indeksami niewątpliwie pomogłoby.

Jednym z wyzwań jest to, że „tydzień” nie jest dopasowany do standardowego kalendarza, lecz zależy od danych i resetuje się, gdy zostanie ustalone, że należy rozpocząć nowy interwał. „Tydzień” lub przynajmniej interwał może trwać od jednego dnia do całego zestawu danych.

Dla dobra, oto szacunkowe koszty w porównaniu z przykładowymi danymi Geoffa (dzięki za to!) Po różnych zmianach:

                                             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.

Szacowana i faktyczna liczba wierszy różni się bardzo.

Plan zawiera spoo tabeli, prawdopodobnie w wyniku rekurencyjnego CTE. Większość akcji odbywa się na stole roboczym, który:

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.

Chyba tak, jak zaimplementowano rekurencję!

— Michael Green
źródło

Dziękuję Ci. Daje poprawny i optymalny wynik na przykładowych danych. Sprawdzę to teraz na prawdziwych danych. Uwaga dodatkowa: MAX(g.IntervalStart)wydaje się dziwna, ponieważ g.IntervalStartznajduje się w GROUP BY. Spodziewałem się, że spowoduje to błąd składniowy, ale działa. Powinien on być po prostu g.IntervalStart as StartDTw SELECT? A może g.IntervalStartnie powinno być w GROUP BY?

— Vladimir Baranov

Próbowałem uruchomić zapytanie na rzeczywistych danych i musiałem je zatrzymać po 10 minutach. Jest całkiem prawdopodobne, że jeśli CTE MissingDaysi Numberedzostaną zastąpione tabelami temp z odpowiednimi indeksami, może mieć przyzwoitą wydajność. Jakie indeksy poleciłbyś? Mogę spróbować jutro rano.

— Vladimir Baranov

Myślałem, że warto zastąpić Numberedtabelą tymczasową i indeksem klastrowym (ContractID, rn). Bez dużego zestawu danych do wygenerowania odpowiedniego planu trudno zgadnąć. Fizjalizacja za MissingDatespomocą indeksów również (StartDay, FollowingDayInt)byłaby dobra.

— Michael Green

Dzięki. Nie mogę tego teraz wypróbować, ale jutro rano.

— Vladimir Baranov

Wypróbowałem to na pół milionie wierszy danych (istniejący zbiór danych, replikowany 4000 razy z różnymi identyfikatorami kontraktów). Działa od około 15 minut i do tej pory zajmował 30 GB miejsca na tempdb. Myślę więc, że konieczna może być dalsza optymalizacja. Oto rozszerzone dane testowe na wypadek, gdyby okazały się pomocne.

— Geoff Patterson

Pogrupuj dzienny harmonogram w [Data rozpoczęcia; Data zakończenia] przedziały z listą dni tygodnia

Porównanie odpowiedzi

Kolejna strategia