sql窗口函数通过在不减少行数的前提下对分组数据执行计算，实现复杂排名和分组分析，1. 使用row_number()、rank()、dense_rank()和ntile()结合over(partition by…order by…)进行分组内排序；2. 利用lag()和lead()获取前后行数据以支持时间序列分析；3. 结合rows between或range between实现移动平均、累计求和等动态计算；4. 在业务决策中通过用户行为分析、绩效对比和趋势预测提升数据洞察力，使分析从静态结果转向动态过程，最终支持更精准的决策。
SQL窗口函数是处理复杂数据分析任务的利器，它能在不聚合整个数据集的情况下，对相关行集进行计算，从而实现排名、移动平均、累计求和等高级分析功能，极大提升了数据洞察的深度和效率。它们让原本需要多步子查询或在应用层处理的逻辑，变得简洁而高效，是现代数据分析师工具箱里不可或缺的一环。
SQL窗口函数提供了一种在结果集的“窗口”上执行计算的强大方式，这个“窗口”是根据特定条件（如分区和排序）定义的一组行。它们允许你在不减少返回行数的情况下，对行组执行聚合、排名或分析操作。你可以想象它像一个可移动的取景框，每次只看一部分数据，但又保持了全局的视野。这与传统的
GROUP BY
聚合函数有本质区别，后者会把多行数据合并成一行，丢失了原始行的细节。窗口函数的魅力在于，它既能提供聚合信息，又能保留每行的独立性，这对于需要行级详细分析的场景来说简直是福音。
-- 基础示例：计算每个部门的平均工资，同时保留每个员工的详细信息 SELECT employee_id, employee_name, department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_department_salary FROM employees; -- 另一个例子：按销售额对每个地区的商店进行排名 SELECT store_id, region, sales, RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rank_in_region FROM sales_data;
在我看来，真正掌握窗口函数，就像是拿到了一把瑞士军刀，它能解决很多看似棘手的问题。从简单的排名到复杂的移动平均、同比环比分析，甚至客户生命周期价值的计算，它都能优雅地完成。
SQL窗口函数如何实现复杂排名和分组分析？
在数据分析中，我们经常需要对数据进行排名，但这种排名往往不是简单的全局排名，而是基于某个分组内部的排名。比如，我想知道每个班级里，学生的成绩排名；或者在每个产品类别中，哪些商品的销售额最高。传统的
GROUP BY
或者子查询在处理这类问题时会显得非常笨拙，甚至无法直接实现。这就是窗口函数大放异彩的地方。
SQL提供了几种不同的排名函数，它们各自有微妙的区别，适用于不同的场景：
-
: 为分区内的每一行分配一个唯一的连续整数。如果有多行具有相同的值，它们会得到不同的行号。它不考虑值的相等性，只管顺序。
ROW_NUMBER()
-
: 为分区内的每一行分配一个排名。如果有多行具有相同的值，它们会得到相同的排名，并且下一个不同的值会跳过相应的排名（例如，1, 2, 2, 4）。
RANK()
-
: 类似于
DENSE_RANK()
RANK()
，但当有多行具有相同的值时，下一个不同的值不会跳过排名（例如，1, 2, 2, 3）。排名是连续的。
-
: 将分区内的行分成
NTILE(n)
n
个近似相等的分组，并为每行分配一个组号。这在需要将数据分成几等份（如四分位数、十分位数）时非常有用。
这些函数都结合
OVER (PARTITION BY ... ORDER BY ...)
子句使用，
PARTITION BY
定义了分组的依据，
ORDER BY
定义了组内排名的顺序。
举个例子，假设我们有一个销售表，记录了不同销售员在不同区域的销售业绩。我们想找出每个区域内销售额前三的销售员。
SELECT region, salesperson, sales_amount, RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank FROM sales_performance WHERE RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) <= 3;
注意，直接在
WHERE
子句中使用窗口函数通常是不行的，因为窗口函数在
WHERE
子句之后执行。正确的做法是将其放在子查询或CTE（Common table Expression）中。
WITH RankedSales AS ( SELECT region, salesperson, sales_amount, RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank FROM sales_performance ) SELECT region, salesperson, sales_amount, sales_rank FROM RankedSales WHERE sales_rank <= 3;
通过这种方式，我们可以非常灵活地实现各种复杂的排名需求，比如找出每个产品类别中最受欢迎的商品，或者每个用户最近的几次购买记录。这比编写多个子查询或连接操作要简洁得多，而且通常性能也更好。
利用SQL窗口函数进行时间序列数据分析有哪些技巧？
时间序列数据分析是数据分析中一个非常常见的场景，比如分析销售额的趋势、用户活跃度的变化、股价的波动等。在这些场景下，我们经常需要比较当前值与前一个或后一个值、计算移动平均、累计总和等。SQL窗口函数在这里展现出了它惊人的能力，让这些分析变得轻而易举。
核心的技巧在于使用
LAG()
、
LEAD()
以及配合
ROWS BETWEEN
或
RANGE BETWEEN
的聚合函数。
-
: 返回当前行之前第
LAG(expression, offset, default_value)
offset
行的
expression
值。这对于计算环比增长、与前一天/月/年的数据进行比较非常有用。
-
: 返回当前行之后第
LEAD(expression, offset, default_value)
offset
行的
expression
值。这在预测趋势或查看未来事件时可能有用，虽然在实际业务中用得相对少一些，但理解其功能很重要。
- 聚合函数与窗口帧: 比如
SUM() OVER (...)
、
AVG() OVER (...)
等，结合窗口帧（
ROWS BETWEEN ... AND ...
或
RANGE BETWEEN ... AND ...
）可以计算移动平均、累计求和等。
我们来看几个实际的例子。
1. 计算日销售额的环比增长率：
假设我们有一个
daily_sales
表，包含
sale_date
和
amount
。
WITH DailySalesWithLag AS ( SELECT sale_date, amount, LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS previous_day_amount FROM daily_sales ) SELECT sale_date, amount, previous_day_amount, (amount - previous_day_amount) * 100.0 / previous_day_amount AS daily_growth_rate_percent FROM DailySalesWithLag WHERE previous_day_amount > 0; -- 避免除以零
这里，
LAG()
函数获取了前一天的销售额，然后我们就可以轻松计算出增长率。
2. 计算7天移动平均销售额：
移动平均是平滑时间序列数据、识别趋势的常用方法。
SELECT sale_date, amount, AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_moving_avg FROM daily_sales;
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
定义了一个窗口，包含当前行和它之前的6行，总共7行。这样，
AVG()
函数就会计算这7天的平均值。
3. 计算累计销售额：
这对于查看总销售额随时间的变化趋势非常有用。
SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sales FROM daily_sales;
当
OVER
子句中只有
ORDER BY
而没有
PARTITION BY
和窗口帧时，默认的窗口帧是从分区开始到当前行（或整个数据集的开始到当前行）。
这些技巧在处理日志数据、金融数据、物联网传感器数据等场景中都非常实用。它们让复杂的时序分析逻辑变得清晰且易于维护，极大地提升了数据分析的效率。
SQL窗口函数在业务决策中如何提升数据洞察力？
在业务决策中，数据洞察力是核心竞争力。而SQL窗口函数，在我看来，就是提升这种洞察力的“放大镜”和“显微镜”。它不仅仅是技术上的优化，更是思维方式上的转变，让我们能从更细致、更全面的角度审视数据，发现那些传统聚合查询难以捕捉的模式和趋势。
举几个实际的业务场景，看看窗口函数是如何帮助我们做出更明智的决策的：
1. 精准的用户行为分析与留存： 假设我们想了解用户首次购买后，在后续特定时间段内的复购情况。传统的做法可能需要复杂的自连接或多次聚合。但用窗口函数，我们可以轻松地计算出每个用户的首次购买日期，然后以此为基准，分析后续的购买行为。
WITH UserFirstPurchase AS ( SELECT user_id, MIN(order_date) OVER (PARTITION BY user_id) AS first_purchase_date, order_date, order_amount FROM orders ) SELECT user_id, first_purchase_date, order_date, order_amount, (order_date - first_purchase_date) AS days_since_first_purchase -- 假设日期可以直接相减得到天数 FROM UserFirstPurchase WHERE (order_date - first_purchase_date) BETWEEN 0 AND 30; -- 分析首购后30天内的行为
通过这种方式，我们可以构建用户留存曲线，识别高价值用户群体，并针对性地制定营销策略。
2. 绩效评估与异常检测： 在员工绩效评估中，我们可能需要将每个员工的业绩与他们所属团队的平均业绩进行比较，或者找出明显偏离平均水平的“异常”员工。
SELECT employee_id, employee_name, department, sales_target_completion, AVG(sales_target_completion) OVER (PARTITION BY department) AS dept_avg_completion, sales_target_completion - AVG(sales_target_completion) OVER (PARTITION BY department) AS deviation_from_avg FROM employee_performance;
通过
deviation_from_avg
，我们可以快速识别出那些表现远超平均水平的“明星员工”，或者需要额外关注和培训的“落后员工”。这比简单地看绝对值更有说服力，因为它考虑了团队的整体表现。
3. 库存优化与预测： 在零售业，了解商品的销售波动性对于库存管理至关重要。我们可以计算商品的移动平均销售量，并与当前库存量进行比较，以优化补货策略。
SELECT product_id, sale_date, daily_sales_volume, AVG(daily_sales_volume) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS thirty_day_moving_avg_sales FROM product_daily_sales;
这个30天移动平均可以作为短期需求预测的一个依据，帮助我们避免库存积压或缺货。
窗口函数让数据分析从“看结果”升级到“看过程”和“看关系”。它能帮助我们发现数据点之间的内在联系，比如一个用户的首次购买行为如何影响其后续的生命周期价值，或者一个产品在市场推广后的销售曲线变化。这种深入的洞察力，是驱动精准业务决策的关键。
