Hur kan jag ta reda på antalet PC-modeller som produceras av en viss leverantör? Hur man bestämmer det genomsnittliga priset på datorer som har samma specifikationer? Dessa och många andra frågor relaterade till viss statistisk information kan besvaras med hjälp av slutliga (sammanlagda) funktioner. Standarden tillhandahåller följande aggregerade funktioner:

Alla dessa funktioner returnerar ett enda värde. Samtidigt funktionerna COUNT, MIN Och MAX gäller för alla datatyper, medan BELOPP Och AVG används endast för numeriska fält. Skillnad mellan funktioner RÄKNA(*) Och RÄKNA(<имя поля>) är att den andra inte tar hänsyn till NULL-värden vid beräkning.

Exempel. Hitta lägsta och högsta pris för persondatorer:

Exempel. Hitta det tillgängliga antalet datorer som tillverkats av tillverkare A:

Exempel. Om vi ​​är intresserade av mängden olika modeller, producerad av tillverkare A, då kan frågan formuleras på följande sätt (med hjälp av det faktum att varje modell registreras en gång i produkttabellen):

Exempel. Hitta antalet tillgängliga olika modeller tillverkade av tillverkare A. Frågan liknar den föregående, där det krävdes för att bestämma det totala antalet modeller som tillverkats av tillverkare A. Här behöver du också hitta antalet olika modeller i PC-bordet (dvs de som finns till försäljning).

För att säkerställa att endast unika värden används vid erhållande av statistiska indikatorer, när argument för aggregerade funktioner kan användas DISTINCT parameter. Annan parameter ALLA är standard och förutsätter att alla returnerade värden i kolumnen räknas. Operatör,

Om vi ​​behöver få antalet producerade PC-modeller alla tillverkare måste du använda GROUP BY-klausul, syntaktisk efter WHERE-klausuler.

GROUP BY-klausul

GROUP BY-klausul används för att definiera grupper av utdatasträngar som kan appliceras på aggregerade funktioner (COUNT, MIN, MAX, AVG och SUM). Om denna sats saknas och aggregerade funktioner används, kommer alla kolumner med namn som nämns i VÄLJ, måste ingå i samla funktioner, och dessa funktioner kommer att tillämpas på hela uppsättningen rader som uppfyller frågepredikatet. Annars alla kolumner i SELECT-listan ingår ej i aggregerade funktioner måste anges i GROUP BY-satsen. Som ett resultat är alla rader för utmatningsfrågor uppdelade i grupper som kännetecknas av samma kombinationer av värden i dessa kolumner. Efter detta kommer aggregerade funktioner att tillämpas på varje grupp. Tänk på det för GROUP BY allt NULL-värden behandlas som lika, dvs. vid gruppering efter ett fält som innehåller NULL-värden kommer alla sådana rader att falla i en grupp.
Om om det finns en GROUP BY-sats, i SELECT-satsen inga aggregerade funktioner, då returnerar frågan helt enkelt en rad från varje grupp. Den här funktionen, tillsammans med nyckelordet DISTINCT, kan användas för att eliminera dubbletter av rader i en resultatuppsättning.
Låt oss titta på ett enkelt exempel:

VÄLJ modell, ANTAL(modell) AS Antal_modell, AVG(pris) AS Genomsnittspris FRÅN PC GROUP BY modell;

I denna begäran, för varje PC-modell, bestäms deras antal och genomsnittliga kostnad. Alla linjer med samma värden modell (modellnummer) bildar en grupp, och SELECT-utgången beräknar antalet värden och genomsnittliga prisvärden för varje grupp. Resultatet av frågan blir följande tabell:

modell	Antal_modell	Genomsnittligt pris
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Om SELECT hade en datumkolumn skulle det vara möjligt att beräkna dessa indikatorer för varje specifikt datum. För att göra detta måste du lägga till datum som en grupperingskolumn, och sedan beräknas de aggregerade funktionerna för varje kombination av värden (modell-datum).

Det finns flera specifika regler för att utföra aggregerade funktioner:

• Om som ett resultat av begäran inga rader mottagna(eller mer än en rad för en given grupp), så finns det ingen källdata för att beräkna någon av de aggregerade funktionerna. I det här fallet blir resultatet av COUNT-funktionerna noll, och resultatet av alla andra funktioner blir NULL.
• Argument aggregerad funktion kan inte i sig innehålla aggregerade funktioner(funktion från funktion). De där. i en fråga är det omöjligt att till exempel få maximala medelvärden.
• Resultatet av att köra COUNT-funktionen är heltal(HELTAL). Andra aggregerade funktioner ärver datatyperna för de värden de bearbetar.
• Om SUM-funktionen ger ett resultat som är större än maxvärdet för den datatyp som används, fel.

