Sumowanie czasów z pominięciem nakładających się na siebie okresów

1

Witam
Prośba o SQL lub procedurę (tsql), nie mogę użyć f.analitycznych:
Potrzebuję zsumować (w minutach) czasy rekordów w tabeli (kolumny czas_begin, czas_end), żeby sumowanie nie dublowało ''nadkładających się' czasów (czyli tylko 1 raz uwzględniło wśród nałożonych się okresów na siebie).

Przykładowo mając poniższe 3 czasy rozpoczęte i zakończone w różnych momentach, mam zsumować pomijając czas zaznaczony x'em (bo się nakłada z innym czasem):

T1: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
T2:                 xxxxxxxxxxxxxxaaaaaaaaaaaaaaaaaaaa
T3:	   				            xxxxxxxxxxxxxxxxxxxxxxaaaaaaaaaaaaaaaaaaaa
0

Wyciągasz MIN i MAX z tych rekordów i mierzysz.

0

Oblicz ilość minut miedzy czas_begin i czas_end dla T1, weź czas_end z T1 jako czas startu dla T2 i oblicz minuty ... i analogicznie T3.

Bez struktury tabeli to hmm niewiele więcej można dodać. Btw. napisz co sam wymyśliłeś / próbowałeś, a nie tak po gotowe rozwiązanie :|

Edit: @Haskell jak zwykle szybszy ;)

0

Mój algorytm (opis słowno-muzyczny):
Moja tabela T

1 Pierwszym kursorem insertuję do tabeli pomocniczej (#T1, jedna kolumna: czas) wszystkie czasy rozpoczęcia i zakończenia (pakuję w tą jedną kolumnę).
2 Drugim kursorem: kursor distinct po tabeli pomocniczej #T1 (posortowany po czasie) pobieram parę czasów: czas1 i kolejny czas2(podzapytankiem).
2a Sprawdzam podzapytaniem czy dla przedziału od czas1 do czas2 w mojej tabeli T jest jakikolwiek rekord mający wspólny 'chociaż 1 moment' :), jeżeli tak, dodaję ten przedział do mojej sumy.

Ten algorytm załatwi wszystkie możliwe przypadki zachodzących i niezachodzących na siebie przedziałów czasowych.
pzdr

0

Zwykły MAX - MIN nie wystarczy, bo w ciągu czasowym mogą istnieć "dziury".

Rozwiązanie dość pokrętne acz działa. Pisane w Oraclu, ale na pewno w T-sqlu też śmignie.
Załóżmy, że twoja tabela nazywa się CZAS, kolumny START i KONIEC.

-- Posortowanie wszystkich występujących czasów w jednej liście
CZAS_1 AS(
SELECT * FROM (
  SELECT DISTINCT START CZ, 'START' typ FROM CZAS
  UNION ALL
  SELECT DISTINCT KONIEC CZ, 'KONIEC' typ FROM CZAS
  ) ORDER BY 1 asc -- bardzo ważne
),

-- Cały dowcip polega na tym, aby rozmontować istniejące przedziały i stworzyć je ponownie łącząc kolejne krańce początkowe z końcowymi. W ten sposób otrzymamy nienachodzące na siebie przedziały.
CZAS_2 AS(
SELECT cc.*, rownum + mod(rownum, 2) NUMER -- skojarzenie ze sobą dwóch krańców
   FROM CZAS_1 cc
     LEFT JOIN CZAS c
        ON cc.cz > c.START
      AND cc.cz < c.KONIEC
WHERE c.START IS NULL -- Bierzemy tylko krańce, tj. wartości, które nie znajdują się pomiędzy żadnymi występującymi wartościami
),

CZASY_3 AS(
SELECT NUMER
             , MAX(CASE WHEN typ='START' THEN CZ ELSE NULL END) START
             , MAX(CASE WHEN typ='KONIEC' THEN CZ ELSE NULL END) KONIEC
   FROM CZAS_2 
  group by NUMER
)

SELECT SUM(KONIEC - START) FROM CZASY_3

Kod może mieć mieć jakieś błędy interpunkcyjne bo go przepisywałem na nowo z testu.

0

Pytający nic nie pisał o dziurach, więc założyłem, że ich nie ma. Poza tym jeżeli czas się nie nakłada, to może w ogóle nie powinien być sumowany.

