Les 10 meilleures questions d'entretien sur les fonctions de fenêtre SQL

De nombreux postes intéressants requièrent des compétences SQL, y compris les fonctions de fenêtre, qui ne sont pas souvent enseignées dans les cours en ligne. Dans cet article, j'aborderai les principales questions sur les fonctions de fenêtre pour tous les niveaux d'expérience.

Si vous passez un entretien d'embauche pour un poste SQL avancé ou pour des postes d'analyste de données de niveau intermédiaire à avancé, vous serez probablement interrogé sur votre connaissance des fonctions de fenêtre SQL. Ne paniquez pas ! Bien que ces fonctions ne soient pas couramment abordées dans les cours en ligne, nous avons les réponses ici même.

Questions courantes d'entretien d'embauche sur SQL Fonctions de fenêtrage

L'idée de cet article est de vous aider à vous préparer aux questions sur les différents sous-thèmes des fonctions de fenêtre. Nous ne pouvons pas vous donner la question exacte que vous recevrez, mais nous pouvons être assez certains des sujets sur lesquels porteront les questions.

Dans certains cas, la question peut être très ouverte, vous laissant entièrement le choix du sous-thème des fonctions de fenêtre à couvrir. Dans ce cas, vous devez connaître l'importance relative de chaque sous-thème. Pour commencer, vous devez être prêt à répondre à une question ouverte comme celle-ci :

1. Qu'est-ce qu'une fonction fenêtre en SQL ?

Les fonctions de fenêtre sont des fonctions SQL qui opèrent sur un ensemble d'enregistrements appelé "fenêtre" ou "cadre de fenêtre". La "fenêtre" est un ensemble de lignes qui sont liées d'une manière ou d'une autre à la ligne en cours de traitement par la requête (par exemple, toutes les lignes avant la ligne actuelle, 5 lignes avant la ligne actuelle ou 3 lignes après la ligne actuelle).

Les fonctions de fenêtre sont similaires aux fonctions d'agrégation dans la mesure où elles calculent des statistiques pour un groupe de lignes. Toutefois, les fonctions de fenêtre ne réduisent pas les lignes ; elles conservent les détails des lignes individuelles.

Les fonctions de fenêtre peuvent être classées dans les quatre catégories suivantes : fonctions d'agrégation, fonctions de classement, fonctions analytiques et fonctions de distribution.

Les fonctions d'agrégation sont celles que vous utilisez avec GROUP BY. Elles comprennent :

• COUNT() compte le nombre de lignes dans la fenêtre.
• AVG() calcule la valeur moyenne d'une colonne donnée pour tous les enregistrements de la fenêtre.
• MAX() obtient la valeur maximale d'une colonne pour tous les enregistrements de la fenêtre.
• SUM() retourne la somme de toutes les valeurs d'une colonne donnée à l'intérieur de la fenêtre.

Dans la catégorie classement :

• ROW_NUMBER() renvoie la position de la ligne dans le jeu de résultats.
• RANK() classe les lignes en fonction d'une valeur donnée. Lorsque deux lignes sont dans la même position, elle leur attribue le même rang et laisse la position suivante vide (par exemple 1, 2, 3, 3, 5...).
• DENSE_RANK() classe également les lignes selon une valeur donnée, mais ne laisse pas la position suivante vide (par exemple 1, 2, 3, 3, 4, 5...).

Pour des informations détaillées, voir cet article sur les fonctions de classement.

Dans la catégorie analytique, les fonctions LEAD(), LAG() ou FIRST_VALUE() nous permettent d'obtenir des données à partir d'autres lignes de la même fenêtre. LEAD() renvoie les valeurs des lignes inférieures à la ligne actuelle ; LAG() celles des lignes supérieures à la ligne actuelle. Pour plus de détails, consultez notre article sur LEAD vs LAG.

Enfin, dans la catégorie des distributions, il existe des fonctions comme PERCENT_RANK() et CUME_DIST() qui permettent d'obtenir des classements par percentile ou des distributions cumulatives. Consultez notre coursFonctions de fenêtrage pour obtenir des instructions étape par étape sur l'utilisation de ces fonctions.

