Słowo Kluczowe "Partycja Według" Oracle

Czy ktoś może wyjaśnić, co robi słowo kluczowe partition by i podać prosty przykład w działaniu, a także Dlaczego ktoś chciałby go używać? Mam zapytanie SQL napisane przez kogoś innego i staram się dowiedzieć, co to robi.

Przykład partycji przez: 

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

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

Author: a_horse_with_no_name, 2009-02-18
Source

5 answers

Klauzula PARTITION BY określa zakres rekordów, które będą używane dla każdej "grupy" w klauzuli OVER.

W twoim przykładzie SQL, DEPT_COUNT zwróci liczbę pracowników w tym dziale dla każdego rekordu pracownika. (To tak, jakby twoja de-nomalizacja tabeli emp; nadal zwracasz każdy rekord w tabeli emp.)

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 była inna kolumna (np. state) to można by policzyć ile działów w tym stanie.

To jest jak zdobycie wyniki GROUP BY (SUM, AVG, itd.) bez agregacji zbioru wyników.

Jest to przydatne, gdy korzystasz z funkcji LAST OVER lub MIN OVER, aby uzyskać na przykład najniższą i najwyższą pensję w dziale, A następnie użyć jej w kalulacji na podstawie tej rekordowej pensji bez sub select, który jest znacznie szybszy.

Przeczytaj połączony artykuł AskTom Aby uzyskać więcej szczegółów.

 221
Author: Guy,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-05-10 18:05:34

Koncepcja jest bardzo dobrze wyjaśniona przez przyjętą odpowiedź, ale uważam, że im więcej przykładu widzi się, tym lepiej tonie. Oto przykład Przyrostowy:

1) szef mówi "daj mi liczbę artykułów, które mamy w magazynie pogrupowane według marek"

Mówisz: "nie ma problemu" 

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

Wynik: 

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

2) boss mówi: "zdobądź listę wszystkich przedmiotów, wraz z ich marką i liczbą przedmiotów, które mają tę markę".]}

Możesz try: 

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

Ale dostajesz: 

ORA-00979: not a GROUP BY expression

Tutaj pojawia się OVER (PARTITION BY BRAND): 

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

Whic oznacza:

  • COUNT(ITEM_ID) - uzyskaj liczbę elementów
  • OVER - nad zbiorem wierszy
  • (PARTITION BY BRAND) - które mają tę samą markę

A wynikiem jest: 

+--------------+---------------+----------+
|  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     | 
+--------------+---------------+----------+

Itd...

 97
Author: Andrejs,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-11-09 21:28:32

Jest to rozszerzenie SQL o nazwie analytics. "Over" w instrukcji select mówi oracle, że funkcja jest funkcją analityczną, a nie grupą według funkcji. Zaletą korzystania z analityki jest to, że możesz zbierać sumy, zliczenia i wiele więcej za pomocą jednego przejścia danych zamiast zapętlania danych za pomocą sub selects lub, co gorsza, PL/SQL.

Na początku wygląda to myląco, ale to szybko będzie druga natura. Nikt nie wyjaśnia tego lepiej niż Tom Kyte. Tak więc link powyżej jest świetny.

Oczywiście, czytanie dokumentacji jest koniecznością.

 25
Author: user60890,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-02-27 04:47:56 
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 otrzymujemy policzenie dla odpowiedniego deptno. Co do deptno 10 mamy 4 rekordy w tabeli EMP podobne wyniki również dla deptno 20 i 30.

 9
Author: ,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-08-25 06:45:14

Słowo kluczowe over partition jest tak, jakbyśmy partycjonowali dane przez client_id tworzenie podzbioru każdego ID 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 client_id

 1
Author: issam,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-10-28 05:56:12