MS Sql 70-461: Chapter 5
Les 1
Een data analysis function is een functie die wordt toegepast op een set van rijen en één waarde teruggeeft. Een voorbeeld hiervan is SUM.
Single grouping set
Een query wordt een grouped query als een group function wordt gebruikt of als de GROUP BYclause wordt toegepast. Voorbeeld: SELECT COUNT(*) as total FROM orders. Omdat er geen GROUP BY clause wordt gebruikt worden alle rijen in de tabel beschouwd als één grote groep en COUNT telt het aantal rijen per groep, dus is het restultaat van de query het aantal rijen dat in de tabel orders zit.
Grouped queries geven enkel één rij per groep terug.
Met de GROUP BY kan opgegeven waorden waarop gegroepeerd moet worden:
SELECT customerId, COUNT(*) as total
FROM orders
GROUP BY customerId
Nu wordt per customerId het aantal rijen getoond.
Met HAVING kunnen groepen gefilterd worden. HAVING is net als WHERE. Waar WHERE wordt toegepast op rijen, wordt HAVING toegepast op groepen. Met HAVING kunnen ook functies zoals COUNT en SUM etc. gebruikt worden, omdat de data al gegroepeerd is (door GROUP BY).
General set functies zijn:
- COUNT
- SUM
- AVG
- MIN
- MAX
Deze functies worden toegepast opeen expressie en negeren NULLs. Een set function ondersteund ook de DISTINCT option:
SELECT custumerId, COUNT(DISTINCT orderDate) FROM t GROUP BY customerId
De logical query processing evalueert de GROUP BY na de FROM en WHERE, maar voor de HAVING, SELECT en ORDER BY clauses. De laatste drie clauses werken dus al met gegroepeerde rijensets.
Elke groep wordt gereprensenteerd door één rij van de groep.
Het is niet mogelijk om een kolom in de SELECT op te nemen waar niet op is gegroepeerd (set function of GROUP BY). Er zijn drie manieren om toch een extra kolom op te nemen in de SELECT:
1) Neem de kolom op in de GROUP BY clause
2) Gebruik een set function, bijv MAX(kolom)
3) Maak gebruik van een CTE (Common Table Expression).
Multiple Grouping Sets
T-SQL ondersteund meerdere sets van groepen in dezelfde query. Er zijn drie clauses die een multiple grouping set definieren: GROUPING SETS, CUBE en ROLLUP. Deze worden gebruikt in de GROUP BY clause. De volgende query definieert vier grouping sets:
SELECT customerId, YEAR(orderDate), Count(*) as total
FROM orders
WHERE orderDate IS NOT NULL
GROUP BY GROUPING SETS
(
(customerId, YEAR(orderDate)),
(customerId),
(YEAR(orderDate)),
()
);
De groups binnen een grouping set worden gescheiden door een komma.
Hetzelfde resultaat kan bereikt worden door vier queries te schrijven met daarin elk een GROUP BY en de resultaten van de queries samen te voegen d.m.v. de UNION ALL operator. Hoewel dit mogelijk is, brengt wel meer code met zich mee en de query wordt niet zo goed geoptimaliseerd als gebruik zou worden gemaakt van de GROUPING SETS clause.
CUBE accepteert een lijst van expressies en definieert alle mogelijke grouping sets die gegenereert kunnen worden op basis van deze lijst. De volgende query heeft dezelfde output als de vorige query:
SELECT customerId, YEAR(orderDate), Count(*) as total
FROM orders
GROUP BY CUBE(customerId, YEAR(orderDate));
De gegenereerde grouping sets zijn:
1) customerId, YEAR(orderDate)
2) customerId
3) YEAR(orderDate)
4) ()
De ROLLUP clause wordt gebruikt als er een hierarchie gevormd wordt door de inputs. In zo'n geval is enkel een subset van de mogelijke grouping sets interessant. Voorbeeld: de hierarchie is land, regio, stad.
SELECT country, region, stad, COUNT(*) as total
FROM orders
GROUP BY ROLLUP(country, region, city)
De gegenereerde grouping sets zijn:
1) country, region, city
2) country, region
3) country
4 ()
Voor alle grouping sets geldt dat NULL wordt gebruikt als een placeholder als een onderdeel geen deel uitmaakt van de grouping set. Dit zorgt er voor dat er geen onderscheid gemaakt kan worden of een NULL een placeholder is of als de kolom NULL bevat (dus een missende waarde). T-Sql biedt hiervoor twee functies: GROUPING en GROUPING_ID. GROUPING accepteert een element en retourneert 0 als het element onderdeel is van de grouping set en anders een 1.
GROUPING_ID accepteert een lijst van gegroepeerde kolommen en geeft een int terug die een bitmap voorstelt. De meest rechtse bit is de meest rechtse input. De bit is 0 als het element onderdeel is van grouping en anders 1. Elke bit stelt 2 tot de macht van de positie van de bit min 1: de meest rechtse bit is 1, een links daarvan is 2, dan 4, dan 8, etc..
Les 2
Pivoting Data
Pivoting groepeert data en transformeert vervolgens de rijen naar kolommen en de kolommen naar rijen. Elke pivot query kent drie elementen:
1) Welke data moet er op de rijen getoond worden. Dit heet onrows of grouping element
2) Welke data moet er in de kolommen getoond worden. Dit heet on cols of spreading element
3) Welke data moet er getoond worden als een kolom en een rij elkaar kruisen. Dit heet data of aggregation element.
Stel je hebt een customerId (grouping), een shipperId (spreading) en op de kruising moet van elke customerId en shipperId moet de som van de vracht (aggregation) getoond worden. De aanbevolen manier voor een pivot query is door gebruik te maken van een CTE:
WITH pivotData AS
(
SELECT custId, shipperId, freight
FROM Orders
)
SELECT custId, [1], [2], [3]
FROM pivotData
PIVOT(SUM(freight) FOR shipperId IN ([1], [2], [3]) AS p;
De PIVOT operator retourneert een table result en deze moet een alias toegewezen krijgen. PIVOT start met het accepteren van de aggregate function welke wordt toegepast op het aggregate element, in de query SUM(freight). Daarna, in de FOR clause wordt de spreading kolom gedefinieerd. In de IN clause worden de kolommen bepaald op basis van de waarden die in de tabel staan. In bovenstaande query zijn 1, 2 en 3 de id's van de customer. Hier worden kolommen van gemaakt, dus moeten hier blokhaken omheen worden gezet, want getallen zijn ongeldige kolomnamen zonder blokhaken.
In tabelvorm:
| grouping kolom (custId) | spreading kolom (shipperId) | spreading kolom (shipperId) | spreading kolom (shipperId) |
| grouping kolom (1(= custId)) | aggregate function (95.03 (=SUM(freight))) | aggregate function (105) | aggregate function (56.59) |
| grouping kolom (2(= custId)) | aggregate function (35.03 (=SUM(freight))) | aggregate function (44) | aggregate function (126.59) |
Bij PIVOT wordt dus niet opgegeven op basis waarvan moet worden gegroepeerd. Dit bepaalt PIVOT op basis van eliminatie. De kolommen waarop gegroepeerd moet worden is datgeen wat overblijft als de aggregation en spreading elementen weg worden gehaald uit de query. In bovenstaande query blijft dus custId over en dus wordt hier op gegroepeerd. Dit is ook direct de reden waarom een CTE wordt gebruikt voor pivotting. Als direcht op de Orders tabel zou worden gequeried, dan wordt er gegroepeerd op alle kolommen, behalve shipperId en freight.
Voorwaarden gebruik PIVOT:
- De aggregation en spreading elementen moeten kolomnamen zijn van een tabel en kunnen geen expressies zijn
- COUNT(*) kan niet worden gebruikt als de aggregate function, in plaats hiervan moet COUNT(kolomnaam) worden gebruikt.
- Een PIVOT ondersteund maar één aggregate function
- De IN clause van de PIVOT accepteert enkel een lijst van statisch waarden. Een subquery wordt niet ondersteund.
Unpivoting data
Unpivoting is het tergenovergestelde van pivotting: het transformeren van kolommen naar rijen en rijen naar kolommen. Bij unpivoting zijn drie elementen nodig:
1) de set van bron kolommen die rijen moeten worden ([1], [2], [3])
2) de naam die de waarden moeten krijgen als kolom (freight)
3) de naam van de doelkolom (shipperId)
SELECT custId, shipperId, freight
FROM myTable
UNPIVOT(freight FOR shipperId IN ([1], [2], [3]))
Het resultaat is dan er nu drie kolommen zijn (custId, shipperId, freight) met als data de bijbehorende gegevens. De UNPIVOT operator filtert de rijen met NULLs, hiervoor worden geen rijen voor aangemaakt.
Les 3
Bij group functies wordt de functie toegepast per groep en niet per rij. Met window functies wordt een set rijen gedefinieerd en dan wordt één resultaat geretourneerd voor elke onderliggende rij en functie. De set van rijen voor de functie waarmee gewerkt wordt, wordt gedefinieert door de clause OVER.
Windows aggregate functions
Window aggregate functions zijn hetzelfde als de group aggregate functies, bijv SUM, COUNT, AVG, MIN en MAX behalve dan dat window aggregate functies toegepast worden op een window of rows die zijn gedefinieerd door de OVER clause.
Een voordeel van window functies is dat zij, in tegenstelling tot grouped queries, een rij retourneren voor elk rij van de onderliggende query.
Met OVER wordt een venster van rijen gedefinieerd voor elke functie. Het venster wordt gedefinieerd ten opzichte van de huidige rij. Als OVER wordt gebruikt met lege haakjes, dan bevat OVER het gehele resultaat van de quey:
SUM(val) OVER() geeft het eindtotaal van alle rijen.
Met PARTITION BY kan een filter worden aangebracht: SUM(val) OVER(PARTITION BY custId). Nu wordt per custId het totaal berekend.
Window aggregate functions kennen ook een manier van filteren genaamd framing. Het idee is dat een volgorde gedefinieerd wordt binnen de partition door gebruik te maken van een window order clause. Vervolgens (gebaseerd op die volgorde) kunnen rijen beperkt worden tussen twee scheidingen. De scheidingen worden gedefinieerd door de window frame clause. Een window frame clause heeft altijd een window order clause nodig.
In de window frame clause worden window frame unit bepaald(ROWS of RANGE) en de omvang van de window frame. Met de ROWS window frame unit,zijn de volgende scheidingen:
- UNBOUNDED PRECEDING of FOLLOWING: respectievelijk vanaf het begin of einde van de partition
- CURRENT ROW: representeert de huidige rij
- ROWS PRECEDING of FOLLOWING: respectievelijk rijen voor of achter de huidige rij
SELECT custid, orderid, orderdate, val,
SUM(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningtotal
FROM Sales.OrderValues;
Resultaat:
custid orderid orderdate val runningtotal
------- -------- ----------- ------- -------------
1 10643 2007-08-25 814.50 814.50
1 10692 2007-10-03 878.00 1692.50
1 10702 2007-10-13 330.00 2022.50
1 10835 2008-01-15 845.80 2868.30
1 10952 2008-03-16 471.20 3339.50
1 11011 2008-04-09 933.50 4273.00
Voor de eerste rij geldt:
BETWEEN UNBOUNDED PRECEDING = vanaf eerste rij t/m huidige rij pas SUM(val) toe = 814,50
Voor de tweede rij geldt:
BETWEEN UNBOUNDED PRECEDING = vanaf eerste rij t/m huidige rij pas SUM(val) toe = 1692,50 ==> waarde van rij 1 + waarde van huidige rij
etc...
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW kan ook geschreven worden als ROWS UNBOUNDED PRECEDING.
Window aggregate functions hebben een beter performance in vergelijking met het gebruik van joins of subqueries, zeker als UNBOUNDED PRECEDING als eerste gebruikt wordt in een window function.
Sql Server 2012 kent een gelimiteerde implementatie van RANGE die enkel UNBOUNDE PRECEDING of FOLLOWING en CURRENT ROW ondersteund. Een subtiel verschil tussen RANGE en ROWS is dat RANGE tied rows meeneemt en ROWS niet. ROWS daarentegen kent een veel betere perfomance dan RANGE.
Window ranking functions
Met window ranking functies kunnen rijen geordend worden binnen een partition gebaseerd op een bepaalde volgorde. Net als bij de andere window functies wordt de gehele tabel beschouwd als één partition als er geen partition clause is opgegeven. De order clause is wel verplicht.Window ranking functies ondersteunen geen window frame clause. T-Sql ondersteund vier window ranking functies:
1. ROW_NUMBER
berekent een unique nummer voor elke rij in de partition
2. RANK
zelfde as ROW_NUMBER, maar geeft dezelfde waarde aan alle rijen als de kolom waarop geordend wordt dezelfde waarden hebben. De RANK functie geeft het aantal rijen terug die een lagere waarde hebben dan de huidge rij plus 1.
3. DENSE_RANK
Deze functie geeft het aantal distinct ordering waarden terug die lager zijn dan de huidige, plus 1. Rijen die dezelfde waarde hebben, krijgen hetzelfde nummer. DENSE_RANK kijkt dus naar de waarde van de kolom en RANK kijkt naar de rijen.
4. NTILE(n)
Met NTILE kunnen tiles worden aangemaakt. In dit geval kan een tile gezien worden als een emmer. Stel dat NTILE(100) wordt gebruikt, dan worden er 100 emmers aangemaakt. Het resultaat van de query geeft 1500 rijen terug. Dit betekent dat er 15 rijen (1500/100) in een emmer (tile) komen. De eerste 15 rijen krijgen dan de waarde 1, de 15 rijen daaropvolgend de waarde 2, etc...
Window offset functies
Window offset functies geven een element van een enkele rij terug die voorkomt in een bepaalde offset van de huidige rij in the window partition, of van de eerste of laatste rij in het window frame. T-Sql ondersteund 4 window offset functies.
1. LAG
Met de LAG functie kan een waarde opgehaald worden van het vorige record binnen dezelfde partition: LAG(orderDate, 1, 'nvt') betekent: Toon van het vorige record (1) de waarde uit kolom orderDate en als er geen vorig record is, toon dan 'nvt'. De tweede parameter geeft aan van hoeveel records terug de waarde moet worden opgehaald.
2. LEAD
Zelfde als LAG, maar in plaats te kijken naar de vorige records, kijkt LEAD naar de volgende records: LEAD(orderDate, 1, 'nvt'): Haal van het volgende record de orderDate op en als deze niet bestaat toon dan 'nvt'.
3. FIRST_VALUE
Toon de waarde van de opgegeven kolom van het eerste record binnen dezelfde partition: FIRST_VALUE(orderDate)
4. LAST_VALUE
Zelfde als FIRST_VALUE, maar toont de waarde van de opgegeven kolom van het laatste record binnen de partition.
Reacties
Een reactie posten