0

Nie żebym się upierał :D ale to co napisałem w 3 poście wydaje się właśnie poprawnym rozwiązaniem - przy założeniu, że są dziury trzeba porównać która data jest późniejsza czy czas_end z wcześniejszego wiersza czy czas_start z "aktualnego" ... technicznie (patrząc pobieżnie) opisane jest pod linkiem @Panczo

0

Dzięki za odpowiedzi.
Zrobiłem po swojemu (bo potrzebowałem na cito), wydaje się mój algorytm najprostszy, mimo, że na początku nieintuicyjny. Nie jest to sam SQL, ale PLSQL.

//Pierwsza pętla: insertuję do tabeli tymczasowej #tt (jedna kolumna CZAS) wszystkie daty (początek, koniec)
//Druga pętla: w kursorze sumuję POSZATKOWANE okresy, ale tylko te, które w całości się mieszczą w którymkolwiek z oryginalnych okresów (z mojej tabeli)

	begin
	declare
	 @suma_czasow int
	,@minuty int
	,@t1 datetime
	,@t2 datetime
	
	---------------------Pierwsza pętla-----------------------------------------
	select tczas.czas  as czas
	into #tt
	from
	( 
		select  czas_rozp czas  from  MOJA_TABELA
		union
		select czas_zakon   as czas from  MOJA_TABELA
	) tczas
	
	set @suma_czasow = 0
	
	-------------------Druga pętla------------------------------------------------
	declare c1 cursor fast_forward
	for 
	select distinct 
			t1.czas t1
			,(select min(czas) from #tt t2 where t2.czas > t1.czas) t2									
	from #tt t1 	
	order by 1,2

	open c1
	fetch next from c1 into  @t1, @t2
	while (@@FETCH_STATUS <> -1)
	begin
		if (@@FETCH_STATUS <>-2)
		begin
			set @minuty = datediff(minute,@t1,@t2)
			if @minuty is null break --ostatni
			
			if 1 = (select top 1 1
					from MOJA_TABELA rcp
					where rcp.czas_rozp <= @t1 and rcp.czas_zakon >=@t2	)
								
			set @suma_czasow = @suma_czasow + @minuty

			fetch next from c1 into  @t1, @t2
		end
	end
	CLOSE c1  
	DEALLOCATE c1

	select @suma_czasow
	end	

Algorytm sprawdzony i działa, akurat u mnie musiałem jeszcze bardziej rozbudować, bo dochodziło grupowanie etc.

2

Przecież to jest proste, bez użycia żadnych kursorów i analitycznych...
http://sqlfiddle.com/#!6/fc3f8/5

0

@Marcin.Miga: lekko modyfikując dane nie łapie juz dobrze: http://sqlfiddle.com/#!6/7b462/2/0 tzn jeżeli koniec i początek wypada w tym samym dniu,
Rozwiazanie @Bazowy ma również tę samą przypadłość: http://sqlfiddle.com/#!6/dc139/6

Moje rozwiązanie w ogóle się nie sprawdza, ale nie chce mi się dochodzić dlaczego ;)

UPDATE:

Chyba mnie zamroczyło, skupiłem się na ciągłości przedziałów, anie na czasie który zajmują... jest ok w obu przypadkach

0

Trzeba to zrobić kursorem serwerowym (procedurą), jednoprzebiegowo. Rozwiązanie Marcina da się pewnie doszlifować, ale powstanie niezrozumiały kod (nie pisz sprytnego kodu). Wydawało mi się, że takie rozwiązanie pochłonie 6 aliasów do tabeli przedziałowej, czyli masakra. Kursor serwerowy zrobi to w czasie liniowym. Stawiam flaszkę, (porządną), że nikt nie napisze szybszego rozwiązania jakimś szemranym selectem.

Algorym jest taki:

  • sortujemy dane po pocz, kon
  • przedz := null
  • for biez in przedzialy
  • jezeli przedz null, przedz := biez
  • jezeli przedz pokrywa sie z biez, rozszerzamy przedz: przedz.kon = biez.kon
  • jezeli biez występuje po przedz, dopisz wartosc przedz do wyniku, a potem przedz := biez
  • przedzial biez nie moze zaczynać się wcześniej niż przedz, bo mamy sortowanie - nie musimy przerabiać takiego przypadku.