Voici un exemple de requête avec des fonctions de fenêtre :

SELECT
    employee_name,
    department_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary) position 
FROM employee

Dans cette requête, la fonction fenêtre RANK() est utilisée pour classer les employés par salaire. Plus loin dans cet article, nous aborderons en détail la syntaxe de la clause OVER() et des sous-clauses PARTITION BY et ORDER BY. Pour l'instant, nous nous contenterons de dire qu'elles sont utilisées pour définir les enregistrements qui composent le cadre de la fenêtre.

Questions sur les fonctions de fenêtre d'entrée de gamme

2. Quelle est la syntaxe de la clause OVER () ?

La clause OVER() est utilisée pour définir les lignes qui seront dans le cadre de la fenêtre. Les sous-clauses suivantes peuvent être présentes dans la clause OVER():

• PARTITION BY définit la partition, ou les groupes de lignes dans le cadre de la fenêtre, que la fonction de fenêtre utilisera pour créer un résultat. (Ceci sera expliqué plus loin).
• ORDER BY définit l'ordre des lignes dans le cadre de la fenêtre.
• ROWS/RANGE définit les limites supérieure et inférieure du cadre de la fenêtre.

Toutes les sous-clauses de OVER() sont facultatives et peuvent être omises. Dans ce cas, les fonctions seront exécutées sur l'ensemble du cadre de la fenêtre.

Le SQL suivant montre la clause OVER() à l'œuvre :

SELECT
first_name,
last_name,
department, 
salary,
AVG(salary) OVER (PARTITION BY department) 
FROM employee

Pour chaque employé, la requête renvoie son prénom, son nom, son salaire et le salaire moyen de son département. La clause OVER (PARTITION BY department) crée une fenêtre de lignes pour chaque valeur de la colonne département. Toutes les lignes ayant la même valeur dans la colonne département appartiendront à la même fenêtre. La fonction AVG() est appliquée à la fenêtre : la requête calcule le salaire moyen dans le département donné.

L'article What Is the OVER Clause ? contient une explication complète de la clause OVER.

3. Décrivez la différence entre Fonctions de fenêtrage et les fonctions agrégées.

La principale différence entre les fonctions de fenêtre et les fonctions d'agrégation est que les fonctions d'agrégation regroupent plusieurs lignes en une seule ligne de résultat; toutes les lignes individuelles du groupe sont réduites et leurs données individuelles ne sont pas affichées. En revanche, les fonctions de fenêtre produisent un résultat pour chaque ligne individuelle. Ce résultat est généralement affiché comme une nouvelle valeur de colonne dans chaque ligne de la fenêtre.

La réduction des lignes est une caractéristique importante des fonctions d'agrégation. Par exemple, nous ne pouvons pas résoudre le problème "Retourner tous les employés avec leur salaire et le salaire maximum dans leur département" avec les fonctions d'agrégation en raison de la limitation de la réduction.

Du côté des similitudes, les fonctions d'agrégation et de fenêtre effectuent toutes deux une opération de type agrégation sur un ensemble de lignes. Certaines fonctions comme AVG(), MAX(), MIN() et SUM() peuvent être utilisées comme fonctions d'agrégation et de fenêtre. Toutefois, lorsque nous avons besoin du résultat de ces fonctions combiné à des données de niveau ligne, il est préférable d'utiliser une fonction de fenêtre plutôt qu'une fonction d'agrégation.

Nous allons montrer deux requêtes SQL qui renvoient le nom du département et le salaire maximum de chaque département. Dans le premier exemple, nous utiliserons MAX() comme fonction d'agrégation :

SELECT   department_name,
         MAX(salary) AS max_salary
FROM     employee
GROUP BY department_name

Ci-dessous, nous pouvons voir le résultat de la requête précédente. Remarquez qu'il y a un enregistrement par département en raison de l'effet d'agrégation de la clause GROUP BY:

