SQL窗口函数详解 数据分析必备的高级查询技巧

sql窗口函数是一种在查询结果每行显示与当前行相关聚合信息的工具，无需group by。它通过partition by分区分组数据、order by排序、rows between定义窗口范围实现功能，常见类型包括排名函数（如row_number(), rank(), dense_rank()）、聚合函数（如sum(), avg()）、偏移函数（如lag(), lead()）及其他函数（如first_value(), last_value()）。例如可用于获取各品类销量第一商品、计算累计销售额、日增长率等场景。相比group by会缩减行数，窗口函数保留原行数并附加计算值，适用于需展示明细且含关联聚合信息的情况。优化方面可从减少排序、合理分区、调整窗口大小、索引创建、避免子查询、数据库特性利用及数据预处理入手。高级应用场景涵盖会话分析、漏斗转化率统计、时间序列趋势预测、异常检测、a/b测试评估、用户行为路径挖掘及地理数据分析等领域。

SQL窗口函数，简单来说，就是让你在查询结果的每一行都能看到与当前行相关的聚合信息，而不需要使用GROUP BY。这就像你在看一场赛跑，窗口函数能让你同时看到每个选手当前的速度，以及他们的平均速度、最高速度等等，而不需要把所有选手分成小组。

窗口函数在数据分析中非常有用，可以进行排名、计算累计值、移动平均等等。掌握它们，你的SQL查询能力就能提升一个档次。

解决方案

窗口函数的基本语法如下：

SELECT     column1,     column2,     ...,     window_function(column) OVER (         [PARTITION BY column1, column2, ...]         [ORDER BY column3, column4, ...]         [ROWS BETWEEN start AND end]     ) AS alias_name FROM     table_name;

• window_function: 窗口函数，例如ROW_NUMBER(), RANK(), SUM(), AVG(), LAG(), LEAD()等等。
• OVER(): 定义窗口的子句。
• PARTITION BY: 将结果集分成多个分区，窗口函数将分别应用于每个分区。
• ORDER BY: 在每个分区内对行进行排序，窗口函数将按照指定的顺序进行计算。
• ROWS BETWEEN start AND end: 定义窗口的大小，即窗口函数将应用于哪些行。

常见窗口函数及其应用

1. 排名函数：ROW_NUMBER(), RANK(), DENSE_RANK()

   • ROW_NUMBER(): 为结果集中的每一行分配一个唯一的序号，从1开始。
   • RANK(): 为结果集中的每一行分配一个排名，相同值的行排名相同，但会跳过后续排名。
   • DENSE_RANK(): 为结果集中的每一行分配一个排名，相同值的行排名相同，且不会跳过后续排名。

   应用场景：

   • 获取每个类别中销量最高的商品。
   • 获取每个用户的活跃度排名。

   示例：

   SELECT     product_name,     category,     sales,     RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS sales_rank FROM     products;

   这个查询会按照类别对商品进行分区，并按照销量降序排序，然后为每个商品分配一个在类别内的销量排名。