1

@jarekczek podejmuje wyzwanie, podaj tylko warunki sprawdzenia szybkości i wolumen danych na których testujemy i przedstaw swoje rozwiązanie.

Poprawienie skryptu @Marcin.Miga nie jest jakieś trudne, trzeba uwzględnić przypadek, że nie powinno być takiej samej daty od i do, jeden krok dodany przed wykonaniem zapytania:
http://sqlfiddle.com/#!6/98a7d/1

0

Moje rozwiązanie jest poprawne, ale jestem ciekaw algorytmu @jarekczek , czy rzeczywiście będzie poprawne i szybsze. Odczyt z cursora rekord po rekordzie chyba jest kosztowniejszy.

1

@Bazowy, az to sprawdzilem, wygenerowałem dane (100 000 rekordów):

truncate table czasy
go
DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @UpperP INT
DECLARE @LowerP INT
DECLARE @StartDate DATETIME

SET @Lower = -730
SET @Upper = -1
SET @LowerP = 1
SET @UpperP = 60
SET @RowCount = 0
WHILE @RowCount < 500000
BEGIN
	SET @RowString = CAST(@RowCount AS VARCHAR(10))
	SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
	SET @StartDate = DATEADD(dd, @Random, GETDATE())
	select @Random = ROUND(((@UpperP - @LowerP -1) * RAND() + @LowerP), 0)

	INSERT INTO czasy
		(d_start
		,d_koniec)
	VALUES
		(@StartDate
		,DATEADD(dd, @random, @StartDate)
)

	SET @RowCount = @RowCount + 1
END

I rozwiązanie OP oparte o cursor trwa niecałą sekundę, Twoje 25 s, rozwiązanie @Marcin.Miga nie wiem bo po 2 minutach przerwałem... Czasy pokazywane w SSMS, ale obrazują sytuacje...

2

Zrobiłem test, skrypt do generowania tabel kilka postów wyżej, przerobiłem kursor OP na zapytanie i stworzyłem dwie procedury:

CZASY KURSOR:

