raport z kolumny będącej wynikiem odejmowania sąsiednich wierszy

Witam!

Mam tabelę w której są m.in. takie kolumny

są to logi każdego wykonania kolejnej części przez pewną maszynę.

Po pierwsze interesuje mnie ile trwa cykl pracy maszyny czyli wiersz kolejny-wiersz poprzedni w danym okresie czasu

Po drugie (na podstawie pierwszego) chciałbym stworzyć raport, który policzy mi dla poszczególnych zmian produkcyjnych:

• sumę cykli krótszych niż czas1
• sumę cykli trwających pomiędzy czas1 i czas2
• sumę cykli dłuższych niż czas2
• ilość elementów wykonanych (WynikOperacji =1 lub =0)
• ilość błędów (WynikOperacji=1)

Ma ktoś jakiś pomysł jak to zrobić?

Czy ty chcesz policzyć ile czasu upłynęło od wykonania się poszczególnych wierszy z zapytania? SELECT * FROM mytable; Zapytanie to zwróci, np. 0.04, czyli dla całego zapytania, a ty chcesz policzyć poszczególne wiersze ile czasu upłynęło? Dla przyładu:

1. wiersz - 3ms
2. wiersz - 2ms
3. wiersz - 5ms
4. wiersz - 1ms

Teraz pomiędzy 1. i 2. wierszem upłynęła 1ms, dalej 3ms i 4ms.

https://stackoverflow.com/questions/12925140/how-to-estimate-sql-query-timing?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa

Nie wiem, czy jest taka opcja dla każdego wiersza osobno, jest opcja pokazywania czasu ile się zapytanie wykonało ale całe, a nie pojedyncze wiersze.

Nie, zupełnie nie o to mi chodzi.
Chciałbym poszeregować dane w kolumnie LUD od najstarszego do najmłodszego i obliczyć różnicę pomiędzy elementem 2 i 1, 3 i 2, 4 i 3 itd.
To obliczenie powinno być podstawą do wykonania raportu z drugiej części mojego postu.

Jeżeli dobrze zrozumiałem to trzeba wykonać złączenia tej tabeli z samą sobą, reszta to już sumowanie:

with rn as (
  select 
     *
     , row_number() over (order by LUD) r 
  from 
      dt
)
, d as (
  select 
      p.r pr 
      , n.r nr
      , p.lud od
      , n.lud do
      , p.wynikoperacji pwo
      , n.wynikoperacji nwo 
      ,datediff(ms,p.lud,n.lud)/1000.0 czas
      ,2 elementy
      ,p.wynikoperacji+n.wynikoperacji bledy
  from 
      rn p
      inner join rn n on p.r+1 = n.r
)

select
     sum(case when czas<1 then 1 else 0 end) [czas<1s]
     ,sum(case when czas between 1 and 2 then 1 else 0 end) [czas1-2s]
     ,sum(case when czas>2 then 1 else 0 end)[czas>2s]
     ,sum(elementy) WyprodukowaneElementy
     ,sum(bledy) iloscbledow
from
    d

http://sqlfiddle.com/#!18/b2706/13

Twoje zapytanie nie do końca działa tak, jak chciałem:

• chciałem sumować czas wystąpień poniżej czas1, pomiędzy czas1 i czas2 i powyżej czas2 - ty sumujesz ilość wystąpień w tych granicach
• w podanym przeze mnie przykładzie powinno być Wyprodukowanych 4, błędów - 3
• co z podziałem na zmiany?

chciałem sumować czas wystąpień poniżej czas1, pomiędzy czas1 i czas2 i powyżej czas2 - ty sumujesz ilość wystąpień w tych granicach

Popraw w takim razie w tych caseach zmien 1 na kolumnę czas

w podanym przeze mnie przykładzie powinno być Wyprodukowanych 4, błędów - 3

Masz 3 cykle:

1. 2018-05-14T1608Z 2018-05-14T1614Z
2. 2018-05-14T1614Z 2018-05-14T1630Z
3. 2018-05-14T1630Z 2018-05-15T0225Z

