SQL窗口函数实战精讲（你不知道的分析函数黑科技）

第一章：SQL窗口函数实战精讲（你不知道的分析函数黑科技）

理解窗口函数的核心机制

窗口函数（Window Function）是SQL中用于执行行间计算的强大工具，与传统聚合函数不同，它不会将多行合并为单行，而是保留原始行并附加计算结果。其核心语法结构如下：

SELECT 
  column,
  AVG(column) OVER (
    PARTITION BY partition_expr 
    ORDER BY order_expr 
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM table;

其中 OVER() 定义了窗口的范围：PARTITION BY 划分数据组，ORDER BY 指定排序逻辑，ROWS BETWEEN 精确控制参与计算的行集。

常用分析函数实战示例

以下是一些高频使用的分析函数及其典型应用场景：

• ROW_NUMBER()：为每行分配唯一序号，常用于去重或分页
• RANK()：带跳跃排名，相同值并列后跳过后续名次
• LAG()/LEAD()：访问前一行或后一行的数据，适用于趋势分析

-- 计算每位员工在其部门内的薪资排名
SELECT 
  name, 
  dept, 
  salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept
FROM employees;

高级窗口控制技巧

灵活使用窗口帧可实现复杂业务逻辑。例如，计算滚动平均销售额：

时间	销售额	3日滚动均值
Day1	100	100.00
Day2	150	125.00
Day3	200	150.00

-- 使用 RANGE 或 ROWS 精确控制窗口边界
SELECT 
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS rolling_3day_avg
FROM sales;

第二章：窗口函数基础与核心概念

2.1 窗口函数语法结构深度解析

窗口函数是SQL中用于执行复杂分析操作的核心工具，其语法结构清晰且高度灵活。基本语法如下：

SELECT 
    column1,
    AVG(column2) OVER (
        PARTITION BY column1 
        ORDER BY column3 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM table_name;

上述代码展示了窗口函数的标准构成：`OVER()` 子句定义窗口框架。其中，`PARTITION BY` 将数据分组，类似 `GROUP BY` 但不聚合行；`ORDER BY` 指定窗口内行的排序逻辑；`ROWS BETWEEN ... AND ...` 明确窗口边界，此处表示当前行与前两行构成滑动窗口。

核心组件语义解析

• PARTITION BY：划分逻辑分区，使计算在组内独立进行；
• ORDER BY：决定窗口内数据处理顺序，对排名或移动计算至关重要；
• Window Frame：如 ROWS 或 RANGE，精确控制参与计算的行集范围。

正确理解各部分协同机制，是构建高效分析查询的基础。

2.2 PARTITION BY 与分组逻辑的差异对比

在SQL中，PARTITION BY 和 GROUP BY 虽然都涉及数据分组，但其底层逻辑和使用场景存在本质区别。

核心行为差异

GROUP BY 对数据进行聚合，每组返回一行结果；而 PARTITION BY 用于窗口函数中，保留原始行数，在每个分区内部执行计算。

语法示例对比

-- GROUP BY：每组仅返回一行
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

-- PARTITION BY：每行保留，按部门分区计算平均值
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

上述代码中，PARTITION BY 在不减少行数的前提下实现分组计算，适用于需同时访问原始记录与聚合信息的场景。

功能对比表

特性	GROUP BY	PARTITION BY
输出行数	压缩为每组一行	保持原始行数
适用上下文	聚合查询	窗口函数
数据粒度	丢失明细	保留明细

2.3 ORDER BY 在窗口中的排序控制机制

在窗口函数中，ORDER BY 子句不仅决定行的逻辑顺序，还直接影响窗口帧的边界定义与计算结果。与全局查询的排序不同，窗口内的 ORDER BY 用于构建有序的数据处理上下文。

排序对窗口行为的影响

当在 OVER() 中指定 ORDER BY，系统将创建一个累积式窗口帧（如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），从而支持逐行聚合。

SELECT 
  id, 
  sales, 
  SUM(sales) OVER (ORDER BY id) AS running_total
FROM sales_data;

上述语句中，ORDER BY id 确保每行的累计和基于 id 的递增顺序累加。若省略 ORDER BY，则整个分区被视为无序集合，无法实现有序累计。

与 PARTITION BY 的协同作用

结合 PARTITION BY 使用时，ORDER BY 在每个分区内独立生效，实现分组内排序与局部累积：

• 未分区时，ORDER BY 影响整个结果集的窗口顺序；
• 分区后，ORDER BY 仅在各组内部生效，互不干扰。

2.4 ROWS/RANGE 框架模式的实际应用场景

在窗口函数的使用中，ROWS 和 RANGE 框架模式决定了当前行与其邻近行的数据范围边界，直接影响聚合计算的结果。

ROWS 模式：基于物理行数的窗口

SELECT 
    order_date, 
    revenue,
    AVG(revenue) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS avg_revenue_3_days
FROM sales;

该查询计算每日及其前三天的平均收入。ROWS 模式按物理行数划分窗口，适合时间序列中固定数量样本的滑动平均分析。

RANGE 模式：基于逻辑值的窗口

RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW

此表达式将窗口限制在当前日期前7天内的所有记录，适用于不规则时间间隔但需按时间跨度聚合的场景。

模式	依据	适用场景
ROWS	物理行数	固定样本数量的移动平均
RANGE	值域范围	时间区间或数值区间聚合

2.5 OVER() 子句的灵活组合技巧

在复杂分析场景中，OVER() 子句可通过多种方式组合窗口函数与分区、排序逻辑，实现精细化的数据计算。

结合PARTITION BY与ORDER BY

通过分区与排序的协同，可在组内进行有序计算。例如：

SELECT 
  sales_date,
  region,
  revenue,
  SUM(revenue) OVER (PARTITION BY region ORDER BY sales_date) AS running_total
FROM sales_data;

该查询按区域分组，并在每组内按日期累计收入。PARTITION BY 划分数据范围，ORDER BY 确保累加顺序，形成动态运行总额。

使用ROWS/RANGE定义窗口范围

可进一步限定计算行集：

AVG(revenue) OVER (
  ORDER BY sales_date 
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

此代码计算当前行及前两行的移动平均，ROWS BETWEEN 明确物理行边界，适用于时间序列平滑处理。

• PARTITION BY：分组逻辑
• ORDER BY：排序依据
• ROWS/RANGE：窗口帧控制

三者组合赋予窗口函数强大表达能力。

第三章：常用分析函数实战演练

3.1 ROW_NUMBER() 实现去重与排名控制

在处理重复数据和排序控制时，`ROW_NUMBER()` 是一个强大的窗口函数，能够在结果集中为每一行分配唯一序号。

基本语法与作用

ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY sort_column)

该函数根据指定的分组字段（PARTITION BY）和排序规则（ORDER BY），为每行生成连续的整数编号，从1开始递增。

去重场景应用

通过子查询结合 `ROW_NUMBER()` 可实现基于优先级的去重：

SELECT * FROM (
  SELECT id, name, ROW_NUMBER() OVER (PARTITION BY id ORDER BY update_time DESC) as rn
  FROM user_log
) t WHERE rn = 1;

上述语句确保每个用户ID仅保留更新时间最新的记录，有效去除历史冗余数据。

3.2 RANK() 与 DENSE_RANK() 的业务选型策略

在处理排序需求时，RANK() 和 DENSE_RANK() 的差异直接影响结果集的连续性。当存在并列排名时，RANK() 会产生跳跃间隙，而 DENSE_RANK() 保持连续编号。

典型应用场景对比

• RANK()：适用于需要体现“实际位次”的场景，如竞赛排名，第三名后若有并列，下一位应为第五名；
• DENSE_RANK()：适合需紧凑分组的业务，如员工绩效等级划分，要求等级之间无断层。

SQL 示例与解析

SELECT 
  name, 
  score,
  RANK() OVER (ORDER BY score DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM exam_results;

上述语句中，RANK() 在相同分数下赋予相同排名，但后续排名跳跃；DENSE_RANK() 则始终递增1，确保等级连续。选择依据取决于业务是否容忍排名间隙。

3.3 LEAD() 和 LAG() 构建时间序列差值分析

在时间序列分析中，LEAD() 和 LAG() 窗口函数可用于获取当前行之后或之前的相邻行数据，适用于计算变化量、增长率等指标。

基本语法与应用场景

SELECT 
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS diff
FROM sales_data;

上述查询中，LAG(revenue, 1) 获取按日期排序的前一行收入值。参数 1 表示偏移量为1行，用于计算日环比差值。

正向预测：LEAD() 函数

与 LAG() 相反，LEAD() 可读取后续行数据，适用于预警或趋势预判场景：

SELECT 
  date,
  revenue,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_revenue
FROM sales_data;

该语句提取下一日收入，便于构建前后对比矩阵。

第四章：复杂场景下的高级应用模式

4.1 多层嵌套窗口函数的数据透视技巧

在复杂数据分析场景中，多层嵌套窗口函数能实现精细化的数据透视。通过在窗口函数内部嵌套另一层聚合或排序函数，可逐级提炼数据特征。

执行逻辑分层

先通过内层窗口函数计算局部指标，再由外层函数进行全局排序或二次聚合，实现维度下钻。

SELECT 
    dept,
    emp_name,
    salary,
    RANK() OVER (
        PARTITION BY dept 
        ORDER BY AVG(salary) OVER (PARTITION BY dept) DESC
    ) AS dept_rank
FROM employees;

上述语句中，内层 AVG(salary) 计算各部门平均薪资，外层 RANK() 依据该均值对部门排序并赋秩，实现部门层级的横向对比。

适用场景

• 跨维度绩效排名
• 动态分组统计
• 趋势内部分析

该技术提升了SQL在BI分析中的表达能力，避免多次中间表汇总。

4.2 累计求和与移动平均的实时计算方案

在流式数据处理中，累计求和与移动平均是常见的实时指标计算需求。为实现低延迟、高吞吐的计算，通常采用滑动窗口机制结合状态存储。

核心算法设计

使用固定大小的环形缓冲区维护最近 N 个值，每次新数据到达时更新总和并剔除过期值：

// RingBuffer 实现移动平均
type MovingAverage struct {
    window     []float64
    sum        float64
    index      int
    isFull     bool
}

func (ma *MovingAverage) Add(value float64) {
    if ma.isFull {
        ma.sum -= ma.window[ma.index] // 剔除旧值
    }
    ma.window[ma.index] = value
    ma.sum += value
    ma.index = (ma.index + 1) % len(ma.window)
    if ma.index == 0 {
        ma.isFull = true
    }
}

func (ma *MovingAverage) Avg() float64 {
    count := len(ma.window)
    if !ma.isFull {
        count = ma.index
        if count == 0 {
            return 0
        }
    }
    return ma.sum / float64(count)
}

上述代码通过环形数组避免重复遍历，Add 操作时间复杂度为 O(1)，Avg 计算仅需常量时间。

性能对比

方案	时间复杂度	内存占用	适用场景
全量重算	O(n)	O(n)	离线批处理
环形缓冲	O(1)	O(k)	实时流处理

4.3 分组内百分比与占比分析的精准实现

在数据分析中，计算分组内的百分比与占比是揭示数据分布规律的关键步骤。通过聚合函数与窗口函数的结合，可实现精细化的比例统计。

基础语法结构

SELECT 
    category,
    COUNT(*) AS group_count,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() AS percentage
FROM sales 
GROUP BY category;

该查询按类别分组，利用 SUM(COUNT(*)) OVER() 计算总记录数，进而得出每组占比。乘以 100.0 确保浮点运算精度。

分组内归一化处理

当需按多级维度分析时，使用分区窗口函数：

SUM(value) / SUM(SUM(value)) OVER(PARTITION BY region)

此表达式计算各区域（region）内品类的贡献率，实现“组内100%”的归一化展示。

结果示例表

Category	Sales	Percentage (%)
A	150	30.0
B	350	70.0

4.4 动态区间统计在用户行为分析中的运用

在用户行为分析中，动态区间统计能够实时捕捉用户活跃度、点击频率和停留时长等关键指标的变化趋势。通过滑动时间窗口技术，系统可对指定时间段内的行为数据进行聚合计算。

核心算法实现

// 滑动窗口统计每分钟用户点击量
type SlidingWindow struct {
    windowSize time.Duration // 窗口大小，例如5分钟
    bucketDuration time.Duration // 分桶粒度，例如1分钟
    buckets map[int64]int64 // 时间戳对应计数
}
func (sw *SlidingWindow) Increment(timestamp int64) {
    key := timestamp / int64(sw.bucketDuration.Seconds())
    sw.buckets[key]++
    sw.cleanupOldBuckets(timestamp)
}

该结构将时间划分为固定桶，Increment 方法记录事件并清理过期桶，确保仅保留有效区间数据。

应用场景

• 实时检测异常流量波动
• 计算DAU/MAU比率变化趋势
• 识别高价值用户行为路径

第五章：1024 SQL 数据分析实战技巧

高效聚合与分组策略

在处理大规模用户行为日志时，合理使用 GROUP BY 与聚合函数可显著提升查询效率。以下语句统计每日活跃用户数，并排除测试账号：

SELECT 
    DATE(event_time) AS log_date,
    COUNT(DISTINCT user_id) AS dau
FROM user_events 
WHERE user_id NOT LIKE 'test_%'
    AND event_time >= '2023-10-01'
GROUP BY DATE(event_time)
ORDER BY log_date;

窗口函数实现排名分析

分析销售数据时，常需获取每个品类销量前两名的商品。利用 ROW_NUMBER() 窗口函数可轻松实现：

WITH ranked_sales AS (
    SELECT 
        product_name,
        category,
        sales_volume,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY sales_volume DESC
        ) AS rn
    FROM product_sales
)
SELECT product_name, category, sales_volume
FROM ranked_sales 
WHERE rn <= 2;

多表关联优化建议

执行 JOIN 操作时，应确保关联字段已建立索引。常见订单与用户关联场景如下：

• 优先使用 INNER JOIN 明确业务范围
• 避免 SELECT *，仅提取必要字段
• 大表放在 JOIN 右侧（基于某些数据库执行器特性）

数据透视转换实战

将交易类型从行转为列，便于报表展示。使用条件聚合实现 PIVOT 效果：

region	online_sales	offline_sales
East	120000	85000
West	98000	110000

SELECT
    region,
    SUM(CASE WHEN channel = 'online' THEN amount ELSE 0 END) AS online_sales,
    SUM(CASE WHEN channel = 'offline' THEN amount ELSE 0 END) AS offline_sales
FROM sales_records 
GROUP BY region;