GoldenLine
Zaloguj się
Karol O.

Karol O. Praca nie musi być
nudna - właśnie
takiej zawsze szukam
;-)

Temat: Zapytanie SQL - over (partition by)

Witam,
potrzebuję zapytania SQL, które pozwoli obliczyc 'zmiany ceny' w kolumnie ROZNICA pomiędzy zadanymi miesiącami (wierszami) w poszczególnych kategoriach.
Interesuje mnie rozwiązanie za pomocą funkcji analitycznej "over (partition by)". Żadnych LAG-ów, tworzenia oddzielnych tabel poprzez 'create table', itd.

Docelowa tabela powinna zatem wyglądać następująco:

MIESIAC | TELEFON | CENA | ROZNICA
-----------------------------------------------
1| STAC| 10 | 0
2| STAC| 8 | -2
3| STAC| 3 | -5
4| STAC| 5 | 2
1| KOM | 20 | 0
2| KOM | 15 | -5
3| KOM | 20 | 5
4| KOM | 10 | -10

Macie jakieś pomysły ?

Pozdrawiam.
Link do wypowiedziLink

konto usunięte

Temat: Zapytanie SQL - over (partition by)

Karol Ostas:
Witam,
potrzebuję zapytania SQL, które pozwoli obliczyc 'zmiany ceny' w kolumnie ROZNICA pomiędzy zadanymi miesiącami (wierszami) w poszczególnych kategoriach.
Interesuje mnie rozwiązanie za pomocą funkcji analitycznej "over (partition by)". Żadnych LAG-ów, tworzenia oddzielnych tabel poprzez 'create table', itd.

Docelowa tabela powinna zatem wyglądać następująco:

MIESIAC | TELEFON | CENA | ROZNICA
-----------------------------------------------
1| STAC| 10 | 0
2| STAC| 8 | -2
3| STAC| 3 | -5
4| STAC| 5 | 2
1| KOM | 20 | 0
2| KOM | 15 | -5
3| KOM | 20 | 5
4| KOM | 10 | -10

Macie jakieś pomysły ?

A wejściowa tabela jak wygląda? Domniemywam że chodzi o Oracle. Co masz na myśli pisząc 'Żadnych LAG-ów'.
p.s. ' tworzenia oddzielnych tabel poprzez 'create table', itd. ' - a da się jakoś inaczej ?
Link do wypowiedziLink
Karol Kanicki

Karol Kanicki Freelance BI
consultant @ Icon
plc

Temat: Zapytanie SQL - over (partition by)

da sie

zrob jednostronnego joina tabeli zrodlowej z sama soba, tylko w warunku joina, przy przyrownywaniu miesiaca dodaj miesiac po jednej ze stron warunku zlaczenia
tab1.miesiac = tab2.miesiac + 1
jednostronny join po to, zebys mogl podstawic '0' tam, gdzie sie zlaczenie sie nie uda, jakies coalesce czy cos
a potem nowa kolumne wyliczasz po prostu w stylu tab2.cena - tab1.cena, nie widze potrzeby uzycia 'partition by' [ btw pewnie miales na mysli jakiegos ranka czy row_number, 'partition by' samo raczej nie wystepuje ]
Link do wypowiedziLink
Karol O.

Karol O. Praca nie musi być
nudna - właśnie
takiej zawsze szukam
;-)

Temat: Zapytanie SQL - over (partition by)

@Tomek P.
- tak, Oracle
- wejściowa tabela wygląda identycznie, tylko nie posiada kolumny ROZNICA
- LAG to funkcja, która "przesuwa" wartości o zadaną pozycję i wynik zapisuje do nowej zmiennej, np: http://www.adp-gmbh.ch/ora/sql/analytical/lag.html

Takiego właśnie rozwiązania chciałem jednak uniknąć, bo zależy mi na poznaniu "sztuczek" z funkcją "partition by".

@Karol K.
Dzięki. Tak myślałem, że ktoś poda takie właśnie rozwiązanie. Też o tym myślałem, ale miałem nadzieję, że ktoś pokusi się właśnie o sugerowane przeze mnie "partition by", na którym "podobno" da się jakoś obliczyć te przyrosty. Pytanie tylko jak ? :)
Link do wypowiedziLink
Paweł Grzegorz Kwiatkowski

Paweł Grzegorz Kwiatkowski Architekt
oprogramowania,
Ericsson

Temat: Zapytanie SQL - over (partition by)

Takiego właśnie rozwiązania chciałem jednak uniknąć, bo zależy mi na poznaniu "sztuczek" z funkcją "partition by".