Så, om begäran inte innehåller GROUP BY-satser, Den där samla funktioner ingår i SELECT-sats, exekveras på alla resulterande frågerader. Om begäran innehåller GROUP BY-klausul, varje uppsättning rader som har samma värden som en kolumn eller grupp av kolumner som anges i GROUP BY-klausul, utgör en grupp och samla funktioner utförs för varje grupp separat.

HA erbjudande

Om WHERE klausul definierar ett predikat för filtrering av rader HA erbjudande gäller efter gruppering för att definiera ett liknande predikat som filtrerar grupper efter värden samla funktioner. Denna klausul behövs för att validera de värden som erhålls med hjälp av aggregerad funktion inte från enskilda rader i postkällan definierad i FRÅN klausul, och från grupper av sådana linjer. Därför kan en sådan kontroll inte ingå i WHERE klausul.

Steg i detta skede lära sig SQL frågor är avsedda att demonstrera det faktum att SQL inte bara kan göra komplexa urval och sortera data, utan också beräkna resultaten av matematiska funktioner, utföra texttransformation, gruppera poster, etc. Mer exakt, det är inte SQL som kan göra allt detta, utan de som stöder det. SQL, med sina standarder, formulerar bara kraven för samma DBMS.

Steg 15. Funktioner SUMMA, AVG, MIN, MAX, COUNT...

Det här steget kommer att visa dig hur du använder enkla funktioner i SQL, som summa, minimi- och maxvärden, medelvärde, etc. Låt oss genast börja med ett exempel på att härleda den genomsnittliga tjänstgöringstiden för alla anställda.

VÄLJ AVG(D_STAFF.S_EXPERIENCE) SOM [GENOMsnittlig ERFARENHET AV ANSTÄLLDA] FRÅN D_STAFF

SQL-funktion AVG.

På samma sätt kan du beräkna minimi- och maxvärden (MIN, MAX), totalsumman (SUM), etc. Jag råder dig att prova detta med hjälp av träningsprogrammet. Det är värt att försöka definiera ytterligare kriterier för att välja poster som är involverade i att bestämma slutvärdet för en funktion med hjälp av WHERE-satsen.

Funktionerna som anges ovan använder hela frågeresultatet för att bestämma deras värde. Sådana funktioner kallas aggregat . Det finns också ett antal funktioner vars argument inte är alla värden i en kolumn som definieras i begäran, utan varje enskilt värde för varje enskild rad i resultatet. Ett exempel på en sådan funktion är SQL-funktionen för att beräkna längden på ett textfält LEN:

VÄLJ S_NAME, LEN(D_STAFF.S_NAME) SOM [LENGTH] FRÅN D_STAFF

Kan användas överlagring av SQL-funktioner som visas nedan och beräkna maximalt värde längden på fältet S_NAME.

VÄLJ MAX(LAN(D_STAFF.S_NAME)) SOM [MAXIMAL LENGTH] FRÅN D_STAFF

SQL-funktion MAX.

Tja, avslutningsvis, alla tillsammans.

VÄLJ SUMMA(D_STAFF.S_EXPERIENCE) SOM [SUMMA], AVG(D_STAFF.S_EXPERIENCE) SOM [AVERAGE], MIN(D_STAFF.S_EXPERIENCE) SOM [MINIMUM], MAX(D_STAFF.S_EXPERIENCE) SOM [MAXIMUM], ANTAL(*) SOM [ANTAL REKORD], MAX(LEN(D_STAFF.S_NAME)) SOM [MAXIMAL LENGTH] FRÅN D_STAFF

Ett exempel på användning av aggregerade SQL-funktioner.

Lägg märke till argumentet till COUNT-funktionen. Jag angav (*) som ett argument eftersom jag vill få det totala antalet poster. Om du till exempel anger COUNT(S_NAME), blir resultatet antalet icke-tomma S_NAME-värden (S_NAME ÄR INTE NULL). Det skulle vara möjligt att skriva COUNT(DISTINCT S_NAME) och få antalet unika S_NAME-värden, men MS Access stöder tyvärr inte detta alternativ. I vårt exempel ger COUNT(S_NAME) och COUNT(*) exakt samma resultat.