Dans l'exemple suivant, nous allons obtenir un résultat similaire mais légèrement différent en utilisant MAX() comme fonction de fenêtre :

SELECT employee_name, 
       salary,
       department_name,
       MAX(salary) OVER (PARTITION BY department_name) AS max_salary
FROM   employee

Comme nous l'avons mentionné précédemment, les fonctions de fenêtre ne réduisent pas les enregistrements. Dans le résultat suivant, nous avons une ligne par employé pour un total de 5 lignes :

Notez que nous avons ajouté les colonnes employee_name et salaire simplement en ajoutant leurs noms à la liste des colonnes dans SELECT. Nous n'avons pas pu les ajouter à la requête avec GROUP BY à cause de la limitation de la réduction.

Dans l'article SQL Fonctions de fenêtrage by Explanation, vous trouverez une explication détaillée des différences entre les fonctions d'agrégation et de fenêtre.

4. Quelle est la différence entre Fonctions de fenêtrage et la clause GROUP BY ?

Les fonctions d'agrégation sont fréquemment utilisées avec la clause GROUP BY, qui définit les groupes de lignes où la fonction d'agrégation fonctionnera. La clause GROUP BY regroupe des lignes individuelles en ensembles de lignes, ce qui permet l'exécution de fonctions d'agrégation telles que SUM(), AVG() ou MAX() sur ces ensembles. Aucune colonne des lignes individuelles ne peut faire partie du résultat, comme nous pouvons le voir dans la requête SQL suivante :

SELECT   
   department_name, 
   AVG(salary)      -- AVG is an aggregate function
FROM  employee
GROUP BY department_name

Dans la requête ci-dessus, nous ne mettons qu'une seule colonne dans la liste SELECT: department_name. Ceci est possible car la colonne department_name apparaît dans la clause GROUP BY. Cependant, nous ne pouvons pas ajouter de colonnes supplémentaires dans la liste SELECT; seules les colonnes spécifiées dans la liste GROUP BY sont autorisées.

La requête SQL suivante est équivalente à la précédente, mais elle utilise des fonctions de fenêtre au lieu de GROUP BY:

SELECT
  department_name,
  AVG(salary) OVER(PARTITION BY department_name) -- AVG is a window function
FROM employee

La requête précédente n'a pas de clause GROUP BY car la fonction AVG() est utilisée comme une fonction fenêtre. Nous pouvons reconnaître que AVG() est une fonction fenêtre grâce à la présence de la clause OVER.

Je vous suggère l'article SQL Fonctions de fenêtrage vs. GROUP BY pour une comparaison complète entre les fonctions fenêtres et la clause GROUP BY.

5. Montrez un exemple de SQL Fonctions de fenêtrage.

C'est une bonne occasion de mentionner une requête qui montre l'importance des fonctions de fenêtre et qui est en même temps liée aux requêtes que nous avons montrées dans les questions précédentes. La requête que je suggère résoudrait cette tâche : "Obtenir les noms des employés, leurs salaires, les noms des départements, et le salaire moyen de ce département."

Cette requête est un moyen simple de montrer comment nous pouvons combiner des données de niveau ligne et des données agrégées. (La fonction fenêtre renvoie les données agrégées).

SELECT employee_name,
       salary,
       department_name,
       AVG(salary) OVER (PARTITION BY department) avg_salary 
FROM employee

Ci-dessus, nous pouvons voir les colonnes de niveau ligne employee_name, salary, et department avec le salaire moyen de chaque département, qui est calculé par la fonction fenêtre AVG(). La sous-clause PARTITION BY définit que les fenêtres d'enregistrements seront créées en fonction de la valeur de la colonne department_name. Tous les enregistrements ayant la même valeur dans department_name seront dans la même fenêtre. Les résultats ressembleraient à ceci :

Pour examiner d'autres exemples de fonctions de fenêtre, essayez l'article SQL Window Function Examples with Explanations.

6. Nommer des fonctions courantes Fonctions de fenêtrage.

