Forberedelse til kodeinterviews · Lektion

Aggregater pr. gruppe uden GROUP BY

Brug en korreleret underforespørgsel til at beregne en gruppes maksimum sammen med detaljerækker

Lektion 2 af 413 trin

Aggregater pr. gruppe uden GROUP BY er en gratis Forberedelse til kodeinterviews-lektion på CoddyKit. Dette er lektion 2 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i Forberedelse til kodeinterviews, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Forberedelse til kodeinterviews-kurset indeholder 4 lektioner i alt.

Problemet med detaljer plus aggregat

Et klassisk interviewspørgsmål lyder: "Vis hver række sammen med et aggregat for dens gruppe." Du kan for eksempel vise hver medarbejder sammen med afdelingens maksimale løn på samme række.

En almindelig GROUP BY samler rækkerne, så den kan ikke bevare detaljerne for den enkelte medarbejder. Du har brug for både detaljerækkerne og et tal for gruppen.

En korreleret underforespørgsel løser det elegant: Den beregner gruppeaggregatet for hver detaljerække uden at samle rækkerne.

Hvorfor almindelig GROUP BY ikke fungerer her

Hvis du skriver SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id, får du én række pr. afdeling, og de individuelle navne går tabt.

Hvis du tilføjer name til SELECT uden også at tilføje den til GROUP BY, får du den klassiske fejl "kolonnen skal optræde i GROUP BY".

Intervieweren undersøger, om du forstår, at GROUP BY reducerer kardinaliteten. Hvis du vil bevare detaljerækkerne, skal du beregne aggregatet på en anden måde.

Korreleret underforespørgsel til undsætning

Placér gruppeaggregatet i SELECT-listen som en korreleret underforespørgsel. Hver medarbejderrække udløser en indre MAX, der er afgrænset til medarbejderens afdeling.

Korrelationen e2.dept_id = e1.dept_id knytter aggregatet til den rigtige gruppe, mens den ydre forespørgsel stadig returnerer én række pr. medarbejder.

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;

Sammenligning af hver række med dens gruppe

Når gruppeaggregatet er med i forespørgslen, kan du sammenligne hver række med det. Et hyppigt spørgsmål er: "Find medarbejdere, der tjener mere end gennemsnittet i deres afdeling."

Her står den korrelerede AVG i WHERE, så hver medarbejder testes mod gennemsnittet i sin egen afdeling.

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
);

Beregning af forskellen fra gruppen

Du kan også vise, hvor langt hver række ligger fra sit gruppeaggregat. Hvis du trækker det korrelerede gennemsnit fra, får du forskellen for hver række.

Bemærk, at den samme korrelerede underforespørgsel kan genbruges i flere SELECT-udtryk. Motoren evaluerer den for hver række, hver gang den optræder.

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;

Find den højst lønnede i hver gruppe

Hvis du kun vil returnere den højst lønnede person pr. afdeling, skal du sammenligne hver løn med den korrelerede MAX og beholde de rækker, der matcher.

Dette mønster returnerer lige resultater: Hvis to medarbejdere deler afdelingens maksimum, vises de begge. Håndteringen af lige resultater er ofte interviewers opfølgende spørgsmå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 vinduesfunktioner

Moderne SQL har et renere værktøj: vinduesfunktioner. MAX(salary) OVER (PARTITION BY dept_id) beregner gruppeaggregatet uden at samle rækkerne og uden en ny korreleret gennemgang.

Interviewere bliver gerne imponerede, når du kan give begge løsninger og forklare, at vinduesversionen normalt klarer sig bedre, fordi den gennemgår tabellen én gang.

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

Afvejning mellem korreleret og vinduesbaseret løsning

Begge tilgange returnerer samme resultatstruktur, men de adskiller sig på følgende punkter:

Korreleret underforespørgsel: portabel og anvendelig på meget gamle motorer, men evalueres igen for hver række.
Vinduesfunktion: én gennemgang, langt hurtigere på store tabeller, men kræver understøttelse af SQL-vinduer.

Sig, hvilken du ville vælge, og hvorfor. Til en enkelt forespørgsel på en lille tabel er begge fine, men til analyse i stor skala bør du foretrække vinduesfunktionen.

Eksempel: ordrer over kundens gennemsnit

Anvend mønsteret på ordrer. Vis ordrer, hvis beløb overstiger den bestillende kundes eget gennemsnitlige ordrebeløb.

Den korrelerede AVG er afgrænset via o2.customer_id = o1.customer_id, så hver ordre får sin kundes personlige sammenligningsgrundlag.

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
);

Vær opmærksom på NULL og tomme grupper

Hvis en gruppe kun har én række, er dens gennemsnit lig med rækkens værdi, så salary > avg er falsk, og rækken fjernes. Nævn dette specialtilfælde på eget initiativ.

NULL-lønninger ignoreres også af AVG og MAX, sådan som SQL-aggregaters semantik foreskriver. Hvis alle værdier i en gruppe er NULL, bliver aggregatet NULL, og sammenligninger giver UNKNOWN, så rækken udelades. Det er netop evnen til at forudse disse tilfælde, der adskiller et grundigt svar fra et overfladisk.

Optælling af rangering inden for en gruppe

Du kan udtrykke en rækkes rangering i sin gruppe med en korreleret COUNT. Hvis du vil finde hver medarbejders lønrangering i afdelingen, skal du tælle, hvor mange kolleger der tjener mere.

Rang 1 betyder den højst lønnede. Hvis du lægger 1 til, omdanner du antallet af medarbejdere med højere løn til en position, der starter ved 1, og korrelationen sørger for, at optællingen begrænses til afdelingen.

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;

Hurtigt tjek

Vælg årsagen til, at en korreleret underforespørgsel er bedre end en almindelig GROUP BY til denne opgave.

Besvar den i den interaktive lektion

Opsummering: Gruppeaggregater uden GROUP BY

Vigtigste pointer:

En korreleret underforespørgsel placerer et aggregat for gruppen på hver detaljerække uden at samle rækkerne.
Brug den i SELECT til at vise aggregatet eller i WHERE til at sammenligne hver række med sin gruppe.
Mønsteret = MAX(...) returnerer alle rækker, der deler den højeste værdi.
En vinduesfunktion med PARTITION BY gør det samme i én gennemgang og skalerer normalt bedre.

Præsenter begge løsninger, og begrund dit valg i interviewet.

Gratis at komme i gang

Lær Forberedelse til kodeinterviews med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 90
Lektioner: 360

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Aggregater pr. gruppe uden GROUP BY” gratis?

Ja — hele teksten til “Aggregater pr. gruppe uden GROUP BY” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af Forberedelse til kodeinterviews-kurset, skal du opgradere til CoddyKit PRO. Forberedelse til kodeinterviews-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Aggregater pr. gruppe uden GROUP BY”?

Brug en korreleret underforespørgsel til at beregne en gruppes maksimum sammen med detaljerækker Du øver dig i Forberedelse til kodeinterviews med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Forberedelse til kodeinterviews?

Der kræves ingen tidligere erfaring. Forberedelse til kodeinterviews på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 2 af 4.

Hvor lang tid tager lektionen “Aggregater pr. gruppe uden GROUP BY”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Forberedelse til kodeinterviews-lektion?

Ja. Alle Forberedelse til kodeinterviews-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Anatomi af en korreleret underforespørgsel
Aggregater pr. gruppe uden GROUP BY
Korrelerede EXISTS og NOT EXISTS
Omskrivning af korrelerede underforespørgsler til joins

← Tilbage til Forberedelse til kodeinterviews