ROWS & RANGE Criterion

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

Pour l'instant, nous ne pouvons pas être flexibles concernant le choix du nombre de lignes avant ou après à prendre en compte. Maintenant, c'est possible avec les critères ROWS & 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 - lignes avant la ligne actuelle
n FOLLOWING - lignes après la ligne actuelle

La différence entre ROWS & RANGE est que le critère ROWS ne se soucie pas des valeurs, seulement des positions, alors que RANGE définit la fenêtre en termes de plages de valeurs plutôt que de positions de lignes.

Pour RANGE nous devons 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 courante, la ligne précédente et la ligne suivante.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

Ici, cela crée une fenêtre qui inclut pour chaque niveau (trié par ordre croissant) le niveau actuel, un niveau avant lui et un niveau après lui. Si le niveau actuel est 5, alors il inclura les niveaux 4, 5 et 6.

Note : L'utilisation de RANGE BETWEEN peut entraîner l'inclusion de plus 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 (tant qu'elles sont disponibles dans l'ensemble de données).

Aussi, RANGE ne prend pas en charge les colonnes de dates.

Voici un simple exemple pour illustrer ROWS vs RANGE :

Utilisation de ROWS :

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

Utilisation de RANGE :

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

Défi

Facile

Tables et colonnes disponibles :
newspapers : date, num_newspapers

Pour ce défi, nous avons des journaux pour la production. Nous aimerions connaître la moyenne du nombre de journaux imprimés deux jours avant la ligne actuelle et un jour après. Nous aimerions également connaître la différence entre le maximum et le minimum du nombre de journaux imprimés à partir de la date actuelle et les trois jours qui la précèdent. Appelez ces colonnes avg_newspapers et diff_newspapers respectivement.

Aide-mémoire

Utilisez ROWS et RANGE pour définir des cadres de fenêtre flexibles dans les fonctions de fenêtre :

ROWS - définit la fenêtre par positions de lignes :

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

RANGE - définit la fenêtre par plages de valeurs (nécessite ORDER BY) :

OVER (ORDER BY column_name RANGE BETWEEN --START-- AND --END--)

Options de limites de fenêtre :

CURRENT ROW - la ligne actuelle
n PRECEDING - n lignes/valeurs avant l'actuelle
n FOLLOWING - n lignes/valeurs après l'actuelle

Exemples :

-- ROWS: inclut la ligne actuelle + 1 avant + 1 après (par position)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- RANGE: inclut les valeurs dans la plage (par valeur)
ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Différences clés :

ROWS - basé sur les positions de lignes, toujours même nombre de lignes
RANGE - basé sur les valeurs, peut inclure plus de lignes avec les mêmes valeurs
RANGE ne prend pas en charge les colonnes de date

Essayez vous-même