Tags:    databaser

Mængdefunktioner

SUM
I 1911 går den engelske konge rundt og planlægger en fejring. Han vil fejre alle sine skibe der er til stede i London sammen med et Chilensk og et Japansk skib (tilfældigvis dem som står i Ships-tabellen). Han planlægger at få alle skibene til at lægge anker ved den kongelige kaj, så folk kan besøge skibene. Der er kun ét problem. Er kajen lang nok til at alle skiben kan lægge til? Kongen, som er lidt forud for sin tid, får den idé at han da kan bruge mængdefunktionen SUM. Glad og fro går han ind i sine kongelige gemakker og sender følgende SQL til sin SQL Server 2005 Royal Edition (ok., han er meget forud for sin tid):

SELECT SUM(Length)
FROM Ships

Resultater er:

-----------
1849

(1 row(s) affected)

Dvs. alle skibene efter hindanden fylder 1849 meter. Da den kongelige kaj er 2 kilometer lang kan kongen glædeligt annoncere at begivenhed finder sted.

Som du nok har regnet summerer SUM funktionen alle tallene i den angivne kolonne, i dette tilfælde Length-kolonnen.

Du kan putte DISTINCT foran kolonneangivelsen. I så tilfælde vil gentagne værdier kun blive medregnen én gang. Så hvis vi havde skrevet:

SELECT SUM(DISTINCT Length)
FROM Ships

havde resultatet været 1758 i stedet for de oprindelige 1849 da kun den ene af de to værdier på 91 medregnes.

SUM som vi lige har set, er en mængdefunktion, også kaldet opsamlingsfunktion (eng.: aggrgation functions eller set function). Mægndefunktioner kan stå i en SELECT sætning. Hvis hele udtrykket ikke har en GROUP BY bisætning virker mængdefunktionerne på alle rækkerne i udtrykket, og resultatet er en resultatmængde indeholdende en enkelt række. Detfor kan en SELECT ikke indeholde både en mængdefunktion og en kolonneangivelse (udover dem der findes i selve mængdefunktionerne selvfølgelig). F.eks. er følgende SELECT ikke lovlig:

SELECT Name, SUM(Speed)
FROM Ships

Denne SELECT sætning indeholder både en kolonne (Name) og en mængdefunktion (SUM). Brugen af SUM bevirker at resultatmængden kun indeholder en enkelt række med en kolonne der viser summen af Speed kolonnen fra alle tabellens rækker, hvorimod specificeringen af Name kolonnen bevirker at resultatmængden indeholer netop denne kolonne fra alle rækkerne i Ships-tabellen. Disse to resultater er selvsagt modsigende og kan derfor ikke begge opfyldes (vi kan ikke have en resultatmængde der indeholder kun en række og på samme tid indeholder alle rækkerne), hvorfor den er ulovlig.

En SELECT med en mængdefunktion vil altid resultere i én række, hverken flere eller færre. Rækken kan godt indeholde én eller flere NULL værdier. Derudover kan man ikke indlejre mængdefunktioner: SUM(MAX(..)) er altså ikke lovlig.

I forhold til NULL værdier gælder følgende:

*Hvis nogle af væriderne er NULL udelades de i selve berengingen.
*Hvis kolonnen kun indeholder NULL er resultatet NULL.

COUNT
Mængdefunktionen COUNT bruges til at finde ud af hvor mange rækker der er i en tabel. Hvis f.eks. vi ønsker at vide hvor mange skibe der er i vores Ships tabel, kan vi bruge følgende:

SELECT COUNT(Name)
FROM Ships

Her tæller vi hvor mange rækker i Ships der ikke har NULL værdier i Name-kolonnen.
Resultatet er:

-----------
14

(1 row(s) affected)

Vi kan bruge DISTINCT til at finde ud af hvor mange rækker der findes med forskellige værdier. Hvor mange forskellige type motorer har vi i Ships-tabellen? Det finder følgende SELECT ud af:

SELECT COUNT(DISTINCT Engine)
FROM Ships

Resultatet er her 9. DISTINCT fjerner altså alle gentagne værdier og udfører herefter additionen.

Et andet eksempel: Hvor mange forskellige tegn starter skibsnavnene med? Følgende SELECT finder ud af det for os:

SELECT COUNT(DISTINCT SUBSTRING(Name, 1, 1))
FROM Ships

Resultatet er:

-----------
3

(1 row(s) affected)

Så kun tre forskellige tegn er det første i skibsnavnene - det skyldes at de fleste af navnene starter med "HM".

Vi kan også finde ud af i hvor mange forksellige år vi søsatte skibene i vores tabel:

SELECT COUNT(DISTINCT YEAR(LaunchDate))
FROM Ships

Da alle skibene er søsat i 1905 er resultatet 1.

Som illustreret med COUNT kan du se at vi kan bruge alle former for udtryk i en mængdefunktion.

Vi kan også bruge to eller flere mængdefunktioner i samme SELECT:

SELECT COUNT(DISTINCT YEAR(LaunchDate)), SUM(Length)
FROM Ships

Resultatet er:

            
----------- -----------
1           1849

(1 row(s) affected)

AVG
AVG funktionen finder det aritmetiske gennemsnit af kolonneværdierne. AVG er blot en forkortelse for det engelske ord average som betyder aritmetisk gennemsnit (det aritmetiske gennemsnit kaldes også får middeltallet eller middelværdien. Det findes andre gennemsnit såsom det geometriske gennemsnit og harmoniske gennemsnit). Middelværdien af et datasæt er summen af pågældende værdier dividieret med antallet af værdier.

