Oracle: PARTITION BY, row_number() i suma
Leszek Pachura - 02-02-2007 00:09
Oracle: PARTITION BY, row_number() i suma
Taki problem.
Jest sobie tabela TAB z polami GR, ORD i VAL. Primary key to (GR, ORD).
Chce teraz - w ramach kazdej wartosci GR - wyciagnac dwa rekordy z najmniejszym
ORD - po czym zsumowac ich VAL.
Przykladowo, dla takich GR,ORD,VAL:
A,1,10
A,2,20
A,3,40
A,4,80
B,5,100
B,7,3
B,9,17
C,1,123
....powinienem otrzymac wynik:
A,30 (= 10 + 20 dla ORD = 1 i 2)
B,103 (= 100 + 3 dla ORD = 5 i 7)
C,123 (tylko jeden rekord)
Wyplodzilem takowe zapytanie:
select GR, sum(VAL)
from (
select GR, VAL, row_number() over (partition by GR order by ORD asc) as RN
from TAB
)
where RN <= 2
group by GR
....ktore nawet dziala, ale nie podoba mi sie w nim to ze Oracle jest zmuszany
do dwukrotnego sortowania po GR (raz przy partition by, raz przy group by). Tym
bardziej ze w rzeczywistosci moje zapytanie jest o wiele bardziej zlozone.
Czy da sie to jakos uproscic - uniknac podzapytania, albo przynajmniej
grupowania na zewnatrz? Czytalem ze mozna zrobic SUM OVER PARTITION, ale zakres
sumowanych rekordow w ramach aktualnego "window" jest chyba zawsze relatywny w
stosunku do aktualnego rekordu - nie mozna chyba zrobic "sum(VAL) over
(partition by GR order by ORD asc rows between 1 and 2)", ani tez umiescic
warunku "row_number()... <= 2" w klauzuli WHERE.
....?
--
Wysłano z serwisu OnetNiusy: http://niusy.onet.pl
=?ISO-8859-2?Q?Micha=B3?= Kuratczyk - 02-02-2007 00:09
Leszek Pachura wrote:
> Jest sobie tabela TAB z polami GR, ORD i VAL. Primary key to (GR, ORD).
Naprawdę łatwiej Ci było to napisać słowami niż przekleić SQLa?
> Przykladowo, dla takich GR,ORD,VAL:
> A,1,10
> A,2,20
[...]
I znowu - jakby był wcześniej CREATE TABLE, a tutaj INSERT, to bym sobie
uruchomił to u siebie i pokombinował. A tak? Nie chce mi się...
> select GR, sum(VAL)
> from (
> select GR, VAL, row_number() over (partition by GR order by ORD asc) as
> RN
> from TAB
> )
> where RN <= 2
> group by GR
>
> ...ktore nawet dziala, ale nie podoba mi sie w nim to ze Oracle jest
> zmuszany do dwukrotnego sortowania po GR (raz przy partition by, raz przy
> group by). Tym bardziej ze w rzeczywistosci moje zapytanie jest o wiele
> bardziej zlozone.
Czy sprawdziłeś, że to wolno działa, czy tylko masz złe przeczucia?
Sprawdzałeś plan? Indeksy? Poza tym jeśli zapytanie w rzeczywistości jest
zupełnie inne, to optymalizowanie tego może nie mieć żadnego sensu...
--
Michał Kuratczyk
Leszek Pachura - 02-02-2007 00:09
> Leszek Pachura wrote:
> > Jest sobie tabela TAB z polami GR, ORD i VAL. Primary key to (GR, ORD).
> Naprawdę łatwiej Ci było to napisać słowami niż przekleić SQLa?
Ale sie czepiasz, drogi kolego. Newsy czytaja ludzie, wydaje mi sie ze slowny
opis lepiej naswietla problem niz copy-paste pieciostronnicowego zapytania,
pelnego dziwnobrzmiacych nazw kolumn i srednio sformatowanego. Do tego
jeszcze DDL-e do wszystkich tabel bioracych w nim udzial, indeksy, dane...
Zreszta, takiemu wymiataczowi jak Ty zalozenie trzykolumnowej
tabeli zajeloby mniej czasu niz odpisywanie na mojego posta.
> Czy sprawdziłeś, że to wolno działa, czy tylko masz złe przeczucia?
To bylo pytanie raczej o skladnie SQL-a Oracle'a, a nie o wydajnosc. Zreszta
i tak nie dysponujesz danymi ktore mam w bazie zeby pod nie optymalizowac,
a tych ktore zamiescilem jest na tyle niewiele ze optymalizator moze
spokojnie wybierac full table scan.
--
Wysłano z serwisu OnetNiusy: http://niusy.onet.pl
Lucyna Witkowska - 03-02-2007 00:01
Leszek Pachura <pachuraWYTNIJTO@op.pl> napisał:
> Wyplodzilem takowe zapytanie:
> select GR, sum(VAL)
> from (
> select GR, VAL, row_number() over (partition by GR order by ORD asc) as RN
> from TAB
> )
> where RN <= 2
> group by GR
> ...ktore nawet dziala, ale nie podoba mi sie w nim to ze Oracle jest zmuszany
> do dwukrotnego sortowania po GR (raz przy partition by, raz przy group by). Tym
> bardziej ze w rzeczywistosci moje zapytanie jest o wiele bardziej zlozone.
> Czy da sie to jakos uproscic - uniknac podzapytania, albo przynajmniej
> grupowania na zewnatrz? Czytalem ze mozna zrobic SUM OVER PARTITION, ale zakres
> sumowanych rekordow w ramach aktualnego "window" jest chyba zawsze relatywny w
> stosunku do aktualnego rekordu - nie mozna chyba zrobic "sum(VAL) over
> (partition by GR order by ORD asc rows between 1 and 2)", ani tez umiescic
> warunku "row_number()... <= 2" w klauzuli WHERE.
Mozna zrobic sum(val) over (partition by GR order by ORD asc rows between
current row and 1 following) i wybierac tylko wiersze gdzie
row_number()=1, ale z powodu ograniczenia z WHERE bez podzapytania sie
IMO nie obejdzie.
Pozdrowienia,
LW
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
Oracle, SQL, PL/SQL. Jak =?ISO-8859-2?Q?napisa=E6_zapytanie=2C?==?ISO-8859-2?Q?_kt=F3re_zwr=F3ci_nazw=EA_atrybutu=2C_kt=F3reg o?==?ISO-8859-2?Q?_warto=B6ci_spe=B3niaj=B1_zadany_warunek?=
Oracle 19g +Insert +Insert +Insert...
[oracle] zapytanie dynamiczne z =?ISO-8859-2?Q?=22dynamiczn=B1_?==?ISO-8859-2?Q?nazw=B1_tabeli=22?=
[Oracle] jak =?ISO-8859-2?Q?ograniczy=E6_pami=EA=E6_dla_se?==?ISO-8859-2?Q?rwera=3F?=
=?ISO-8859-2?Q?=5BOT=5D_Zdany_egzamin_Oracle_1Z0-007_a?==?ISO-8859-2?Q?_brak_informacji_na_stronie_Prometric_-_czy?==?ISO-8859-2?Q?_co=B6_nie_tak=3F?=
[oracle] czy da =?ISO-8859-2?Q?si=EA_z_poziomu_procedury_?==?ISO-8859-2?Q?zrobi=E6_kopi=EA_zapasow=B1=3F?=
[oracle 10g] czy =?ISO-8859-2?Q?mo=BFna_wy=B3=B1czy=E6_wszys?==?ISO-8859-2?Q?tkie_wi=EAzy_w_schemacie=3F?=
MSSQL Express czy Oracle Express
=?iso-8859-2?q?[oracle]_Jak_sprawdzi=E6_wielko=B6=E6_tabeli_=3F=3F?=
=?ISO-8859-2?Q?Poszukjue_ksi=B1=BFki_"Oracle_?= =?ISO-8859-2?Q?optymalizacja_wydajno=B6ci"..?=
zanotowane.pldoc.pisz.plpdf.pisz.plptsite.xlx.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