Les fonctions de fenêtre peuvent être classées en quatre catégories : fonctions d'agrégation, fonctions de classement, fonctions analytiques et fonctions de distribution.

Les fonctions d'agrégation sont les fonctions d'agrégation ordinaires que vous utilisez avec GROUP BY : MAX(), MIN(), AVG(), SUM() et COUNT(). Ces fonctions, comme nous l'avons déjà montré, peuvent être utilisées comme fonctions de fenêtre.

Les fonctions de classement sont ROW_NUMBER(), RANK() et DENSE_RANK(). Elles sont utilisées pour obtenir différentes positions dans un classement. Vous trouverez une explication détaillée des fonctions de classement dans l'article suivant.

Les fonctions analytiques sont LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE() et NTH_VALUE(). Ces fonctions nous permettent d'obtenir des données à partir de lignes autres que la ligne actuelle (par exemple, la ligne précédente, la ligne suivante, la dernière ligne dans un cadre de fenêtre, etc.) La fonction NTILE() divise les lignes d'une partition en n groupes et renvoie le numéro du groupe.

Enfin, les fonctions de distribution PERCENT_RANK() et CUME_DIST() nous permettent d'obtenir des données sur le percentile ou la distribution cumulative (respectivement) pour chaque ligne de la fenêtre.

Je préfère les fonctions analytiques car elles nous permettent de comparer ou de calculer les différences entre les différents enregistrements de la fenêtre (entre autres choses). Par exemple, si j'ai une série chronologique avec des valeurs boursières, je peux calculer de combien l'action a augmenté à chaque moment.

Voici un autre exemple de fonctions analytiques. Les fonctions de fenêtre analytique LEAD() et LAG() renvoient une colonne d'une ligne suivante/précédente. Ainsi, si nous disposons d'un tableau de cryptomonnaies, avec un horodatage et une valeur de cotation ...

Table Actions

... nous pouvons obtenir le rapport suivant. Pour calculer le pourcentage de variation, nous avons besoin de données provenant de deux lignes différentes : La valeur de la ligne actuelle, et la valeur de la ligne précédente. La fonction LEAD() renvoie la valeur de la ligne précédente. Voici le résultat :

La colonne % Variation a été calculée avec ce type d'expression :

(Current_value - Previous_value ) / Previous_value

Notez que la valeur de crypto-monnaie de l'horodatage précédent peut être obtenue avec :

LEAD(value) OVER (PARTITION BY crypto_symbol ORDER BY timestamp) 

Voici la requête complète :

SELECT Symbol, 
       Timestamp, 
       Value, 
       (Value - LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) /   
       LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) AS “% variation”
FROM   Cryptocurrencies

Si vous voulez aller plus loin avec LAG() et LEAD(), je vous suggère de lire l'article The LAG() Function and the LEAD() Function in SQL. Vous y trouverez une explication détaillée du fonctionnement des fonctions de fenêtre dans les fenêtres ordonnées.

Questions intermédiaires sur les fonctions de fenêtre

7. Comment définir le cadre de la fenêtre ?

Les fonctions de fenêtrage calculent un résultat agrégé sur la base d'un ensemble d'enregistrements appelé "fenêtre" ou "cadre de fenêtre". Les cadres de fenêtre sont définis par la clause OVER().

Une clause OVER() vide signifie que la fenêtre est l'ensemble des données :

SELECT employee_name,
       salary,
       department_name,
       AVG(salary) OVER () avg_salary 
FROM employee

La requête ci-dessus calcule le salaire moyen et l'affiche à côté des autres détails des employés pour tous les employés de la table.

Il existe plusieurs sous-clauses qui peuvent être placées à l'intérieur de la clause OVER() pour définir précisément une fenêtre.

La sous-clause PARTITION BY spécifie que tous les enregistrements ayant la même valeur dans une colonne donnée appartiennent à la même fenêtre. En d'autres termes, PARTITION BY précise comment la fenêtre est définie. Ainsi, la requête suivante calcule le salaire moyen pour chaque département ; les calculs sont effectués sur la base des regroupements des valeurs de la colonne department_name.

