[SQL] Suma z pięciu ostatnich dostępnych w bazie dni

Mam pytanie. Zastanawiam się, czy jest możliwe stworzenie takiego zapytania, które będzie mi liczyło (może też tworzyć nową tabelę) sumę z 5 ostatnich dni w bazie.

Może lepiej to będzie zilustrowane na przykładzie.
Powiedzmy, ze w bazie dostępne są dane na dni 3,4,7,8,9,10 sierpnia.
To chciałbym, na każdy dzień zliczało mi sumę z 5 ostatnich dni (włącznie). Tj. na dzień 9 sierpnia będzie suma dla 9,8,7,4,3. Dla 10 sierpnia będzie kolejno suma dla 10,9,8,7,6..itd.
Oczywiście dla 3, 4,7 i 8 nie policzy, bo wstecz nie ma 5 dni.

Czy coś takiego jest możliwe?

aleks.mm napisał(a):

Czy coś takiego jest możliwe?

Wszystko jest możliwe. Sky is the limit.

Zainteresuj się funkcjami agregującymi oraz porównywaniem dat w WHERE.

Oczywiście znam "SUM" :)
Bardziej chodziło mi o to jak rozwiązać "problem" pływającego" zakresu dat.
Żeby rekord dla 10 sierpnia pokazywał sumę "Kwoty" z 10,9,8,7,6.

Już napisałem, żebyś zainteresował się porównywaniem dat w funkcji WHERE jeżeli to ma być 5 ostatnich dni kalendarzowych. Jeżeli to ma być 5 ostatnich dat, nawet jeżeli nie są z 5 ostatnich dni kalendarzowych, to wystarczy skorzystać z podzapytania w którym będzie użyty LIMIT (albo TOP w przypadku SQL Server) i DISTINCT.

Ok, mógłbyś podać jakiś przykład, albo źródło gdzie coś takiego jest wyjaśnione?

W Google jest wszystko, wpisz LIMIT i nazwę swojej bazy danych. Oprócz tego pamiętaj o DISTINCT.

Chce się tylko upewnić, czy dobrze się rozumiemy.
Dla pojedynczego dnia jestem w stanie i umiem to zrobić.

Ale chciałbym to zrobić jednym zapytaniem dla np. roku. Bo siedzę od jakiegoś czasu i nie umiem sobie poradzić właśnie z tym sumowaniem po "pływającym" zakresie 5 dni.

W załączniku starałem się pokazać sens.

GOOGLE: SQL BETWEEN