Så, hvad er middelværdien af hastigheden på skibene? Lad os finde ud af det med følgende SELECT sætning:

SELECT AVG(Speed)
FROM Ships

Resultatet er: 17,6092857142857. Så skibene i vores tabel har i gennemsnit en hastighed på 17,61 knob.

Du kan bruge DISTINCT sammen med AVG funktionen for at få et uvægtede gennemsnit. Med uvægtede menes at hver værdi kun medregnes én gang selvom de måtte eksistere i flere rækker. Hvad er det uvægtede gennemsnit af hastigheden:

SELECT AVG(DISTINCT Speed)
FROM Ships

Det er: 17,9608333333333.

Vi kan også bruge AVG til at finde den gennemsnitlige navnlængde på skibene:

SELECT AVG(LEN(RTRIM(NAME)))
FROM Ships

Først fjerner vi med RTRIM eventuelle mellemrum efter navnet. Derefter bruger vi LEN til at finde længden på navnet, endelig finder mængdefunktionen AVG den gennemsnitlige længde.

Resultatet er:

-----------
20

(1 row(s) affected)

Bemærk at i ovenstående SELECT er det kun AVG der af de tre funktioner er en mængdefunktion.

MIN/MAX
Disse to opsamlingsfunktioner kan bruges til at finde den mindste og største værdi i en kolonne. De kan anvendes på såvel numerisk- som tekstdata.

Hvor lang er det længste skib? Det finder følgende SELECT sætning ud af:

SELECT MAX(Length)
FROM Ships

og resultatet er:

----
205

(1 row(s) affected)

Så det længste skib er altså 205 meter. Hvad med det mindste skib?

SELECT MIN(Length)
FROM Ships

Ovenstående SELECT fortæller os at det er 68 meter.

Hvad er det første bogstav i navent på det sidste (alfabetisk ment) skibsværft? Det kan vi finde ud af på følgende måde:

SELECT SUBSTRING(MAX(Name), 1, 1)
FROM Builders

Resultatet er:

----
T

(1 row(s) affected)

Her bruger vi MAX på en tekststreng og den finder det sidste navn alfabetisk.

Hvis MIN eller MAX bruges på en tom mængde er resultatet NULL. Det ses i følgende eksempel.

SELECT MIN(Speed)
FROM Ships
WHERE Builder=11

Denne SELECT referere en Builder værdi på 11 som ikke findes i vores tabel. Resultater er derfor NULL:

----------------------
NULL

(1 row(s) affected)

Endnu et eksempel. Lad os sige vi ønsker at udskrive det første, alfabetisk set, navn på skibe bygget af et givent værft og såfremt det pågældene værft ikke har bygget nogle skibe skal teksten "No ships built by shipyard!" udskrives. Det sørger følgende SELECT for:

SELECT 
	CASE WHEN MIN(Name) IS NULL
		THEN 'No ship built by shipyard!'
		ELSE MIN(Name)
	END
FROM Ships
WHERE Builder=@Builder

Her skal @Builder erstattes med ID nummeret på pågældende skibsværft. Hvis vi f.eks. erstatter det med 5 bliver resultatet:

--------------------------------------------------
HMS Albion (1898)                                 

(1 row(s) affected)

Hvis vi i stedet bruger 11 får vi:

--------------------------------------------------
No ship built by shipyard!

(1 row(s) affected)

I denne SELECT bruges MIN altså på tekst og finder det alfabetisk set mindste navn. Derudover anvender jeg et CASE udtryk for at udskrive en brugervenlig tekst såfremt resultatet af mængdefunktionene er NULL.

VAR/STDEV
Med funktionerne VAR og STDDEV (i MySQL hedder de VARIANCE og STDDEV), kan vi finde en kolonnes varians og stadard afgivelse. De kan selvsagt kun bruges på numerisk data.

Mængdefunktionen VAR udregner variansen på dets argument. Variansen er et udtryk for hvor tæt et datasæts værdier er på dets gennemsnit. Så variansen kan sige noget om hvor store afvigelser der er i datasættet. Lad os kigge på vores skibes afvigelse fra hastighedsgennemsnittet:

SELECT VAR(Speed)
FROM Ships

Resultatet er: 20,0593456043956.

Standard afvigelsen, også kaldet spredningen, er defineret som kvadratroden af variansen og kan i SQL Server findes med STDEV funktionen:

SELECT STDEV(Speed)
FROM Ships

Resultatet er

----------------------
4,47876608056233

(1 row(s) affected)

Ligesom variansen siger spredgningen noget om hvor tæt på middelværdien datasættet er.

Spredningen bruges oftere end variansen ved vurdering af et datasæt. Vairansen er dog også vigtig, f.eks. i regressionsanalyse.

Referencer

Billederne i denne artikel er fra wikipedia og altså underlagt GNU Free Documentation License.

Hvis man vil læse lidt mere om SQL kan bogen "Introduction to SQL" af Rick F. van der Lans anbefales. Det er en stor bog; en SQL bibel kan man vist godt kalde den. Hvis man er nybegynder med SQL er "Head First SQL" et rigtig godt sted at starte.

Derudover kan alt fra C. J. Dates hånd anbefales. Ligeledes kan SQL bøger skrevet af Joe Celko stærkt anbefales; især "Celko's SQL Puzzles and Answers, Second Edition" er sjov og spændende.

Hvad synes du om denne artikel? Giv din mening til kende ved at stemme via pilene til venstre og/eller lægge en kommentar herunder.

Del også gerne artiklen med dine Facebook venner:  

Karma barometer (30 dage)

IT jobs | Kontakt | Om Udvikleren.dk | Om karma | IT nyheds arkiv | Annoncering

X