SELECT 
   employee_name,
   salary,
   department_name,
   AVG(salary) OVER (PARTITION BY department_name) avg_salary 
FROM employee

ORDER BY peut également être utilisé à l'intérieur de OVER(). Il est utilisé pour mettre les lignes de la fenêtre dans un ordre spécifique. Les fenêtres ordonnées sont très importantes car elles permettent l'utilisation de plusieurs fonctions analytiques comme LAG(), LEAD() et FIRST_VALUE().

SELECT 
   employee_name,
   salary,
   department_name,
   LAG(salary) OVER (ORDER BY salary) prev_salary 
FROM employee

Cette requête affiche le salaire de l'employé qui précède immédiatement l'employé actuel dans l'ordre des salaires. Notez que vous pouvez combiner les clauses ORDER BY et PARTITION BY dans une seule requête : l'ordre est appliqué à chaque partition individuellement.

Deux sous-clauses similaires de OVER() sont RANGE et ROWS. Elles définissent des limites pour le cadre de la fenêtre en mettant des limites supérieures et/ou inférieures à la fenêtre d'enregistrements. Cela signifie que les fonctions de la fenêtre peuvent être calculées sur la base d'un sous-ensemble de lignes au lieu de toutes les lignes de la fenêtre. La différence entre ROW et RANGE est expliquée en détail dans notre aide-mémoire sur les fonctions de fenêtre SQL. Les deux questions suivantes vous permettront d'en savoir plus sur ROWS et RANGE et sur les différentes options de limites disponibles.

8. Comment ORDER BY fonctionne-t-il avec OVER ?

Certaines fonctions de fenêtre (comme LAG(), LEAD() et FIRST_VALUE()) fonctionnent sur une fenêtre ordonnée d'enregistrements. Lorsque nous utilisons l'une de ces fonctions, nous avons besoin de la sous-clause ORDER BY pour définir les critères d'ordre. Un bon exemple de cela est la requête précédente que nous avons utilisée pour calculer le pourcentage de variation des crypto-monnaies :

SELECT Symbol, 
       Timestamp, 
       Value, 
       (Value - LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) /   
       LEAD(Value) OVER (PARTITION BY Symbol ORDER BY Timestamp) AS “% variation”
FROM   Cryptocurrencies

Dans la requête ci-dessus, la clause OVER comporte deux sous-clauses : PARTITION BY et ORDER BY. PARTITION BY définit quels enregistrements se trouvent dans chaque fenêtre et ORDER BY définit l'ordre des enregistrements dans la fenêtre. (Dans cet exemple, nous ordonnons les enregistrements en fonction de leur horodatage.) Ensuite, la fonction LEAD() renvoie la valeur de l'enregistrement précédent.

Si la clause OVER ne comprend pas de ORDER BY et que nous n'avons pas de ROWS/RANGE, alors le cadre de la fenêtre est formé par toutes les lignes conformes à la clause PARTITION BY. Toutefois, lorsque nous utilisons une clause ORDER BY sans ROWS/RANGE, le cadre de la fenêtre comprend les lignes situées entre la première ligne (sur la base de la clause ORDER BY) et la ligne actuelle. En d'autres termes, les lignes qui suivent la ligne actuelle ne seront pas incluses dans le cadre de la fenêtre. (Nous expliquerons plus en détail ces limites dans la prochaine question).

Les fonctions de fenêtre qui nécessitent une sous-clause ORDER BY sont les suivantes :

• RANK()
• DENSE_RANK()
• LEAD()
• LAG()
• FIRST_VALUE()
• LAST_VALUE()
• NTH_VALUE()
• PERCENT_RANK()
• CUME_LIST()

Pour en savoir plus sur le fonctionnement de ORDER BY, consultez l'article Comment calculer la différence entre deux lignes en SQL.

Questions sur les fonctions avancées de la fenêtre

9. Expliquez ce que fait UNBOUNDED PRECEDING.

