Funciones de ventana

Las funciones de ventana realizan cálculos sobre un conjunto de filas relacionadas con la fila actual. A diferencia de las funciones de agregación, que devuelven un único resultado para un grupo de filas, las funciones de ventana devuelven un valor para cada fila teniendo en cuenta una "ventana" de filas relacionadas.

Consulte también: [SELECT] [WITH (CTE)] [ Referencia de funciones de ventana ]

---

Sintaxis

Las funciones de ventana usan la cláusula OVER para definir la especificación de ventana:

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

windowFunctionCall::=

Función de ventana

Las funciones de ventana son funciones especializadas que realizan cálculos sobre un conjunto de filas definido por la especificación de ventana. Pueden ser funciones de ventana dedicadas (como ROW_NUMBER o LAG) o funciones de agregación (como SUM o COUNT) usadas con una cláusula 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::=

Especificación de ventana

La especificación de ventana define cómo se particionan, ordenan y enmarcan las filas para el cálculo de la función de ventana. PARTITION BY divide las filas en grupos, ORDER BY determina la secuencia dentro de cada partición y la cláusula de marco opcional especifica qué filas, en relación con la fila actual, se incluyen en el cálculo.

[ <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::=

Funciones de ventana disponibles

Categoría	Funciones
Clasificación	ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
Desplazamiento	LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()
Distribución	PERCENT_RANK(), CUME_DIST()
Agregación	SUM(), COUNT(), AVG(), MIN(), MAX() con la cláusula OVER

---

Ejemplos

Clasificar con ROW_NUMBER, RANK y DENSE_RANK

Use las funciones de clasificación para asignar rangos a las filas según los valores de las columnas.

-- 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';

Totales acumulados y porcentajes

Las funciones de agregación como SUM y COUNT pueden usarse como funciones de ventana para calcular totales acumulados y porcentajes.

-- 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';

Comparar filas con LAG y LEAD

Use LAG y LEAD para acceder a valores de filas anteriores o posteriores.

-- 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;

Crear grupos con NTILE

Use NTILE para dividir las filas en un número especificado de grupos.

-- 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';

Usar PARTITION BY para análisis agrupados

Use PARTITION BY para realizar cálculos de ventana dentro de grupos.

-- 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;

Usar marcos de ventana

Especifique una cláusula de marco para controlar qué filas se incluyen en el cálculo de la ventana.

-- 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;

Para obtener documentación detallada de cada función de ventana, consulte la Referencia de funciones de ventana.

Para obtener más información, consulte https://sqlite.org/windowfunctions.html.