Steg 16: Konvertera text

Ofta, textvärden ifyllda av användare programvara på olika sätt: vem skriver fullt namn med stor bokstav, vem är det inte; någon skriver allt med stora bokstäver. Många rapporteringsformulär kräver ett enhetligt tillvägagångssätt, och inte bara rapporteringsformulär. För att lösa detta problem har SQL två funktioner UCASE och LCASE. Ett exempel på en begäran och resultatet av dess behandling visas nedan:

VÄLJ UCASE(D_STAFF.S_NAME) AS , LCASE(D_STAFF.S_NAME) SOM FRÅN D_STAFF

SQL-funktionerna UCASE och LCASE.

Steg 17. SQL och arbeta med strängar

Det finns också en underbar MID-funktion som hjälper dig att lösa problemet med att extrahera en del av en sträng från hela värdet av ett textfält. Här också bästa kommentaren Det kommer att finnas ett exempel - ett exempel på att "mobba" namnen på användarprofiler.

VÄLJ UCASE(MID(P_NAME,3,5)) FRÅN D_PROFIL

Överlagring av SQL-funktioner UCASE och MID.

Vi "klippte ut" 5 tecken vardera från profilnamnsvärdena, med början från den 3:e, och slutade med ett gäng upprepade "skräp". För att bara lämna unika värden kommer vi att använda nyckelord DISTINKT.

VÄLJ DISTINKT UCASE(MID(P_NAME,3,5)) SOM FRÅN D_PROFIL

Välja unika aggregerade funktionsvärden.

Ibland måste man använda uttryck med LEN-funktionen som argument till MID-funktionen. I följande exempel vi visar redan de sista 5 tecknen i profilnamnen.

VÄLJ UCASE(MID(P_NAME,LEN(P_NAME)-4,5)) FRÅN D_PROFIL

Använder SQL LEN-funktioner.

Steg 18. Använda SQL-funktioner i postvalskriterierna. HA operatör

Efter att ha förstått funktionerna uppstår frågan nästan omedelbart: hur kan de användas i kriterierna för att välja poster? Vissa funktioner, nämligen de som inte är aggregerade funktioner, är ganska lätta att använda. Här är till exempel en lista över anställda vars fullständiga namn. mer än 25 tecken.

VÄLJ S_NAME FRÅN D_STAFF WHERE LEN(D_STAFF.S_NAME) > 25

Använder den icke-aggregerade LEN-funktionen i SQL-termer begäran.

Tja, om du till exempel behöver visa identifierarna för alla positioner som är upptagna av mer än en anställd i företaget, så kommer detta tillvägagångssätt inte att fungera. Vad jag menar är att följande fråga kanske inte är meningsfull, men den är felaktig ur en strukturerad frågesynpunkt. Detta beror på det faktum att för att korrekt bearbeta sådana SQL-frågor med hjälp av aggregerade funktioner räcker det inte med en linjär passage genom personalposter.

VÄLJ S_POSITION FRÅN D_STAFF WHERE COUNT(S_POSITION)>1

För sådana fall introducerades nyckelordet HAVING i SQL, vilket kommer att hjälpa oss att lösa problemet med befattningar och anställda.

VÄLJ S_POSITION FRÅN D_STAFF GROUP EFTER S_POSITION MED ANTAL(S_POSITION)>1

Använda aggregerade funktioner i SQL-frågevillkor.

Steg 19. Gruppering av data i SQL-frågeresultat med hjälp av operatorn GROUP BY

GROUP BY-operatorn behövs för att gruppera värdena för aggregerade funktioner efter värdena för deras associerade fält. Det behövs när vi vill använda det aggregerade funktionsvärdet i postvalskriterierna (föregående steg). Det behövs också när vi vill inkludera värdet av en aggregerad funktion i frågeresultatet. Men i verkligheten enkel version gruppering motsvarar att markera de unika värdena i en kolumn. Låt oss titta på ett exempel på begäran.

VÄLJ S_POSITION FRÅN D_STAFF

Och det här är två alternativ som gör att du bara kan visa unika S_POSITION-värden.

VÄLJ S_POSITION FRÅN D_STAFF GROUP EFTER S_POSITION

VÄLJ DISTINCT S_POSITION FRÅN D_STAFF

