Rapportering Samlede data ved hjelp av konsernfunksjoner
SQL har mange forhåndsdefinerte samlede funksjoner som kan brukes til å skrive spørringer for å produsere akkurat denne typen information.The GROUP BY-ledd angir hvordan å gruppere rader fra en datatabell når samle informasjon, mens HAVING filtrerer ut rader som ikke hører hjemme i spesifiserte grupper.
Aggregate funksjoner utføre en rekke handlinger som å telle alle radene i en tabell, i snitt en kolonne data, og summere numeriske data. Aggregater kan også søke i en tabell for å finne den høyeste "MAX" eller laveste "MIN" verdier i en kolonne. Som med andre typer søk, kan du begrense eller filtrere ut radene disse funksjonene virker på med WHERE-leddet. For eksempel, hvis en leder må vite hvor mange ansatte jobber i en organisasjon, den samlede funksjon kalt COUNT (*) kan brukes til å produsere denne information.The COUNT (*) -funksjonen vist nedenfor SELECT-setning teller alle radene i en . tabellen
Velg COUNT (*) FROM ansatte; COUNT (*) ---------- 24
Resultatet tabellen for COUNT (*) -funksjonen er en enkelt kolonne fra en enkelt rad kjent som en skalar resultat eller verdi. Legg merke til at resultattabellen har en kolonneoverskrift som tilsvarer navnet på samlefunksjon spesifisert i SELECT-leddet.
Noen av de mest brukte aggregerte funksjoner er som følger -
SUM ([ALL | DISTINCT] uttrykk) AVG ([ALL | DISTINCT] uttrykk) ANTALL ([ALL | DISTINCT] uttrykk) COUNT (* ) MAX (uttrykk) MIN (uttrykk)
ALLE og ulike søkeord er valgfrie, og gjøre som de gjør med SELECT klausuler som du har lært å write.The ALL søkeord er standard der alternativet er allowed.The uttrykk oppført i syntaks kan være en konstant, en funksjon, eller en kombinasjon av kolonnenavn, konstanter og funksjoner forbundet med aritmetikk operators.However, er samlet de mest brukte funksjonene med et kolonnenavn. Unntatt ANTALL-funksjonen, alle de samlede funksjoner vurdere NULL verdier
Det er to regler som du må forstå og følge når du bruker aggregater.
Aggregate funksjoner som kan brukes både i SELECT Og etter å ha klausuler (den HAVING er dekket senere i dette kapitlet).
Aggregate funksjoner kan ikke brukes i en WHERE-leddet. Brudd vil produsere Oracle ORA-00934 gruppe funksjon er ikke tillatt her feilmelding.
Illustrasjoner
under SELECT spørring teller antall ansatte i organisasjonen.
Velg COUNT (*) CountFROM ansatte, COUNT ----- 24
under SELECT spørring returnerer gjennomsnittet av lønningene til ansatte i organisasjonen
SELECT AVG (Lønn) average_salFROM ansatte;. AVERAGE_SAL- ---------- 15694
under SELECT spørring returnerer summen av lønningene til ansatte i organisasjonen
SELECT SUM (Lønn) total_salFROM ansatte;. TOTAL_SAL -------- - 87472
under SELECT spørring returnerer de eldste og nyeste innleide datoene for ansatte i organisasjonen
SELECT MIN (hire_date) eldste, MAX (hire_date) latestFROM ansatte; ELDSTE SISTE ---------. ----------- 16-JAN-83 01-JUL-2012
GROUP BY
Aggregate funksjoner er vanligvis brukes i forbindelse med et GROUP BY-ledd. GROUP BY-leddet gjør at du kan bruke aggregerte funksjoner for å svare på mer komplekse ledelsesmessige spørsmål som:
Hva er gjennomsnittlig lønn for ansatte i hver avdeling
Hvor mange ansatte arbeider i hver avdeling?
Hvor mange ansatte arbeider på et bestemt prosjekt?
Grupper etter funksjon etablerer datagrupper basert på søyler og samles informasjon i en gruppe bare. Den gruppering kriteriet er definert av kolonnene spesifisert i GROUP BY-ledd. Etter dette hierarkiet, er data først organisert i grupper og deretter WHERE klausulen begrenser radene i hver gruppe.
Retningslinjer for å bruke GROUP BY-ledd
(1) Alle de avhengige kolonner eller søyler brukt i GROUP BY funksjonen må danne grunnlaget for gruppering, må derfor tas med i GROUP BY-leddet også
SELECT DEPARTMENT_ID, SUM (lønn) Fra ansatte, DEPARTMENT_ID, * FEIL på linje 2.: ORA-00937: ikke en enkelt- gruppe gruppe funksjon
(2) GROUP BY-ledd støtter ikke bruk av kolonnen alias, men de faktiske navnene.
(3) GROUP BY-ledd kan bare brukes med aggregerte funksjoner som SUM, AVG, COUNT, MAX, og MIN.If den brukes med én rad, funksjoner, kast og unntak som Oracle. "ORA-00979: ikke et GROUP BY uttrykket"
(4) Samlede funksjoner kan ikke brukes i et GROUP BY klausulen. Oracle vil returnere "ORA-00934: gruppe funksjonen ikke tillatt" her feilmelding
Under søket viser tellingen av ansatte som arbeider i hver avdeling
SELECT DEPARTMENT_ID, COUNT (*) FROM employeesGROUP BY DEPARTMENT_ID..;
På samme måte under spørring for å finne summen av lønn for respektive jobb IDer i hver avdeling. Legg merke til gruppen er etablert basert på Institutt og Job id. Slik at de vises i GROUP BY-leddet
Velg DEPARTMENT_ID, JOB_ID, SUM (SAL) FRA employeesGROUP BY DEPARTMENT_ID, JOB_ID;.
under søket produserer også samme resultat. Vær oppmerksom på at grupperingen er basert på avdelingen id og jobb id kolonner, men ikke brukt for visning formål
Velg SUM (lønn) Fra employeesGROUP BY DEPARTMENT_ID, JOB_ID;.
Bruk av DISTINCT, ALLE søkeord med Aggregate funksjoner
Ved å angi nøkkelordet DISTINCT med input parameter, mener gruppen av funksjon bare den unike verdien av kolonnen for aggregering. Ved å angi ALL søkeord med input parameter, gruppe av funksjon vurderer alle verdiene i kolonnen for aggregering, inkludert nullverdier og duplikater. ALL er standardspesifikasjonen.
Den HAVING
HAVING brukes for aggregerte funksjoner på samme måte som en WHERE klausulen brukes for kolonnenavn og expressions.Essentially, det å ha og HVOR klausuler gjøre det samme, det er filter rader fra inkludering i følge tabell basert på en betingelse. Selv om det kan se ut som en HAR klausul filtrerer ut grupper, det gjør not.Rather, en HAR klausul filtrerer rader.
Når alle rader for en gruppe er eliminert så er group.To oppsummere, de viktigste forskjellene mellom WHERE og ha klausuler er:.
En WHERE klausulen brukes til å filtrere rader FØR grupperingen handling (dvs. før beregning av de samlede funksjoner)
En HAVING filtrerer rader ETTER GROUPING handling (dvs. etter beregning av de samlede funksjoner).
SELECT JOB_ID, SUM (lønn) Fra employeesGROUP BY JOB_IDHAVING SUM (lønn)> 10000;
HAR klausul er en betinget alternativ som er direkte relatert til GROUP BY-leddet alternativet, fordi en HAR klausul eliminerer rader fra et resultat tabell basert på resultatet av et GROUP BY-ledd.
Velg department_id, AVG (Lønn) FROM employeesHAVING AVG (Lønn)> 33000; FEIL på linje 1: ORA-00937: ikke en enkeltgruppe gruppe funksjon