QUALIFY: de SQL-filterinstructie waarvan je niet wist dat je hem nodig had

Basisconcept en syntaxis van de SQL-QUALIFY-clausule

QUALIFY is een clausule die wordt gebruikt om de resultaten van een vensterfunctie te filteren. Om de QUALIFY-clausule succesvol te gebruiken, moet er daarom minstens één WINDOW-functie in de SELECT-lijst of QUALIFY-clausule staan – alleen de rijen waarvoor de booleaanse expressie TRUE oplevert, worden geretourneerd.

Tip: Bekijk de SQL Window Functions Cheat Sheet.

Zonder QUALIFY-clausule zou filteren op de resultaten van een WINDOW-functie nesten vereisen. SQL-ontwikkelaars raden doorgaans af om onnodig geneste queries te gebruiken, omdat die de code minder leesbaar maken en het debuggen en samenwerken lastiger.

Met andere woorden: het hoofddoel van de QUALIFY-clausule is het vereenvoudigen van queries die moeten filteren op het resultaat van vensterfuncties; dat betekent dat QUALIFY wordt geëvalueerd nadat vensterfuncties zijn berekend.

Dit is de typische volgorde van uitvoering voor een query met een QUALIFY-clausule:

• FROM
• WHERE
• GROUP BY en aggregatie
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

De algemene syntaxis van een QUALIFY-instructie is als volgt:

QUALIFY <predicate>Mogelijk gemaakt door

In de algemene vorm ziet het er ongeveer zo uit:

SELECT <column_list>
  FROM <data_source>
  [GROUP BY ...]
  [HAVING ...]
  QUALIFY <predicate>
  [ ... ]Mogelijk gemaakt door

Opmerking: <predicate> is een expressie die wordt gebruikt om het resultaat te filteren nadat aggregaties en vensterfuncties zijn berekend.

Wanneer gebruik je SQL QUALIFY

De QUALIFY-clausule lijkt sterk op de HAVING-clausule in de zin dat ze de noodzaak van een subquery om te filteren wegneemt. Je kunt QUALIFY bijvoorbeeld gebruiken om de resultaten van een analytische functie te filteren – een functie die een aggregaatwaarde berekent op basis van een groep rijen.

Om te verduidelijken wanneer je QUALIFY precies zou gebruiken, volgt hier een kort scenario:

Stel, je werkt als data scientist en een stakeholder vraagt je om van elke klant de gegevens van de laatste login. Je eerste query ziet er dan zo uit:

SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) as previous_logins
FROM login_logs
WHERE TRUE Mogelijk gemaakt door

Dit is een goed begin, maar het voldoet niet aan het verzoek van de stakeholder, omdat het alle logins retourneert en niet alleen de meest recente. Om de laatste login terug te geven, moeten we een filter toevoegen.

Nieuwkomers in SQL trappen soms in de val om dit te proberen te filteren met de WHERE-clausule, zoals hieronder:

SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) AS previous_logins
FROM login_logs
WHERE TRUE 
  AND last_login = 1Mogelijk gemaakt door

Helaas zal deze code niet uitvoeren; hetzelfde geldt als je GROUP BY en HAVING probeert.

De reden hiervoor heeft alles te maken met de volgorde van bewerkingen die we in de vorige sectie hebben besproken.

WHERE, GROUP BY en HAVING worden allemaal geëvalueerd vóór WINDOW-functies; dat betekent dat ze geen WINDOW-functies kunnen filteren omdat ze niet weten dat die bestaan – de WINDOW-functie is nog niet geëvalueerd op het moment dat die drie clausules worden uitgevoerd.

Een manier om het filter ná de WINDOW-functie te laten komen, is door een Common Table Expression (CTE) te gebruiken.

Zo ziet onze code er dan uit:

WITH 
logins AS (
  SELECT 
      user_id, 
      ip, 
      country_code,
      os,
      RANK() OVER (
        PARTITION BY user_id ORDER BY log_datetime DESC
      ) AS previous_logins
  FROM login_logs
  WHERE TRUE
)

SELECT user_id, ip, country_code, os 
FROM logins
WHERE previous_logins = 1Mogelijk gemaakt door

Technisch gezien is deze code geldig – hij voert prima uit en retourneert de resultaten waar je stakeholder naar op zoek is.

We moesten echter twee queries introduceren en er zijn extra regels code bijgekomen, wat overbodig kan worden als je dit proces regelmatig herhaalt.

De beste oplossing voor dit probleem is het gebruik van QUALIFY.

Zo ziet de code er dan uit:

-- Starter code from @Jiho Choi on StackOverflow 
SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) as previous_logins
FROM login_logs
WHERE TRUE 
QUALIFY previous_logins = 1Mogelijk gemaakt door