Nåväl, låt oss nu återgå till att gruppera funktionsvärden efter värdena för de fält som är associerade med dem. För varje användarprofil kommer vi att visa antalet poster som är associerade med den i tabellen D_STAFF_PROFILE.

VÄLJ PROFILE_ID SOM , ANTAL(PROFILE_ID) SOM [ANTAL REKORD] FRÅN D_STAFF_PROFILE GROUP BY PROFILE_ID

Använda en SQL-aggregatfunktion tillsammans med gruppering.

GROUP BY-operatorn låter dig också gruppera resultatet av en fråga efter mer än ett fält, separerade med kommatecken. Jag hoppas att det efter allt ovanstående inte behövs några ytterligare kommentarer om resultatet av den senaste frågan.

VÄLJ S.S_POSITION AS , S.S_NAME SOM [ANSTÄLLD], ANTAL(SP.STAFF_ID) SOM [ANTAL REKORD I TABELL D_STAFF_PROFILE] FRÅN D_STAFF S, D_STAFF_PROFILE SP DÄR S.XD_IID=SP.STAFF_ID GRUPPER AV S,S. S_NAME

Radgruppering SQL-resultat fråga på flera fält.

Låt oss lära oss att sammanfatta. Nej, det här är inte resultaten av att studera SQL, utan resultaten av värdena i kolumnerna i databastabellerna. Aggregat SQL-funktioner agera på värdena i en kolumn för att producera ett enda resulterande värde. De vanligaste SQL-aggregatfunktionerna är SUM, MIN, MAX, AVG och COUNT. Det är nödvändigt att skilja mellan två fall av användning av aggregerade funktioner. Först används aggregerade funktioner på egen hand och returnerar ett enda resultatvärde. För det andra används aggregerade funktioner med SQL GROUP BY-satsen, det vill säga gruppering efter fält (kolumner) för att erhålla de resulterande värdena i varje grupp. Låt oss först överväga fall av användning av aggregerade funktioner utan gruppering.

SQL SUM-funktion

SQL SUM-funktionen returnerar summan av värdena i en databastabellkolumn. Det kan endast tillämpas på kolumner vars värden är siffror. SQL-frågor för att få den resulterande summan att börja så här:

VÄLJ SUMMA (COLUMN_NAME) ...

Detta uttryck följs av FROM (TABLE_NAME), och sedan kan ett villkor anges med hjälp av WHERE-satsen. Dessutom kan kolumnnamnet föregås av DISTINCT, vilket betyder att endast unika värden kommer att räknas. Som standard tas alla värden i beaktande (för detta kan du specifikt ange inte DISTINCT, utan ALLA, men ordet ALLA krävs inte).

Exempel 1. Det finns en företagsdatabas med uppgifter om dess divisioner och anställda. Tabellen Personal har också en kolumn med uppgifter om anställdas löner. Urvalet från tabellen ser ut så här (för att förstora bilden, klicka på den med vänster musknapp):

För att få summan av alla löner, använd följande fråga:

VÄLJ SUMMA (Lön) FRÅN Personal

Denna fråga returnerar värdet 287664.63.

Och nu . I övningarna börjar vi redan komplicera uppgifterna och föra dem närmare de som vi stöter på i praktiken.

SQL MIN funktion

SQL MIN-funktionen fungerar också på kolumner vars värden är siffror och returnerar minimum av alla värden i kolumnen. Denna funktion har en syntax som liknar den för SUM-funktionen.

Exempel 3. Databasen och tabellen är desamma som i exempel 1.

Behöver veta minimum lön anställda på avdelningen med nummer 42. För att göra detta, skriv följande begäran:

Frågan returnerar värdet 10505.90.

Och igen träna för oberoende beslut . I den här och några andra övningar behöver du inte bara personaltabellen utan också orgtabellen, som innehåller data om företagets divisioner:

Exempel 4. Organisationstabellen läggs till i tabellen Personal, som innehåller data om företagets avdelningar. Dra tillbaka minimal mängdår arbetat av en anställd på en avdelning i Boston.

SQL MAX-funktion

SQL MAX-funktionen fungerar på liknande sätt och har en liknande syntax, som används när du behöver bestämma maxvärdet bland alla värden i en kolumn.

Exempel 5.

Vi måste ta reda på den maximala lönen för anställda på avdelning nummer 42. För att göra detta, skriv följande begäran:

Frågan returnerar värdet 18352.80

Det är dags övningar för oberoende lösning.