CREATE PROCEDURE CZASY_KURSOR
   AS
   declare
     @suma_czasow int
    ,@minuty int
    ,@t1 datetime
    ,@t2 datetime

    ---------------------Pierwsza pętla-----------------------------------------
    select tczas.czas  as czas
    into #tt
    from
    ( 
        select  d_start czas  from czasy
        union
        select d_koniec   as czas from  czasy
    ) tczas

    set @suma_czasow = 0

    -------------------Druga pętla------------------------------------------------


    declare c1 cursor fast_forward
    for 
    select distinct 
            t1.czas t1
            ,(select min(czas) from #tt t2 where t2.czas > t1.czas) t2                                  
    from #tt t1     
    order by 1,2

    open c1
    fetch next from c1 into  @t1, @t2
    while (@@FETCH_STATUS <> -1)
    begin
        if (@@FETCH_STATUS <>-2)
        begin
            set @minuty = datediff(minute,@t1,@t2)
            if @minuty is null break --ostatni

            if 1 = (select top 1 1
                    from czasy rcp
                    where rcp.d_start <= @t1 and rcp.d_koniec >=@t2 )

            set @suma_czasow = @suma_czasow + @minuty

            fetch next from c1 into  @t1, @t2
        end
    end
    CLOSE c1  
    DEALLOCATE c1

    select @suma_czasow WYNIK

i CZASY_SELECT

CREATE PROCEDURE [dbo].[CZASY_SELECT]
AS
select 
	tczas.czas  as czas
 into 
	#tt
from
    ( 
        select  d_start czas  from czasy
        union
        select d_koniec   as czas from  czasy
    ) tczas;

WITH WYNIK AS (
	select
		*
		,(select TOP 1 1
                    from czasy rcp
                    where rcp.d_start <= DT.t1 and rcp.d_koniec >=DT.t2 ) RCP
	FROM
		(
		select distinct 
				t1.czas t1
				,(select min(czas) from #tt t2 where t2.czas > t1.czas) t2                                  
		from #tt t1)   DT)


SELECT SUM(DATEDIFF(MINUTE,T1,T2)) W FROM WYNIK WHERE RCP=1
    
drop table #tt
GO

I użyłem narzędzia:

http://sqlblog.com/blogs/adam_machanic/archive/2016/01/04/sqlquerystress-the-source-code.aspx

Wyniki:

screenshot-20170929121653.png

screenshot-20170929121743.png

@jarekczek piłka po Twojej stronie, na razie 1:0 dla zapytań ;)

0

Panowie, pijemy na mój koszt. Jestem słaby z sqla. Na początek zrobiłem sobie porównanie funkcji sum kursorem i selectem. Otrzymałem wynik 15 sekund kontra niemierzalny. Morale momentalnie spadło. Potestowałem jeszcze Wasze rozwiązania, przeczytałem SQL Server cursor performance problems i poddałem się.

Bardzo dobra lekcja, dzięki :)

Swoją drogą i tak nie rozumiem, czemu rozwiązanie Bogatego Rycerza działa szybciej, niż mój kursor robiący zwykłe count po wszystkich rekordach. Jakieś czary.

create procedure jar as
begin
  declare c1 cursor fast_forward
    for select d_start, d_koniec from czasy
  declare @suma int
  declare @t1 date, @t2 date
  set @suma = 0
  open c1
    fetch NEXT FROM c1 INTO  @t1, @t2
    while (@@FETCH_STATUS = 0)
    BEGIN
      SET @suma = @suma + 1
      fetch NEXT FROM c1 INTO  @t1, @t2
    END
    CLOSE c1  
    DEALLOCATE c1

  select @suma
end
0

Ja tego nie robiłem dla tej flaszki ;) Wypij w ten weekend nasze zdrowie i będziemy kwita.

Co do twojego cursora to zwróć uwagę, że samo przygotowanie danych mocno ogranicza liczbę dat. Twoja próba to najbardziej jaskrawy przykład złego użycia kursów, tzn. prosta operacja sumowania/liczenia realizowana haubicą. To zauważam często u programistów, którzy sqla traktują jak język programowania, a nie jak język zapytań.

0

Ja tego nie robiłem dla tej flaszki ;) Wypij w ten weekend nasze zdrowie i będziemy kwita.

Honor nie do końca pozwala mi się zgodzić. Ale zostawmy to na chwilę.

Męczy mnie ten kursor, bo teoretycznie operacja liniowa nie powinna na dłuższym dystansie być gorsza od operacji rzędu n^2, które powstają przy łączeniu tabel. No i do czegoś doszedłem. Najpierw zdziwiło mnie, czemu kursor Bogatego był 3 razy szybszy od mojego. Okazało się, że jak dodam sobie UNION do mojego wejściowego zestawu wierszy, to też osiągam porównywalne wyniki. UNION tak, a UNION ALL - już nie. No bo dane zawierają 75% duplikatów przy bazie wielkości 160K rekordów.

Proszę więc o jeszcze jedną rundę. Tym razem rozstrzelmy daty nie o 730 dni, a o 7300. Wystarczy 160K rekordów. Wtedy duplikatów będzie już mało (20%). Moje wyniki:
SELECT: 12 sekund
CURSOR: 3 sekundy

I mój kandydat, kursor jednoprzebiegowy:

if OBJECT_ID('jar') is not null drop procedure jar
go
-- line 1
create procedure jar as
begin
  begin transaction -- przyśpiesza o 30%
  declare c1 cursor fast_forward
    for select d_start, d_koniec from czasy
    -- ten union nadal przyśpiesza o 20%!
    -- union select '2017-01-01', '2017-01-01'
    order by d_start, d_koniec
  declare @suma int
  declare @pocz date, @kon date
  declare @lastPocz date, @lastKon date
  declare @msg varchar(2000)

  set @suma = 0
  set @lastPocz = null

  open c1
  fetch next from c1 into  @pocz, @kon
  while (@@fetch_status = 0)
  begin
    -- print @pocz
    -- print @kon
    -- print datediff(minute, @pocz, @kon)
    -- pierwszy rekord - inicjalizujemy zmienne
    if @lastPocz is null begin
      set @lastPocz = @pocz
      set @lastKon = @kon
      goto nast
    end
    -- przypadek 1. - bieżący przedział zawiera się w ostatnim
    -- ignorujemy
    if @kon <= @lastKon begin
      goto nast
    end
    -- przypadek 2. - bieżący przedział rozszerza ostatni
    if @pocz <= @lastKon and @kon >= @lastKon begin
      -- rozszerzamy ostatni przedział
      set @lastKon = @kon
      goto nast
    end
    -- przypadek 3. - bieżący przedział jest rozłączny z ostatnim
    if @pocz > @lastKon begin
      set @suma = @suma + datediff(minute, @lastPocz, @lastKon)
      -- bieżący przedział stanie się ostatnim
      set @lastPocz = @pocz
      set @lastKon = @kon
      goto nast
    end

    -- tu nie powinniśmy nigdy dotrzeć    
    set @msg = 'problem ' + cast(@lastPocz as varchar)
      + ' ' + cast(@lastKon as varchar)
      + ' ' + cast(@pocz as varchar) + ' ' + cast(@kon as varchar)
    raiserror(@msg, -1, -1)

    nast:
    fetch next from c1 into @pocz, @kon
  end
  set @suma = @suma + datediff(minute, @lastPocz, @lastKon)
  close c1  
  deallocate c1
  select @suma
  commit
end
go

declare @t0 datetime, @t datetime
set @t0 = (select current_timestamp )
print ''
print @t0
exec jar
set @t = (select current_timestamp )
print datediff(millisecond, @t0, @t)
go

Być może wyjdzie z tego fajna konkluzja: każdy może sobie tak dobrać parametry bazy danych, że jego rozwiązanie będzie lepsze. Wtedy każdy może się poczuć wygranym tego zakładu :)

0

Na spacerze z psem natknęło mnie jeszcze. Ja też mogę wykorzystać fakt, że wiele wartości się powtarza i zrobić preprocessing. Jeżeli blok

  declare c1 cursor fast_forward
    for select d_start, d_koniec from czasy
    -- ten union nadal przyśpiesza o 20%!
    -- union select '2017-01-01', '2017-01-01'
    order by d_start, d_koniec

zastąpię takim:

  declare c1 cursor fast_forward
    for select c1.d_start as start,
      max(c2.d_koniec) as koniec
      from czasy c1
      left join czasy c2 on c1.d_start = c2.d_start
      group by c1.d_start
    order by start, koniec

to nawet przy gęstych danych mam dobry czas. Trochę nieporównywalne czasy, bo w międzyczasie wymieniłem chłodzenie w laptopie i mi przyśpieszył, ale teraz na gęstych danych (szerokość 730) mam takie wyniki:

SELECT 283 ms
CURSOR 233 ms

1

Trochę się pobawiłem, czasy na wolumenie 160k rekordów, mamy minimalne lepsze na korzyść selecta, zobacz jak wyjdzie u ciebie, ja przemyślałem sprawę i błąd selectów polegał na tym, że skupiliśmy się (ja i inni odpowiadający) na wyciągnięciu okresów i później zsumowaniu różnicy, kiedy to nie jest potrzebne do policzenia wyniku, w sensie czy policzę np od 1 do 31 stycznia, czy od 1-11, 12-26, 27-31...
Moja procedura:

ALTER PROCEDURE [dbo].[CZASY_SELECT]
AS

--tabela do obliczen
create table #agg (
	id int identity(1,1) primary key
	,d_start date
	,d_koniec date
)
--dodaj dane pogrupowane po dacie startu
insert into #agg (d_start,d_koniec)
select d_start, max(d_koniec)  from czasy group by d_start

--okresy zawierające się w innych
SELECT a.ID INTO #DID from #agg a
INNER join #agg s on a.d_start between s.d_start and s.d_koniec
					and a.d_koniec between s.d_start and s.d_koniec
					and a.id<>s.id
GROUP BY A.ID

--widok wykluczający okresy zawierające się w innych
;WITH W AS (
SELECT 
	A.* 
FROM 
	#AGG A
	LEFT JOIN #DID D ON D.ID=A.ID
WHERE 
	D.ID IS NULL
)

--liczenie
select
	sum(datediff(minute,case when d_start > pd and d_start < nd then nd else d_start end,d_koniec))