Jak doliczyłeś się 4?

Skoro liczba błędów to 3 to musisz zmienić kolumnę p.wynikoperacji+n.wynikoperacji bledy aby dawała Ci prawidłowa wartość

co z podziałem na zmiany?

Nie wiem, trzeba dopisać, nigdzie nie podałes jak te zmiany wyglądają

Panczo napisał(a):

chciałem sumować czas wystąpień poniżej czas1, pomiędzy czas1 i czas2 i powyżej czas2 - ty sumujesz ilość wystąpień w tych granicach

Popraw w takim razie w tych caseach zmien 1 na kolumnę czas

A możesz troszkę jaśniej?

w podanym przeze mnie przykładzie powinno być Wyprodukowanych 4, błędów - 3

Masz 3 cykle:

1. 2018-05-14T1608Z 2018-05-14T1614Z
2. 2018-05-14T1614Z 2018-05-14T1630Z
3. 2018-05-14T1630Z 2018-05-15T0225Z

Jak doliczyłeś się 4?

Tak naprawdę ilość wyprodukowanych to ilość wystąpień/wierszy w kolumnie LUD, dlatego 4. Zapytanie, które podałeś wygenerowało 6.

Skoro liczba błędów to 3 to musisz zmienić kolumnę p.wynikoperacji+n.wynikoperacji bledy aby dawała Ci prawidłowa wartość

OK, zmienię - jak rozkminię całość.

co z podziałem na zmiany?

Nie wiem, trzeba dopisać, nigdzie nie podałes jak te zmiany wyglądają

Standardowo :)
pierwsza 6-14
druga 14-22
trzecia 22-6

create or replace procedure a()
	begin
		declare v1 int default 0;
		declare v1_2 int default 1;
		declare v2 int default (select count(*) from marian_z);
		set @outx = '';

		while v1 < v2 do

			set @outx = (timestampdiff(SECOND,(select lud from marian_z group by lud asc limit 1 offset v1),(select lud from marian_z group by lud asc limit 1 offset v1_2)));
			
			set v1 = v1 + 1;
			set v1_2 = v1 + 1;
			
			
		update `marian_z` set `result_something` = @outx where id3 = v1;
		 -- select @outx from marian_z as result_something limit 1 offset v1;
        
		end while;

	select * from marian_z group by lud asc;
   
	select sum(result_something) as time_less_than_time1 from marian_z where result_something<(select result_something from marian_z group by lud asc limit 1 offset 1) ;
	select sum(result_something) as time_between_time1_and_time2 from marian_z where result_something<(select result_something from marian_z group by lud asc limit 1 offset 1) and result_something>(select result_something from marian_z limit 1 offset 2) ;
	select sum(result_something) as time_more_than_time2 from marian_z where result_something>(select result_something from marian_z group by lud asc limit 1 offset 2);
	select sum(result_operation) as mistakes from marian_z where result_operation=1;

	end; 
   
call a//

   

@Marian_Z: widzę że czekasz na gotowca, a nie chcesz sam dojść do rozwiązania... Przepisanie tego co napisał @Neutral na TSQL nie jest żadnym problemem, ale to IMO jest jeden z rażących przykładów, kiedy używa się pętli w miejscu gdzie nie są zupełnie potrzebne...

Może wysil się trochę i spróbuj zrozumieć co masz już napisane:

SUM(CASE WHEN czas<1 THEN 1 ELSE 0 END) 

To znaczy, że dla wartości czas<1 ma wziąć 1,a dla każdej innej wartości 0, czyli sumuje tylko czasy < 1s, skoro chcesz sumować czas to wystarczy zmienić 1 na czas: CASE WHEN czas<1 THEN czas ELSE 0 END to naprawde nie jest rudne.

Skoro Liczba wystąpień to liczba wyprudukowanych elementów to wystarczy wziąć maksymalną wartośc pola nr.

Skoro zmiana jest liczona standardowo to możesz wyciągnąć godzinę DATEPART(HOUR,lud) i zbudować case z 3 warunkami.