W kontekście zapytań sql, PARTITION BY to nie funkcja, a element klauzuli analitycznej. Ogólna jej forma to:



 funkcja_analityczna  over (klauzula_analityczna)


LAG to jedna z funkcji analitycznych.

Rozumiem, że chcesz rozwiązanie, które korzysta z funkcji funkcji analitycznych, ale różnych niż LEAD/LAG ?

-- edited:
Chyba, że miałeś na myśli modelowanie i PARTITION BY np. w ramach:
http://docs.oracle.com/cd/B19306_01/server.102/b14223/...Paweł Grzegorz Kwiatkowski edytował(a) ten post dnia 04.03.13 o godzinie 13:46
Link do wypowiedziLink
Karol O.

Karol O. Praca nie musi być
nudna - właśnie
takiej zawsze szukam
;-)

Temat: Zapytanie SQL - over (partition by)

@Paweł

Dokładnie tak. Swoją drogą dzięki za uściślenie tematu.

Ps1. Korzystam z narzędzia Oracle SQL Developer (3.1.07) i na nim muszę oprzeć rozwiązanie problemu policzenia przyrostów dla kilku milionów rekordów.

Ps2. Nie mam i nie będę miał uprawnień do tworzenia tabel poprzez CREATE TABLE, dlatego poszukuję innych (wydajnych) rozwiązań a przy okazji chcę się nauczyć czegoś nowego.
Link do wypowiedziLink
Paweł Grzegorz Kwiatkowski

Paweł Grzegorz Kwiatkowski Architekt
oprogramowania,
Ericsson

Temat: Zapytanie SQL - over (partition by)

Szczerze, to nie wiem czy gra warta świeczki w takie kombinowanie ;-)

Przy założeniu, że liczba miesięcy jest skończona (np. <=12), można pokusić się o ponumerowanie rekordów per telefon (i tu skorzystać z funkcji analitycznych) + pivot z wyliczeniem różnic + operacja odwrotna.

Telefon1, Cena_M1, Cena_M2, ..., Cena_M12
...

Co do braku "create table", możesz kombinowac z query factoring", tj. używać konstrukcji:



with 

 tabelka as ( select ...),

tabelka2 as (select ....)

select ... from tabelka, tabelka2;
Link do wypowiedziLink
Karol O.

Karol O. Praca nie musi być
nudna - właśnie
takiej zawsze szukam
;-)

Temat: Zapytanie SQL - over (partition by)

Dzięki, dokładnie w taki sposób jak sugerujesz, radzę sobie z tworzeniem dodatkowych niezbędnych podzapytań i obliczeń. "WITH" już od dawna jest moim wybawieniem z wielu kłopotów.

Też dochodzę do wniosku, że chyba nie ma co za bardzo przekombinowywać i pozostanę przy LAG-u z wyzerowaniem pierwszego rekordu w poszczególnych grupach.

Gdyby ktoś jednak pokusił się o inne rozwiązanie i chciał się nim podzielić to zapraszam do dyskusji.
Link do wypowiedziLink

konto usunięte

Temat: Zapytanie SQL - over (partition by)

dla tabeli w postgresie:

CREATE TABLE test
(
id serial NOT NULL,
miesiac integer,
telefon text,
cena numeric,
CONSTRAINT test_pkey PRIMARY KEY (id )
)

i danych jak podałeś, właściwie proste zapytanie:



select t.*,coalesce(t.cena-t2.cena,0) as roznica

from public.test t

left join public.test t2 ON (t.miesiac=t2.miesiac+1 and t.telefon=t2.telefon)

order by telefon,miesiac


daje wynik dokładnie jak oczekiwany.

Jeśli to nie jest zsumowane to pozostaje zsumować albo zwykłym group by
a jeśli chcesz over to:



select t.*,coalesce(sum(t.cena) over (partition by t.telefon,t.miesiac)-sum(t2.cena) over (partition by t2.telefon,t2.miesiac),0) as roznica

from public.test t

left join public.test t2 ON (t.miesiac=t2.miesiac+1 and t.telefon=t2.telefon)

order by telefon,miesiac


tylko ze dla tej struktury co podałeś to nie ma sensu bo rozbicie po kategori i miesiacu daje nam poprostu wiersz..

Jeśli coś zle zrozumiałem to sorry :)Wojtek Stępień edytował(a) ten post dnia 05.03.13 o godzinie 00:03
Link do wypowiedziLink
Karol O.

Karol O. Praca nie musi być
nudna - właśnie
takiej zawsze szukam
;-)

Temat: Zapytanie SQL - over (partition by)

Dzięki. Takie też rozwiązanie sugerował @Karol K. Pozostałem jednak przy LAG-u ;-)
Link do wypowiedziLink

