窗口函数

窗口函数对与当前行相关的一组行执行计算。与为一组行返回单个结果的聚合函数不同，窗口函数为每一行返回一个值，同时考虑由相关行组成的“窗口”。

另请参阅： [SELECT] [WITH (CTEs)] [ 窗口函数参考 ]

---

语法

窗口函数使用 OVER 子句定义窗口规范：

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

windowFunctionCall::=

窗口函数

窗口函数是一类专用函数，对窗口规范定义的一组行执行计算。它们既可以是专用的窗口函数（如 ROW_NUMBER 或 LAG），也可以是与 OVER 子句一起使用的聚合函数（如 SUM 或 COUNT）。

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

窗口规范

窗口规范定义在窗口函数计算中如何对行进行分区、排序和划定帧。PARTITION BY 将行划分为多个组，ORDER BY 确定每个分区内的顺序，可选的帧子句指定相对于当前行的哪些行参与计算。

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

可用的窗口函数

类别	函数
排名	ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()
偏移	LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()
分布	PERCENT_RANK()、CUME_DIST()
聚合	SUM()、COUNT()、AVG()、MIN()、MAX()，配合 OVER 子句使用

---

示例

使用 ROW_NUMBER、RANK 和 DENSE_RANK 进行排名

使用排名函数根据列值为行分配排名。

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

累计总和与百分比

SUM 和 COUNT 等聚合函数可以用作窗口函数，以计算累计总和与百分比。

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

使用 LAG 和 LEAD 比较行

使用 LAG 和 LEAD 访问前面或后面的行中的值。

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

使用 NTILE 创建分组

使用 NTILE 将行划分为指定数量的组。

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

使用 PARTITION BY 进行分组分析

使用 PARTITION BY 在组内执行窗口计算。

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

使用窗口帧

指定帧子句以控制哪些行参与窗口计算。

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

有关每个窗口函数的详细文档，请参阅窗口函数参考。

有关更多信息，请参阅 https://sqlite.org/windowfunctions.html。