SQL-summa med villkor. SQL-aggregatfunktioner - SUM, MIN, MAX, AVG, COUNT. Parametrar eller argument
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 antalet olika modeller som produceras av tillverkare A, kan frågan formuleras enligt följande (med hjälp av det faktum att i produkttabellen registreras varje modell en gång):
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 utgångslinjer 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, bör 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. Observera att för GROUP BY behandlas alla NULL-värden 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 rader med samma modellvärde bildar en grupp, och utdata från SELECT 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 t.ex. omöjligt att erhålla 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 använda datatypen, fel.
Så, om begäran inte innehåller GROUP BY-klausuler, 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.
SUM-funktionen i SQL-språket, trots sin enkelhet, används ganska ofta när man arbetar med en databas. Med dess hjälp är det bekvämt att få några mellanliggande eller slutliga resultat utan att tillgripa hjälpen av extra DBMS-verktyg.
Funktionssyntax
I majoriteten SQL-språk syntaxen för summan är densamma - endast namnet på fältet eller någon aritmetisk operation av flera av dem används som ett argument, över vilket summan krävs.
I undantagsfall är det möjligt att överföra ett specifikt värde som ett tal eller variabel, men sådana "scheman" används praktiskt taget inte, eftersom de inte har mycket värde. Nedan är funktionssyntaxen i SQL:
sum(a) - här används något numeriskt värde eller uttryck som parameter a
Det är värt att notera att före parametern kan du ställa in nyckelord, till exempel DISTINCT eller ALL, som endast tar unika respektive alla värden.
Exempel på användning av SUM i SQL
För att helt förstå hur funktionen fungerar är det värt att överväga flera exempel. I SQL kan SUM användas både som ett returresultat och som ett mellanvärde, till exempel för att testa ett villkor.
För det första fallet, överväg alternativet när du behöver returnera försäljningsbeloppet för varje produkt, med hänsyn till att antalet gjorda köp kan vara i plural. För att få resultatet räcker det med att köra följande fråga:
VÄLJ Produkt, summa(PurchaseAmount) FRÅN Försäljningsgrupp Efter produkt;
Som svar på detta kommando det kommer att finnas en unik lista över produkter med det totala inköpsbeloppet för var och en av dem.
För det andra exemplet måste du få en lista över produkter vars försäljningsbelopp översteg ett visst värde, till exempel 100. Du kan få resultatet för denna uppgift på flera sätt, varav det mest optimala är att utföra en begäran:
VÄLJ produkt FRÅN (VÄLJ produkt, summa(köpbelopp) som belopp FRÅN Försäljning) VAR Summa > 100.
SQL - Lektion 11. Totalt antal funktioner, beräknade kolumner och vyer
Totalfunktioner kallas också statistiska, aggregerade eller summafunktioner. Dessa funktioner bearbetar en uppsättning strängar för att räkna och returnera ett enda värde. Det finns bara fem sådana funktioner:- Funktionen AVG() returnerar medelvärdet för en kolumn.
- COUNT() Funktion returnerar antalet rader i en kolumn.
- Funktionen MAX() returnerar det största värdet i en kolumn.
- Funktionen MIN() returnerar det minsta värdet i kolumnen.
- SUM() Funktionen returnerar summan av kolumnvärdena.
VÄLJ MIN(pris), MAX(pris), AVG(pris) FRÅN priser;
Nu vill vi ta reda på hur mycket varorna togs till oss av leverantören "House of Printing" (id=2). Att göra en sådan begäran är inte så lätt. Låt oss fundera på hur man komponerar det:
1. Först, från tabellen Supplies (incoming) väljer du identifierarna (id_incoming) för de leveranser som utfördes av leverantören "Print House" (id=2):
2. Från tabellen Supply Journal (magazine_incoming) måste du nu välja varorna (id_product) och deras kvantiteter (kvantitet), som utfördes i de leveranser som finns i punkt 1. Det vill säga, frågan från punkt 1 blir kapslad:
3. Nu måste vi lägga till priserna för de hittade produkterna till den resulterande tabellen, som lagras i tabellen Priser. Det vill säga, vi måste gå med i Supply Magazine (magazine_incoming) och Prices-tabellerna med hjälp av kolumnen id_product:
4. Den resulterande tabellen saknar helt klart kolumnen Belopp, det vill säga beräknad kolumn. Möjligheten att skapa sådana kolumner finns i MySQL. För att göra detta behöver du bara ange i frågan namnet på den beräknade kolumnen och vad den ska beräkna. I vårt exempel kommer en sådan kolumn att kallas summa, och den kommer att beräkna produkten av kolumnerna kvantitet och pris. Namnet på den nya kolumnen skiljs åt med ordet AS:
VÄLJ magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming_vendor=WHERE);
5. Bra, allt vi behöver göra är att lägga samman summakolumnen och slutligen ta reda på hur mycket leverantören "House of Printing" gav oss varorna för. Syntaxen för att använda SUM()-funktionen är följande:
SELECT SUM(kolumnnamn) FRÅN tabellnamn;
Vi vet namnet på kolumnen - summa, men vi har inte namnet på tabellen, eftersom det är resultatet av en fråga. Vad ska man göra? För sådana fall har MySQL Views. En vy är en urvalsfråga som får ett unikt namn och som kan lagras i en databas för senare användning.
Syntaxen för att skapa en vy är följande:
CREATE VIEW view_name AS-begäran;
Låt oss spara vår begäran som en vy med namnet report_vendor:
SKAPA VISNING report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (VÄLJ id_incoming WHERE id_incoming WHERE id_incoming );
6. Nu kan du använda den sista funktionen SUM():
VÄLJ SUMMA(summa) FRÅN rapportleverantör;
Så vi uppnådde resultatet, även om vi för detta var tvungna att använda kapslade frågor, sammanfogningar, beräknade kolumner och vyer. Ja, ibland måste du tänka för att få ett resultat, utan detta kommer du ingenstans. Men vi berörde två mycket viktiga ämnen - beräknade kolumner och vyer. Låt oss prata om dem mer i detalj.
Beräknade fält (kolumner)
Med ett exempel tittade vi på ett matematiskt beräknat fält idag. Här skulle jag vilja tillägga att du inte bara kan använda multiplikationsoperationen (*), utan även subtraktion (-), addition (+) och division (/). Syntaxen är som följer:VÄLJ kolumnnamn 1, kolumnnamn 2, kolumnnamn 1 * kolumnnamn 2 AS calculated_column_name FROM tabellnamn;
Den andra nyansen är nyckelordet AS, vi använde det för att ställa in namnet på den beräknade kolumnen. Faktum är att det här nyckelordet används för att ställa in alias för alla kolumner. Varför är detta nödvändigt? För kodreducering och läsbarhet. Vår syn kan till exempel se ut så här:
CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS summa FROM magazine_incoming AS A, priser AS B WHERE A.id_product= B.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
Håller med om att detta är mycket kortare och tydligare.
Representation
Vi har redan tittat på syntaxen för att skapa vyer. När vyer väl har skapats kan de användas på samma sätt som tabeller. Det vill säga köra frågor mot dem, filtrera och sortera data och kombinera vissa vyer med andra. Å ena sidan är detta ett mycket bekvämt sätt att förvara ofta använda komplexa frågor(som i vårt exempel).Men kom ihåg att vyer inte är tabeller, det vill säga att de inte lagrar data, utan bara hämtar dem från andra tabeller. För det första, när data i tabellerna ändras, kommer presentationsresultaten också att förändras. Och för det andra, när en begäran görs till en vy, genomsöks den nödvändiga informationen, det vill säga prestanda för DBMS reduceras. Därför bör du inte missbruka dem.
I den lärobok du kommer att lära dig hur du använder SUM-funktion V SQL Server(Transact-SQL) med syntax och exempel.
Beskrivning
I SQL Server (Transact-SQL) SUM-funktion returnerar det totala värdet av ett uttryck.
Syntax
Syntaxen för SUM-funktionen i SQL Server (Transact-SQL) är:
ELLER syntaxen för SUM-funktionen när du grupperar resultat efter en eller flera kolumner är:
Parametrar eller argument
expression1 , expression2 , ... expression_n är uttryck som inte ingår i SUM-funktionen och måste inkluderas i GROUP BY-satsdelen i slutet av SQL-satsen.
aggregate_expression är kolumnen eller uttrycket som kommer att aggregeras.
tabeller - tabeller som du vill hämta poster från. Det måste finnas minst en tabell i FROM-satsen.
WHERE villkor - valfritt. Dessa är villkoren som måste uppfyllas för de valda posterna.
Ansökan
SUM-funktionen kan användas i följande versioner av SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Exempel med ett fält
Låt oss titta på några exempel på SQL Server SUM-funktioner för att förstå hur man använder SUM-funktionen i SQL Server (Transact-SQL).
Du kan till exempel ta reda på den totala kvantiteten för alla produkter vars kvantitet är större än 10.
I det här exemplet på SUM-funktionen har vi kallat uttrycket SUM(quantity) "Total Quantity". När du returnerar en resultatuppsättning - "Total kvantitet" visas som fältnamnet.
Exempel på användning av DISTINCT
Du kan använda operatorn DISTINCT i SUM-funktionen. Till exempel den nedan SQL-sats returnerar den totala lönen med unika lönevärden där lönen är under $29 000 per år.
Om de två lönerna var $24 000 per år, skulle bara ett av dessa värden användas i SUM-funktionen.
Exempel på användning av formeln
Uttrycket i SUM-funktionen behöver inte vara ett enda fält. Du kan också använda formeln. Du kan till exempel beräkna den totala provisionen.
Transact-SQL
VÄLJ SUMMA(försäljning * 0,03) SOM "Total provision" FRÅN beställningar;
VÄLJ SUMMA (försäljning * 0,03 ) SOM "Total provision" FRÅN beställningar ; |
Exempel på användning av GROUP BY
I vissa fall måste du använda operatorn GROUP BY med SUM-funktionen.
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. SQL-aggregatfunktioner arbetar 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 resulterande vä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. 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).
Om du vill köra databasfrågor från den här lektionen på MS SQL Server, men detta DBMS inte är installerat på din dator, kan du installera det med instruktionerna på denna länk .
Först kommer vi att arbeta med företagsdatabasen - Företag1. Skriptet för att skapa denna databas, dess tabeller och fylla tabellerna med data finns i filen på denna länk .
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änder vi följande fråga (på MS SQL Server - med föregående konstruktion USE company1;):
VÄLJ SUMMA (Lön) FRÅN Personal
Denna fråga returnerar värdet 287664.63.
Och nu . I övningarna har vi redan börjat komplicera uppgifterna och föra dem närmare de man 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 fråga (på MS SQL Server - med föregående konstruktion USE company1;):
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. Skriv ut det minsta antalet år som en anställd arbetat 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 i avdelning nummer 42. För att göra detta, skriv följande fråga (på MS SQL Server - med prefixet USE company1;):
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.
Låt oss säga att du vill ta reda på den genomsnittliga tjänstgöringstiden för anställda i avdelning nummer 42. För att göra detta, skriv följande fråga (på MS SQL Server - med den föregående konstruktionen USE company1;):
Resultatet blir 6,33
Exempel 8. Vi arbetar med ett bord - Personal. Visa medellönen för 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 (på MS SQL Server - med prefixet USE company1;):
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 (på MS SQL Server - med föregående konstruktion USE company1;):
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å att använda 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 .
Vi kommer att arbeta med databasen "Annonsportal 1". Skriptet för att skapa denna databas, dess tabell och fylla i datatabellen finns i filen på denna länk .
Exempel 12. Så 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änd SQL GROUP BY-satsen och ta reda på hur mycket pengar som tjänats in på att publicera annonser i varje kategori. Vi skriver följande fråga (på MS SQL Server - med föregående konstruktion USE adportal1;):
VÄLJ Kategori, SUMMA (Pengar) SOM pengar FRÅN ANNONSER GRUPPER EFTER Kategori
Exempel 13. Databasen och tabellen är desamma som i föregående exempel.
Använd SQL GROUP BY-satsen för att ta reda på vilken del av varje kategori som hade flest listor. Vi skriver följande fråga (på MS SQL Server - med föregående konstruktion USE adportal1;):
VÄLJ Kategori, Del, MAX (Enheter) AS Maximum FRÅN ANNONSGRUPP EFTER Kategori
Resultatet blir följande tabell:
Totala och individuella värden kan erhållas i en tabell kombinera frågeresultat med UNION-operatorn .
Relationsdatabaser och SQL-språk
