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 itemsOVER– 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.