Näst högsta lönen på fem sätt

"Hitta den näst högsta lönen" är den vanligaste SQL-frågan i intervjuer. Intervjuare tycker om den eftersom det finns många korrekta svar och flera subtila fallgropar.

Anta en tabell employee med kolumnerna id och salary. Uppgiften är att returnera det näst högsta distinkta lönevärdet.

• Om lönerna är 300, 200, 200, 100 är svaret 200, inte den andra raden.
• Om det inte finns någon näst högsta distinkt lön är det förväntade svaret vanligtvis NULL.

I de följande avsnitten löser vi problemet på fem olika sätt och diskuterar när varje metod passar bäst.

Den mest intuitiva lösningen: den näst högsta lönen är den största lönen som är strikt mindre än det övergripande maximumet.

Detta liknar nästan vanlig engelska och fungerar i alla SQL-dialekter. Den inre underfrågan hittar det högsta värdet, och den yttre MAX hittar det största värdet under detta.

Bonus: om det inte finns någon näst högsta distinkt lön aggregerar den yttre MAX noll rader och returnerar automatiskt NULL. Det kostnadsfria NULL-värdet är precis vad intervjuare vill se.

Lägg märke till att vi aldrig använde DISTINCT i metod 1, men dubbletter hanteras ändå korrekt.

Om tre personer tjänar 200 och den bäst betalda tjänar 300, returnerar den inre frågan 300. Det yttre filtret behåller alla rader under 300, och MAX av dem blir 200 oavsett hur många 200-värden som finns.

Det här är den viktiga insikten: aggregeringsfunktioner slår ihop dubbletter åt dig. Många kandidater överkonstruerar lösningen med DISTINCT när aggregeringsfunktionen redan gör rätt sak.

I MySQL och PostgreSQL kan du sortera unika löner i fallande ordning och hoppa över den första.

• OFFSET 1 hoppar över den högsta.
• LIMIT 1 behåller bara nästa.

DISTINCT är avgörande här. Annars skulle dubbletter av den högsta lönen göra att OFFSET 1 hamnar på en upprepning av maximumvärdet i stället för den faktiska näst högsta lönen.

Fallgrop: om det inte finns något andra unikt värde returnerar detta noll rader, inte NULL. Vi åtgärdar det specialfallet i lektion 4.

SQL Server och moderna Oracle stöder inte LIMIT ... OFFSET. I stället använder de ANSI-standardens syntax OFFSET ... FETCH.

Logiken är identisk med metod 2: sortera unika löner i fallande ordning, hoppa över en rad och hämta en. Att känna till syntaxen i olika databasmiljöer visar en intervjuare att du har erfarenhet från verkligheten.

Den moderna, skalbara metoden använder en fönsterfunktion. DENSE_RANK tilldelar rang 1 till den högsta lönen, rang 2 till nästa unika lön och ger lika löner samma rang utan luckor.

Vi beräknar rangen i en underfråga och filtrerar sedan på rang 2 i den yttre frågan. Kom ihåg att du inte kan filtrera direkt på en fönsterfunktion i WHERE, så omslutningen med en underfråga är obligatorisk.

Valet av rangordningsfunktion har betydelse för semantiken hos "unika" värden:

• ROW_NUMBER ger varje rad ett unikt nummer. Två personer med lönen 300 skulle alltså hamna på rad 1 och 2, och rang 2 skulle vara en upprepning av den högsta lönen. Fel.
• RANK lämnar luckor efter lika värden: två löner på 300 får rang 1, och nästa lön hoppar till rang 3. Du skulle missa den på rang 2. Fel.
• DENSE_RANK ger lika värden samma rang utan luckor, så rang 2 är alltid den näst högsta unika lönen. Rätt.

Ett klassiskt knep från tiden före fönsterfunktioner: en lön är den N:e högsta om exakt N-1 unika löner är strikt högre än den.

För den näst högsta lönen vill vi ha exakt en unik lön över den. Metoden är elegant, men kan vara långsam i stora tabeller eftersom den inre räkningen körs för varje yttre rad.

Den generaliseras smidigt till den N:e högsta genom att ändra räkningen till N - 1, vilket är en anledning till att intervjuare gärna ser den.

Anta lönerna: 500, 500, 350, 350, 100.

• Metod 1: MAX är 500 och det största värdet under 500 är 350. Svaret är 350.
• Metod 4 (DENSE_RANK): 500 -> rang 1, 350 -> rang 2, 100 -> rang 3. Rang 2 är 350.
• Metod 5: för lönen 350 är exakt en unik lön (500) högre. Träff. Svaret är 350.

Alla fem metoder ger samma resultat: den näst högsta unika lönen är 350, även när det finns dubbletter.

Råd inför intervjun:

• Formulera frågan först: "Vill du ha unika löner och NULL om ingen finns?" Att be om ett förtydligande ger poäng.
• DENSE_RANK är det bästa standardvalet; metoden generaliseras enkelt till den N:e högsta och till grupper.
• MAX under MAX är den bästa enrads lösningen och returnerar NULL automatiskt.
• LIMIT/OFFSET är kortfattat, men dialektspecifikt och returnerar inga rader i specialfallet.

Att tydligt nämna avvägningarna är det som skiljer ett svar på mellannivå från ett juniorsvar.

Se upp för de här fallgroparna som intervjuare brukar lägga in:

• Att använda ROW_NUMBER i stället för DENSE_RANK och få den högsta lönen två gånger.
• Att glömma DISTINCT i LIMIT/OFFSET-versionen när det finns flera med den högsta lönen.
• Att anta att ORDER BY salary DESC LIMIT 1,1 returnerar ett unikt värde, vilket det inte gör.
• Att returnera den andra raden i stället för det andra värdet.

Du har nu fem sätt att hitta den näst högsta lönen:

• MAX under MAX – portabelt och returnerar NULL automatiskt.
• LIMIT/OFFSET och OFFSET/FETCH – kortfattade och dialektspecifika.
• DENSE_RANK – det skalbara standardvalet som hanterar lika löner korrekt.
• Korrelerad räkning – elegant och generaliseras till den N:e högsta.

Viktigast att komma ihåg: fråga om du behöver unika värden, föredra DENSE_RANK för lika löner och kom ihåg vilka metoder som returnerar NULL respektive inga rader när det inte finns något andra värde.