Deze oplossing werkt omdat QUALIFY-clausules in de SQL-volgorde van bewerkingen worden geëvalueerd ná WINDOW-functies, waardoor ze zich bewust zijn van hun bestaan en ze in dezelfde query kunnen filteren.

Je vraagt je misschien af wat de voordelen van QUALIFY zijn, afgezien van minder regels code, en het antwoord is dat er eigenlijk geen prestatievoordelen zijn.

De CTE-oplossing die we gebruikten en de QUALIFY-oplossing voeren in vergelijkbare tijd uit, dus we kunnen niet zeggen dat er veel prestatieverbetering is door de QUALIFY-oplossing.

Het belangrijkste voordeel is het gebruiksgemak: minder code en beter leesbaar.

SQL QUALIFY vergelijken met andere filtermethoden

Je weet nu dat de QUALIFY-clausule een andere filtermethode is die in SQL beschikbaar is. Je weet ook wanneer je de QUALIFY-clausule het beste gebruikt.

Maar wat is het verschil tussen QUALIFY en de andere filtermethoden?

Laten we elke filtermethode kort herhalen.

De WHERE-clausule

WHERE wordt gebruikt om records in een tabel te filteren; dat betekent dat het rijbewerkingen uitvoert. We gebruiken het wanneer we de records willen ophalen die aan een specifieke voorwaarde voldoen. We willen bijvoorbeeld een dataset filteren op iemands leeftijd. Zo ziet dat er in code uit:

SELECT name, gender, height, weight, age 
FROM gym_members_info 
WHERE age < 23; Mogelijk gemaakt door

Dit zijn de belangrijkste punten om te onthouden over de WHERE-clausule.

1. Wordt gebruikt om records te filteren op basis van een specifieke voorwaarde.
2. Bewerkingen worden op de rijen uitgevoerd.
3. Je kunt de WHERE-clausule gebruiken zonder een GROUP BY-clausule.
4. WHERE kan worden gebruikt met SELECT-, UPDATE- en DELETE-instructies.

Laten we dit vergelijken met de HAVING-clausule.

De HAVING-clausule

HAVING wordt gebruikt om records uit groepen, gedefinieerd door een GROUP BY-clausule, te filteren op basis van een specifieke voorwaarde. Het meest kenmerkende verschil tussen WHERE en HAVING is dus dat HAVING een GROUP BY-clausule vereist om succesvol te kunnen worden geëvalueerd.

Het belangrijkste voordeel hiervan is dat je je HAVING-clausule kunt toepassen op een subset van geaggregeerde groepen, wat niet mogelijk is in een WHERE-blok.

Bijvoorbeeld:

SELECT gender, AVG(height) 
FROM gym_members_info
GROUP BY gender 
HAVING AVG(height) > 170 Mogelijk gemaakt door

De bovenstaande code retourneert de geslachtsgroepen waarvan de gemiddelde lengte groter is dan 170 centimeter; met andere woorden, HAVING voert een kolombewerking uit.

Een ander aandachtspunt is dat HAVING alleen kan worden gebruikt met de SELECT-instructie, in tegenstelling tot WHERE.

De QUALIFY-clausule

QUALIFY is het equivalent van HAVING, maar voert filters uit op de resultaten van WINDOW-functies, terwijl HAVING filters uitvoert op aggregatiefuncties en GROUP BY-clausules – lees meer over Hoe je GROUP BY gebruikt.

Als we informatie willen weten over de langste persoon binnen elke leeftijdsgroep, kunnen we een WINDOW-functie gebruiken om de berekening uit te voeren en een QUALIFY-clausule om de resultaten te filteren, zodat we alleen de langste persoon uit elke leeftijdsgroep terugkrijgen.

Zo ziet het er in code uit:

SELECT name, gender, height, weight, age 
RANK() over (
    PARTITION BY age ORDER BY height DESC
  ) as ranked_ages
FROM gym_members_info
WHERE TRUE
QUALIFY ranked_ages = 1Mogelijk gemaakt door

Kort gezegd specificeert de QUALIFY-clausule een voorwaardelijke expressie die wordt gebruikt om de output te filteren van een geordende analytische functie die al is berekend volgens door de gebruiker opgegeven criteria.

Conclusie en verder leren

SQL speelt een grote rol in de typische data science-werkstroom; weten hoe je nuttige bewerkingen in SQL implementeert, zoals de QUALIFY-clausule, is enorm waardevol omdat je er schonere code mee schrijft, wat samenwerken prettiger maakt.

Wil je je SQL-vaardigheden naar een hoger niveau tillen? Bekijk dan de onderstaande bronnen:

• SQL Basics Cheat Sheet
• Cursus Introduction to SQL
• Skill track SQL Fundamentals