Fonctions de fenêtrage

Les fonctions de fenêtrage effectuent des calculs sur un ensemble de lignes liées à la ligne courante. Contrairement aux fonctions d'agrégation, qui renvoient un seul résultat pour un groupe de lignes, les fonctions de fenêtrage renvoient une valeur pour chaque ligne en tenant compte d'une "fenêtre" de lignes liées.

Voir aussi : [SELECT] [WITH (CTE)] [ Référence des fonctions de fenêtrage ]

---

Syntaxe

Les fonctions de fenêtrage utilisent la clause OVER pour définir la spécification de fenêtre :

SELECT <windowFunction> OVER ( <windowSpec> ) FROM <multipartIdentifier>;

windowFunctionCall::=

Fonction de fenêtrage

Les fonctions de fenêtrage sont des fonctions spécialisées qui effectuent des calculs sur un ensemble de lignes défini par la spécification de fenêtre. Il peut s'agir de fonctions de fenêtrage dédiées (comme ROW_NUMBER ou LAG) ou de fonctions d'agrégation (comme SUM ou COUNT) utilisées avec une clause OVER.

{ <aggregateFunction> | ROW_NUMBER() | RANK() | DENSE_RANK() | PERCENT_RANK() 
  | CUME_DIST() | NTILE(<int>) | LAG(<expr> [,<offset> [,<default>]]) 
  | LEAD(<expr> [,<offset> [,<default>]]) | FIRST_VALUE(<expr>) 
  | LAST_VALUE(<expr>) | NTH_VALUE(<expr>, <int>) }

windowFunction::=

Spécification de fenêtre

La spécification de fenêtre définit comment les lignes sont partitionnées, ordonnées et cadrées pour le calcul de la fonction de fenêtrage. PARTITION BY répartit les lignes en groupes, ORDER BY détermine l'ordre au sein de chaque partition, et la clause de cadre, facultative, précise quelles lignes, par rapport à la ligne courante, sont incluses dans le calcul.

[ <windowName> ]
[ PARTITION BY <fieldList> ]
[ ORDER BY <fieldList> [ ASC | DESC ] ]
[ { ROWS | RANGE | GROUPS } 
    { UNBOUNDED PRECEDING | <int> PRECEDING | CURRENT ROW }
  | { ROWS | RANGE | GROUPS } BETWEEN 
    { UNBOUNDED PRECEDING | <int> PRECEDING | CURRENT ROW | <int> FOLLOWING }
    AND
    { <int> PRECEDING | CURRENT ROW | <int> FOLLOWING | UNBOUNDED FOLLOWING } ]

windowSpec::=

Fonctions de fenêtrage disponibles

Catégorie	Fonctions
Classement	ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
Décalage	LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()
Distribution	PERCENT_RANK(), CUME_DIST()
Agrégation	SUM(), COUNT(), AVG(), MIN(), MAX() avec la clause OVER

---

Exemples

Classer les lignes avec ROW_NUMBER, RANK et DENSE_RANK

Utilisez les fonctions de classement pour attribuer un rang aux lignes en fonction des valeurs de colonnes.

-- Rank contributors by contribution count
SELECT
    login,
    contributions,
    ROW_NUMBER() OVER (ORDER BY contributions DESC) as row_num,
    RANK() OVER (ORDER BY contributions DESC) as rank,
    DENSE_RANK() OVER (ORDER BY contributions DESC) as dense_rank
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

Totaux cumulés et pourcentages

Les fonctions d'agrégation comme SUM et COUNT peuvent être utilisées comme fonctions de fenêtrage pour calculer des totaux cumulés et des pourcentages.

-- Running total and percentage of contributions
SELECT
    login,
    contributions,
    SUM(contributions) OVER (ORDER BY contributions DESC) as running_total,
    SUM(contributions) OVER () as total_contributions,
    ROUND(100.0 * contributions / SUM(contributions) OVER (), 2) as pct_of_total
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

Comparer des lignes avec LAG et LEAD

Utilisez LAG et LEAD pour accéder aux valeurs des lignes précédentes ou suivantes.

-- Compare each release to previous release
SELECT
    tag_name,
    name,
    published_at,
    LAG(tag_name, 1) OVER (ORDER BY published_at) as previous_release,
    LEAD(tag_name, 1) OVER (ORDER BY published_at) as next_release,
    julianday(published_at) - julianday(LAG(published_at, 1) OVER (ORDER BY published_at)) as days_since_last_release
FROM github.repos.releases
WHERE owner = 'stackql'
  AND repo = 'stackql'
ORDER BY published_at;

Créer des groupes avec NTILE

Utilisez NTILE pour répartir les lignes en un nombre donné de groupes.

-- Group contributors into quartiles
SELECT
    login,
    contributions,
    NTILE(4) OVER (ORDER BY contributions DESC) as quartile,
    CASE NTILE(4) OVER (ORDER BY contributions DESC)
        WHEN 1 THEN 'Top Contributors'
        WHEN 2 THEN 'Active Contributors'
        WHEN 3 THEN 'Moderate Contributors'
        WHEN 4 THEN 'Occasional Contributors'
    END as tier
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

Utiliser PARTITION BY pour une analyse par groupe

Utilisez PARTITION BY pour effectuer des calculs de fenêtrage au sein de groupes.

-- Track cumulative issues by state
SELECT
    number,
    title,
    state,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as issue_sequence,
    COUNT(*) OVER (ORDER BY created_at) as cumulative_issues,
    COUNT(*) OVER (PARTITION BY state ORDER BY created_at) as cumulative_by_state
FROM github.issues.issues
WHERE owner = 'stackql'
  AND repo = 'stackql'
ORDER BY created_at;

Utiliser des cadres de fenêtre

Indiquez une clause de cadre pour contrôler quelles lignes sont incluses dans le calcul de fenêtrage.

-- Calculate moving average of commit activity
WITH weekly_totals AS (
    SELECT
        week,
        SUM(json_each.value) as commits_this_week
    FROM github.repos.stats_commit_activity, JSON_EACH(days)
    WHERE owner = 'stackql'
      AND repo = 'stackql'
    GROUP BY week
)
SELECT
    week,
    commits_this_week,
    ROUND(AVG(commits_this_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1) as four_week_moving_avg,
    SUM(commits_this_week) OVER (ORDER BY week) as cumulative_commits
FROM weekly_totals
ORDER BY week;

Pour une documentation détaillée de chaque fonction de fenêtrage, consultez la Référence des fonctions de fenêtrage.

Pour plus d'informations, consultez https://sqlite.org/windowfunctions.html.