Un cadre de fenêtre est un ensemble de lignes qui sont d'une manière ou d'une autre liées à la ligne actuelle, qui est évaluée séparément dans chaque partition. Lorsque nous utilisons la clause ORDER BY, nous pouvons éventuellement définir des limites supérieures et inférieures pour le cadre de la fenêtre. Les limites peuvent être définies comme suit

• UNBOUNDED PRECEDING
• n PRECEDING
• CURRENT ROW
• n FOLLOWING
• UNBOUNDED FOLLOWING

Ces limites peuvent être définies avec les sous-clauses RANGE ou ROWS de la clause OVER(). UNBOUNDED PRECEDING indique que la limite inférieure de la fenêtre est le premier enregistrement de la fenêtre ; de la même manière, la limite supérieure peut être définie avec UNBOUNDED FOLLOWING ou CURRENT ROW. Ces limites ne doivent être utilisées qu'avec des fenêtres ordonnées.

Dans l'image suivante, nous pouvons voir comment fonctionnent les différentes limites :

Par exemple, si nous voulons obtenir la valeur moyenne d'une crypto-monnaie en considérant uniquement les valeurs survenues jusqu'à la valeur actuelle, nous pouvons utiliser la clause suivante OVER():

AVG(value) OVER (PARTITION BY symbol_name 
                 ORDER BY timestamp 
                 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                 ) 

Dans cette clause OVER(), nous avons défini CURRENT ROW comme la limite supérieure de la fenêtre de calcul de la moyenne. C'est exactement ce dont nous avons besoin, car nous ne voulons pas inclure les valeurs enregistrées après l'horodatage actuel dans le calcul de la moyenne.

10. Décrire l'ordre des opérations de SQL et la place de Fonctions de fenêtrage dans cet ordre.

Les sous-clauses d'un SQL SELECT sont exécutées dans l'ordre suivant :

1. FROM / JOINS
2. WHERE
3. GROUP BY
4. Fonctions d'agrégation
5. HAVING
6. Fonctions de fenêtrage
7. SELECT
8. DISTINCT
9. UNION / INTERSECT / EXCEPT
10. ORDRE PAR
11. OFFSET
12. LIMIT / FETCH / TOP

Comme les fonctions de fenêtre sont calculées à l'étape 6, nous ne pouvons pas les placer dans la clause WHERE (qui est calculée à l'étape 2). Cependant, nous pouvons contourner cette limitation en utilisant une CTE (common table expression), où nous pouvons appeler les fonctions de fenêtre et stocker leurs résultats en tant que colonnes dans la CTE. Le CTE sera traité comme une table et les résultats des fonctions de fenêtre seront évalués comme des valeurs de colonnes normales par le site WHERE.

Il existe un article intéressant sur les raisons pour lesquelles les fonctions de fenêtre ne sont pas autorisées dans les clauses WHERE, que vous devriez lire si vous cherchez des exemples.

D'autre part, nous pouvons utiliser les résultats d'agrégation/groupement dans les fonctions de fenêtre, car ils sont déjà calculés au moment où les fonctions de fenêtre sont traitées.

Vous voulez améliorer vos compétences en SQL Fonctions de fenêtrage?

Cet article couvre plusieurs questions d'entretien d'embauche possibles sur les fonctions de fenêtre SQL. Mon dernier conseil est de relier les questions de cet article aux questions que vous recevrez lors d'un entretien. Le voici :

Essayez d'associer chaque question de cet article à un sujet relatif aux fonctions de fenêtre, comme "OVER clause ", " name a function " ou "ORDER BY sub-clause ". Ensuite, si l'on vous interroge sur les fonctions de fenêtrage lors de l'entretien, identifiez le sujet de la question et utilisez les informations fournies ici pour en discuter.

Si vous souhaitez approfondir les fonctions de fenêtre SQL, je vous suggère l'article Cours du mois - Fonctions de fenêtrage, qui décrit notre cours Fonctions de fenêtrage. Développer vos compétences est un investissement qui pourrait très bien vous aider à décrocher le poste !