Forberedelse til SQL-intervju · leksjon

Aggregater per gruppe uten GROUP BY

Bruk en korrelert underspørring til å beregne maksimum per gruppe sammen med detaljrader.

Leksjon 2 av 413 trinn

Aggregater per gruppe uten GROUP BY er en gratis leksjon i Forberedelse til SQL-intervju på CoddyKit. Dette er leksjon 2 av 4. Du kan lese hele leksjonen gratis nedenfor – og deretter øve praktisk i nettleseren med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i Forberedelse til SQL-intervju, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Forberedelse til SQL-intervju inneholder totalt 4 leksjoner.

Problemet med detaljrader og aggregater

Et vanlig intervjuspørsmål er: «Vis hver rad sammen med et aggregat for gruppen den tilhører.» List for eksempel opp hver ansatt med avdelingens høyeste lønn på samme linje.

En vanlig GROUP BY slår sammen rader, så den kan ikke beholde detaljene per ansatt. De trenger detaljradene og et tall på gruppenivå samtidig.

En korrelert underspørring løser dette elegant: Den beregner gruppeaggregatet for hver detaljrad uten å slå sammen noe.

Hvorfor vanlig GROUP BY mislykkes her

Hvis De skriver SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id, får De én rad per avdeling og mister de individuelle navnene.

Hvis De legger name til i SELECT uten å legge det til i GROUP BY, får De den klassiske feilen «kolonnen må vises i GROUP BY».

Intervjueren undersøker om De forstår at GROUP BY reduserer kardinaliteten. For å beholde detaljradene må De beregne aggregatet på en annen måte.

Korrelert underspørring til unnsetning

Plasser gruppeaggregatet i SELECT-listen som en korrelert underspørring. Hver ansattrad utløser en indre MAX begrenset til den ansattes avdeling.

Korrelasjonen e2.dept_id = e1.dept_id knytter aggregatet til riktig gruppe, mens den ytre spørringen fortsatt returnerer én rad per ansatt.

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT MAX(e2.salary)
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;

Sammenligne hver rad med gruppen sin

Når gruppeaggregatet er med i spørringen, kan De sammenligne hver rad med det. Et vanlig spørsmål er: «Finn ansatte som tjener mer enn gjennomsnittet i avdelingen sin.»

Her ligger den korrelerte AVG-en i WHERE, slik at hver ansatt testes mot gjennomsnittet i sin egen avdeling.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

Beregne forskjellen fra gruppen

De kan også vise hvor langt hver rad ligger fra gruppeaggregatet. Når De trekker fra det korrelerte gjennomsnittet, får De avstanden for hver rad.

Legg merke til at den samme korrelerte underspørringen kan brukes på nytt i flere SELECT-uttrykk. Motoren evaluerer den per rad hver gang den forekommer.

