Słowo kluczowe „Partition By” Oracle

Czy ktoś może wyjaśnić, co partition byrobi słowo kluczowe, i podać prosty przykład tego działania, a także dlaczego warto go użyć? Mam zapytanie SQL napisane przez kogoś innego i staram się dowiedzieć, co on robi.

Przykład partycji według:

SELECT empno, deptno, COUNT(*) 
OVER (PARTITION BY deptno) DEPT_COUNT
FROM emp

Przykłady, które widziałem w Internecie, wydają się zbyt dogłębne.

PARTITION BYKlauzula określa zakres rekordów, które będą używane dla każdego „grupa” w OVERklauzuli.

W twoim przykładzie SQL DEPT_COUNTzwraca liczbę pracowników w tym dziale dla każdego rekordu pracownika. (To tak, jakby cofnąć nominalizację empstołu; nadal zwracasz każdy rekord w emptabeli).

emp_no  dept_no  DEPT_COUNT
1       10       3
2       10       3
3       10       3 <- three because there are three "dept_no = 10" records
4       20       2
5       20       2 <- two because there are two "dept_no = 20" records

Gdyby istniała inna kolumna (np. state), Możesz policzyć, ile departamentów w tym stanie.

To jak uzyskiwanie wyników GROUP BY( SUM, AVGitp.) Bez agregowania zestawu wyników (tj. Usuwania pasujących rekordów).

Jest to przydatne, gdy używasz funkcji LAST OVERlub, MIN OVERaby na przykład uzyskać najniższą i najwyższą pensję w dziale, a następnie użyć jej w obliczeniach w stosunku do tej pensji bez subselekcji, co jest znacznie szybsze.

Przeczytaj link do artykułu AskTom, aby uzyskać więcej informacji.

Koncepcja jest bardzo dobrze wyjaśniona przez przyjętą odpowiedź, ale uważam, że im więcej widzisz, tym lepiej się w nim zatapia. Oto przykład przyrostowy:

1) Szef mówi „zdobądź dla mnie liczbę artykułów w magazynie pogrupowanych według marki”

Mówisz : „bez problemu”

SELECT 
      BRAND
      ,COUNT(ITEM_ID) 
FROM 
      ITEMS
GROUP BY 
      BRAND;

Wynik:

+--------------+---------------+
|  Brand       |   Count       | 
+--------------+---------------+
| H&M          |     50        |
+--------------+---------------+
| Hugo Boss    |     100       |
+--------------+---------------+
| No brand     |     22        |
+--------------+---------------+

2) Szef mówi „Teraz podaj mi listę wszystkich produktów, wraz z ich marką ORAZ liczbą przedmiotów, które posiada dana marka”

Możesz spróbować:

 SELECT 
      ITEM_NR
      ,BRAND
      ,COUNT(ITEM_ID) 
 FROM 
      ITEMS
 GROUP BY 
      BRAND;

Ale dostajesz:

ORA-00979: not a GROUP BY expression 

Oto, co OVER (PARTITION BY BRAND)przychodzi:

 SELECT 
      ITEM_NR
      ,BRAND
      ,COUNT(ITEM_ID) OVER (PARTITION BY BRAND) 
 FROM 
      ITEMS;

Które oznacza:

• COUNT(ITEM_ID) - uzyskać liczbę przedmiotów
• OVER - Nad zestawem rzędów
• (PARTITION BY BRAND) - które mają tę samą markę

Rezultat to:

+--------------+---------------+----------+
|  Items       |  Brand        | Count()  |
+--------------+---------------+----------+
|  Item 1      |  Hugo Boss    |   100    | 
+--------------+---------------+----------+
|  Item 2      |  Hugo Boss    |   100    | 
+--------------+---------------+----------+
|  Item 3      |  No brand     |   22     | 
+--------------+---------------+----------+
|  Item 4      |  No brand     |   22     | 
+--------------+---------------+----------+
|  Item 5      |  H&M          |   50     | 
+--------------+---------------+----------+

itp...

Jest to rozszerzenie SQL o nazwie analityka. „Over” w instrukcji select mówi wyroczni, że funkcja jest funkcją analityczną, a nie grupą według funkcji. Zaletą korzystania z analityki jest to, że można zbierać sumy, liczby i wiele więcej za pomocą tylko jednego przejścia danych zamiast zapętlania danych za pomocą subselekcji lub, co gorsza, PL / SQL.

Z początku wydaje się to mylące, ale szybko stanie się drugą naturą. Nikt nie wyjaśnia tego lepiej niż Tom Kyte. Powyższy link jest świetny.

Oczywiście czytanie dokumentacji jest koniecznością.

EMPNO     DEPTNO DEPT_COUNT

 7839         10          4
 5555         10          4
 7934         10          4
 7782         10          4 --- 4 records in table for dept 10
 7902         20          4
 7566         20          4
 7876         20          4
 7369         20          4 --- 4 records in table for dept 20
 7900         30          6
 7844         30          6
 7654         30          6
 7521         30          6
 7499         30          6
 7698         30          6 --- 6 records in table for dept 30

Tutaj zaczynamy liczyć na odpowiednie deptno. Jeśli chodzi o deptno 10, mamy 4 rekordy w tabeli emp podobne wyniki dla deptno 20 i 30 również.

słowo kluczowe ponad partycją jest tak, jakbyśmy partycjonowali dane, tworząc identyfikator_klienta podzbiorem każdego identyfikatora klienta

select client_id, operation_date,
       row_number() count(*) over (partition by client_id order by client_id ) as operationctrbyclient
from client_operations e
order by e.client_id;

to zapytanie zwróci liczbę operacji wykonanych przez identyfikator_klienta

Myślę, że ten przykład sugeruje niewielki niuans dotyczący sposobu partycjonowania i sposobu grupowania według. Mój przykład pochodzi z Oracle 12, jeśli mój przykład jest błędem kompilacyjnym.

Próbowałem :

SELECT t.data_key
,      SUM ( CASE when t.state = 'A' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_a_rows
,      SUM ( CASE when t.state = 'B' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_b_rows
,      SUM ( CASE when t.state = 'C' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_c_rows
,      COUNT (1) total_rows
from mytable t
group by t.data_key  ---- This does not compile as the compiler feels that t.state isn't in the group by and doesn't recognize the aggregation I'm looking for

Działa to jednak zgodnie z oczekiwaniami:

SELECT distinct t.data_key
,      SUM ( CASE when t.state = 'A' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_a_rows
,      SUM ( CASE when t.state = 'B' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_b_rows
,      SUM ( CASE when t.state = 'C' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_c_rows
,      COUNT (1) total_rows
from mytable t;

Wytwarzanie liczby elementów w każdym stanie na podstawie klucza zewnętrznego „data_key”. Jeśli więc data_key = „APPLE” miał 3 wiersze ze stanem „A”, 2 wiersze ze stanem „B”, wiersz ze stanem „C”, odpowiadający wiersz dla „APPLE” to „APPLE”, 3, 2 , 1, 6.