konto usunięte

Temat: Zapytanie SQL - over (partition by)

faktycznie, nie zauwazylem.. :)
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: Zapytanie SQL - over (partition by)

Hej,
Można jeszcze jakoś tak:



select *

from tabelka

model

partition by (telefon)

dimension by (miesiac)

measures (CENA, 0 as roznica)

rules

(

  roznica[miesiac>1]=cena[cv()]-cena[cv()-1]

);


Nie chciało mi się tworzyć tabelki więc przepraszam za potencjalne literówki :)
Link do wypowiedziLink
Karol O.

Karol O. Praca nie musi być
nudna - właśnie
takiej zawsze szukam
;-)

Temat: Zapytanie SQL - over (partition by)

@Kamil
To rzeczywiście mogło by być ciekawe rozwiązanie, ale niestety na razie sypie mi się błędem:

ORA-01747: niepoprawna specyfikacja użytk.tablica.kolumna, tablica.kolumna lub kolumna
01747. 00000 - "invalid user.table.column, table.column, or column specification"

Próbowałem coś poczytać o tym na necie i pozmieniać kod, ale nic mi z tego dotychczas nie wyszło.
Link do wypowiedziLink
Piotr B.

Piotr B. IT

Temat: Zapytanie SQL - over (partition by)

proste zapytanie i wynik dokladnie jak oczekiwany

select a.MIESIAC, a.TELEFON, a.CENA, nvl((a.CENA - b.CENA ),0) ROZNICA
from tabelka a, tabelka b
where b.MIESIAC(+)=a.MIESIAC-1
and b.TELEFON(+)=a.TELEFON
order by a.TELEFON desc, a.MIESIAC;
Link do wypowiedziLink
Karol O.

Karol O. Praca nie musi być
nudna - właśnie
takiej zawsze szukam
;-)

Temat: Zapytanie SQL - over (partition by)

@Piotr
Oczywiście, że tak. Ten sposób już był przerabiany powyżej dwukrotnie.
Rozwiązanie z LAG również działa bez zarzutu.

Teraz "molestujemy" się, aby wyjść na wynik w inny, nietypowy sposób, który pozwoli na poznanie dodatkowych możliwości i tricków z SQL-a ;-)
Link do wypowiedziLink
Tomasz Poradowski

Tomasz Poradowski Specjalista od
wytwarzania
oprogramowania

Temat: Zapytanie SQL - over (partition by)

Karol Ostas:
Teraz "molestujemy" się, aby wyjść na wynik w inny, nietypowy sposób, który pozwoli na poznanie dodatkowych możliwości i tricków z SQL-a ;-)

To może tak:



SELECT t.*,

       nvl(cena - first_value(cena)

           over(PARTITION BY telefon ORDER BY miesiac RANGE BETWEEN 1 preceding AND 1 preceding),

           0) roznica

  FROM koszty t

 ORDER BY telefon,

          miesiac
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: Zapytanie SQL - over (partition by)

@Piotr
Zapytanie z self join'em na dużej ilości wierszy będzie skutkować hash join'em, który może się zakończyć multipass'em ze względu na ograniczoną ilość miejsca w PGA (odgórnie przez _pga_max_size). Funkcje analityczne i modelowanie SQL pozwalają na uniknięcie tego problemu.

@Karol
Dla tabeli 


select miesiac, telefon, cena

from tabelka;


i danych z Twojego posta, zapytanie:



select *

from tabelka

model

partition by (telefon)

dimension by (miesiac)

measures (CENA, 0 as roznica)

rules

(

  roznica[miesiac>1]=cena[cv()]-cena[cv()-1]

);


Działa bez problemów.Kamil Stawiarski edytował(a) ten post dnia 07.03.13 o godzinie 19:27
Link do wypowiedziLink
Karol O.

Karol O. Praca nie musi być
nudna - właśnie
takiej zawsze szukam
;-)

Temat: Zapytanie SQL - over (partition by)

@Tomasz
Świetne, właśnie o takim rozwiązaniu myślałem pisząc swojego posta na GL. Wiedziałem, że się da używając PARTITION BY... Nie wiedziałem jak :) Dzięki.

@Kamil
Bardzo dziękuję za Twoje zaangażowanie, pomysł i ponowne przetestowanie zapytania. Oczywiście działa ono bez zarzutu, daje poprawny wyniki i jest bardzo ciekawe !
Nie wiem jakim cudem udało mi się sknocić poprzednio jego wykonanie :)
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

[SQL] Zapytanie TSQL z wyko...




Wyślij zaproszenie do
Anuluj