Exempel 6. Vi arbetar återigen med två bord - Personal och Org. Visa namnet på avdelningen och det maximala värdet av den provision som en anställd har fått på avdelningen som tillhör gruppen av avdelningar (Division) Östra. Använda sig av JOIN (sammanfoga tabeller) .

SQL AVG-funktion

Det som står angående syntaxen för de tidigare beskrivna funktionerna gäller även för SQL AVG-funktionen. Denna funktion returnerar medelvärdet av alla värden i en kolumn.

Exempel 7. Databasen och tabellen är desamma som i de tidigare exemplen.

Anta att du vill ta reda på den genomsnittliga tjänstgöringstiden för anställda på avdelning nummer 42. För att göra detta, skriv följande fråga:

Resultatet blir 6,33

Exempel 8. Vi arbetar med ett bord - Personal. Dra tillbaka genomsnittslön anställda med 4 till 6 års erfarenhet.

SQL COUNT-funktion

SQL COUNT-funktionen returnerar antalet poster i en databastabell. Om du anger SELECT COUNT(COLUMN_NAME) ... i frågan blir resultatet antalet poster utan att ta hänsyn till de poster där kolumnvärdet är NULL (odefinierat). Om du använder en asterisk som argument och startar en SELECT COUNT(*) ...-fråga, blir resultatet antalet av alla poster (rader) i tabellen.

Exempel 9. Databasen och tabellen är desamma som i de tidigare exemplen.

Du vill veta antalet anställda som får provision. Antalet anställda vars Comm-kolumnvärden inte är NULL kommer att returneras av följande fråga:

VÄLJ ANTAL (Komm) FRÅN Personal

Resultatet blir 11.

Exempel 10. Databasen och tabellen är desamma som i de tidigare exemplen.

Om du vill ta reda på det totala antalet poster i tabellen, använd sedan en fråga med en asterisk som argument till COUNT-funktionen:

VÄLJ ANTAL (*) FRÅN Personal

Resultatet blir 17.

I nästa övning för oberoende lösning du måste använda en underfråga.

Exempel 11. Vi arbetar med ett bord - Personal. Visa antalet anställda på planeringsavdelningen (Plains).

Aggregera funktioner med SQL GROUP BY

Låt oss nu titta på hur vi använder aggregerade funktioner tillsammans med SQL GROUP BY-satsen. SQL GROUP BY-satsen används för att gruppera resultatvärden efter kolumner i en databastabell. Webbplatsen har en lektion tillägnad separat till denna operatör .

Exempel 12. Det finns en databas för annonsportalen. Den har en annonstabell som innehåller information om annonser som skickats in för veckan. Kolumnen Kategori innehåller data om stora annonskategorier (till exempel Fastigheter) och Kolumnen Delar innehåller data om mindre delar som ingår i kategorierna (till exempel är delarna Lägenheter och Sommarhus delar av kategorin Fastigheter). Kolumnen Enheter innehåller uppgifter om antalet inskickade annonser och kolumnen Pengar innehåller uppgifter om hur mycket pengar som tagits emot för att skicka in annonser.

Kategori	Del	Enheter	Pengar
Transport	Bilar	110	17600
Fastighet	Lägenheter	89	18690
Fastighet	Dachas	57	11970
Transport	Motorcyklar	131	20960
Byggmaterial	Brädor	68	7140
Elektroteknik	TV-apparater	127	8255
Elektroteknik	Kylskåp	137	8905
Byggmaterial	Regips	112	11760
Fritid	Böcker	96	6240
Fastighet	Hemma	47	9870
Fritid	musik	117	7605
Fritid	Spel	41	2665

Använder sig av SQL-sats GRUPPER EFTER, hitta summan pengar som erhållits för att lägga upp annonser i varje kategori. Vi skriver följande begäran:

VÄLJ Kategori, SUMMA (Pengar) SOM Pengar FRÅN Annonser GRUPP EFTER Kategori

Exempel 13. Databasen och tabellen är desamma som i föregående exempel.

Använd SQL GROUP BY-satsen och ta reda på i vilken del av varje kategori inlämningen gjordes största antal annonser Vi skriver följande begäran:

VÄLJ Kategori, Del, MAX (Enheter) AS Maximum FRÅN Annonser GRUPP EFTER Kategori

Resultatet blir följande tabell:

Final och individuella värderingar i ett bord kan du få kombinera frågeresultat med UNION-operatorn .

Relationsdatabaser data och SQL-språk