SELECT e1.name,
       e1.salary,
       e1.salary - (SELECT AVG(e2.salary)
                    FROM employees e2
                    WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;

Finne den best betalte i hver gruppe

For å returnere bare den høyest lønnede personen per avdeling sammenligner De hver lønn med den korrelerte MAX-en og beholder treffene.

Dette mønsteret returnerer også like resultater: Hvis to ansatte deler den høyeste lønnen i avdelingen, vises begge. Håndtering av slike like resultater er ofte intervjueren sitt oppfølgingsspørsmål.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

Alternativet med vindusfunksjoner

Moderne SQL har et ryddigere verktøy: vindusfunksjoner. MAX(salary) OVER (PARTITION BY dept_id) beregner gruppeaggregatet uten å slå sammen rader og uten en ny korrelert gjennomgang.

Intervjuere setter pris på at De kan presentere begge løsningene og forklare at vindusversjonen vanligvis gir bedre ytelse fordi den leser tabellen én gang.

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;

Avveininger mellom korrelert og vindusbasert løsning

Begge tilnærmingene returnerer samme resultatstruktur, men de er forskjellige:

Korrelert underspørring: portabel og fungerer på svært gamle motorer, men evalueres på nytt for hver rad.
Vindusfunksjon: én gjennomgang, langt raskere på store tabeller, men krever støtte for vindusfunksjoner i SQL.

Si hvilken De ville valgt, og hvorfor. For en engangsforespørsel på en liten tabell fungerer begge fint. For analyse i stor skala bør De foretrekke vindusfunksjonen.

Gjennomgått eksempel: Bestillinger over kundens gjennomsnitt

Bruk mønsteret på bestillinger. Vis bestillinger der beløpet er høyere enn kundens eget gjennomsnittlige bestillingsbeløp.

Den korrelerte AVG-en begrenses av o2.customer_id = o1.customer_id, slik at hver bestilling får kundens personlige utgangspunkt.

SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

Følg med på tilfeller med NULL og tomme grupper

Hvis en gruppe bare har én rad, er gjennomsnittet likt verdien i raden. Da er salary > avg usant, og raden faller bort. Nevn dette spesialtilfellet på eget initiativ.

NULL-lønninger ignoreres også av AVG og MAX, i tråd med SQLs aggregatsemantikk. Hvis alle verdiene i en gruppe er NULL, blir aggregatet NULL, og sammenligninger gir UNKNOWN, slik at raden utelates. Det er denne typen forutseende som skiller et grundig svar fra et overflatisk.

Telle rangering innenfor en gruppe

De kan uttrykke en rads rangering i gruppen med en korrelert COUNT. For å finne hver ansatts lønnsrangering i avdelingen teller De hvor mange kolleger som tjener mer.

Rang 1 betyr høyest lønn. Når De legger til 1, blir antallet med høyere lønn til en posisjon som starter på 1, og korrelasjonen begrenser den til avdelingen.

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT COUNT(*) + 1
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id
          AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;

Hurtigsjekk

Velg grunnen til at en korrelert underspørring er bedre enn en vanlig GROUP BY for denne oppgaven.

Besvar den i den interaktive leksjonen

Oppsummering: Aggregater per gruppe uten GROUP BY

Viktigste punkter:

En korrelert underspørring legger et aggregat på gruppenivå på hver detaljrad uten å slå dem sammen.
Bruk den i SELECT for å vise aggregatet, eller i WHERE for å sammenligne hver rad med gruppen sin.
Mønsteret = MAX(...) returnerer alle rader som deler førsteplassen.
En vindusfunksjon med PARTITION BY gjør det samme i én gjennomgang og skalerer vanligvis bedre.

Presenter begge løsningene og begrunn valget Deres i intervjuet.

Gratis å komme i gang

Lær deg SQL med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs: 30
Leksjoner: 120

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «Aggregater per gruppe uten GROUP BY» gratis?

Ja – hele teksten i «Aggregater per gruppe uten GROUP BY» er gratis å lese her på nettet. For å øve interaktivt med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt, og for å låse opp resten av Forberedelse til SQL-intervju-kurset, kan du oppgradere til CoddyKit PRO. Kurset i Forberedelse til SQL-intervju inneholder totalt 4 leksjoner.

Hva lærer jeg i «Aggregater per gruppe uten GROUP BY»?

Bruk en korrelert underspørring til å beregne maksimum per gruppe sammen med detaljrader. Du øver på Forberedelse til SQL-intervju med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med Forberedelse til SQL-intervju?

Ingen tidligere erfaring er nødvendig. Forberedelse til SQL-intervju på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 2 av 4.

Hvor lang tid tar leksjonen «Aggregater per gruppe uten GROUP BY»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne Forberedelse til SQL-intervju-leksjonen?

Ja. Alle Forberedelse til SQL-intervju-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

Anatomien til en korrelert underspørring
Aggregater per gruppe uten GROUP BY
Korrelert EXISTS og NOT EXISTS
Skrive om korrelerte underspørringer til joiner

← Tilbake til Forberedelse til SQL-intervju