Ja wiem, że fajnie jest wymagać, a ciężej dać od siebie, ale pokaż, że Ci zależy i pochwal się tym co już zrobiłeś, bo jak np. rozwiązać taki zakres: 2018-05-14T1630Z 2018-05-15T0225Z, bo to jest II i III zmiana...

Myślę @Panczo, że trochę za surowo mnie oceniasz :)
Jak napisałem w moim poprzednim poście - muszę to co napisałeś przetrawić, bo programistą z zawodu nie jestem.
Tak więc na razie wygląda to tak
http://sqlfiddle.com/#!18/6c5e4/1/0
Podział na zmiany dodam jutro.
Dla mnie przerobienie programu @Neutral na TSQL to jest problem, bo nie wiem nawet w czym to napisał.

W każdym razie dzięki, bo twój kod działa bardzo dobrze.

Nie oceniam surowo, tylko postawa którą prezentujesz jest trochę roszczeniowa, dodatkowo nie stosujesz się do wskazówek, które dostajesz:

1. Użyj max do policzenia wierszy, skoro masz cztery rekorfy o id: 1,2,3,4 to masz 3 zakresy" 1-2,2-3,3-4 więc count nie jest najlepszym pomysłem, zmien na max(nr)
2. Przy zliczaniu błędów pomijasz zupełnie kolumnę p.wynikoperacji, a dla pierwszego rekordu musisz ja brać pod uwagę, bo ta wartość występuje tylko w tym wierszu, po złączeniu rekordów w okresy masz tak:
   1,2
   2,3
   3,4
   Czyli dla pierszego wiersza case when p.r=1 then musisz uwzględnić wartość z rekordu o id 1: p.wynikoperacji
3. Pominałeś odpowiedź jaka zmiana jest dla ostaniego okresu który masz w przykładowych danych....

Znalazłem rozwiązanie podziału na zmiany w swoich starszych zapytaniach.

No więc będzie to tak wyglądało

with rn as (
  select 
     *
     , row_number() over (order by LUD) r 
  from 
      dt
)
, d as (
  select
      n.lud
      , p.r pr 
      , n.r nr
      , p.lud od
      , n.lud do
      , p.wynikoperacji pwo
      , n.wynikoperacji nwo 
      ,datediff(ms,p.lud,n.lud)/1000.0 czas
      ,n.wynikoperacji elementy
    
  from 
      rn p
      inner join rn n on p.r+1 = n.r
)
/* podglad danych do raportu
select * from d
*/
select
     DATEPART(YEAR,DATEADD(YEAR,CASE WHEN (DATEPART(HH,LUD)<6 and 
             DATEPART(dd,LUD)=1 and datepart(mm,lud)=1) THEN -1 ELSE 0 END,lud)) rok
     ,DATEPART(MONTH,DATEADD(MONTH,CASE WHEN (DATEPART(HH,LUD)<6 and DATEPART(dd,LUD)=1) THEN -1 ELSE 0 END,lud)) miesiac
     ,DATEPART(DAY,DATEADD(DAY,CASE WHEN DATEPART(HH,LUD)<6 THEN -1 ELSE 0 END,lud)) dzien
     ,datepart(hour, dateadd(hour,-6,LUD))/8+1 zmiana
     ,sum(case when czas<7 then czas else 0 end) normalna_praca
     ,sum(case when czas between 7 and 60 then czas else 0 end) mikroprzestoje
     ,sum(case when czas>60 then czas else 0 end) przestoje_powyzej_1_min
     ,count(elementy) WyprodukowaneElementy
     ,sum(case when elementy=1 then 1 else 0 end) iloscbledow
from
    d
 group by
        DATEPART(YEAR,DATEADD(YEAR,CASE WHEN (DATEPART(HH,LUD)<6 and DATEPART(dd,LUD)=1 and datepart(mm,lud)=1) THEN -1 ELSE 0 END,lud)), 
	DATEPART(MONTH,DATEADD(MONTH,CASE WHEN (DATEPART(HH,LUD)<6 and DATEPART(dd,LUD)=1) THEN -1 ELSE 0 END,lud)),
	DATEPART(DAY,DATEADD(DAY,CASE WHEN DATEPART(HH,LUD)<6 THEN -1 ELSE 0 END,lud)), 
	datepart(hour, dateadd(hour,-6,LUD))/8+1