Wydaje mi się, że można coś podobnego osiągnąć z pomocą funkcji analitycznej / analytic function 'sum' (w SQL Server nazywa się to 'funkcja okna / window function).

Np. jeżeli kolumna 'quote' zawiera kwoty a kolumna 'day' zawiera daty,

qoute  day                         window_sum
60	  16-JUL-17 20:39:41	   60
60  	  24-JUL-17 20:39:41	    60
60 	  26-JUL-17 20:39:41	  120
60	  27-JUL-17 20:39:41	  180
60	  28-JUL-17 20:39:41	  240
60	  31-JUL-17 20:39:41	  240
50	  01-AUG-17 20:39:41   230
40	  03-AUG-17 20:39:41	  150
30	  04-AUG-17 20:39:41	  180
20	  06-AUG-17 20:39:41	  140
12	  08-AUG-17 20:39:41  123
11	  08-AUG-17 20:39:41	  123
10	  08-AUG-17 20:39:41	  123

to coś takiego:

select      quote, day, 
               sum(quote) over (order by day range between interval '5' day preceding and interval '0' day preceding) window_sum
from         t1 
order by   day;

zwróci w kolumnie window_sum sumę za ostatnich 5 dni.
W swoim przykładzie podajesz dodatkowy podział na kategorie a, b, c, w czym może być pomocny 'partition by'.

W.P.

--tabelka testowa
create table #test
(
id int

)

insert into #test (id) values(1)
insert into #test (id) values(2)
insert into #test (id) values(3)
insert into #test (id) values(4)
insert into #test (id) values(7)
insert into #test (id) values(8)
insert into #test (id) values(9)
insert into #test (id) values(10)

--wlasciwe zapytanie
select
t.id
,(select sum(id) from #test  where id < t.id) as summ
 from #test t

drop table #test

Cześć, to jest rozwiązanie, ale bez opcji brania tylko 5 ostatnich dni. Żeby to jeszcze ogarnąć trzeba pokombinować z podzapytaniem i funkcją TOP

Wesoły Pomidor napisał(a):

Wydaje mi się, że można coś podobnego osiągnąć z pomocą funkcji analitycznej / analytic function 'sum' (w SQL Server nazywa się to 'funkcja okna / window function).

Np. jeżeli kolumna 'quote' zawiera kwoty a kolumna 'day' zawiera daty,

qoute  day                         window_sum
60	  16-JUL-17 20:39:41	   60
60  	  24-JUL-17 20:39:41	    60
60 	  26-JUL-17 20:39:41	  120
60	  27-JUL-17 20:39:41	  180
60	  28-JUL-17 20:39:41	  240
60	  31-JUL-17 20:39:41	  240
50	  01-AUG-17 20:39:41   230
40	  03-AUG-17 20:39:41	  150
30	  04-AUG-17 20:39:41	  180
20	  06-AUG-17 20:39:41	  140
12	  08-AUG-17 20:39:41  123
11	  08-AUG-17 20:39:41	  123
10	  08-AUG-17 20:39:41	  123

to coś takiego:

select      quote, day, 
               sum(quote) over (order by day range between interval '5' day preceding and interval '0' day preceding) window_sum
from         t1 
order by   day;

zwróci w kolumnie window_sum sumę za ostatnich 5 dni.
W swoim przykładzie podajesz dodatkowy podział na kategorie a, b, c, w czym może być pomocny 'partition by'.

W.P.

Super, podoba mi się to rozwiązanie, jednak są jeszcze dwie kwestie, które muszę przeskoczyć:

1. Rozumiem, że interval 5 będzie zwracał 5 ostatnich dni kalendarzowych, prawda? Ja niestety potrzebuję 5 ostatnich dni, które są dostępne w tabeli z której będziemy wyliczali sumę (zdecydowanie nie zawsze będą to dni kalendarzowe następujące po sobie - czyli jak w bazie będzie 3,4,7,8,9,10 sierpnia to na 9 sierpnia ma sumować 9+8+7+4+3).
2. Niestety tymczasowo muszę rozwiązać ten problem w Accessie, a widzę, że tam nie ma tej funkcji, prawda? :(

aleks.mm napisał(a):

2. Niestety tymczasowo muszę rozwiązać ten problem w Accessie, a widzę, że tam nie ma tej funkcji, prawda? :(

Zgadza się, podane rozwiązanie będzie działać tylko na MSSQL od wersji 2012 w górę, to taki cukier składniowy. W Access musisz skorzystać z podzapytania w którym za pomocą TOP 5 możesz wyciągnąć pięć ostatnich dat.

No tak, ale właśnie cały szkopuł w tym, że nie wiem jak to zrobić, aby to było płynne.

Nie chodzi mi o jednorazowe wyciągnięcie 5 dat, bo to wiem jak zrobić. Mogę to także zrobić inner joinem.

Chodzi o to, że nie będę przecież "ręcznie" liczył 365 dni.

Jeżeli masz jakiś pomysł jak to osiągnąć to podpowiedz jak. Znam działanie TOP, ale na chwilę obecną nie mogę sobie zwizualizować tego jak miałoby to wyglądać.

aleks.mm napisał(a):

Znam działanie TOP, ale na chwilę obecną nie mogę sobie zwizualizować tego jak miałoby to wyglądać.

Zamiast sobie wizualizować, napisz zapytanie, które będzie korzystać z podzapytania w warunku WHERE.

Jednak cały czas nie rozumiem w jaki sposób ma to działać w tym WHERE. Jak mam to napisać, aby dla każdego dnia przesuwał się zakres 5-cio dniowy.

Poza tym, działa Wam w Accessie podzapytanie w WHERE? Bo wprawdzie nie wywala mi błędu, ale też nie dostaję wyniku (wieczne "wykonywanie kwerendy"). Próbowałem nie tylko w tym kontekście, ale także innych, znacznie prostszych.

Napisz co już masz, jak te zapytanie wygląda.

Jako warunek w WHERE mógłbym wstawić:

SELECT TOP 5 S1.DATA
FROM (SELECT DISTINCT data_s AS DATA FROM tbl_S ORDER BY data_s DESC)  AS S1;

Ale:

1. Jest to rozwiązanie na najświeższą, ostatnią wstępującą w bazie datę
2. W Accessie nie działają mi podzapytania w WHERE, dlatego radzę sobie zastępczo INNER JOINem.

Mozna np. jak poniżej w MS Access:

parameters @data date;


SELECT 
@data as data
,a.Typ as typ
,Sum(a.Kwota)  as suma_kwot

FROM
Arkusz2 as a

WHERE
a.Data IN
(
SELECT TOP 5
*
FROM
(
SELECT DISTINCT
a.Data

FROM
Arkusz2 as a

WHERE
a.Data <= @data


ORDER BY
a.Data DESC
)

)

GROUP BY
a.Typ

Ok, ale to wciąż rozwiązanie dla poszczególnego, jednego wybranego dnia.
A mnie zależy na tym, abym mógł obsłużyć wiele dni jednym zapytaniem.

Wywal ten warunek z parametrem

a.Date <= @date

Haskell napisał(a):

Wywal ten warunek z parametrem

a.Date <= @date

Nic to nie da.

aleks.mm napisał(a):

Haskell napisał(a):

Wywal ten warunek z parametrem

a.Date <= @date

Nic to nie da.
Jak to Ci nie da? Przecież parametr "@data" możesz ustawić sobie na początku zapytania. np set @data = '2017-08-09'. Wtedy zapytanie masz dla dowolnego dnia jaki sobie wybierzesz

aleks.mm napisał(a):

Nic to nie da.

Wywal ten warunek na datę, a zamiast tego daj warunek na klienta. W ten sposób będziesz miał 5 ostatnich dat, dla każdego klienta osobno.

polgol napisał(a):

aleks.mm napisał(a):

Haskell napisał(a):

Wywal ten warunek z parametrem

a.Date <= @date

Nic to nie da.
Jak to Ci nie da? Przecież parametr "@data" możesz ustawić sobie na początku zapytania. np set @data = '2017-08-09'. Wtedy zapytanie masz dla dowolnego dnia jaki sobie wybierzesz

Mam wrażenie, że cały czas się nie rozumiemy. Ja nie chcę wybierać daty. Ja chcę to policzyć dla wszystkich dat dostępnych w bazie (ew. wybranego zakresu, przykładowo miesiąca, kwartału, roku, itp.).
Chcę, żeby zapytanie wyrzucało wszystkie daty z bazy i dla każdej z nich (oprócz tych początkowych, bo siłą rzeczy nie będzie 5 poprzednich) obliczyło sumę z 5 ostatnich dni. Już abstrahując od podziału na (w tym wypadku) TYP.

W sample_v2 dodałem Arkusz2, najprościej pokazane. Powiedzmy, że daty mam już posumowane, tak jak w arkuszu.

http://sqlfiddle.com/#!17/1678d/7

Marcin.Miga napisał(a):

http://sqlfiddle.com/#!17/1678d/7

Na podstawie rozwiązania Marcin.Miga dostosowane jedynie do Arkusza2
http://sqlfiddle.com/#!17/39012/15

No tak, tylko nie pamiętam, czy Excel/Access dopuszczają Count(DISTINCT ...) bo coś mi świta, że nie :(

Marcin.Miga napisał(a):

No tak, tylko nie pamiętam, czy Excel/Access dopuszczają Count(DISTINCT ...) bo coś mi świta, że nie :(

Można obejść w MS Access np tak.

INTO v_daty
FROM Arkusz2 as s1
ORDER BY s1.data```

Marcin.Miga napisał(a):

No tak, tylko nie pamiętam, czy Excel/Access dopuszczają Count(DISTINCT ...) bo coś mi świta, że nie :(

Niestety racja, takiej operacji Access nie zrobi :(

Moim pomysłem jest stworzenie tabeli, (2 kolumny) która wyciągnie daty po distinct i doda RowNumber zaczynający się od zera. Jednak nie mogę teraz odszukać w necie sposobu na RowNumber w Access (jeszcze zaczynający się od 0).
A widzę, że będzie potrzebny nie tylko w tym kroku, bo w waszych przykładach występuje "ID"

Jakby się to udało to LEFT JOIN do tabeli źródłowej..

ID zupełnie nie jest potrzebny do tego zadania. Stworzyłem je sobie, bop lubię je mieć, a poza tym łatwiej mi sprawdzić poprawność. (i jest to dobra praktyka).
Zmodyfikowałem VIEW (dla ciebie to kwerenda), tak by nie korzystało z Count(DISTINCT ...)
http://sqlfiddle.com/#!17/678e7/2
Jakbyś czegoś nie rozumiał, skąd się co bierze, to chętnie wytłumaczę. Aha, i poprawiłem mały błąd we VIEW