Menu
CoddyTech

Critère ROWS & RANGE

Fait partie de la section Les fondamentaux du Journey SQL de Coddy. Leçon 69 sur 72.

Pour le moment, nous ne pouvons pas choisir librement le nombre de lignes précédentes ou suivantes à prendre en compte. C’est désormais possible avec les critères ROWS et RANGE. Pour les utiliser, nous écrivons :

OVER (ROWS BETWEEN --START-- AND --END--)

OVER (RANGE BETWEEN --START-- AND --END--)

Et nous pouvons spécifier les options suivantes :

  • CURRENT ROW - la ligne actuelle
  • n PRECEDING - les lignes précédant la ligne actuelle
  • n FOLLOWING - les lignes suivant la ligne actuelle 

La différence entre ROWS et RANGE, c’est que le critère ROWS ne tient pas compte des valeurs, seulement des positions, tandis que RANGE définit la fenêtre en fonction des plages de valeurs plutôt que des positions des lignes.

Pour RANGE, nous devons impérativement spécifier ORDER BY --column_name--, car sinon il ne saurait pas comment choisir la fenêtre.

Par exemple :

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Ici, cela crée une fenêtre qui inclut la ligne actuelle, la ligne précédente et la ligne suivante.

ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Ici, cela crée une fenêtre qui inclut, pour chaque niveau (trié par ordre croissant), le niveau actuel, le niveau précédent et le niveau suivant. Si le niveau actuel est 5, la fenêtre inclura les niveaux 4, 5 et 6.

Remarque : L’utilisation de RANGE BETWEEN peut inclure davantage de lignes dans votre fenêtre, car elle inclut toutes les lignes qui partagent les mêmes valeurs que celles de la plage, tandis que ROWS BETWEEN inclura toujours le même nombre de lignes (dans la mesure où elles sont disponibles dans l’ensemble de données). 

De plus, pour les dates stockées sous forme de texte (comme '2024-01-05'), un décalage RANGE n’effectue aucun calcul de date : avec 1 PRECEDING, la fenêtre ne contient que les lignes ayant la même date. Pour compter les jours, triez plutôt par un nombre, par exemple julianday(date).

Voici un exemple simple pour illustrer la différence entre ROWS et RANGE :

Avec ROWS :

SELECT employee_name, salary,
    AVG(salary) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg_salary_rows

Utiliser RANGE :

SELECT employee_name, salary,
    AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
    ) as avg_salary_range
challenge icon

Défi

Facile

Tables et colonnes disponibles :

  • newspapers : date, num_newspapers

Pour ce défi, nous avons des données sur la production de journaux. Nous aimerions connaître le nombre moyen de journaux imprimés entre les deux jours précédant la ligne actuelle et le jour suivant. Nous aimerions également connaître la différence entre le nombre maximal et le nombre minimal de journaux imprimés entre la date actuelle et les trois jours qui la précèdent. Nomme ces colonnes respectivement avg_newspapers et diff_newspapers.

Essayez vous-même

quiz iconTestez-vous

Cette leçon comprend un petit quiz. Commencez la leçon pour y répondre et suivre votre progression.

Toutes les leçons de Les fondamentaux

Entraînez-vous par vous-même : Playground SQL