from (
	select 
		*
		,lag(d_start,1) over (order by d_start) pd
		,lag(d_koniec,1) over (order by d_start) nd
	from W
	) A


	
drop table #agg
DROP TABLE #DID

Trochę zmieniłem skrypt testujący:

SET NOCOUNT ON
declare @t0 datetime, @I INT, @SUM INT, @W INT

SET @I=1
SET @SUM=0
WHILE @I<=10
BEGIN
	set @t0 = GETDATE()
	exec CZASY_SELECT
	SET @W = datediff(millisecond, @t0, GETDATE())
	PRINT 'WYKONANIE #' + CONVERT(VARCHAR(2),@I) + ': ' +  CONVERT(VARCHAR(10),@W)
	SET @SUM = @SUM + @W
	SET @I = @I+1
END 
PRINT 'RAZEM: ' + CONVERT(VARCHAR(20),@SUM)
PRINT 'ŚREDNIA: ' + CONVERT(VARCHAR(20),@SUM/10)

Wyniki dla procedury JAR (wprowadziłem zmianę w kursorze i grupuje po dacie od)

10588320
WYKONANIE #1: 490
10588320
WYKONANIE #2: 356
10588320
WYKONANIE #3: 303
10588320
WYKONANIE #4: 276
10588320
WYKONANIE #5: 246
10588320
WYKONANIE #6: 226
10588320
WYKONANIE #7: 216
10588320
WYKONANIE #8: 203
10588320
WYKONANIE #9: 193
10588320
WYKONANIE #10: 180
RAZEM: 2689
ŚREDNIA: 268

dla CZASY_SELECT

10588320
WYKONANIE #1: 490
10588320
WYKONANIE #2: 363
10588320
WYKONANIE #3: 286
10588320
WYKONANIE #4: 260
10588320
WYKONANIE #5: 236
10588320
WYKONANIE #6: 210
10588320
WYKONANIE #7: 206
10588320
WYKONANIE #8: 190
10588320
WYKONANIE #9: 176
10588320
WYKONANIE #10: 173
RAZEM: 2590
ŚREDNIA: 259

Potraktuje to jako remis bo czasy są z niewielką przewagą dla selektów, mam wrażenie, że proces generowania danych ma słabości, bo mi przy stworzeniu tych 160000 okresów wyszło, ze nie mam żadnych luk...

Postanowiłem zwiększyć pulę do 500k rekordów i zobaczyć wyniki (w myśl zasady, że na większym zestawie będzie większa różnica), załączam skrypt insertu w załączniku (skrypt usuwa i zakłada tabelę czasy), cobyśmy testowali na tych samych danych, wyniki:

CZASY_SELECT

WYKONANIE #1: 620
WYKONANIE #2: 470
WYKONANIE #3: 380
WYKONANIE #4: 343
WYKONANIE #5: 300
WYKONANIE #6: 286
WYKONANIE #7: 260
WYKONANIE #8: 250
WYKONANIE #9: 230
WYKONANIE #10: 210
RAZEM: 3349
ŚREDNIA: 334

JAR

WYKONANIE #1: 990
WYKONANIE #2: 663
WYKONANIE #3: 560
WYKONANIE #4: 473
WYKONANIE #5: 423
WYKONANIE #6: 390
WYKONANIE #7: 373
WYKONANIE #8: 370
WYKONANIE #9: 366
WYKONANIE #10: 380
RAZEM: 4988
ŚREDNIA: 498

I Select jest średnio 32% szybszy, co i tak jest ciekawe bo przerabianych rekordów jest tyle samo...

0

Ładnie, czytelnie. Rzeczywiście szybsze. Rozumiem, że bez funkcji analitycznej (LAG) jest trochę wolniej. Dam już spokój tym biednym przedziałom, chyba mają dość :) Let's don't beat a dead horse.

0

Lag umożliwia zapis krótszy niż join o zlaczeniu a.id =a.id-1, nie testowałem jakby to wyglądało z joinem...

Tak czy siak fajnie wyjść na forum poza "standardowe problemy". Dzięki za wspólną zabawę wbrew pozorom mnie też to w kilku momentach zaskoczyło... Taka wymiana zawsze wzbogaca. Na zdrowie ;)

1 użytkowników online, w tym zalogowanych: 0, gości: 1