2. 聚合函数：SUM(), AVG(), MIN(), MAX(), count()

   • SUM(): 计算窗口内指定列的总和。
   • AVG(): 计算窗口内指定列的平均值。
   • MIN(): 计算窗口内指定列的最小值。
   • MAX(): 计算窗口内指定列的最大值。
   • COUNT(): 计算窗口内指定列的行数。

   应用场景：

   • 计算累计销售额。
   • 计算移动平均值。

   示例：

   SELECT     order_date,     sales,     SUM(sales) OVER (ORDER BY order_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales FROM     orders;

   这个查询会按照订单日期升序排序，并计算每个订单日期的累计销售额。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW定义了窗口的大小，表示从第一行到当前行。

3. 偏移函数：LAG(), LEAD()

   • LAG(column, offset, default): 访问窗口内当前行之前offset行的column值。如果offset超出窗口范围，则返回default值。
   • LEAD(column, offset, default): 访问窗口内当前行之后offset行的column值。如果offset超出窗口范围，则返回default值。

   应用场景：

   • 计算同比、环比增长率。
   • 查找连续出现的事件。

   示例：

   SELECT     order_date,     sales,     LAG(sales, 1, 0) OVER (ORDER BY order_date ASC) AS previous_day_sales,     (sales - LAG(sales, 1, 0) OVER (ORDER BY order_date ASC)) / LAG(sales, 1, 0) OVER (ORDER BY order_date ASC) AS daily_growth_rate FROM     orders;

   这个查询会计算每个订单日期的日增长率，通过LAG()函数获取前一天的销售额。

4. 其他窗口函数：FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()

   • FIRST_VALUE(column): 返回窗口内第一行的column值。
   • LAST_VALUE(column): 返回窗口内最后一行的column值。
   • NTH_VALUE(column, N): 返回窗口内第N行的column值。

   应用场景：

   • 获取每个类别中第一个商品的名称。
   • 获取每个用户最近一次购买的商品。

   示例：

   SELECT     product_name,     category,     price,     FIRST_VALUE(product_name) OVER (PARTITION BY category ORDER BY price ASC) AS cheapest_product FROM     products;

   这个查询会按照类别对商品进行分区，并按照价格升序排序，然后获取每个类别中最便宜的商品名称。

SQL窗口函数和GROUP BY有什么区别？什么时候用窗口函数？

GROUP BY用于将结果集按照指定的列进行分组，然后对每个分组进行聚合计算。GROUP BY会改变结果集的行数，每个分组只返回一行。

窗口函数则不会改变结果集的行数，它会为每一行都计算一个值，这个值是基于当前行所在的窗口计算出来的。

什么时候用窗口函数？

• 当你需要在结果集中看到每一行的详细信息，并且还需要看到与当前行相关的聚合信息时，就应该使用窗口函数。
• 当你需要进行排名、计算累计值、移动平均等操作时，窗口函数是更好的选择。

如何优化SQL窗口函数的性能？

窗口函数的性能优化是一个需要仔细考虑的问题，尤其是在处理大数据集时。以下是一些可以尝试的优化方法：

1. 避免不必要的排序：

   ORDER BY子句是窗口函数中性能开销较大的部分。如果你的业务逻辑允许，尽量避免在窗口函数中使用ORDER BY。例如，如果你只需要计算总和，而不需要按照特定顺序计算累计总和，就可以省略ORDER BY。

2. 合理使用PARTITION BY：

   PARTITION BY子句可以将数据分成多个分区，窗口函数将分别应用于每个分区。合理使用PARTITION BY可以减少窗口函数需要处理的数据量，从而提高性能。确保你的分区策略与你的查询需求相符。

3. 优化窗口大小：

   ROWS BETWEEN子句定义了窗口的大小。如果窗口大小不合理，可能会导致性能问题。例如，如果窗口太大，窗口函数需要处理的数据量就会很大。如果窗口太小，窗口函数可能无法得到正确的结果。根据你的业务逻辑和数据分布，选择合适的窗口大小。

4. 索引优化：

   如果窗口函数中使用了ORDER BY或PARTITION BY子句，可以考虑在相应的列上创建索引。索引可以加快排序和分区操作的速度，从而提高窗口函数的性能。

5. 选择合适的窗口函数：

   不同的窗口函数性能不同。例如，ROW_NUMBER()通常比RANK()和DENSE_RANK()性能更好，因为ROW_NUMBER()只需要为每一行分配一个唯一的序号，而RANK()和DENSE_RANK()需要比较相同值的行。

6. 避免在窗口函数中使用子查询：

   尽量避免在窗口函数中使用子查询，因为子查询可能会导致性能问题。如果必须使用子查询，可以考虑将子查询的结果物化成临时表，然后再在窗口函数中使用临时表。

7. 使用数据库特定的优化技巧：

   不同的数据库系统可能有不同的窗口函数优化技巧。例如，postgresql支持并行窗口函数计算，可以利用多核CPU来提高性能。查阅你使用的数据库系统的文档，了解相关的优化技巧。

8. 数据预处理：

   如果你的数据量非常大，可以考虑对数据进行预处理，例如数据采样、数据聚合等。预处理可以减少窗口函数需要处理的数据量，从而提高性能。

9. 硬件升级：

   如果以上优化方法都无法满足你的性能需求，可以考虑升级硬件，例如增加内存、使用更快的CPU、使用SSD硬盘等。

窗口函数在实际数据分析中有哪些更高级的应用场景？

除了前面提到的排名、累计计算、移动平均等基础应用，窗口函数在实际数据分析中还有很多更高级的应用场景：

1. 会话分析：

   在Web或App行为分析中，可以将用户的连续行为视为一个会话。窗口函数可以用来识别会话，并计算会话时长、会话内的行为次数等指标。例如，可以使用LAG()函数来判断用户是否在一定时间内进行了连续操作，从而确定会话的开始和结束。

   SELECT     user_id,     timestamp,     CASE         WHEN timestamp - LAG(timestamp, 1, timestamp) OVER (PARTITION BY user_id ORDER BY timestamp ASC) > interval '30 minutes' THEN 1         ELSE 0     END AS session_start FROM     user_actions;

   这个查询可以识别每个用户的会话开始时间，假设会话超时时间为30分钟。

2. 漏斗分析：

   漏斗分析用于分析用户在完成某个目标过程中的转化率。窗口函数可以用来计算每个步骤的转化率，并找出转化率最低的步骤，从而优化用户体验。例如，可以使用COUNT()和LAG()函数来计算每个步骤的用户数量，并计算转化率。

3. 时间序列分析：

   窗口函数可以用于对时间序列数据进行平滑处理、趋势分析、季节性分析等。例如，可以使用AVG()函数计算移动平均值，从而平滑时间序列数据。可以使用LAG()和LEAD()函数计算同比、环比增长率，从而分析时间序列数据的趋势。

4. 异常检测：

   窗口函数可以用于检测异常值。例如，可以使用AVG()和STDDEV()函数计算移动平均值和标准差，然后将偏离移动平均值超过一定倍数的标准差的值视为异常值。

5. A/B测试分析：

   在A/B测试中，窗口函数可以用来计算每个版本的转化率、点击率等指标，并进行统计显著性检验。例如，可以使用SUM()和COUNT()函数计算每个版本的转化次数和总用户数，然后使用CHISQ_TEST()函数进行卡方检验。

6. 用户行为路径分析：

   窗口函数可以用来分析用户的行为路径，例如用户在网站上浏览了哪些页面，按照什么顺序浏览的。可以使用LAG()和LEAD()函数来获取用户的前后行为，然后分析用户的行为路径。

7. 地理空间数据分析：

   窗口函数可以用于分析地理空间数据，例如计算每个区域的人口密度、计算每个区域的平均房价等。可以使用ST_DISTANCE()函数计算两个地理位置之间的距离，然后使用窗口函数计算指定区域内的总人口数或平均房价。

这些只是窗口函数的一些高级应用场景，实际上，窗口函数的应用非常广泛，只要你需要对数据进行分组、排序、聚合、偏移等操作，都可以考虑使用窗口函数。