Viktig! Om funktionsparametern har typen String och innehåller ett fältnamn som innehåller mellanslag, måste detta fältnamn omges av hakparenteser.
Till exempel: "[Antal varv]".
1. Belopp (totalt)- beräknar summan av uttrycksvärdena som skickas till den som ett argument för alla detaljposter. Du kan skicka en Array som en parameter. I det här fallet kommer funktionen att tillämpas på innehållet i arrayen.
Exempel:
Belopp (Sales.SumTurnover)
2. Räkna - beräknar antalet icke-NULL-värden. Du kan skicka en Array som en parameter. I det här fallet kommer funktionen att tillämpas på innehållet i arrayen.
Syntax:
Kvantitet ([Various] Parameter)
För att få olika värden bör du ange Distinct före metodparametern Kvantitet.
Exempel:
Kvantitet (försäljning. entreprenör)
Kvantitet (Olika försäljningar. Entreprenör)
3. Max
- får maxvärdet. Du kan skicka en Array som en parameter. I det här fallet kommer funktionen att tillämpas på innehållet i arrayen.
Exempel:
Maximum (Saldon. Kvantitet)
4. Minimum - får minimivärdet. Du kan skicka en Array som en parameter. I det här fallet kommer funktionen att tillämpas på innehållet i arrayen.
Exempel:
Minimum (Saldon. Kvantitet)
5. Genomsnitt - får medelvärdet för icke-NULL-värden. Du kan skicka en Array som en parameter. I det här fallet kommer funktionen att tillämpas på innehållet i arrayen.
Exempel:
Genomsnitt (Saldon.Belopp)
6. Array - bildar en array som innehåller parametervärdet för varje detaljerad post.
Syntax:
Array ([Various] Expression)
En värdetabell kan användas som en parameter. I det här fallet kommer resultatet av funktionen att vara en array som innehåller värdena i den första kolumnen i värdetabellen som skickas som en parameter. Om uttrycket innehåller Array-funktionen anses detta uttryck vara aggregerat. Om nyckelordet Various anges, kommer den resulterande arrayen inte att innehålla dubbletter av värden.
Exempel:
Array (motpart)
7.Värdetabell - bildar en värdetabell som innehåller lika många kolumner som funktionens parametrar. Detaljerade poster erhålls från datauppsättningar som behövs för att få alla fält som deltar i funktionsparameteruttrycken.
Syntax:
ValueTable ([Various] Expression1 [AS ColumnName1] [, Expression2 [AS ColumnName2], ...])
Om funktionsparametrarna är restfält, kommer den resulterande värdetabellen att innehålla värden för poster med unika kombinationer av dimensioner från andra perioder. I det här fallet erhålls värden endast för restfält, dimensioner, konton, periodfält och deras detaljer. Värden för andra fält i poster från andra perioder anses vara lika med NULL. Om ett uttryck innehåller funktionen ValuesTable, anses detta uttryck vara aggregerat. Om nyckelordet Different anges, kommer den resulterande värdetabellen inte att innehålla rader som innehåller samma data. Varje parameter kan följas av ett valfritt AS-nyckelord och ett namn som kommer att tilldelas kolumnen i värdetabellen.
Exempel:
Värdetabell (olika nomenklatur, funktionsnomenklatur AS-funktion)
8. Minimera (GroupBy) - utformad för att ta bort dubbletter från en array.
Syntax:
Komprimera (uttryck, kolumnnummer)
Alternativ :
- Uttryck- ett uttryck av typen Array ellerValuesTable, vars värden för elementen ska komprimeras;
- Kolumnnummer- (om uttrycket är av typen ValueTable) typen String. Siffror eller namn (avgränsade med kommatecken) på kolumnerna i värdetabellen, bland vilka du måste söka efter dubbletter. Som standard - alla kolumner.
Minimera (värdetabell (Telefonnummer, Adress), "Telefonnummer");
9. GetPart - får en värdetabell som innehåller specifika kolumner från den ursprungliga värdetabellen.
Syntax:
GetPart (Expression, ColumnNumbers)
Alternativ :
- Uttryck- typ Värdetabell. Tabellen med värden som kolumnerna ska hämtas från;
- Kolumnnummer- typ String. Siffror eller namn (avgränsade med kommatecken) på kolumnerna i värdetabellen som ska erhållas.
Exempel:
GetPart (Komprimera (värdetabell (Telefonnummer, Adress), "Telefonnummer"), "Telefonnummer");
10. Beställning - är avsedd för ordning av elementen i arrayen och värdetabellen.
Syntax:
Ordna (uttryck, kolumnnummer)
Alternativ :
- Uttryck- Array ellerValuesTable för att hämta kolumnerna;
- Kolumnnummer- (om uttrycket är av typen ValueTable) nummer eller namn (avgränsade med kommatecken) på värdetabellens kolumner som du vill beställa. Kan innehålla beställningsriktning och behov av autobeställning: Fallande / Stigande + Autobeställning.
Exempel:
Sortera (värdetabell (telefonnummer, adress, samtalsdatum), "Samtalsdatum fallande");
11. JoinStrings - designad för att kombinera strängar till en linje.
Syntax:
JoinStrings (Värde, Element Separator, Kolumn Separatorer)
Alternativ :
- Menande- uttryck som ska kombineras till en rad. Om det är en array, kommer elementen i arrayen att sammanfogas till en sträng. Om ValuesTable är, kommer alla kolumner och rader i tabellen att kombineras till en rad;
- Objektseparator- en sträng som innehåller text som ska användas som en separator mellan elementen i arrayen och raderna i värdetabellen. Som standard - radmatningstecken;
- Kolumnavdelare- en sträng som innehåller text som ska användas som avgränsare mellan kolumnerna i värdetabellen. Standard "; ".
ConnectStrings (ValuesTable (PhoneNumber, Address));
12. Gruppbearbetning - returnerar DataCompositionGroupProcessingData-objektet. Objektet placeras i dataegenskapen i form av en värdetabell för grupperingsvärdena för varje uttryck som anges i parametern för funktionen Expressions. När du använder hierarkisk gruppering hanteras varje nivå i hierarkin separat. Värden för hierarkiska poster placeras också i datan. Objektets CurrentItem-egenskap innehåller en rad med värdetabellen för vilken funktionen för närvarande beräknas.
Syntax:
Gruppbearbetning (uttryck, hierarkiuttryck, gruppnamn)
Alternativ :
- Uttryck... Uttryck att värdera. En sträng som innehåller uttrycken som ska utvärderas, separerade med kommatecken. Efter varje uttryck kan det finnas ett valfritt AS-nyckelord och ett kolumnnamn i den resulterande värdetabellen. Varje uttryck bildar en kolumn i värdetabellen för Data-egenskapen för DataGroupProcessingDataCompositionData-objektet.
- ExpressionsHierarkier... Uttryck att utvärdera för hierarkiska poster. Liknar parametern Expressions, förutom att parametern Hierarchy Expressions används för hierarkiska poster. Om parametern inte är specificerad, används uttrycken som anges i parametern Expression för att beräkna värden för hierarkiska poster.
- Grupp namn... Namnet på den gruppering där bearbetningsgrupperingen ska beräknas. Linje. Om inte specificerat utförs beräkningen i den aktuella grupperingen. Om beräkningen finns i tabellen och parametern innehåller en tom sträng, eller inte är specificerad, beräknas värdet för gruppering - en rad. Layoutskrivaren, när den genererar datakompositionslayouten, ersätter det angivna namnet med grupperingsnamnet i den resulterande layouten. Om grupperingen inte är tillgänglig kommer funktionen att ersättas med ett NULL-värde.
Syntax:
Alla (Uttryck)
Parameter:
- Uttryck- Boolesk typ.
Varje()
14. Alla- om minst en post har värdet True, är resultatet True, annars False
Syntax:
Alla (uttryck)
Parameter:
- Uttryck- Boolesk typ.
Några()
15.StandardGeneralCollectionDeviation (Stddev_Pop) - beräknar standardavvikelsen för befolkningen. Beräknas med formeln: SQRT (GeneralCollection (X) Variance).
Syntax:
GeneralCollection StandardDeviation (Expression)
Parameter:
- Uttryck- typ nummer.
Exempel:
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Resultat: 805,694444
16.StandardSample Deviation (Stddev_Samp) - beräknar den kumulativa standardavvikelsen. Beräknat med formeln: SQRT (SampleDispersion (X)).
Syntax:
SampleDefusion (uttryck)
Parameter:
- Uttryck- typ nummer.
Exempel:
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Resultat: 28,3847573
17.Samplingvarians (Var_Samp) - beräknar den typiska skillnaden för en serie tal utan att ta hänsyn till NULL-värden i denna uppsättning. Beräknas med formeln: (Mängd (X ^ 2) - Belopp (X) ^ 2 / Belopp (X)) / (Mängd (X) - 1). Om Count (X) = 1, returneras NULL.
Syntax:
Exempelvarians (uttryck)
Parameter:
- Uttryck- typ nummer.
VÄLJ GeneralCollection Variance (Y) FRÅN Tabell
Resultat: 716,17284
19. Samvarians av GeneralPop (Covar_Pop) - beräknar kovariansen för ett antal numeriska par. Det beräknas med formeln: (Summa (Y * X) - Summa (X) * Summa (Y) / n) / n, där n är antalet par (Y, X) där varken Y eller X är NULL .
Syntax:
Allmän kovarians (Y, X)
Alternativ :
- Y- Nummertyp;
- X- typ nummer.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Resultat: 59,4444444
20.CovarianceSample (Covar_Samp) - beräknar den typiska skillnaden för en serie tal utan att ta hänsyn till NULL-värden i denna uppsättning. Det beräknas med formeln: (Summa (Y * X) - Summa (Y) * Summa (X) / n) / (n-1), där n är antalet par (Y, X) där varken Y inte heller X är NULL.
Syntax:
Samplingskovarians (Y, X)
Alternativ :
- Y- Nummertyp;
- X- typ nummer.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Resultat: 66.875
21. Korrelation (Corr) - beräknar korrelationskoefficienten för ett antal numeriska par. Den beräknas med formeln: GeneralSets kovarians (Y, X) / (GeneralSets standardavvikelse (Y) * GeneralSovs standardavvikelse (X)). Par där Y eller X är NULL räknas inte.
Syntax:
Korrelation (Y, X)
Alternativ :
- Y- Nummertyp;
- X- typ nummer.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Resultat: 0,860296149
22. RegressionSlope (Regr_Slope) - beräknar linjens lutning. Den beräknas med formeln: Samvarians av GeneralCollection (Y, X) / Varians av GeneralCollection (X). Beräknat utan att ta hänsyn till NULL-par.
Syntax:
Regressionslutning (Y, X)
Alternativ :
- Y- Nummertyp;
- X- typ nummer.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Resultat: 8,91666667
23. RegressionIntercept (Regr_Intercept) - beräknar Y-punkten för skärningspunkten för regressionslinjen. Beräknat med formeln: Medel (Y) - Regressionslutning (Y, X) * Medel (X). Beräknat utan att ta hänsyn till NULL-par.
Syntax:
Regressionssegment (Y, X)
Alternativ :
- Y- Nummertyp;
- X- typ nummer.
VÄLJ regressionsräkning (Y, X) FRÅN tabell
Position: 9
25. RegressionR2 (Regr_R2) - beräknar bestämningskoefficienten. Beräknat utan att ta hänsyn till NULL-par.
Syntax:
Regression R2 (Y, X)
Alternativ :
- Y- Nummertyp;
- X- typ nummer.
- Null - om GeneralCollection Variance (X) = 0;
- 1 - om Varians av GeneralCollection (Y) = 0 OCH Varians av GeneralCollection (X)<>0;
- POW (Korrelation (Y, X), 2) - om Variance of GeneralCollection (Y)> 0 AND Variance of GeneralCollection (X)<>0.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Resultat: 0,740109464
26. RegressionAverageX (Regr_AvgX) - beräknar medelvärdet av X efter eliminering av X- och Y-par, där antingen X eller Y är tomma. Genomsnitt (X) beräknas utan hänsyn till NULL-par.
Syntax:
Regressionsmedelvärde X (Y, X)
Alternativ :
- Y- Nummertyp;
- X- typ nummer.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Resultat: 5
27. RegressionAverageY (Regr_AvgY) - beräknar medelvärdet av Y efter eliminering av X- och Y-par, där antingen X eller Y är tomma. Genomsnitt (Y) beräknas utan hänsyn till NULL-par.
Syntax:
Regressionsmedelvärde Y (Y, X)
Alternativ :
- Y- Nummertyp;
- X- typ nummer.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Resultat: 24,2222222
28. RegressionSXX (Regr_SXX) - beräknas med formeln: RegressionQuantity (Y, X) * Varians av GeneralCollection (X). Beräknat utan att ta hänsyn till NULL-par.
Syntax:
Regression SXX (Y, X)
Alternativ :
- Y- Nummertyp;
- X- typ nummer.
Exempel:
VÄLJ Regression SYY (Y, X) FRÅN tabell
Resultat: 6445.55556
30. RegressionSXY (Regr_SXY) - beräknas med formeln: RegressionNumber (Y, X) * Samvarians av GeneralCollection (Y, X). Beräknat utan att ta hänsyn till NULL-par.
Syntax:
RegressionSXY (Y, X)
Alternativ :
- Y- Nummertyp;
- X- typ nummer.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Position: 535
31. Rang
Syntax:
PlaceInOrder (Order, Hierrachial Order, GroupName)
Alternativ :
- Beställa- typ String. Innehåller uttryck i den sekvens som du vill placera gruppposter av, separerade med kommatecken. Ordningsriktningen styrs av orden stigande, fallande. Du kan även följa fältet med strängen Autoordering, vilket innebär att du vid beställning av länkar måste använda de beställningsfält som definierats för det refererade objektet. Om ingen sekvens anges, beräknas värdet i grupperingssekvensen;
- Hierrachia-orden- typ String. Innehåller ordningsuttryck för hierarkiska poster;
- Grupp namn- typ String. Namnet på den gruppering där bearbetningsgrupperingen ska beräknas. Om inte specificerat utförs beräkningen i den aktuella grupperingen. Om beräkningen finns i tabellen och parametern innehåller en tom sträng, eller inte är specificerad, beräknas värdet för gruppering - en rad. Layoutskrivaren, när den genererar datakompositionslayouten, ersätter det angivna namnet med grupperingsnamnet i den resulterande layouten. Om grupperingen inte är tillgänglig kommer funktionen att ersättas med ett NULL-värde.
Exempel:
PlaceOrder ("[Antal varv]")
32. Klassificering ABC (Klassificering ABC)
Syntax:
ABC-klassificering (värde, antal grupper, procentandel för grupper, gruppnamn)
Alternativ :
- Menande- typ String. som du vill beräkna klassificeringen efter. Strängen där uttrycket är specificerat;
- Antal grupper- typ nummer. Anger antalet grupper som ska delas upp i;
- Andel För grupper- typ String. Så mycket som hur många grupper du behöver för att dela minus 1. Separerade med kommatecken. Om inte inställt, då automatiskt;
- Grupp namn- typ String. Namnet på den gruppering där bearbetningsgrupperingen ska beräknas. Om inte specificerat utförs beräkningen i den aktuella grupperingen. Om beräkningen finns i tabellen och parametern innehåller en tom sträng, eller inte är specificerad, beräknas värdet för gruppering - en rad. Layoutskrivaren, när den genererar datakompositionslayouten, ersätter det angivna namnet med grupperingsnamnet i den resulterande layouten. Om grupperingen inte är tillgänglig kommer funktionen att ersättas med ett NULL-värde.
Exempel:
Klassificering ABC ("Belopp (bruttovinst)", 3, "60, 90")
Exempel 21... Få det totala antalet leverantörer (sökord RÄKNA ):
VÄLJ ANTAL (*) SOM N
Som ett resultat får vi en tabell med en kolumn och en rad som innehåller antalet rader från tabell P:
Använda aggregerade funktioner med grupperingar
Exempel 23 . För varje del, erhåll den totala levererade kvantiteten (sökord GRUPP AV …):
SUMMA (PD.VOLYM) SOM SM
GRUPPER EFTER PD.DNUM;
Denna begäran kommer att utföras enligt följande. Först kommer raderna i källtabellen att grupperas så att varje grupp inkluderar rader med samma DNUM-värden. Sedan kommer VOLYM-fältet att summeras inom varje grupp. Från varje grupp kommer en rad att inkluderas i resultattabellen:
Kommentar... I listan över valbara fält i en SELECT-sats som innehåller en GROUP BY-sats, kan du inkludera endast samla funktioner och fält, som ingår i grupperingsvillkoret... Följande fråga ger ett syntaxfel:
SUMMA (PD.VOLYM) SOM SM
GRUPPER EFTER PD.DNUM;
Anledningen till felet är att listan över valda fält inkluderar PNUM-fältet, vilket Utesluten till sektionen GROUP BY. Faktum är att varje resulterande grupp av rader kan innehålla flera rader med olika värdena i PNUM-fältet. En sammanfattningsrad kommer att bildas från varje grupp av rader. Samtidigt finns det inget entydigt svar på frågan om vilket värde som ska väljas för PNUM-fältet i sammanfattningsraden.
Kommentar... Vissa SQL-dialekter betraktar inte detta som en bugg. Frågan kommer att köras, men det är omöjligt att förutsäga vilka värden som kommer att anges i PNUM-fältet i resultattabellen.
Exempel 24 ... Få artikelnummer med en total leverans som överstiger 400 (sökord HAR …):
Kommentar... Villkoret att den totala tillförda kvantiteten måste vara mer än 400 kan inte formuleras i WHERE-klausulen, eftersom du kan inte använda aggregerade funktioner i det här avsnittet. Villkor som använder aggregerade funktioner bör placeras i en speciell sektion MED:
SUMMA (PD.VOLYM) SOM SM
GRUPPER EFTER PD.DNUM
HA SUMMA (PD.VOLYM)> 400;
Som ett resultat får vi följande tabell:
Kommentar... En fråga kan innehålla både villkoren för att välja rader i WHERE-satsen och villkoren för att välja grupper i HAVING-satsen. Gruppvalsvillkor kan inte överföras från HAVING-satsen till WHERE-satsen. På samma sätt kan radvalsvillkor inte överföras från en WHERE-satsdel till en HAVING-sats, förutom villkor som inkluderar fält från GROUP BY-listan.
Använda underfrågor
Ett mycket bekvämt verktyg som låter dig formulera frågor på ett mer begripligt sätt är möjligheten att använda underfrågor kapslade i huvudfrågan.
Exempel 25 ... Få en lista över leverantörer vars status är lägre än den maximala statusen i leverantörstabellen (jämförelse med en underfråga):
VAR P.STATYS<
(VÄLJ MAX (P.STATUS)
Kommentar... Eftersom fältet P.STATUS jämförs med resultatet av underfrågan, sedan måste underfrågan formuleras för att returnera en tabell bestående av exakt en rad och en kolumn.
Kommentar
Kör en gång kapslad underfråga och få det maximala statusvärdet.
Skanna leverantörstabellen P, varje gång jämför värdet av leverantörsstatusen med resultatet av underfrågan, och välj endast de rader där statusen är mindre än maxvärdet.
Exempel 26 ... Använder ett predikat I
(VÄLJ DISTINCT PD.PNUM
WHERE PD.DNUM = 2);
Kommentar... I det här fallet kan den kapslade underfrågan returnera en tabell som innehåller flera rader.
Kommentar... Resultatet av att köra frågan kommer att motsvara resultatet av följande sekvens av åtgärder:
Kör en gång kapslad underfråga och få en lista över leverantörsnummer som tillhandahåller artikelnummer 2.
Skanna leverantörstabellen P, kontrollera varje gång för att se om underfrågan innehåller leverantörsnumret.
Exempel 27 ... Använder ett predikat EXISTERA ... Få en lista över leverantörer som levererar artikelnummer 2:
PD.PNUM = P.PNUM AND
Kommentar... Resultatet av att köra frågan kommer att motsvara resultatet av följande sekvens av åtgärder:
Skanna leverantörstabell P, varje gång en underfråga körs med det nya värdet på leverantörsnumret hämtat från tabell P.
Inkludera i frågeresultatet endast de rader från leverantörstabellen för vilka den kapslade underfrågan returnerade en icke-tom uppsättning rader.
Kommentar... Till skillnad från de två föregående exemplen innehåller den kapslade underfrågan en parameter (extern referens) som skickas från huvudfrågan - P.PNUM-leverantörsnumret. Sådana underfrågor kallas korrelerade (korrelerade ). Xref kan ha olika värden för varje kandidatrad som utvärderas av underfrågan, så underfrågan måste köras om för varje rad som väljs i huvudfrågan. Sådana underfrågor är specifika för EXIST-predikatet, men kan också användas i andra underfrågor.
Kommentar... Det kan tyckas som om frågor med korrelerade underfrågor kommer att köras långsammare än frågor med okorrelerade underfrågor. I själva verket är detta inte fallet, eftersom hur användaren formulerade begäran, definierar inte hur denna begäran kommer att verkställas. SQL-språket är icke-procedurmässigt, men deklarativt. Detta innebär att användaren som skickar in begäran helt enkelt beskriver, vad som ska bli resultatet av begäran och hur detta resultat kommer att erhållas är DBMS självt ansvar.
Exempel 28 ... Använder ett predikat INTE EXISTERA ... Få en lista över leverantörer som inte levererar artikelnummer 2:
PD.PNUM = P.PNUM AND
Kommentar... I likhet med föregående exempel använder detta en korrelerad underfråga. Skillnaden är att huvudfrågan kommer att välja de rader från leverantörstabellen för vilka den kapslade underfrågan inte kommer att returnera en enda rad.
Exempel 29 ... Skaffa namnen på de leverantörer som levererar alla delar:
VÄLJ DISTINKT PNAME
PD.DNUM = D.DNUM OCH
PD.PNUM = P.PNUM));
Kommentar... Den här frågan innehåller två kapslade underfrågor och implementerar en relationsoperation delande relationer.
Den innersta underfrågan parametriseras av två parametrar (D.DNUM, P.PNUM) och har följande betydelse: välj alla rader som innehåller leveransdata från leverantören med PNUM-numret för delen med DNUM-numret. Negation INTE FINNS anger att denna leverantör inte levererar delen. En extern underfråga till den, som i sig är en kapslad och parametriserad parameter P.PNUM, är vettig: välj en lista över delar som inte tillhandahålls av PNUM-leverantören. Negering av INTE FINNS innebär att för en leverantör med ett PNUM-nummer ska det inte finnas några delar som inte skulle levereras av den leverantören. Detta innebär just att endast de leverantörer som levererar alla delar väljs ut i den externa förfrågan.
Kom ihåg att de olika elementen i fönsterdefinitionen (partitionering, ordning och beskärning) är väsentligt olika filtreringsalternativ. Det finns andra filtreringsbehov som dessa definitioner inte uppfyller. Vissa av dessa behov kan tillgodoses av FILTER erbjudanden som inte implementerades i SQL Server 2012. Det finns även försök att lösa detta problem genom att lägga förslag på utbyggnad av standarden, vilket jag hoppas på något sätt kommer att dyka upp i standarden och SQL Server.
Jag börjar med FILTERförslaget. Standarden definierar att i aggregeringsfunktionerna låter denna sats dig filtrera raduppsättningen som aggregeringen tillämpas på med hjälp av ett predikat, formatet för denna sats ser ut så här:
Som ett exempel kommer jag att ge en fråga som beräknar skillnaden mellan den aktuella kvantiteten och den genomsnittliga månatliga kvantiteten för en anställd till det aktuella datumet (inte månaden för den aktuella raden):
SQL Server 2012 stöder ännu inte FILTER-satsen. För att vara ärlig så känner jag inte till DBMS som stödde det. Om du behöver en sådan möjlighet finns det en ganska enkel alternativ lösning - använd som input för aggregeringsfunktionen CASE-uttryck:
<функция агрегирования>(FALL NÄR<условие поиска>SEDAN<входное выражение>SLUTET)
Här är en komplett fråga som gör samma sak:
VÄLJ empid, ordermonth, qty, qty - AVG (CASE WHEN ordermonth<= DATEADD(month, -3, CURRENT_TIMESTAMP) THEN qty END) OVER(PARTITION BY empid) AS diff FROM Sales.EmpOrders;
Det som fortfarande saknas i standarden (sedan SQL 2008) och SQL Server 2012 är möjligheten att referera till elementen i den aktuella raden för filtreringsändamål. Detta kan tillämpas i en FILTER-sats, i en alternativ lösning med ett CASE-uttryck och i andra fall där filtrering behövs.
För att visa detta behov, föreställ dig för en sekund att elementet i den aktuella raden kan refereras med prefixet $ current_row. Föreställ dig nu att du vill skriva en fråga för vyn Sales.OrderValues som för varje order beräknar skillnaden mellan värdet på den aktuella ordern och medelvärdet för en specifik anställd för alla kunder förutom kunden som äger ordern. Den här uppgiften löses av följande fråga med FILTER-satsen:
Alternativt kan du använda CASE-uttrycket:
Fungerar inte i T-SQL SELECT orderid, orderdate, empid, custid, val, val - AVG (CASE WHEN custid<>$ current_row.custid THEN val END) OVER (PARTITION BY empid) SOM diff FROM Sales.OrderValues;
Låt mig återigen påminna er om att detta bara är mina uppfinningar för att illustrera vad som saknas i språkstandarden, därför, som de säger, "försök inte att upprepa detta hemma".
Förslag till förbättring
Den jämförande fönsterprincipen ser intressant ut. Det är ganska enkelt och tillfredsställer behovet av att referera till element från den aktuella linjen. Men det som verkligen får dig att vicka är detta extremt coola förslag för att utöka standarden som kallas "mönsterigenkänning i strängar" och löser problemet med att komma åt element från den aktuella strängen, såväl som många andra problem.
Mönster i sekvenser av strängar definieras med hjälp av semantik som liknar reguljära uttryck. Denna mekanism kan användas för att definiera ett tabelluttryck samt filtrera rader i en fönsterdefinition. Det kan också användas i strömningstekniker som StreamInsight i SQL Server, såväl som i frågor som fungerar med icke-roaming data. Här är en länk till ett allmänt tillgängligt dokument: http://www.softwareworkshop.com/h2/SQL-RPR-review-paper.pdf. Innan du läser detta dokument föreslår jag att du befriar huvudet från onödiga tankar och får en rejäl kaffeboost. Det är inte lätt att läsa, men idén är oerhört intressant och jag hoppas att den tar sig in i SQL-standarden och kommer att användas inte bara för data i rörelse, utan även för inaktiv data.
DISTINKT nyckelord i aggregerade funktioner
SQL Server 2012 stöder inte parametern DISTINCT i aggregerade fönsterfunktioner. Föreställ dig att du vill fråga i vyn Sales.OrderValues och få, för varje beställning, antalet specifika kunder som den nuvarande medarbetaren har arbetat med från start till aktuellt datum. Du måste utföra en fråga så här:
Fungerar inte i T-SQL SELECT empid, orderdate, orderid, val, COUNT (DISTINCT custid) OVER (PARTITION BY empid ORDER BY orderdate) SOM numcusts FROM Sales.OrderValues;
Men eftersom denna begäran inte stöds måste en lösning sökas. Ett alternativ är att använda funktionen ROW_NUMBER. Jag kommer att prata om det mer i detalj lite senare, men för nu räcker det med att säga att det returnerar ett unikt heltalsvärde för varje rad i sektionen, med början på ett och i steg om 1, i enlighet med definitionen av beställning i fönstret. Du kan använda ROW_NUMBER-funktionen för att tilldela nummer till rader, uppdelade efter empid och custid, och ordnade efter orderdatum. Det betyder att rader numrerade 1 hänvisar till första gången en anställd arbetade med denna kund vid beställning av beställningar efter datum. Med ett CASE-uttryck kan du endast returnera custid om radnumret är 1, och annars returnera NULL. Här är en fråga som implementerar den beskrivna logiken, med resultatet av sitt arbete:
SELECT empid, orderdate, orderid, custid, val, CASE WHEN ROW_NUMBER () OVER (PARTITION BY empid, custid ORDER BY orderdate) = 1 THEN custid END AS distinct_custid FROM Sales.OrderValues;
Observera att för varje anställd returneras endast det första custidvärdet vid beställning efter datum, och NULL returneras för efterföljande värden. Nästa steg är att bestämma generaliserat tabellvärde (CTE) baserat på den föregående frågan, och sedan tillämpa aggregeringen av det aktuella radantalet på resultatet av CASE-uttrycket:
MED C AS (VÄLJ empid, orderdatum, orderid, custid, val, CASE NÄR ROW_NUMBER () OVER (PARTITION BY empid, custid ORDER BY orderdate) = 1 DÅ custid END AS distinct_custid FROM Sales.OrderValues) VÄLJ empid, orderdatum, orderid valid , COUNT (distinct_custid) OVER (PARTITION BY empid ORDER BY orderdate) SOM numcusts FROM C;
Kapslade aggregat
Vid det här laget vet du att det finns grupp- och fönsteraggregat. Som redan nämnts är funktionerna desamma, men sammanhanget är annorlunda. Gruppaggregat fungerar på den grupp av rader som definieras av GROUP BY-satsen och returnerar ett värde per grupp. Fönsteraggregat fungerar på radfönster och returnerar ett värde för varje rad i den underliggande frågan. Kom ihåg historien om logisk frågebehandling från artikeln Queries. Låt mig påminna er om i vilken ordning, enligt konceptet, olika frågeklausuler ska behandlas:
Bulkaggregat används när begäran är bulk och de är tillåtna i faser som behandlas efter definitionen av grupperna, nämligen från fas 4 och framåt. Kom ihåg att i frågeresultatet representeras varje grupp av endast en rad. Fönsteraggregat är tillåtna från fas 5 och framåt eftersom de fungerar på raderna i den underliggande frågan efter HAVING-fasen.
De två typerna av aggregat – även om de använder samma funktionsnamn och beräkningslogik – fungerar i olika sammanhang. Tillbaka till det viktiga jag ville ta upp i det här avsnittet: vad händer om du vill summera värdet grupperat efter anställds ID och samtidigt aggregera alla dessa belopp för alla anställda?
Detta är ett helt lagligt, men vid första anblicken, konstigt tillvägagångssätt - att tillämpa ett fönsteraggregat på ett fönster som innehåller rader med attribut erhållna med hjälp av gruppaggregat. Jag sa "konstigt" för vid första anblicken ser uttrycket SUM (SUM (val)) i frågan olämpligt ut. Men den har rätt att existera. Titta på frågan som löser problemet:
Gruppens sammanlagda SUM (värde) beräknar det totala priset för alla beställningar för varje anställd. Detta innebär att som ett resultat av basfrågan finns det en rad för varje anställd med denna summa. Därefter beräknar fönsteraggregatet summan av beloppen för enskilda anställda, med andra ord, beräknar totalbeloppet, och dividerar gruppaggregatet med fönsteraggregatet för att beräkna procentandelen av varje anställd och totalsiffran.
Det blir lättare att se logiken i de kapslade aggregaten om frågeanalysen delas upp i två steg. På den första beräknas gruppaggregatet:
SELECT empid, SUM (val) AS emptotal FROM Sales.OrderValues GROUP BY empid;
Detta resultat kan betraktas som startpunkten för ytterligare fönsteraggregation. Således kan du tillämpa SUM-aggregatet på uttrycket som representeras av aliaset emptotal. Tyvärr kan du inte applicera det direkt på en pseudonym av de skäl som nämnts tidigare (minns du "allt på en gång"-principen?). Men det kan appliceras på basuttrycket så här: SUM (SUM (val)) OVER (...) och vi kan anta att det är SUM (emptotal) OVER (...). Vi får alltså följande:
VÄLJ empid, SUM (val) SOM emptotal, SUM (val) / SUM (SUM (värd)) ÖVER () * 100. SOM pct FRÅN Sales.OrderValues GRUPPER EFTER empid;
Observera att komplexiteten med direkt kapsling kan undvikas genom att använda tabelluttryck som CTE. CTE kan bestämmas baserat på en fråga som beräknar gruppaggregatet, och den yttre frågan beräknar fönsteraggregatet, ungefär så här:
MED C AS (VÄLJ empid, SUM (val) SOM emptotal FRÅN Sales.OrderValues GRUPP EFTER empid) VÄLJ empid, emptotal, emptotal / SUM (emptotal) ÖVER () * 100. SOM pct FRÅN C;
Låt oss titta på ett annat exempel på komplexiteten i samband med fönster- och gruppfunktioner. Nästa uppgift är en variant av frågan från föregående artikel. Du måste skapa en fråga för tabellen Sales.Orders som visas för varje anställd för de exakta datumen för beställningar och de exakta namnen på de kunder som den nuvarande medarbetaren arbetade med från början till det aktuella datumet. Första försöket till implementering:
MED C AS (VÄLJ empid, orderdatum, CASE NÄR ROW_NUMBER () ÖVER (PARTITION BY empid, custid ORDER BY orderdate) = 1 DÅ custid END AS distinct_custid FROM Sales.Orders) VÄLJ empid, orderdate, COUNT (distinct_PARTITION) OVER (distinct_custid) ORDER BY orderdate) AS numcusts FROM C GROUP BY empid, orderdate;
Men när du kör begäran får du följande felmeddelande:
Kolumnen "C.distinct_custid" är ogiltig i urvalslistan eftersom den inte finns i vare sig en aggregatfunktion eller GROUP BY-satsen.
Den yttre COUNT-funktionen är inte ett gruppaggregat, utan ett fönsteraggregat. Således kan den bara fungera på element som är giltiga endast om de är självdefinierade, det vill säga inte som input till ett fönsteraggregat. Glöm nu fönsteraggregatet för en sekund och berätta för mig om följande fråga är korrekt (för korthetens skull utelämnas definitionen av CTE)?
VÄLJ empid, orderdate, distinct_custid FRÅN C GROUP BY empid, orderdate;
Svaret är klart nej. Attributet distinct_custid i SELECT-listan är ogiltigt eftersom det inte finns i vare sig aggregatfunktionen eller GROUP BY-satsen, vilket är ungefär vad felmeddelandet säger. Vad du behöver göra är att tillämpa det fönsterbelagda SUM-aggregatet med en löpande total ram på COUNT-gruppaggregatet som räknar specifika förekomster:
MED C AS (VÄLJ empid, orderdatum, CASE NÄR ROW_NUMBER () ÖVER (PARTITION BY empid, custid ORDER BY orderdate) = 1 DÅ custid END AS distinct_custid FROM Sales.Orders) VÄLJ empid, orderdate, SUM (COUNT (distinctOVER_custid)) (PARTITION BY empid ORDER BY orderdate) SOM numcusts FRÅN C GROUP BY empid, orderdate;
Det är tydligt att detta inte är det enda sättet att få det önskade resultatet, men min uppgift var att illustrera principen om att kapsla gruppaggregat till fönsteraggregat. Som ni minns, i enlighet med ordningen för logisk frågebehandling, bearbetas fönsterfunktioner i SELECT- eller ORDER BY-steget, det vill säga efter GROUP BY. Av denna anledning är gruppaggregat synliga som inmatningsuttryck för fönsteraggregat. Kom också ihåg att om din kod blir svår att förstå kan du alltid använda tabelluttryck för att undvika direkt funktionstillägg och förbättra läsbarheten för din kod.
Många databasfrågor behöver inte den detaljnivå som tillhandahålls av SQL-frågorna som diskuterades i de tidigare exemplen. Så i alla frågor som anges nedan behöver du bara ta reda på ett eller flera värden som sammanfattar informationen i databasen:
- 1) hur stor är inkomsten för alla invånare?
- 2) vad är den högsta och lägsta totala inkomsten för en individ?
- 3) vad är den genomsnittliga inkomsten per capita för en invånare i Zelenograd?
- 4) vad är den genomsnittliga inkomsten per capita för invånarna i varje lägenhet?
- 5) hur många boende finns det i varje lägenhet?
I SQL kan frågor av denna typ skapas med hjälp av aggregerade funktioner och satserna GROUP BY och HAVING som används i SELECT-satsen.
Använda aggregerade funktioner
För att sammanfatta informationen i databasen tillhandahåller SQL aggregerade funktioner. En aggregerad funktion tar en hel kolumn med data som ett argument och returnerar ett enda värde som sammanfattar den kolumnen på ett specifikt sätt.
Till exempel tar den aggregerade funktionen AVG () en kolumn med tal som ett argument och beräknar deras medelvärde.
För att beräkna den genomsnittliga inkomsten per capita för en invånare i Zelenograd behöver du följande fråga:
VÄLJ "GENOMsnittlig inkomst, AVG (SUMD) FRÅN PERSON
SQL har sex aggregerade funktioner som ger olika typer av sammanfattningar (Figur 3.16):
SUM () beräknar summan av alla värden som finns i en kolumn;
AVG () beräknar medelvärdet av värdena som finns i kolumnen;
- - MIN () hittar det minsta av alla värden som finns i kolumnen;
- - MAX () hittar den största bland alla värden som finns i kolumnen;
- - COUNT () räknar antalet värden som finns i kolumnen;
COUNT (*) räknar antalet rader i frågeresultattabellen.
Argumentet till en aggregerad funktion kan vara ett enkelt kolumnnamn, som i föregående exempel, eller ett uttryck, som i följande fråga som beräknar skatten per capita:
VÄLJ AVG (SUMD * 0,13)
Ris. 3.16.
Den här frågan skapar en temporär kolumn som innehåller värdena (SUMD * 0.13) för varje rad i PERSON-tabellen och beräknar sedan medelvärdet av den temporära kolumnen.
Summan av inkomsten för alla invånare i Zelenograd kan beräknas med hjälp av SUM-aggregatfunktionen:
VÄLJ SUMMA (SUMD) FRÅN PERSON
Den aggregerade funktionen kan också användas för att beräkna summor för en tabell över resultat som erhålls genom att sammanfoga flera källtabeller. Du kan till exempel beräkna den totala inkomsten som invånarna får från en källa som heter "stipendium":
VÄLJ SUMMA (PENGAR)
FRÅN VINST, HAVE_D
WHERE PROFIT.ID = HAVE_D.ID
AND PROFIT.SOURCE ^ Stipendium ''
De aggregerade funktionerna MIN () och MAX () låter dig hitta de minsta respektive största värdena i tabellen. Kolumnen kan dock innehålla numeriska värden eller strängvärden, eller datum- eller tidsvärden.
Du kan till exempel definiera:
(a) den lägsta totala inkomst som intjänats av invånare och den högsta skatt som ska betalas:
VÄLJ MIN (SUMD), MAX (SUMD * 0,13)
b) Födelsedatum för den äldsta och yngsta bosatta:
VÄLJ MIN (RDATE), MAX (RDATE)
c) Efternamn, förnamn och patronymer för de allra första och allra sista invånarna i listan, sorterade i alfabetisk ordning:
VÄLJ MIN (FIO), MAX (FIO)
När du använder dessa aggregerade funktioner måste du komma ihåg att numeriska data jämförs enligt aritmetiska regler, datum jämförs sekventiellt (tidigare datumvärden anses vara mindre än senare), tidsintervall jämförs baserat på deras varaktighet.
När du använder MIN () och MAX () med strängdata, beror resultatet av att jämföra de två strängarna på vilken teckenkodningstabell som används.
Den aggregerade funktionen COUNT () räknar antalet värden i en kolumn av valfri typ:
(a) hur många lägenheter finns det i det första mikrodistriktet?
VÄLJ ANTAL (ADR)
VAR ADR SOM *%, 1_
(b) hur många invånare har inkomstkällor?
VÄLJ C0UNT (DISTINCT NOM)
(c) hur många inkomstkällor används av invånarna?
VÄLJ ANTAL (DISTINKT ID)
Nyckelordet DISTINCT indikerar att icke-duplicerade värden i kolumnen räknas.
Den speciella aggregatfunktionen COUNT (*) räknar raderna i resultattabellen, inte datavärdena:
(a) hur många lägenheter finns det i det andra mikrodistriktet?
VAR ADR SOM "%, 2 _-%"
(b) hur många inkomstkällor har Ivan Ivanovich?
FRÅN PERSON, HAVE_D
VAR FIO = "Ivanov Ivan Ivanovich"
OCH PERSON.NOM = HAVE_D.NOM
(c) hur många invånare bor i en lägenhet på en given adress?
VÄLJ ANTAL (*) FRÅN PERSON DÄR ADR = "Zelenograd, 1001-45"
Ett sätt att förstå hur sammanfattningsfrågor med aggregerade funktioner exekveras är att tänka på exekvering av frågor i två delar. Först fastställs det hur frågan skulle fungera utan aggregerade funktioner, vilket returnerar flera rader med resultat. Aggregatfunktioner tillämpas sedan på frågeresultaten och returnerar en enda sammanfattningsrad.
Tänk till exempel på följande komplexa fråga: hitta den genomsnittliga totala inkomsten per capita, summan av den totala inkomsten för invånarna och den genomsnittliga inkomsten för källan som en procentandel av den totala inkomsten för invånaren. Svaret ges av operatören
VÄLJ AVG (SUMD), SUM (SUMD), (100 * AVG (MONEY / SUMD))
FRÅN PERSON, PROFIT, HAVE_D DÄR PERSON.NOM = HAVE_D.NOM OCH HAVE_D.ID = PROFIT.ID
Utan aggregerade funktioner skulle frågan se ut så här:
VÄLJ SUMMA, SUMMA, M0NEY / SUMMA FRÅN PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM OCH HAVE_D.ID = PROFIT.ID
och skulle returnera en rad med resultat för varje invånare och specifik inkomstkälla. Aggregatfunktioner använder kolumnerna i resultattabellen för den här frågan för att skapa en enradstabell med sammanfattningsresultaten.
I raden med returnerade kolumner kan du ange en aggregerad funktion istället för valfritt kolumnnamn. Till exempel kan det inkluderas i ett uttryck som adderar eller subtraherar värdena för två aggregerade funktioner:
VÄLJ MAX (SUMD) -MIN (SUMD)
En aggregatfunktion kan dock inte vara ett argument till en annan aggregatfunktion, dvs. kapslade aggregatfunktioner är förbjudna.
Dessutom kan aggregerade funktioner och vanliga kolumnnamn inte användas i listan över returnerade kolumner samtidigt, eftersom det inte är någon mening med detta, till exempel:
VÄLJ FIO, SUMMA (SUMD)
Här instruerar det första elementet i listan DBMS att skapa en tabell som kommer att bestå av flera rader och innehålla en rad för varje invånare. Det andra objektet i listan ber DBMS att få ett resultatvärde, vilket är summan av värdena i SUMD-kolumnen. Dessa två riktlinjer motsäger varandra, vilket resulterar i ett fel.
Ovanstående gäller inte fall av bearbetning av delfrågor och frågor med gruppering.
Ytterligare funktioner har lagts till i SQL som gör att du kan beräkna generiska gruppvärden. För att använda aggregatfunktioner antas en preliminär grupperingsoperation. Vad är kärnan i grupperingsoperationen? Vid gruppering delas hela uppsättningen av tupler i en relation in i grupper, där tupler samlas in som har samma attributvärden som anges i grupperingslistan.
Låt oss till exempel gruppera R1 efter värdet i disciplinkolumnen. Vi kommer att få 4 grupper för vilka vi kan beräkna några gruppvärden, till exempel antalet tuplar i en grupp, det högsta eller lägsta värdet i kolumnen Poäng.
Detta görs med hjälp av aggregerade funktioner. Aggregatfunktioner beräknar ett enda värde för hela tabellgruppen. En lista över dessa funktioner presenteras i tabell 5.7.
Tabell 5.7.Aggregerade funktioner
| R1 | |||||
| Fullständiga namn | Disciplin | Kvalitet | |||
| Grupp 1 | Petrov F.I. | Databas | |||
| K. A. Sidorov | Databas | ||||
| Mironov A.V. | Databas | ||||
| Stepanova K.E. | Databas | ||||
| Krylova T.S. | Databas | ||||
| Vladimirov V.A. | Databas | ||||
| Grupp 2 | K. A. Sidorov | Informationsteori | |||
| Stepanova K.E. | Informationsteori | ||||
| Krylova T.S. | Informationsteori | ||||
| Mironov A.V. | Informationsteori | Null | |||
| Grupp 3 | Trofimov P.A. | Nät och telekommunikation | |||
| Ivanova E.A. | Nät och telekommunikation | ||||
| N. V. Utkina | Nät och telekommunikation | ||||
| Grupp 4 | Vladimirov V.A. | engelsk | |||
| Trofimov P.A. | engelsk | ||||
| Ivanova E.A. | engelsk | ||||
| Petrov F.I. | engelsk | i | |||
Aggregatfunktioner används som fältnamn i en SELECT-sats, med ett undantag: de tar fältnamnet som ett argument. Endast numeriska fält kan användas med funktionerna SUM och AVG. Både numeriska och teckenfält kan användas med funktionerna COUNT, MAX och MIN. När de används med teckenfält kommer MAX och MIN att översätta dem till ASCII-motsvarigheten och bearbeta dem alfabetiskt. Vissa DBMS tillåter användning av kapslade aggregat, men detta är en avvikelse från ANSI-standarden med alla följder.
Du kan till exempel räkna ut antalet elever som klarat prov i varje disciplin. För att göra detta måste du utföra en fråga grupperad efter fältet "Disciplin" och visa som ett resultat namnet på disciplinen och antalet rader i gruppen för denna disciplin. Att använda *-tecknet som argument till COUNT-funktionen innebär att alla rader i gruppen räknas.
VÄLJ R1 Disciplin. СОUNТ (*)
GRUPPER EFTER R1 Disciplin
Resultat:
Om vi vill räkna antalet personer som klarade provet i någon disciplin, måste vi utesluta odefinierade värden från det initiala förhållandet innan vi grupperar. I det här fallet kommer begäran att se ut så här:
VÄLJ R1 Disciplin. COUNT (*)
FRÅN R1 VAR R1.
ÄR INTE NULL poäng
GRUPP EFTER Rl. Disciplin
Vi får resultatet:
I det här fallet linjen med eleven
| Mironov A, V. | Informationsteori | Null | ||
kommer inte att ingå i uppsättningen av tupler före grupperingen, så antalet tupler i gruppen för disciplinen "Informationsteori" kommer att vara 1 mindre.
Aggregatfunktioner kan också användas utan den preliminära grupperingsoperationen, i detta fall betraktas hela relationen som en grupp och ett värde per grupp kan beräknas för denna grupp.
Med hänvisning igen till sessionsdatabasen (tabellerna Rl, R2, R3), kommer vi att hitta antalet godkända prov:
WHERE Grade> 2:
Detta skiljer sig naturligtvis från att välja ett fält, eftersom ett enda värde alltid returneras, oavsett hur många rader som finns i tabellen. Argumentet för aggregerade funktioner kan vara separata kolumner med tabeller. Men för att till exempel beräkna antalet distinkta värden för en viss kolumn i en grupp, är det nödvändigt att använda nyckelordet DISTINCT tillsammans med kolumnnamnet. Låt oss beräkna antalet olika betyg som erhållits för varje disciplin:
VÄLJ Rl. Disciplin.
ANTAL (DISTINKT R1.Utvärdering)
DÄR R1 INTE ÄR NULL
GRUPP EFTER Rl. Disciplin
Resultat:
Resultatet kan inkludera ett grupperingsfältvärde och flera aggregerade funktioner, och flera fält kan användas i grupperingsvillkor. I det här fallet bildas grupper av en uppsättning specificerade grupperingsfält. Aggregatoperationer kan tillämpas på sammanlänkningen av flera källtabeller. Låt oss till exempel ställa frågan: bestäm för varje grupp och varje disciplin antalet personer som klarade provet och medelpoängen för disciplinen.
VÄLJ R2. Grupp. R1 Disciplin. COUNT (*), AVP (uppskattning)
VAR Rl Fullständigt namn = R2 Fullständigt namn OCH
Rl. ÄR INTE NULL OCH
Rl. Poäng> 2
GRUPPER EFTER R2. Grupp. Rl Disciplin
Resultat:
Vi kan inte använda aggregatfunktioner i WHERE-satsen eftersom predikat utvärderas i termer av en enda rad och aggregatfunktioner utvärderas i termer av grupper av rader.
GROUP BY-satsen låter dig definiera en delmängd av värdena i ett visst fält i termer av ett annat fält och tillämpa en aggregatfunktion på delmängden. Detta gör det möjligt att kombinera fält och aggregerade funktioner i en enda SELECT-sats. Aggregatfunktioner kan användas både i uttrycket för att mata ut resultaten från SELECT-raden och i uttrycket för att bearbeta de genererade HAVING-grupperna. I det här fallet beräknas varje aggregatfunktion för varje vald grupp. Värdena som erhålls vid beräkning av aggregatfunktioner kan användas för att visa motsvarande resultat eller för urvalsvillkor för grupper.
Låt oss konstruera en fråga som visar grupperna där mer än en tvåa erhölls i en disciplin i proven:
VÄLJ R2. Grupp
VAR Rl Fullständigt namn = R2 Fullständigt namn OCH
Rl. Poäng = 2
GRUPPER EFTER R2. Grupp. R1 Disciplin
HA räkning (*)> 1
I framtiden, som ett exempel, kommer vi att arbeta inte med Session DB, utan med Bank DB, som består av en tabell F, som lagrar relationen F som innehåller information om konton i en viss banks filialer:
F =
Q = (gren, stad);
eftersom man utifrån detta bättre kan illustrera arbetet med aggregerade funktioner och gruppering.
Anta till exempel att vi vill hitta det totala kontosaldot i filialer. Du kan göra en separat fråga för var och en av dem genom att välja SUM (Återstående) från tabellen för varje gren. GROUP BY låter dig dock lägga dem alla i ett kommando:
VÄLJ gren, SUMMA
GRUPP EFTER gren:
GROUP BY tillämpar aggregerade funktioner oberoende för varje grupp, identifierade av värdet i fältet Branch. En grupp består av rader med samma fältvärde för Branch, och SUM-funktionen tillämpas separat för varje sådan grupp, det vill säga det totala kontosaldot beräknas separat för varje filial. Värdet på fältet som GROUP BY appliceras på har per definition endast ett värde per utdatagrupp, liksom resultatet av en aggregerad funktion. Därför kan vi kombinera ett aggregat och ett fält i en fråga. Du kan också använda GROUP BY med flera fält.
Låt oss anta att vi bara skulle vilja se de totala värdena på kontosaldon som överstiger $ 5000. För att se totala saldon över $5000, måste du använda HAVING-satsen. HAVING-satsen definierar kriterierna som används för att ta bort specifika grupper från utdata, precis som WHERE-satsen gör för enskilda rader.
Rätt kommando skulle vara följande:
SELECT Branch, SUM (Saldo)
GRUPP EFTER gren
HA SUMMA (Återstående) > 5000;
Argument i HAVING-satsen följer samma regler som i SELECT-satsen, som använder GROUP BY. De måste ha ett värde per utdatagrupp.
Följande kommando kommer att vara förbjudet:
SELECT Branch SUM (Saldo)
FRÅN FRÅN GRUPP EFTER gren
HA öppningsdatum = 1999-12-27;
Fältet OpenDate kan inte användas i en HAVING-sats eftersom det kan ha mer än ett värde per visningsgrupp. För att undvika denna situation bör HAVING-satsen endast hänvisa till aggregat och fält valda av GROUP BY. Det finns ett korrekt sätt att göra ovanstående fråga:
SELECT Branch, SUM (Saldo)
WHERE öppningsdatum = "12/27/1999"
GRUPPER EFTER gren;
Innebörden av denna fråga är följande: hitta saldobeloppet för varje gren av konton som öppnades den 27 december 1999.
Som nämnts tidigare kan HAVING endast använda argument som har samma värde per utdatagrupp. I praktiken är referenser till aggregerade funktioner de vanligaste, men fält som valts med GROUP BY är också giltiga. Till exempel vill vi se det totala kontosaldot för filialer i St. Petersburg, Pskov och Uryupinsk:
VÄLJ Branch.SUM (återstående)
VAR F. Branch = Q. Branch
GRUPP EFTER gren
HA filial IN ("St. Petersburg". "Pskov". "Uryupinsk");
Därför, i de aritmetiska uttrycken för predikaten som ingår i urvalsvillkoret för HAVING-satsen, kan du direkt bara använda specifikationerna för de kolumner som anges som grupperingskolumner i GROUP BY-satsen. Resten av kolumnerna kan endast specificeras inom specifikationerna för aggregatfunktionerna COUNT, SUM, AVG, MIN och MAX, som i det här fallet beräknar något aggregerat värde för hela gruppen rader. Situationen är liknande med underfrågorna som ingår i predikaten för urvalsvillkoret i HAVING-sektionen: om underfrågan använder egenskapen för den aktuella gruppen, kan den endast specificeras genom att referera till grupperingskolumnerna.
Resultatet av HAVING-satsen är en grupperad tabell som endast innehåller de radgrupper för vilka resultatet av utvärderingen av sökvillkoret är TRUE. I synnerhet, om en HAVING-sats förekommer i ett tabelluttryck som inte innehåller GROUP BY, kommer resultatet av dess exekvering att vara antingen en tom tabell eller resultatet av de föregående avsnitten i tabelluttrycket, betraktad som en grupp utan gruppera kolumner.
SQL kapslade frågor
Låt oss nu återgå till databasen "Session" och överväga att använda dess exempel på användningen av kapslade frågor.
Med SQL kan du kapsla frågor inuti varandra. Vanligtvis genererar den inre frågan ett värde som kontrolleras i det yttre frågepredikatet (i WHERE- eller HAVING-satsen) för att avgöra om det är sant eller inte. Predikatet EXISTS kan användas tillsammans med en underfråga, som returnerar sant om underfrågans utdata inte är tom.
När den kombineras med andra funktioner hos select-operatorn, såsom gruppering, är en underfråga ett kraftfullt verktyg för att uppnå önskat resultat. I FROM-delen av SELECT-satsen är det tillåtet att tillämpa synonymer på tabellnamn om vi behöver mer än en instans av någon relation när vi skapar en fråga. Synonymer anges med nyckelordet AS, som kan utelämnas helt. Därför kan FROM-delen se ut så här:
FRÅN Rl AS A, Rl AS B
FRÅN Rl A. Rl B:
båda uttrycken är likvärdiga och behandlas som att tillämpa en SELECT-sats på två instanser av R1-tabellen.
Låt oss till exempel visa hur vissa frågor till sessionsdatabasen ser ut i SQL:
- Lista över de som klarat alla obligatoriska prov.
WHERE Poäng> 2
HAR ANTAL (*) = (VÄLJ ANTAL (*)
WHERE R2.Group = R3.Group AND Fullständigt namn och fullständigt namn)
Här definierar inline-frågan det totala antalet tentor som varje student i elevgruppen måste göra, och detta antal jämförs med antalet tentor som denna student har gjort.
- Lista över de som skulle göra DB-provet, men ännu inte blivit godkända.
SELESTHIO
DÄR R2.Fpynna = R3.Grupp OCH Disciplin = "DB" OCH INTE FINNS
(VÄLJ fullständigt namn FRÅN Rl DÄR fullständigt namn = namn och fullständigt namn OCH Disciplin = "DB")
Predikatet EXISTS (SubQuery) är sant när SubQuery inte är tom, det vill säga den innehåller minst en tupel, annars är EXISTS-predikatet falskt.
Predikatet INTE FINNS är omvänt sant endast när underfrågan är tom.
Notera hur INTE FINNS med en underfråga låter dig klara dig utan skillnadsoperationen. Till exempel kan formuleringen av en fråga med ordet "alla" göras som med en dubbel negation. Betrakta ett exempel på en bas som simulerar leverans av enskilda delar av enskilda leverantörer, den representeras av en SP-relation "Leverantörer-delar" med ett schema
SP (Supplier_number. Part_number) P (part_number. Name)
Så här är svaret på förfrågan formulerat: "Hitta leverantörer som levererar alla delar."
VÄLJ DISTINKT SUPPLIER_NUMBER FRÅN SP SP1 DÄR INTE FINNS
(VÄLJ artikelnummer
FRÅN P DÄR INTE FINNS
(VÄLJ * FRÅN SP SP2
WHERE SP2.supplier_number = SP1.supplier_number AND
sp2.part_number = P.part_number)):
Faktum är att vi har omformulerat denna begäran enligt följande: "Hitta leverantörer så att det inte finns någon del som de inte skulle leverera." Det bör noteras att denna fråga också kan implementeras genom aggregerade funktioner med en underfråga:
VÄLJ DISTINKT leverantörsnummer
GRUPP EFTER leverantörsnummer
HAR KONTAKT DELnummer) =
(SELECT Count (part_number)
SQL92-standarden utökar jämförelseoperatorer till flera jämförelser med nyckelorden ANY och ALL. Detta tillägg används när man jämför värdet för en specifik kolumn med datakolumnen som returneras av en underfråga.
Nyckelordet ANY i ett jämförelsepredikat betyder att predikatet kommer att vara sant om jämförelsepredikatet är sant för minst ett värde från underfrågan. Nyckelordet ALL kräver att jämförelsepredikatet är sant när det jämförs med alla rader i underfrågan.
Låt oss till exempel hitta elever som klarade alla prov för ett betyg som inte är lägre än "bra". Vi arbetar med samma bas "Session", men lägger till en ytterligare relation R4, som kännetecknar leveransen av laborationer under terminen:
R1 = (namn, disciplin, bedömning);
R2 = (namn, grupp);
R 3 = (Grupper, Disciplin)
R4 = (namn, disciplin, antal_labbarbete, betyg);
Välj R1.Fullständigt namn Från R1 Där 4> = Alla (Välj Rl. Utvärdering
Där R1.fio = R11.fio)
Låt oss ta ett annat exempel:
Välj studenter som har ett provresultat som inte är mindre än minst ett poäng på sina laborationer inom denna disciplin:
Välj R1.
Från R1 Där R1.Utvärdering> = ALLA (Välj R4.Utvärdering
Där Rl Disciplin = R4. Disciplin OCH R1. Phio = R4. Phio)
SQL Outer Joins
SQL2-standarden har utökat konceptet med villkorliga anslutningar. I SQL1-standarden användes endast villkoren som specificerades i WHERE-satsen i SELECT-satsen vid sammanfogning av relationer, och i det här fallet sammanlänkades endast tuplingar av de ursprungliga relationerna, för vilka dessa villkor var definierade och sanna, enligt den specificerade förhållanden, in i det resulterande förhållandet. Men i verkligheten är det ofta nödvändigt att sammanfoga tabeller på ett sådant sätt att resultatet inkluderar alla rader från den första tabellen, och istället för de rader i den andra tabellen för vilka sammanfogningsvillkoret inte är uppfyllt, skulle resultatet innehålla odefinierat värden. Eller vice versa, alla rader från den högra (andra) tabellen ingår, och de saknade delarna av raderna från den första tabellen är utfyllda med nollvärden. Dessa kopplingar kallades för yttre kopplingar, till skillnad från de som definierades i SQL1-standarden, som kom att kallas inre.
I allmänhet är syntaxen för FROM-delen i SQL2-standarden följande:
FRÅN<список исходных таблиц> |
< выражение естественного объединения > |
< выражение объединения >
< выражение перекрестного объединения > |
< выражение запроса на объединение >
<список исходных таблиц>::= <имя_таблицы_1>
[synonymnamn för tabell_1] [...]
[,<имя_таблицы_п>[ <имя синонима таблицы_n> ] ]
<выражение естественного объединениям:: =
<имя_таблицы_1>NATURLIG (INRE | HELT | VÄNSTER | HÖGER) GÅ MED<имя_таблицы_2>
<выражение перекрестного объединениям: = <имя_таблицы_1>KORS-GÅ MED<имя_таблицы_2>
<выражение запроса на объединением:=
<имя_таблицы_1>UNION GÅ MED<имя_таблицы_2>
<выражение объединениям:= <имя_таблицы_1>(INRE |
FULLT | VÄNSTER | HÖGER) JOIN (PÅ skick)<имя_таблицы_2>
I dessa definitioner står INNER för en inre koppling, LEFT står för en vänster koppling, det vill säga resultatet inkluderar alla rader i tabell 1, och delar av de resulterande tuplarna för vilka det inte fanns några motsvarande värden i tabell 2 är utfyllda med NULL-värden (odefinierad). Nyckelordet RIGHT betyder en höger yttre koppling, och till skillnad från en vänster koppling ingår i detta fall alla rader i tabell 2 i den resulterande relationen, och de saknade delarna från tabell 1 kompletteras med nollvärden. Nyckelordet FULL definierar en fullständig yttre koppling. gå med: både vänster och höger. Med en fullständig yttre koppling utförs både höger och vänster yttre koppling, och den resulterande relationen inkluderar alla rader från tabell 1, utfyllda med nollvärden, och alla rader från tabell 2, även utfyllda med nollvärden.
Nyckelordet YTTRE betyder extern, men om nyckelorden FULL, VÄNSTER, HÖGER anges anses föreningen alltid vara extern.
Låt oss titta på några exempel på yttre sammanfogningar. Låt oss gå tillbaka till sessionsdatabasen igen. Låt oss skapa en relation där alla betyg som alla elever fått på alla tentor som de var tvungna att göra kommer att stå sig. Om en student inte klarade detta prov, kommer han att ha ett odefinierat värde istället för ett betyg. För att göra detta kommer vi successivt att utföra den naturliga inre kopplingen av tabellerna R2 och R3 med attributet Group, och koppla samman den resulterande relationen med den vänstra yttre naturliga kopplingen med tabellen R1 med hjälp av kolumnerna Namn och Disciplin. Samtidigt tillåter standarden användningen av en parentesstruktur, eftersom resultatet av föreningen kan vara ett av argumenten i FROM-delen av SELECT-satsen.
VÄLJ Rl. Fullständigt namn, R1. Disciplin. Rl.Utvärdering
FRÅN (R2 NATURLIG INNER JOIN R3) VÄNSTER JOIN Rl ANVÄNDA (namn. Disciplin)
Resultat:
| Fullständiga namn | Disciplin | Kvalitet | ||
| Petrov F.I. | Databas | |||
| K. A. Sidorov | Databas | 4 | ||
| L. V. Mironov | Databas | |||
| Stepanova K.E. | Databas | |||
| Krylova T.S. | Databas | |||
| Vladimirov V.A. | Databas | |||
| Petrov F.I. | Informationsteori | Null | ||
| K. A. Sidorov | Informationsteori | |||
| Mironov A.V. | Informationsteori | Null | ||
| Stepanova K.E. | Informationsteori | |||
| Krylova T.S. | Informationsteori | |||
| Vladimirov V.A. | Informationsteori | Null | ||
| Petrov F.I. | engelsk | |||
| K. A. Sidorov | engelsk | Null | ||
| Mironov A.V. | engelsk | Null | ||
| Stepanova K.E. | engelsk | Null | ||
| Krylova T.S. | engelsk | Null | ||
| Vladimirov V.A. | engelsk | |||
| Trofimov P.A. | Nät och telekommunikation | |||
| Ivanova E.A. | Nät och telekommunikation | |||
Låt oss överväga ytterligare ett exempel, för detta tar vi databasen "Library". Den består av tre relationer, namnen på attributen skrivs här med latinska bokstäver, vilket är nödvändigt i de flesta kommersiella DBMS.
BÖCKER (ISBN, TITEL. AUTOR. COAUTOR. ÅRJZD, SIDOR)
LÄSAR (NUM_READER. NAME_READER, ADRESS. HOOM_PHONE. WORK_PHONE. Födelsedag)
EXEMPLARE (INV, ISBN, YES_NO. NUM_READER. DATE_IN. DATE_DUT)
Här beskriver tabellen BÖCKER alla böcker som finns i biblioteket, den har följande attribut:
- ISBN - unik kod för boken;
- TITL - bokens titel;
- AUTOR - författarens efternamn;
- COAUTOR - medförfattarens efternamn;
- YEARIZD - publiceringsår;
- PAGES är antalet sidor.
Tabellen READER lagrar information om alla läsare av biblioteket och den innehåller följande attribut:
- NUM_READER - unikt nummer för bibliotekskortet;
- NAME_READER - läsarens efternamn och initialer;
- ADRESS - läsarens adress;
- HOOM_PHONE - hemtelefonnummer;
- WORK_PHONE - arbetstelefonnummer;
- BIRTH_DAY - läsarens födelsedatum.
Tabellen EXEMPEL innehåller information om det aktuella tillståndet för alla instanser av alla böcker. Den innehåller följande kolumner:
- INV - unikt lagernummer för kopian av boken;
- ISBN - boknummer, som identifierar vilken bok det är och hänvisar till information från den första tabellen;
- YES_NO - tecken på närvaron eller frånvaron av denna instans i biblioteket för närvarande;
- NUM_READER - numret på bibliotekskortet om boken har utfärdats till läsaren, och null annars;
- DATE_IN - om läsaren har boken är detta datumet då den gavs ut till läsaren; a DATE_OUT är det datum då läsaren ska lämna tillbaka boken till biblioteket.
Låt oss definiera en lista med böcker för varje läsare; om läsaren inte har några böcker är bokinstansens nummer NULL. För att utföra denna sökning måste vi använda den vänstra yttre kopplingen, det vill säga vi tar alla rader från READER-tabellen och sammanfogar dem med raderna från EXEMPLARE-tabellen, om det inte finns någon rad i den andra tabellen med motsvarande bibliotek kortnummer, så kommer attributet EXEMPLARE.INV att finnas på raden i den resulterande relationen vara undefined null:
VÄLJ READER.NAME_READER, EXEMPLARE.INV
FRÅN LÄSARE HÖGER JOIN EXEMPLARE PÅ LEADER.NUM_READER = EXEMPLARE.NUM_READER
Den yttre joinoperationen, som vi redan nämnt, kan användas för att bilda källor i FROM-satsen, så till exempel kommer följande frågetext att vara giltig:
FRÅN (BÖCKER VÄNSTER GÅ MED EXEMPEL)
LEFT JOIN (LÄSARE NATURAL JOIN-EXEMPEL)
Samtidigt, för böcker, av vilka inte ett enda exemplar är i händerna på läsarna, kommer värdena på bibliotekskortnumret och datumen för att ta och returnera boken att vara odefinierade.
Cross join i tolkningen av SQL2-standarden motsvarar den utökade kartesiska produktoperationen, det vill säga operationen att sammanfoga två tabeller, där varje rad i den första tabellen är kopplad till varje rad i den andra tabellen.
Drift begäran om enandeär ekvivalent med operationen av mängdteoretisk förening i algebra. I det här fallet kvarstår kravet på likvärdighet mellan systemen för de ursprungliga relationerna. Sammanslagningsbegäran exekveras enligt följande schema:
VÄLJ - fråga
UNION SELECT - fråga
UNION SELECT - fråga
Alla frågor som deltar i en join-operation får inte innehålla uttryck, det vill säga beräknade fält.
Du måste till exempel visa en lista över läsare som håller i Idiot-boken eller Brott och straff-boken. Så här kommer frågan se ut:
VÄLJ LÄSARE. NAME_READER
FRÅN LÄSARE, EXEMPLARE.BOOKS
BOOKS.TITLE = "(! LANG: Idiot"!}
VÄLJ READER.NAME_READER
FRÅN LÄSARE, EXEMPEL, BÖCKER
WHERE EXEMPLARE.NUM_READER = READER.NUM_READER OCH
EXEMPLRE.ISBN = BÖCKER.ISBN OCH
BOOKS.TITLE = "(! LANG: Brott och straff"!}
Som standard exkluderas alltid dubbletter av tupler när en unionsfråga körs. Därför, om det finns läsare som har båda böckerna i sina händer, kommer de fortfarande att inkluderas i den resulterande listan bara en gång.
En anslutningsförfrågan kan kombinera valfritt antal ursprungliga frågor.
Så till den tidigare förfrågan kan du lägga till fler läsare som håller i boken "Slott":
VÄLJ LÄSARE. NAME_READER
FRÅN LÄSARE. EXEMPEL, BÖCKER
WHERE EXEMPLARE.NUM_READER = READER.NUM_READER OCH.
EXEMPLRE.ISBN = BÖCKER.ISBN OCH
BOOKS.TITLE = "(! LANG: Slott"!}
Om du behöver behålla alla rader från den ursprungliga relationen, måste du använda nyckelordet ALL i unionsoperationen. Om dubbletter av tupler sparas kommer flödesdiagrammet för unionsfrågan att se ut så här:
VÄLJ - fråga
VÄLJ - fråga
VÄLJ - fråga
Du kan dock uppnå samma resultat genom att helt enkelt modifiera WHERE-satsen i den första delen av den ursprungliga frågan, ELLER de lokala förhållandena och eliminera dubbletter av tupler.
VÄLJ DISTINCT READER.NAME_READER
FRÅN LÄSARE. EXEMPLARE.BOOKS
WHERE EXEMPLARE.NUM_READER = READER.NUM_READER OCH
EXEMPLRE.ISBN = BÖCKER.ISBN OCH
BOOKS.TITLE = "(! LANG: Idiot" OR!}
BOOKS.TITLE = "(! LANG: Brott och straff" OR!}
BOOKS.TITLE = "(! LANG: Slott"!}
Ingen av de ursprungliga frågorna i en UNION-operation ska innehålla en ORDER BY-resultatbeställningssats, men resultatet av kopplingen kan beställas genom att skriva en ORDER BY-sats som specificerar en lista med ordningskolumner efter texten i den senaste ursprungliga SELECT-frågan.
