Coding Interview Prep · Leçon

FIRST_VALUE, LAST_VALUE et limites de fenêtre

Extraire les valeurs limites et comprendre le piège lié à la fenêtre de LAST_VALUE

FIRST_VALUE, LAST_VALUE et limites de fenêtre est une leçon Coding Interview Prep gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage Coding Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours Coding Interview Prep comprend 4 leçons au total.

Récupérer les valeurs aux limites

Les recruteurs demandent : « Affichez chaque ligne avec la première et la dernière valeur de son groupe. » Pensez à la date de première connexion de chaque utilisateur ou au dernier prix d'une partition affiché à côté de chaque ligne détaillée.

Les fonctions concernées sont FIRST_VALUE et LAST_VALUE. Elles semblent simples, mais LAST_VALUE dissimule l'un des pièges les plus connus concernant le cadre de fenêtre en SQL. Cette leçon vous permet d'utiliser les deux de manière fiable.

Principes de base de FIRST_VALUE

FIRST_VALUE(col) renvoie la valeur de col de la première ligne de la fenêtre et l'associe à chaque ligne. Avec un tri par date, chaque ligne reçoit la valeur la plus ancienne de sa partition.

Comme le cadre par défaut commence à la première ligne de la partition, FIRST_VALUE se comporte généralement exactement comme on s'y attend.

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS first_login
FROM logins;

Le cadre de fenêtre par défaut

Voici le point essentiel. Lorsque vous ajoutez ORDER BY à une fenêtre, le cadre par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Cela signifie que la fenêtre de chaque ligne s'étend uniquement du début de la partition jusqu'à la ligne actuelle, et non jusqu'à la fin. FIRST_VALUE n'est pas affecté (la première ligne est toujours comprise dans le cadre), mais LAST_VALUE l'est fortement.

Le piège de LAST_VALUE

Exécutez LAST_VALUE avec seulement un ORDER BY et la plupart des candidats s'attendent à obtenir la valeur finale de la partition. Pourtant, comme le cadre se termine à la ligne actuelle, la « dernière valeur du cadre » est simplement la valeur de la ligne actuelle.

Cette requête renvoie donc login_date lui-même sur chaque ligne, ce qui semble défectueux. C'est le piège des fonctions de fenêtre le plus fréquemment rencontré.

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS wrong_last_login
FROM logins;

Corriger LAST_VALUE avec un cadre complet

La correction consiste à élargir le cadre pour couvrir toute la partition : ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

La fenêtre de chaque ligne s'étend alors sur toute la partition, et LAST_VALUE renvoie la véritable valeur finale. Présentez explicitement cette correction en entretien : cela prouve que vous comprenez les cadres, et pas seulement les noms des fonctions.

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS last_login
FROM logins;

Une solution plus simple

De nombreux ingénieurs contournent entièrement le cadre : pour obtenir la dernière valeur, utilisez FIRST_VALUE avec un ordre de tri inversé.

FIRST_VALUE(login_date) OVER (... ORDER BY login_date DESC) renvoie la date la plus récente sans qu'une clause de cadre soit nécessaire. C'est une astuce simple et facile à retenir, qu'il est utile de mentionner.

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date DESC
  ) AS last_login
FROM logins;

ROWS et RANGE dans les cadres

Les cadres se présentent sous deux formes. ROWS compte les lignes physiques ; RANGE regroupe les valeurs égales de ORDER BY (les lignes paires).

Le cadre par défaut utilise RANGE, ce qui explique que les valeurs identiques de l'ordre de tri partagent la même limite de cadre. Pour corriger LAST_VALUE, préférez ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING écrit explicitement afin d'éviter les surprises liées aux égalités.

NTH_VALUE pour des positions arbitraires

Au-delà de la première et de la dernière valeur, NTH_VALUE(col, n) récupère la valeur à la position n dans le cadre, par exemple le deuxième prix le plus élevé.

Cette fonction obéit aux mêmes règles de cadre que LAST_VALUE. Associez-la donc à un cadre complet lorsque vous voulez obtenir la n-ième valeur dans toute la partition, plutôt que seulement jusqu'à la ligne actuelle.

SELECT
  product_id,
  price,
  NTH_VALUE(price, 2) OVER (
    PARTITION BY product_id
    ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS second_highest_price
FROM prices;

Exemple détaillé : première et dernière valeurs ensemble

Un rapport courant affiche chaque transaction à côté du montant de la première et de la dernière transaction du client. Combinez les deux fonctions, en n'oubliant pas le cadre explicite pour LAST_VALUE.

Chaque ligne contient alors la première et la dernière valeur de toute la partition, prêtes à servir pour calculer un écart ou effectuer une étape d'étiquetage.

SELECT
  customer_id,
  txn_date,
  amount,
  FIRST_VALUE(amount) OVER w AS first_amt,
  LAST_VALUE(amount) OVER w AS last_amt
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

Les fenêtres nommées pour rester DRY

Remarquez que la requête précédente utilisait une clause WINDOW w AS (...) et faisait référence à OVER w deux fois. Définir la fenêtre une seule fois évite de répéter une longue spécification de cadre et empêche les deux fonctions de diverger.

La plupart des grandes bases de données prennent en charge les fenêtres nommées. En utiliser une est une touche élégante que les recruteurs apprécient lorsque plusieurs colonnes partagent une fenêtre.

Exemple détaillé : écart entre la première et la dernière valeur

Une question complémentaire fréquente porte sur l'évolution entre la première et la dernière transaction d'un client. Avec les deux valeurs limites sur chaque ligne, soustrayez-les, puis dédupliquez pour ne conserver qu'une ligne par client si nécessaire.

Cela combine la correction du cadre complet avec une arithmétique simple : c'est le type de réponse de bout en bout que les recruteurs veulent voir construite proprement.

SELECT DISTINCT
  customer_id,
  LAST_VALUE(amount) OVER w - FIRST_VALUE(amount) OVER w AS first_to_last_delta
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

Vérification rapide

Le piège classique de LAST_VALUE.

Récapitulatif

Les fonctions de valeur de borne dépendent du cadre :

FIRST_VALUE fonctionne avec le cadre par défaut ; LAST_VALUE, non.
Le cadre par défaut se termine à la ligne actuelle. Corrigez donc LAST_VALUE avec ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, ou inversez le tri et utilisez FIRST_VALUE.
NTH_VALUE(col, n) récupère des positions arbitraires ; les fenêtres nommées permettent de garder les spécifications multi-colonnes DRY.

Vous disposez maintenant de la boîte à outils comprenant LAG, LEAD, NTILE et les fonctions de valeur de borne.

Questions Fréquemment Posées

La leçon « FIRST_VALUE, LAST_VALUE et limites de fenêtre » est-elle gratuite ?

Oui — le texte complet de « FIRST_VALUE, LAST_VALUE et limites de fenêtre » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours Coding Interview Prep, passe à CoddyKit PRO. Le cours Coding Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « FIRST_VALUE, LAST_VALUE et limites de fenêtre » ?

Extraire les valeurs limites et comprendre le piège lié à la fenêtre de LAST_VALUE Tu pratiques Coding Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer Coding Interview Prep ?

Aucune expérience préalable n'est requise. Coding Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « FIRST_VALUE, LAST_VALUE et limites de fenêtre » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon Coding Interview Prep ?

Oui. Chaque leçon Coding Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

LAG et LEAD pour les lignes adjacentes
Évolution d’une période à l’autre
NTILE pour créer des tranches
FIRST_VALUE, LAST_VALUE et limites de fenêtre

← Retour à Coding Interview Prep