Słowo kluczowe Oracle 'Partition By' i 'Row Number'

PARTITION BY segreguj zestawy, dzięki czemu możesz pracować(ROW_NUMBER(),COUNT(),SUM(),itp.) na powiązanych zestawach niezależnie.

W zapytaniu powiązany zestaw składa się z wierszy o podobnym cdt.country_code, cdt.konto, cdt.waluta. Kiedy partycjonujesz na tych kolumnach i stosujesz na nich numer ROW_NUMBER. Pozostałe Kolumny na tej kombinacji / zestawie otrzymają numer sekwencyjny od ROW_NUMBER

Ale to zapytanie jest śmieszne, jeśli twoja partycja przez jakieś unikalne Dane i umieścić numer row_number na nim, będzie po prostu produkować ten sam numer. To tak, jakbyś robił zamówienie na partycji, która jest gwarantowana jako unikalna. Przykład, pomyśl o GUID jako unikalnej kombinacji cdt.country_code, cdt.account, cdt.currency

newid() produkuje GUID, więc czego oczekujesz od tego wyrażenia?

select
   hi,ho,
   row_number() over(partition by newid() order by hi,ho)
from tbl;

...Tak, wszystkie partycjonowane (żaden nie był partycjonowany, każdy wiersz jest partycjonowany w swoim własnym wierszu) rows ' row_numbers są ustawione na 1

Zasadniczo powinieneś podzielić na nie-unikalne kolumny. ORDER BY on OVER potrzebował partycji BY, aby mieć unikalną kombinację, w przeciwnym razie wszystkie liczby wierszy staną się 1

create table tbl(hi varchar, ho varchar);

insert into tbl values
('A','X'),
('A','Y'),
('A','Z'),
('B','W'),
('B','W'),
('C','L'),
('C','L');

To jest analogiczne do twojego zapytania:

select
   hi,ho,
   row_number() over(partition by hi,ho order by hi,ho)
from tbl;

Jaki będzie wynik tego?

HI  HO  COLUMN_2
A   X   1
A   Y   1
A   Z   1
B   W   1
B   W   2
C   L   1
C   L   2

Widzisz połączenie HI HO? Pierwsze trzy wiersze mają unikalną kombinację, stąd są one ustawione na 1, wiersze B mają takie same W, stąd różne liczby wierszy, podobnie z wierszami HI C.

Teraz, dlaczego jest ORDER BY potrzebne tam? Jeśli poprzedni programista chce tylko umieścić numer wiersza na podobnych danych (np. HI B, wszystkie dane są B-W, B-W), może to zrobić:

select
   hi,ho,
   row_number() over(partition by hi,ho)
from tbl;

Ale niestety, Oracle (i SQL Server też) nie zezwala na partycję bez ORDER BY; podczas gdy w Postgresql, {[18] } na partycji jest opcjonalne: http://www.sqlfiddle.com/#!1/27821/1

select
   hi,ho,
   row_number() over(partition by hi,ho)
from tbl;

Twój ORDER BY na Twojej partycji wygląda trochę zbędnie, nie z powodu winy poprzedniego dewelopera, niektóre bazy danych po prostu nie pozwalają PARTITION bez ORDER BY, może nie być w stanie znaleźć dobrej kolumny kandydata do sortowania. Jeśli zarówno partycja według kolumn, jak i kolejność według kolumn są takie same, Usuń ORDER BY, ale ponieważ niektóre bazy danych na to nie pozwalają, możesz to zrobić: {]}

SELECT cdt.*,
        ROW_NUMBER ()
        OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currency
              ORDER BY newid())
           seq_no
   FROM CUSTOMER_DETAILS cdt

Nie możesz znaleźć dobrej kolumny do sortowania podobnych danych? Równie dobrze możesz sortować losowo, partycjonowane dane mają te same wartości w każdym razie. Możesz użyć GUID na przykład (używasz newid() dla SQL Server). Tak, że ma to samo wyjście wykonane przez poprzedniego dewelopera, szkoda, że niektóre bazy danych nie pozwalają PARTITION Z Nie ORDER BY

Chociaż naprawdę, umyka mi to i nie mogę znaleźć dobrego powodu, aby umieścić numer na tych samych kombinacjach(B-W, B - W w przykładzie powyżej). Sprawia wrażenie, że baza danych ma nadmiarowe dane. Jakoś mi to przypomniało: jak zdobyć jeden unikalny rekord z tej samej listy rekordów z tabeli? Brak unikalnych ograniczeń w tabeli

To naprawdę wygląda tajemnie widząc partycję BY z tą samą kombinacją kolumn z ORDER BY, nie można łatwo wywnioskować intencji kodu.

Test na żywo: http://www.sqlfiddle.com/#!3/27821/6

Ale jak dbaseman zauważył również, bezużyteczne jest dzielenie i porządkowanie na tych samych kolumnach.

Masz taki zestaw danych:

create table tbl(hi varchar, ho varchar);

insert into tbl values
('A','X'),
('A','X'),
('A','X'),
('B','Y'),
('B','Y'),
('C','Z'),
('C','Z');

Potem dzielisz przez hi, ho; a potem zamawiasz przez hi, ho. Nie ma sensu numerować podobnych danych :-) http://www.sqlfiddle.com/#! 3 / 29ab8/3

select
   hi,ho,
   row_number() over(partition by hi,ho order by hi,ho) as nr
from tbl;

Wyjście:

HI  HO  ROW_QUERY_A
A   X   1
A   X   2
A   X   3
B   Y   1
B   Y   2
C   Z   1
C   Z   2

Wystarczy użyć partycji na nie-unikalnej kolumnie, a następnie posortować na nie-unikalnych kolumnach unikalną - ING kolumnę. Przykład sprawi, że będzie to bardziej jasne:

create table tbl(hi varchar, ho varchar);

insert into tbl values
('A','D'),
('A','E'),
('A','F'),
('B','F'),
('B','E'),
('C','E'),
('C','D');

select
   hi,ho,
   row_number() over(partition by hi order by ho) as nr
from tbl;

PARTITION BY hi Działa na nie unikalnych kolumnach, a następnie na każdej podzielonej kolumnie, można order na unikalnej kolumnie (ho), ORDER BY ho

Wyjście:

HI  HO  NR
A   D   1
A   E   2
A   F   3
B   E   1
B   F   2
C   D   1
C   E   2

Ten zestaw danych ma większy sens

Test na żywo: http://www.sqlfiddle.com/#! 3 / d0b44/1

I jest to podobne do twojego zapytania z tymi samymi kolumnami na obu partycjach by i ORDER BY:

select
   hi,ho,
   row_number() over(partition by hi,ho order by hi,ho) as nr
from tbl;

A to jest ouput:

HI  HO  NR
A   D   1
A   E   1
A   F   1
B   E   1
B   F   1
C   D   1
C   E   1

Test na żywo: http://www.sqlfiddle.com/#! 3 / d0b44/3

Wreszcie to może być prawo zapytanie:

SELECT cdt.*,
     ROW_NUMBER ()
     OVER (PARTITION BY cdt.country_code, cdt.account -- removed: cdt.currency
           ORDER BY 
               -- removed: cdt.country_code, cdt.account, 
               cdt.currency) -- keep
        seq_no
FROM CUSTOMER_DETAILS cdt