http://sqlfiddle.com/#!18/da023/1

Jeszcze raz dzięki za pomoc!

No nie do końca jest poprawnie, w danych masz 12 rekordów, a suma wyprodukowanych elementów daje Ci 11. Zmień w pierwszym wierszu wynikoperacji na 1 i sprawdź jaką masz liczbę błedów i czy jest ona prawidłowa...

Zmień tak:

WITH rn
     AS (
     SELECT LUD,
            CAST(WynikOperacji AS INTEGER) WynikOperacji,
            ROW_NUMBER() OVER(ORDER BY LUD) r
     FROM dt),
     d
     AS (
     SELECT n.lud,
            p.r pr,
            n.r nr,
            DATEDIFF(ms, p.lud, n.lud) / 1000.0 czas,
            CASE
                WHEN p.r = 1
                THEN p.wynikoperacji + n.wynikoperacji
                ELSE n.wynikoperacji
            END bledy
     FROM rn p
          INNER JOIN rn n ON p.r + 1 = n.r)

/* podglad danych do raportu
select * from d
*/

     SELECT DATEPART(YEAR, DATEADD(YEAR,
                                   CASE
                                       WHEN(DATEPART(HH, LUD) < 6
                                            AND DATEPART(dd, LUD) = 1
                                            AND DATEPART(mm, lud) = 1)
                                       THEN-1
                                       ELSE 0
                                   END, lud)) rok,
            DATEPART(MONTH, DATEADD(MONTH,
                                    CASE
                                        WHEN(DATEPART(HH, LUD) < 6
                                             AND DATEPART(dd, LUD) = 1)
                                        THEN-1
                                        ELSE 0
                                    END, lud)) miesiac,
            DATEPART(DAY, DATEADD(DAY,
                                  CASE
                                      WHEN DATEPART(HH, LUD) < 6
                                      THEN-1
                                      ELSE 0
                                  END, lud)) dzien,
            DATEPART(hour, DATEADD(hour, -6, LUD)) / 8 + 1 zmiana,
            SUM(CASE
                    WHEN czas < 7
                    THEN czas
                    ELSE 0
                END) normalna_praca,
            SUM(CASE
                    WHEN czas BETWEEN 7 AND 60
                    THEN czas
                    ELSE 0
                END) mikroprzestoje,
            SUM(CASE
                    WHEN czas > 60
                    THEN czas
                    ELSE 0
                END) przestoje_powyzej_1_min,
            SUM(bledy) iloscbledow,
            MAX(nr) - MIN(pr) + CASE
                                    WHEN MIN(pr) = 1
                                    THEN 1
                                    ELSE 0
                                END WyprodukowaneElementy
     FROM d
     GROUP BY DATEPART(YEAR, DATEADD(YEAR,
                                     CASE
                                         WHEN(DATEPART(HH, LUD) < 6
                                              AND DATEPART(dd, LUD) = 1
                                              AND DATEPART(mm, lud) = 1)
                                         THEN-1
                                         ELSE 0
                                     END, lud)),
              DATEPART(MONTH, DATEADD(MONTH,
                                      CASE
                                          WHEN(DATEPART(HH, LUD) < 6
                                               AND DATEPART(dd, LUD) = 1)
                                          THEN-1
                                          ELSE 0
                                      END, lud)),
              DATEPART(DAY, DATEADD(DAY,
                                    CASE
                                        WHEN DATEPART(HH, LUD) < 6
                                        THEN-1
                                        ELSE 0
                                    END, lud)),
              DATEPART(hour, DATEADD(hour, -6, LUD)) / 8 + 1;

http://sqlfiddle.com/#!18/4b1b9/11

Działa bardzo dobrze.
Pozostaje mi podziękować za szybką i merytoryczną pomoc.