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 actuellen PRECEDING- les lignes précédant la ligne actuellen 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 FOLLOWINGIci, 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 FOLLOWINGIci, 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_rowsUtiliser RANGE :
SELECT employee_name, salary,
AVG(salary) OVER (
ORDER BY salary
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
) as avg_salary_rangeDéfi
FacileTables 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
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
1Introduction
IntroductionQu’est-ce qu’une base de donnéesConcepts des bases de donnéesValeurs uniques4D’autres mots-clés
Le mot-clé INLe mot-clé BETWEENLe mot-clé LIKELe mot-clé ASRécapitulatif - Modèles de téléphones portables2Conditions
Bases des conditionsLe mot-clé ANDLe mot-clé ORLe mot-clé NOTCombinaison de plusieurs conditionsParenthèsesBooléens5Opérations arithmétiques
Opérateurs mathématiquesColonnes mathématiquesL’opération moduloLa fonction ROUND()3Format de retour spécifique
Valeurs NULLTrier les résultats, partie 1Trier les résultats, partie 2Récapitulatif - Entreprise de cybersécuritéLimiter le nombre d’enregistrementsRécapitulatif - Usine de véhicules6Défis d’introduction
Récapitulatif - Élection parlementaireRécapitulatif - Arrestation criminelle par la policeRécapitulatif - Récipient de boisson au barRécapitulatif - Ingénieur de nouvelles colonnes9Tables multiples
Jointure de base, partie 1Jointure de base, partie 2Récapitulatif - JointureAuto-jointureRécapitulatif - Auto-jointureUnionSimplifier les requêtes, mot-clé WITHRécapitulatif - Requêtes WITHRécapitulatif - Entrepreneur immobilier12Fonctions de fenêtre – partie 2
Fonctions RANK & DENSE_RANKRécapitulatif – RANK & DENSE_RANKFonction NTILEFonctions d’agrégationCritère ROWS & RANGEEntraînez-vous par vous-même : Playground SQL