Oracle Partition By Trefwoord

Kan iemand uitleggen wat het trefwoord PARTITION BYdoet en een eenvoudig voorbeeld geven van de werking ervan, en ook waarom iemand het zou willen gebruiken? Ik heb een SQL-query geschreven door iemand anders en ik probeer erachter te komen wat het doet.

Een voorbeeld van partitie door:

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

De voorbeelden die ik online heb gezien, lijken iets te diepgaand.


Antwoord 1, autoriteit 100%

De PARTITION BY-clausule stelt het bereik van records in dat zal worden gebruikt voor elke “GROUP” binnen de OVER-clausule.

In uw voorbeeld-SQL, zal DEPT_COUNThet aantal werknemers binnen die afdeling retourneren voor elk werknemersrecord. (Het is alsof u de tabel empde-nomaliseert; u retourneert nog steeds elk record in de tabel 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

Als er een andere kolom was (bijv. state) dan zou je kunnen tellen hoeveel afdelingen in die staat.

Het is alsof je de resultaten krijgt van een GROUP BY(SUM, AVG, enz.) zonder de resultatenset te aggregeren (bijv. overeenkomende records verwijderen).

Het is handig wanneer u de functies LAST OVERof MIN OVERgebruikt om bijvoorbeeld het laagste en hoogste salaris op de afdeling te krijgen en dat vervolgens in een berekening tegen deze records salaris zondereen sub selecteren, dat is veel sneller.

Lees het gelinkte AskTom-artikel voor meer informatie.


Antwoord 2, autoriteit 69%

Het concept wordt heel goed uitgelegd door het geaccepteerde antwoord, maar ik merk dat hoe meer voorbeelden je ziet, hoe beter het tot je doordringt. Hier is een incrementeel voorbeeld:

1) Boss zegt“geef me het aantal artikelen dat we op voorraad hebben, gegroepeerd op merk”

U zegt: “geen probleem”

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

Resultaat:

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

2) De baas zegt“Geef me nu een lijst van alle items, met hun merk EN het aantal items dat het betreffende merk heeft”

U kunt proberen:

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

Maar je krijgt:

ORA-00979: not a GROUP BY expression 

Hier komt de OVER (PARTITION BY BRAND)om de hoek kijken:

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

Wat betekent:

  • COUNT(ITEM_ID)– verkrijg het aantal items
  • OVER– Over de reeks rijen
  • (PARTITION BY BRAND)– die hetzelfde merk hebben

En het resultaat is:

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

enz…


Antwoord 3, autoriteit 10%

Het is de SQL-extensie genaamd analytics. De “over” in de select-instructie vertelt orakel dat de functie een analytische functie is, niet een groep op functie. Het voordeel van het gebruik van analyses is dat u sommen, tellingen en nog veel meer kunt verzamelen met slechts één doorloop van de gegevens in plaats van door de gegevens te bladeren met subselecties of erger, PL/SQL.

Het ziet er op het eerste gezicht verwarrend uit, maar dit zal snel een tweede natuur worden. Niemand legt het beter uit dan Tom Kyte. Dus de link hierboven is geweldig.

Natuurlijk, het lezen van de documentatieis een must.


Antwoord 4, autoriteit 3%

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

Hier krijgen we de telling voor het respectieve deptno.
Wat betreft deptno 10 hebben we 4 records in de tabel emp vergelijkbare resultaten voor deptno 20 en 30 ook.


Antwoord 5

het sleutelwoord over partition is alsof we de gegevens partitioneren op client_id
maak een subset van elke klant-ID

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;

deze query retourneert het aantal bewerkingen dat door de client_id is uitgevoerd


Antwoord 6

Ik denk dat dit voorbeeld een kleine nuance suggereert over hoe het partitioneren werkt en hoe group by werkt. Mijn voorbeeld komt uit Oracle 12, als mijn voorbeeld toevallig een compileerfout is.

Ik heb het geprobeerd:

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

Dit werkt echter zoals verwacht :

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;

Het aantal elementen in elke staat produceren op basis van de externe sleutel “data_key”. Dus als data_key = ‘APPLE’ 3 rijen had met status ‘A’, 2 rijen met status ‘B’, een rij met status ‘C’, zou de corresponderende rij voor ‘APPLE’ ‘APPLE’ zijn, 3, 2 , 1, 6.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Other episodes