深入理解窗口函数和使用介绍

最新推荐文章于 2024-02-22 15:30:39 发布
最新推荐文章于 2024-02-22 15:30:39 发布
阅读量2k 收藏 18
点赞数 5
分类专栏： 大数据 数据库 文章标签： 大数据 数据分析 窗口函数 数据仓库 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wt334502157/article/details/132452358
版权

深入理解窗口函数及其应用

​ 在数据库查询和分析领域，窗口函数（Window Functions）是一种强大且灵活的工具，可以帮助开发者执行各种复杂的分析和聚合操作。窗口函数不仅可以简化查询，还能提高性能并减少数据处理的复杂性。本文将深入介绍数据库窗口函数的概念、基本用法以及实际应用示例。

1. 窗口函数：基本概念

1-1. 什么是窗口函数？

​ 窗口函数（Window Function）是一种在关系型数据库中执行聚合、分析和排序操作的高级工具。与常规的聚合函数（如SUM、AVG、COUNT等）不同，窗口函数允许在结果集中的每一行上进行计算，而不需要将数据分组。窗口函数提供了对每一行的子集（称为窗口）执行计算的能力，同时保留原始数据行的上下文信息。

​ 想象一家咖啡馆，里面有一堵特殊的墙，我们称之为“洞察之墙”。这堵墙背后有一个魔法窗户，这就是窗口函数。这个窗户可以让你透过它去看到一系列的数据，但是你可以使用特殊的工具，比如望远镜和魔法棒，来查看和分析这些数据。

现在，让我们来看看这些工具的具体用途：

  1. 望远镜 - RANK、DENSE_RANK、ROW_NUMBER： 想象你站在窗户前，你可以通过使用望远镜，看到墙上的标识。这些标识告诉你每个数据在排序中的位置，就像在一个比赛中知道运动员的排名一样。RANK表示前几名的排名，DENSE_RANK会跳过重复的排名，而ROW_NUMBER则是唯一的编号。

  2. 魔法棒 - SUM、AVG、COUNT： 窗户旁边有一个魔法棒，它可以帮助你在窗户中选取一定范围的数据。比如，你可以用魔法棒圈选一段时间内的销售额，然后通过SUM函数计算总和，AVG函数计算平均值，COUNT函数计算数量。

  3. 时光机 - LAG、LEAD： 在窗户下面，有一个叫做时光机的按钮。当你按下按钮时，你可以在不同的时间点之间切换，就像穿越时间一样。你可以看到当前数据和前后时间点的数据，比如昨天的销售额和明天的预测。

  4. 魔法秤 - PERCENTILE： 有一个魔法秤，可以帮助你找出数据的相对位置。你可以在窗户中选取一些数据，然后魔法秤会告诉你这些数据在整体数据中的位置，比如你的咖啡订单在所有订单中的百分之多少。

  5. 飞行地图 - PARTITION： 窗户上有一个飞行地图，可以帮助你划分不同的区域。你可以选择在地图上的某个区域，然后对这个区域内的数据进行分析。这就像在不同的国家中选择一个国家，然后研究该国的文化和特点。

通过这些工具，你可以在“洞察之墙”上观察、分析、比较和预测数据，从而获得有关数据世界的深入了解。这就是窗口函数的魔法！

1-2. 窗口函数使用场景

窗口函数在SQL查询中的使用情况逐渐增多，尤其在处理复杂分析、排序和分组需求时非常有用。它们通常用于以下几种场景：

  1. 排名和排序：窗口函数能够计算每行在结果集中的排名，包括处理并列排名的情况。这对于确定最高销售额、排名前N的产品等非常有用。

  2. 累计和和平均值：通过窗口函数，可以在结果集中的每一行上计算累计和、累计平均值等聚合操作，而不需要在原始数据上进行逐步累加。

  3. 移动平均和滑动窗口分析：窗口函数可以用于计算移动平均、滑动窗口的统计数据等，这在时间序列分析和趋势预测中非常有用。

  4. 前后行比较：窗口函数允许访问结果集中的前一行和后一行数据，从而可以进行前后行数据的比较和分析。

  5. 填充和插值：在存在缺失数据的情况下，窗口函数可以用来填充缺失值，执行插值操作，以保持数据的完整性和连续性。

  6. 分组内聚合：在特定分组内执行聚合操作，不同于普通的聚合函数需要使用GROUP BY子句进行分组。

在使用窗口函数时，通常会涉及到窗口框架的定义，包括分区（PARTITION BY）、排序（ORDER BY）以及窗口范围（ROWS BETWEEN）。这些参数决定了窗口函数的计算范围和顺序。不同的数据库系统可能对窗口函数的语法和支持略有不同，但基本概念和用法是相似的。

1-3. 窗口函数的语法结构

窗口函数的语法通常如下：

<窗口函数> OVER (
    [PARTITION BY <分区列>]
    [ORDER BY <排序列>]
    [ROWS <行范围>]
)
  • <窗口函数>：代表要执行的窗口函数，如SUM、AVG、ROW_NUMBER等。
  • PARTITION BY子句：用于将数据分成不同的分区，窗口函数将在每个分区内执行。
  • ORDER BY子句：定义数据的排序方式，决定窗口函数的计算顺序。
  • ROWS子句：指定窗口的范围，可以是行数、区间等。

1-4. 窗口函数的优缺点

  • 窗口函数优点

窗口函数（Window Functions）在数据库查询和分析中具有许多优点，使得它们成为处理复杂分析和聚合操作的强大工具。以下是窗口函数的一些优点：

  1. 避免数据分组：传统的聚合函数通常需要使用GROUP BY子句将数据分组，而窗口函数可以在不进行数据分组的情况下进行计算。这意味着您可以在结果集的每一行上执行聚合操作，而无需创建多个分组。

  2. 保留原始数据行上下文：窗口函数在计算时保留了原始数据行的上下文信息，这对于进行排名、前后行比较等操作非常有用。您可以获取每一行周围的数据，而不仅仅是聚合结果。

  3. 灵活的窗口定义：窗口函数允许您通过定义分区、排序方式和窗口范围来灵活地控制计算的范围。这使您能够针对不同的需求定制窗口函数的行为。

  4. 避免自连接：在某些情况下，窗口函数可以避免使用自连接来执行一些计算。这可以减少查询的复杂性并提高可读性。

  5. 高性能处理大数据量：尽管在某些数据库系统中窗口函数的性能可能受到限制，但在某些情况下，它们仍然可以通过充分利用索引和缓存等优化策略来处理大规模数据集。

  6. 简化查询语句：窗口函数能够将复杂的查询逻辑集中到一条查询语句中，而不需要创建多个临时表或子查询。

  7. 适用于各种分析场景：窗口函数可以用于排名、累计求和、移动平均、分组内聚合、数据填充和插值等多种分析场景，使得查询和分析更加灵活。

  8. 更少的数据转移：由于窗口函数可以在结果集中计算，减少了数据从数据库传输到应用程序的次数，从而提高了查询的效率。

总之，窗口函数是一种非常有用的工具，可以大大简化复杂的分析和聚合操作，同时提供更高的灵活性和性能。无论是进行数据分析还是生成报告，窗口函数都能在很多情况下大幅提升工作效率。

  • 窗口函数缺点

  1. 性能开销： 窗口函数可能会对查询的性能产生影响，特别是在处理大数据量时。窗口函数需要在内存中维护中间结果集，这可能导致额外的计算和内存开销。

  2. 排序成本： 许多窗口函数需要对数据进行排序，以便在窗口范围内进行计算。排序操作在大数据集上可能是昂贵的，特别是在没有正确索引支持的情况下。

  3. 窗口范围限制： 窗口函数的窗口范围可能会受到限制。某些数据库系统可能不支持复杂的窗口范围定义，从而限制了一些分析的可能性。

  4. 可读性和复杂性： 使用窗口函数的查询可能会变得复杂，对于不熟悉窗口函数的开发人员来说，可读性可能会降低。一些复杂的分析可能需要深入理解窗口函数的工作原理。

  5. 数据库兼容性： 不同的数据库系统对窗口函数的支持和实现方式可能会有所不同。一些数据库可能不支持所有的窗口函数，或者其语法和语义可能存在差异。

  6. 数据类型和精度： 窗口函数的计算可能受到数据类型和精度的影响，特别是在执行累计计算时。确保正确的数据类型和精度可能需要额外的处理。

  7. 数据倾斜： 如果数据倾斜，即某些分区中的数据量明显多于其他分区，窗口函数的性能可能会受到影响，因为在数据分发和计算上会出现不均衡。

  8. 查询复杂性： 在一些复杂的业务场景中，可能需要使用多个窗口函数组合，以达到所需的分析目标。这可能会增加查询的复杂性。

尽管窗口函数具有这些缺点，但对于许多分析场景来说，它们仍然是强大且必要的工具。在使用窗口函数时，需要权衡性能、可读性和分析需求，并考虑如何优化查询以减少潜在的缺点影响。

1-5. 窗口函数示例演示

在介绍窗口函数之前，先展示几个常用示例，来感受窗口函数的功能与场景，这里先不着急看语法结构，仅仅了解一下功能有个大致体会，后续会逐个详细介绍

假设我们有一个名为sales的表，记录了不同产品的销售数据，包括日期、产品名称和销售额。我们将使用这个表来演示窗口函数的不同应用。

  • 示例1：排名和排序

在这个示例中，我们将计算每个产品的销售额排名：

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

​ 该查询将从 “sales” 表中选择产品名称、销售日期、销售金额以及根据每个产品的销售金额降序排列的销售排名。排名将在每个产品名称的分区内计算，排名值越低表示销售金额越高。查询的结果将包含这些列的数据

product_namesale_datesale_amountsales_rank
Product A2023-01-0110001
Product A2023-01-0215001
Product A2023-01-0312001
Product B2023-01-018002
Product B2023-01-029002
Product B2023-01-0311002
  • 示例2：累计求和

我们将计算每个产品的销售累计总额：

SELECT
	product_name,
	sale_date,
	sale_amount,
	SUM( sale_amount ) OVER ( PARTITION BY product_name ORDER BY sale_date ) AS cumulative_sales 
FROM
	sales;

​ 该查询将从 “sales” 表中选择产品名称、销售日期、销售金额以及根据每个产品的销售日期排序计算的累计销售额。累计销售额是指在给定产品的每一行中，该产品自第一次销售起到当前行的销售金额总和。查询的结果将包含这些列的数据，以及每行的累计销售额

product_namesale_datesale_amountcumulative_sales
Product A2023-01-0110001000
Product A2023-01-0215002500
Product A2023-01-0312003700
Product B2023-01-01800800
Product B2023-01-029001700
Product B2023-01-0311002800
  • 示例3：移动平均

我们将计算每个产品的销售额的3天移动平均：

SELECT
	product_name,
	sale_date,
	sale_amount,
	AVG( sale_amount ) OVER ( PARTITION BY product_name ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg 
FROM
	sales;

​ 该查询将从 “sales” 表中选择产品名称、销售日期、销售金额以及在每个产品的销售日期排序范围内计算的移动平均销售额。移动平均是指在给定产品的每一行中，以当前行和前两行的销售金额计算的平均值。查询的结果将包含这些列的数据，以及每行的移动平均销售额。

product_namesale_datesale_amountmoving_avg
Product A2023-01-0110001000
Product A2023-01-0215001250
Product A2023-01-0312001233.33
Product B2023-01-01800800
Product B2023-01-02900850
Product B2023-01-031100933.33
  • 示例4：前后行比较

我们将比较每个产品的销售额与前一天的销售额：

SELECT
	product_name,
	sale_date,
	sale_amount,
	sale_amount - LAG( sale_amount ) OVER ( PARTITION BY product_name ORDER BY sale_date ) AS sales_change 
FROM
	sales;

​ 该查询将从 “sales” 表中选择产品名称、销售日期、销售金额以及当前销售金额与前一行销售金额之间的差异（变化）。查询的结果将包含这些列的数据，以及每行的销售额变化情况。这可以帮助分析每个产品的销售趋势和波动情况。

product_namesale_datesale_amountsales_change
Product A2023-01-011000NULL
Product A2023-01-021500500
Product A2023-01-031200-300
Product B2023-01-01800NULL
Product B2023-01-02900100
Product B2023-01-031100200
  • 示例5：分组内聚合

在这个示例中，我们将计算每个月的销售总额和每个月的产品销售排名：

SELECT
	product_name,
	sale_date,
	sale_amount,
	SUM( sale_amount ) OVER (
	PARTITION BY DATE_FORMAT( sale_date, 'YYYY-MM' )) AS monthly_total,
	RANK() OVER ( PARTITION BY DATE_FORMAT( sale_date, 'YYYY-MM' ) ORDER BY sale_amount DESC ) AS monthly_rank 
FROM
	sales;

​ 该查询将从 “sales” 表中选择产品名称、销售日期、销售金额以及在每个月内计算的销售总额和销售金额排名。查询的结果将包含这些列的数据，以及每行的每月销售总额和排名。这可以帮助分析每个月的销售情况以及产品在每个月的销售排名。

product_namesale_datesale_amountmonthly_totalmonthly_rank
Product A2023-01-01100037001
Product A2023-02-01150030002
Product A2023-03-01120027002
Product B2023-01-0580037001
Product B2023-02-0590030002
Product B2023-03-05110027002

以上是窗口函数在不同场景下的示例和对应的查询结果样本数据，这些示例展示了窗口函数在处理复杂的查询和分析需求时的灵活应用。

2. 常用的窗口函数功能介绍

​ ROW_NUMBER()、RANK()和DENSE_RANK()都属于窗口函数中的排名类功能。它们在查询结果集中为每一行分配排名值，以反映行在排序顺序中的位置。尽管它们都涉及排名，但它们在处理重复值和排名分配方式上有所不同，因此适用于不同的排名需求。

这些排名函数在数据库查询中通常用于以下类型的分析：

  1. 竞争排名：用于确定一组数据中的前几名，或者查找具有最高/最低值的行。比如，找到销售额最高的产品，或者找到工资最高的员工。

  2. 分组排名：在每个分组内为数据行分配排名，通常是在分组内部根据特定的排序顺序。这在处理部门、地区等分组时非常有用。

  3. 排名序号：为数据行分配排名序号，以便标识每一行在结果集中的位置，无论是否存在相同的值。

ROW_NUMBER()、RANK()和DENSE_RANK()都是窗口函数，在数据库查询中用于为结果集中的每一行分配排名值。尽管它们的目标相似，但它们在处理重复值和排名分配方式上有一些关键的区别。

  • ROW_NUMBER( )

ROW_NUMBER()函数为每一行分配一个唯一的整数行号，无论是否存在重复的值。它根据指定的排序顺序为每行分配一个递增的整数值。当遇到重复值时，下一个行号会递增，不会跳过任何行。

1,2,3,4,5,6,7,8...

区别和特点：

  1. 每一行都有唯一的行号。

  2. 不会跳过重复的值，每行都有一个唯一的行号。

  3. 常用于需要唯一标识每一行的情况。

  • RANK( )

RANK()函数为每一行分配排名值，根据指定的排序顺序。它在遇到相同的值时，会为它们分配相同的排名，并且下一个排名会跳过相同数量的排名。因此，如果有重复的值，会跳过对应数量的排名，下一个值的排名会依次增加。

1,2,3,3,3,6,6,8...

区别和特点：

  1. 相同的值会共享相同的排名，下一个排名会跳过相同数量的排名。

  2. 会产生间隔的排名，不会连续递增。

  3. 常用于需要排名和处理重复值的情况。

  • DENSE_RANK()

DENSE_RANK()函数也为每一行分配排名值，根据指定的排序顺序。与RANK()不同，DENSE_RANK()不会跳过相同的排名，即使存在重复的值，它们也会共享相同的排名。因此，排名值会连续递增。

1,2,2,3,3,4,5,6...

区别和特点：

  1. 相同的值会共享相同的排名，不会跳过任何排名。
  2. 排名值会连续递增，不会产生间隔。
  3. 常用于需要排名而不需要跳过排名的情况。

综上所述，ROW_NUMBER()、RANK()和DENSE_RANK()是用于为结果集中的每一行分配排名值的窗口函数。它们在处理重复值和排名分配方式上有不同的特点，因此在不同的情况下，您可以根据具体需求选择适合的窗口函数来实现排名操作。

  • 假设我们有一个名为 “students” 的表，包含以下字段：student_id、student_name 和 score。我们可以使用以下示例数据来进行演示：
student_idstudent_namescore
1Alice85
2Bob92
3Carol85
4David78
5Emily92
6Frank78

下面是使用不同的窗口函数对上述数据进行排序的示例查询：

SELECT
    student_id,
    student_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM
    students;

在上述查询中，我们使用了 ROW_NUMBER()、RANK() 和 DENSE_RANK() 三种窗口函数，按照分数 score 的降序对学生进行排序，并为每个学生分别计算了对应的行号、排名和密集排名。

运行这个查询，你会得到类似下面的结果：

student_idstudent_namescorerow_numrank_numdense_rank_num
2Bob92111
5Emily92211
1Alice85332
3Carol85432
4David78553
6Frank78653

在上述结果中，你可以看到：

  • ROW_NUMBER() 每次遇到一个新行，都会递增行号，不考虑相同分数的情况。
  • RANK() 在遇到相同分数时会跳过排名，下一个分数会跳过相同分数的数量。例如，第1和第2名有两个人（92分），所以下一个分数是第3名。
  • DENSE_RANK() 在遇到相同分数时不会跳过排名，下一个分数会紧跟在相同分数的后面。

这个示例可以帮助你理解 ROW_NUMBER()、RANK() 和 DENSE_RANK() 这三种窗口函数在处理相同值时的不同行为。

2-1. ROW_NUMBER()-为每行分配唯一编号

ROW_NUMBER()是窗口函数中常用的一个函数，它为结果集中的每一行分配一个唯一的行号。这个行号基于指定的排序顺序，并且不会跳过重复的值。以下是三个使用ROW_NUMBER()函数的示例：

假设我们有一个名为employees的表，包含了员工信息，包括员工ID、姓名和工资。

  • 简单的行号分配
SELECT
	employee_id,
	first_name,
	last_name,
	ROW_NUMBER() OVER ( ORDER BY employee_id ) AS row_num 
FROM
	employees;

​ 该查询将从 “employees” 表中选择员工的ID、名字、姓氏，并为每个员工根据员工ID排序分配一个唯一的行号。查询的结果将包含这些列的数据，以及每行的行号。这可以用于标识每个员工在查询结果中的位置。

employee_idfirst_namelast_namerow_num
101JohnDoe1
102JaneSmith2
103AliceJohnson3
…………
  • 基于工资的行号分配
SELECT
	employee_id,
	first_name,
	last_name,
	salary,
	ROW_NUMBER() OVER ( ORDER BY salary DESC ) AS salary_rank 
FROM
	employees;

​ 该查询将从 “employees” 表中选择员工的ID、名字、姓氏、薪水，并为每个员工根据薪水的高低分配一个薪水排名。薪水排名表示员工薪水在所有员工中的相对位置，排名越低表示薪水越高。查询的结果将包含这些列的数据，以及每行的薪水排名。这可以用于分析员工的薪酬情况和薪水排名。

employee_idfirst_namelast_namesalarysalary_rank
105MichaelJohnson900001
110SarahWilliams850002
103AliceJohnson800003
……………
  • 分组内的行号分配
SELECT
	department_id,
	employee_id,
	first_name,
	last_name,
	ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS dept_salary_rank 
FROM
	employees;

​ 该查询将从 “employees” 表中选择员工所属的部门ID、员工的ID、名字、姓氏，并为每个部门内的员工根据薪水的高低分配一个薪水排名。部门内薪水排名表示员工在所属部门中薪水的相对位置，排名越低表示薪水越高。查询的结果将包含这些列的数据，以及每行的部门内薪水排名。这可以用于分析每个部门内的员工薪水情况和排名

department_idemployee_idfirst_namelast_namedept_salary_rank
101105MichaelJohnson1
101106EmilyWilliams2
101107WilliamSmith3
……………

以上示例展示了ROW_NUMBER()函数的不同用法，用于为每行分配行号并创建不同的排名。这在处理排名、排序和分组内的序号等场景中非常有用。

2-2. RANK() -返回排序后的排名序号

​ RANK()是窗口函数中常用的一个函数，它用于为结果集中的每一行分配一个排名值，基于指定的排序顺序。如果存在相同的值，它们将共享相同的排名，并且下一个排名将会跳过对应数量的排名。以下是三个使用RANK()函数的示例：

假设我们有一个名为sales的表，包含了销售数据，包括销售日期、产品名称和销售额。

  • 基于销售额的排名
SELECT
	product_name,
	sale_date,
	sale_amount,
	RANK() OVER ( ORDER BY sale_amount DESC ) AS sales_rank 
FROM
	sales;

​ 该查询将从 “sales” 表中选择产品名称、销售日期、销售金额，并为每个销售记录根据销售金额的高低分配一个全局销售排名。销售排名表示销售记录在所有记录中的相对位置，排名越低表示销售金额越高。查询的结果将包含这些列的数据，以及每行的销售排名。这可以用于分析整体销售情况和排名。

product_namesale_datesale_amountsales_rank
Product A2023-01-0115001
Product B2023-01-0213002
Product C2023-01-0113002
………4
  • 基于销售日期的排名
SELECT
	product_name,
	sale_date,
	sale_amount,
	RANK() OVER ( PARTITION BY product_name ORDER BY sale_date ) AS sales_date_rank 
FROM
	sales;

​ 该查询将从 “sales” 表中选择产品名称、销售日期、销售金额，并为每个产品的销售记录根据销售日期的先后顺序分配一个在产品内部的销售日期排名。销售日期排名表示每个产品内销售记录在销售日期方面的相对位置，排名越低表示销售日期越早。查询的结果将包含这些列的数据，以及每行的销售日期排名。这可以用于分析每个产品内销售日期的排序情况。

product_namesale_datesale_amountsales_date_rank
Product A2023-01-0110001
Product A2023-01-0212002
Product A2023-01-0315003
………4
  • 基于部门的工资排名
SELECT
	department_id,
	employee_id,
	first_name,
	last_name,
	salary,
	RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS dept_salary_rank 
FROM
	employees;

​ 该查询将从 “employees” 表中选择员工所属的部门ID、员工的ID、名字、姓氏、薪水，并为每个部门内的员工根据薪水的高低分配一个在部门内的薪水排名。薪水排名表示员工在所属部门中薪水的相对位置，排名越低表示薪水越高。查询的结果将包含这些列的数据，以及每行的部门内薪水排名。这可以用于分析每个部门内员工薪水情况和排名。

department_idemployee_idfirst_namelast_namesalarydept_salary_rank
101105MichaelJohnson900001
101106EmilyWilliams850002
101107WilliamSmith800003
……………4

以上示例展示了RANK()函数的不同用法，用于为每行分配排名值，创建不同排序下的排名。这在排名和分组内的排名等场景中非常有用。

2-3. DENSE_RANK()-重复值共享排名

DENSE_RANK()是窗口函数中常用的一个函数，它类似于RANK()函数，用于为结果集中的每一行分配一个排名值，基于指定的排序顺序。与RANK()不同的是，DENSE_RANK()不会跳过相同的值，即使存在重复值，它们也会共享相同的排名。以下是三个使用DENSE_RANK()函数的示例：

假设我们有一个名为sales的表，包含了销售数据，包括销售日期、产品名称和销售额。

  • 基于销售额的稠密排名
SELECT
	product_name,
	sale_date,
	sale_amount,
	DENSE_RANK() OVER ( ORDER BY sale_amount DESC ) AS dense_sales_rank 
FROM
	sales;

​ 该查询将从 “sales” 表中选择产品名称、销售日期、销售金额，并为每个销售记录根据销售金额的高低分配一个全局的密集销售排名。密集销售排名表示销售记录在所有记录中的相对位置，排名越低表示销售金额越高。与 RANK() 不同的是，DENSE_RANK() 在遇到相同排名的情况时不会跳过排名。查询的结果将包含这些列的数据，以及每行的全局密集销售排名。这可以用于分析整体销售情况和密集销售排名。

product_namesale_datesale_amountdense_sales_rank
Product A2023-01-0115001
Product B2023-01-0213002
Product C2023-01-0113002
………3
  • 基于销售日期的稠密排名
SELECT
	product_name,
	sale_date,
	sale_amount,
	DENSE_RANK() OVER ( PARTITION BY product_name ORDER BY sale_date ) AS dense_sales_date_rank 
FROM
	sales;

​ 该查询将从 “sales” 表中选择产品名称、销售日期、销售金额，并为每个产品的销售记录根据销售日期的先后顺序分配一个在产品内部的密集销售日期排名。密集销售日期排名表示每个产品内销售记录在销售日期方面的相对位置，排名越低表示销售日期越早。与 RANK() 不同的是，DENSE_RANK() 在遇到相同排名的情况时不会跳过排名。查询的结果将包含这些列的数据，以及每行的密集销售日期排名。这可以用于分析每个产品内销售日期的排序情况。

product_namesale_datesale_amountdense_sales_date_rank
Product A2023-01-0110001
Product A2023-01-0212002
Product A2023-01-0315003
………4
  • 基于部门的工资稠密排名
SELECT
	department_id,
	employee_id,
	first_name,
	last_name,
	salary,
	DENSE_RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS dept_dense_salary_rank 
FROM
	employees;

​ 该查询将从 “employees” 表中选择员工所属的部门ID、员工的ID、名字、姓氏、薪水，并为每个部门内的员工根据薪水的高低分配一个在部门内的密集薪水排名。密集薪水排名表示员工在所属部门中薪水的相对位置，排名越低表示薪水越高。与 RANK() 不同的是，DENSE_RANK() 在遇到相同排名的情况时不会跳过排名。查询的结果将包含这些列的数据，以及每行的部门内密集薪水排名。这可以用于分析每个部门内员工薪水情况和排名。

department_idemployee_idfirst_namelast_namesalarydept_dense_salary_rank
101105MichaelJohnson900001
101106EmilyWilliams850002
101107WilliamSmith800003
……………4

以上示例展示了DENSE_RANK()函数的不同用法，用于为每行分配稠密排名值，创建不同排序下的排名。这在排名和分组内的排名等场景中非常有用。

2-4. NTILE(X)-分组函数

NTILE(X) 是用于将结果集划分为指定数量的等分区间，并为每个行分配一个区间编号。这对于分析数据分布、分位数等情况非常有用。X 代表你希望将结果集分成的区间数量。

语法：

NTILE(X) OVER (ORDER BY column_expression)
  • X：表示要分成的区间数量，通常为一个正整数。
  • ORDER BY column_expression：用于指定如何对结果集进行排序，以便将行分配到区间时基于某个列进行排序。

工作原理：

  1. 结果集中的行按照 ORDER BY 子句指定的列进行排序。
  2. 结果集被等分成 X 个区间。
  3. 每个区间内的行数尽量平均分布，但可能有些区间的行数会比其他区间多一个。
  4. 对每个行分配一个区间编号，从 1 开始，依次递增。

假设我们有一个名为 “students” 的表，包含以下字段：student_id 和 score。我们可以使用以下示例数据来进行演示：

student_idscore
185
292
385
478
592
678

下面是两个使用 NTILE(X) 函数的示例查询：

示例1：将分数等分为两个区间，分配区间编号。

SELECT
    student_id,
    score,
    NTILE(2) OVER (ORDER BY score) AS score_ntile
FROM
    students;

运行这个查询，你会得到类似下面的结果：

student_idscorescore_ntile
4781
6781
1851
3852
2922
5922

在上述结果中，我们将分数等分为两个区间，第一个区间包含分数 78 和 85，第二个区间包含分数 92。NTILE(2) 函数根据分数将学生分配到不同的区间，并为每个学生分配了一个区间编号。

示例2：将分数等分为三个区间，分配区间编号。

SELECT
    student_id,
    score,
    NTILE(3) OVER (ORDER BY score) AS score_ntile
FROM
    students;

运行这个查询，你会得到类似下面的结果：

student_idscorescore_ntile
4781
6781
1852
3852
2923
5923

在上述结果中，我们将分数等分为三个区间，每个区间的分数范围相差不大。NTILE(3) 函数将学生根据分数分配到不同的区间，并为每个学生分配了一个区间编号。

这两个示例演示了 NTILE(X) 函数将结果集分割成指定数量的等分区间，并为每个行分配一个区间编号。在第一个示例中，我们将分数分成两个区间，而在第二个示例中，我们将分数分成三个区间。

2-5. ROWS、RANGE窗口边界

​ 定义窗口的边界以及如何对窗口内的行进行排序和分组。窗口规范包括两个关键部分：ROWS 和 RANGE。

1. ROWS：
在窗口函数中，ROWS 关键字用于定义窗口的行边界，即决定哪些行被包括在窗口内进行计算。ROWS 支持多种方式来定义窗口的行边界：

  • UNBOUNDED PRECEDING：表示窗口的起始行是结果集的第一行。
  • n PRECEDING：表示窗口的起始行是当前行往前数 n 行的位置。
  • CURRENT ROW：表示窗口的起始行是当前行。
  • n FOLLOWING：表示窗口的结束行是当前行往后数 n 行的位置。
  • UNBOUNDED FOLLOWING：表示窗口的结束行是结果集的最后一行。

2. RANGE：
在窗口函数中，RANGE 关键字用于定义窗口的值范围，即决定哪些行的值被包括在窗口内进行计算。RANGE 通常用于处理数字数据类型，例如时间序列数据，以确保相同值在窗口中得到合适的处理。

  • n PRECEDING 和 n FOLLOWING：类似于 ROWS，但根据值的范围进行定义。

ROWS 和 RANGE 是用于定义窗口函数计算范围的关键字。ROWS 主要根据行的位置来定义窗口，而 RANGE 则根据值的范围来定义窗口，通常用于处理数值类型的数据。这两种窗口规范可以根据实际需求选择，以便在分析和聚合数据时获得所需的结果。

假设我们有一个名为 “sales” 的表，包含以下字段：sale_date 和 sale_amount。我们可以使用以下示例数据来进行演示：

sale_datesale_amount
2023-08-01100
2023-08-02150
2023-08-03120
2023-08-04200
2023-08-05180
2023-08-06250

下面是两个使用窗口函数中 ROWS 和 RANGE 的示例查询：

示例1：使用ROWS计算累计销售金额。

SELECT
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM
    sales;

在上述查询中，我们使用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 来计算每天的累计销售金额。这会将窗口从每天的第一天开始一直累计到当前行的销售金额。

结果：

sale_datesale_amountcumulative_sum
2023-08-01100100
2023-08-02150250
2023-08-03120370
2023-08-04200570
2023-08-05180750
2023-08-062501000

示例2：使用RANGE计算累计销售金额，限制范围在100元之内。

SELECT
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (ORDER BY sale_date RANGE BETWEEN 100 PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM
    sales;

在上述查询中，我们使用 RANGE BETWEEN 100 PRECEDING AND CURRENT ROW 来计算每天的累计销售金额，但是限制范围在当前行的前100元之内，确保在100元范围内计算累计金额。

结果：

sale_datesale_amountcumulative_sum
2023-08-01100100
2023-08-02150250
2023-08-03120470
2023-08-04200590
2023-08-05180770
2023-08-06250950

在这两个示例中，我们分别使用了 ROWS 和 RANGE 来计算每天的累计销售金额。通过指定不同的窗口规范，可以对数据进行不同的聚合和分析。

2-6. LEAD() 和 LAG()-获取前后行的值

LEAD() 和 LAG() 是窗口函数，用于在查询结果中访问当前行之后或之前的行的值。它们通常用于分析时序数据、计算行与前后行之间的差异等。这两个函数的功能如下：

1. LEAD()：
LEAD() 函数用于获取当前行后面的行的值。可以指定要获取的后面行的数量和可选的默认值。它的一般语法为：

LEAD(column_expression, offset, default_value) OVER (ORDER BY order_expression)
  • column_expression：要获取值的列或表达式。
  • offset：表示要获取后面的第几行。默认为 1。
  • default_value：可选参数，如果没有更多后面的行，则返回的默认值。

2. LAG()：
LAG() 函数用于获取当前行前面的行的值。你可以指定要获取的前面行的数量和可选的默认值。一般语法为：

LAG(column_expression, offset, default_value) OVER (ORDER BY order_expression)
  • column_expression：要获取值的列或表达式。
  • offset：表示要获取前面的第几行。默认为 1。
  • default_value：可选参数，如果没有更多前面的行，则返回的默认值。

下面是两个使用 LEAD() 和 LAG() 的示例查询：

示例1：使用LEAD()查找每个学生的下一个成绩。

假设我们有一个名为 “student_scores” 的表，包含以下字段：student_id、score 和 exam_date。我们可以使用以下示例数据来进行演示：

student_idscoreexam_date
1852023-08-01
1922023-08-02
2782023-08-01
2882023-08-02
2952023-08-03
SELECT
    student_id,
    exam_date,
    score,
    LEAD(score) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_score
FROM
    student_scores;

在上述查询中，我们使用 LEAD(score) 来获取每个学生的下一个考试的分数。

结果：

student_idexam_datescorenext_score
12023-08-018592
12023-08-0292
22023-08-017888
22023-08-028895
22023-08-0395

示例2：使用LAG()计算每个学生的成绩变化。

SELECT
    student_id,
    exam_date,
    score,
    LAG(score) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_score
FROM
    student_scores;

在上述查询中，我们使用 LAG(score) 来获取每个学生的上一个考试的分数。

结果：

student_idexam_datescoreprevious_score
12023-08-0185
12023-08-029285
22023-08-0178
22023-08-028878
22023-08-039588

通过 LEAD() 和 LAG() 函数，你可以方便地获取当前行前后的值，进行数据分析和计算。

2-7. FIRST_VALUE和LAST_VALUE-获取第一|最后行值

FIRST_VALUE() 和 LAST_VALUE() 是窗口函数，用于获取窗口内的第一个和最后一个行的特定列的值。这两个函数通常用于分析时序数据，以及在分析窗口内的行时获取窗口的开头和结尾的值。下面我将详细介绍这两个函数的功能，并提供示例查询和样例数据。

1. FIRST_VALUE()：
FIRST_VALUE() 函数用于获取窗口内的第一个行的特定列的值。它的一般语法为：

FIRST_VALUE(column_expression) OVER (ORDER BY order_expression)
  • column_expression：要获取值的列或表达式。
  • ORDER BY order_expression：用于指定窗口内行的排序顺序。

2. LAST_VALUE()：
LAST_VALUE() 函数用于获取窗口内的最后一个行的特定列的值。它的一般语法为：

LAST_VALUE(column_expression) OVER (ORDER BY order_expression)
  • column_expression：要获取值的列或表达式。
  • ORDER BY order_expression：用于指定窗口内行的排序顺序。

下面是两个使用 FIRST_VALUE() 和 LAST_VALUE() 的示例查询：

示例1：使用FIRST_VALUE()获取每个部门的第一个雇员的姓名。

假设我们有一个名为 “employees” 的表，包含以下字段：employee_id、first_name、last_name 和 department_id。我们可以使用以下示例数据来进行演示：

employee_idfirst_namelast_namedepartment_id
1AliceJohnson1
2BobSmith1
3CarolWilliams2
4DavidBrown2
5EmilyDavis1
SELECT
    department_id,
    first_name,
    last_name,
    FIRST_VALUE(first_name) OVER (PARTITION BY department_id ORDER BY employee_id) AS first_employee
FROM
    employees;

在上述查询中，我们使用 FIRST_VALUE(first_name) 来获取每个部门的第一个雇员的名字。

结果：

department_idfirst_namelast_namefirst_employee
1AliceJohnsonAlice
1BobSmithAlice
1EmilyDavisAlice
2CarolWilliamsCarol
2DavidBrownCarol

示例2：使用LAST_VALUE()获取每个部门的最后一个雇员的姓名。

SELECT
    department_id,
    first_name,
    last_name,
    LAST_VALUE(first_name) OVER (PARTITION BY department_id ORDER BY employee_id) AS last_employee
FROM
    employees;

在上述查询中，我们使用 LAST_VALUE(first_name) 来获取每个部门的最后一个雇员的名字。

结果：

department_idfirst_namelast_namelast_employee
1AliceJohnsonEmily
1BobSmithEmily
1EmilyDavisEmily
2CarolWilliamsDavid
2DavidBrownDavid

通过 FIRST_VALUE() 和 LAST_VALUE() 函数，你可以轻松地获取窗口内的第一个和最后一个行的特定列的值，用于分析和计算。

2-8.NTH_VALUE(x,n)-指定顺序第n个值

NTH_VALUE(x, n) 是窗口函数中的一个功能，用于获取窗口内指定位置的行的特定列的值。它允许你根据指定位置（第 n 行）来获取值，通常用于获取排名靠前或靠后的特定行的值。下面我将详细介绍这个函数的功能，并提供不同角度的示例来展示它的使用。

语法：

NTH_VALUE(column_expression, n) OVER (ORDER BY order_expression)
  • column_expression：要获取值的列或表达式。
  • n：要获取的行的位置，从 1 开始计数。
  • ORDER BY order_expression：用于指定窗口内行的排序顺序。

以下是三个不同角度的示例来充分介绍 NTH_VALUE(x, n) 函数的使用：

示例1：获取窗口内排名第一的学生的分数。

假设我们有一个名为 “student_scores” 的表，包含以下字段：student_id、score 和 exam_date。我们可以使用以下示例数据来进行演示：

student_idscoreexam_date
1852023-08-01
1922023-08-02
2782023-08-01
2882023-08-02
2952023-08-03
SELECT
    exam_date,
    NTH_VALUE(score, 1) OVER (ORDER BY score DESC) AS top_score
FROM
    student_scores;

在上述查询中，我们使用 NTH_VALUE(score, 1) 来获取窗口内排名第一的学生的分数。

结果：

exam_datetop_score
2023-08-0195
2023-08-0295
2023-08-0195
2023-08-0295
2023-08-0395

示例2：获取窗口内排名第三的学生的分数。

SELECT
    exam_date,
    NTH_VALUE(score, 3) OVER (ORDER BY score DESC) AS third_top_score
FROM
    student_scores;

在上述查询中，我们使用 NTH_VALUE(score, 3) 来获取窗口内排名第三的学生的分数。

结果：

exam_datethird_top_score
2023-08-0188
2023-08-0288
2023-08-0188
2023-08-0288
2023-08-0388

示例3：获取窗口内排名倒数第二的学生的分数。

SELECT
    exam_date,
    NTH_VALUE(score, 2) OVER (ORDER BY score) AS second_last_score
FROM
    student_scores;

在上述查询中，我们使用 NTH_VALUE(score, 2) 来获取窗口内排名倒数第二的学生的分数。

结果：

exam_datesecond_last_score
2023-08-0185
2023-08-0285
2023-08-0188
2023-08-0288
2023-08-0388

这三个示例展示了 NTH_VALUE(x, n) 函数在不同情况下获取窗口内指定位置的行的值的功能。你可以根据需要使用这个函数来获得特定位置的数据，以满足分析和计算的需求。

3. 高级窗口函数技巧

当配合样例数据来展示高级窗口函数技巧时，更容易理解其功能和效果。以下是每个技巧的SQL示例，结合样例数据和结果：

3-1. 窗口函数嵌套

示例数据：

sale_datesale_amount
2023-08-01100
2023-08-02150
2023-08-03200
……

示例SQL：

SELECT
    sale_date,
    AVG(sale_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg,
    (AVG(sale_amount) - LAG(AVG(sale_amount), 1) OVER (ORDER BY sale_date)) / LAG(AVG(sale_amount), 1) OVER (ORDER BY sale_date) AS avg_change_rate
FROM
    daily_sales;

示例结果：

sale_datemoving_avgavg_change_rate
2023-08-01100NULL
2023-08-021250.25
2023-08-031500.20
………

3-2. 多个分区和排序键

示例数据：

product_idregionsale_amount
1A1000
2A800
1B1200
………

示例SQL：

SELECT
    product_id,
    region,
    sale_amount,
    RANK() OVER (PARTITION BY product_id ORDER BY sale_amount DESC) AS product_rank,
    RANK() OVER (PARTITION BY region ORDER BY sale_amount DESC) AS region_rank
FROM
    sales;

示例结果：

product_idregionsale_amountproduct_rankregion_rank
1A100012
2A80021
1B120021
……………

3-3. 分位数和百分比分析

示例数据：

sale_datesale_amount
2023-08-01100
2023-08-02150
2023-08-03200
……

示例SQL：

SELECT
    sale_date,
    sale_amount,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sale_amount) OVER () AS percentile_75,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY sale_amount) OVER () AS percentile_90
FROM
    daily_sales;

示例结果：

sale_datesale_amountpercentile_75percentile_90
2023-08-01100175190
2023-08-02150175190
2023-08-03200175190
…………

3-4. 带有复杂条件的窗口函数

示例数据：

product_idsale_datesale_amount
12023-08-011000
12023-08-021200
12023-08-031300
………

示例SQL：

SELECT
    product_id,
    sale_date,
    sale_amount,
    AVG(sale_amount) OVER (PARTITION BY product_id) AS avg_sale_amount,
    CASE
        WHEN sale_amount > AVG(sale_amount) OVER (PARTITION BY product_id) THEN sale_amount / AVG(sale_amount) OVER (PARTITION BY product_id)
        ELSE NULL
    END AS sale_amount_ratio_above_avg
FROM
    sales;

示例结果：

product_idsale_datesale_amountavg_sale_amountsale_amount_ratio_above_avg
12023-08-0110001166.670.857
12023-08-0212001166.671.029
12023-08-0313001166.671.114
……………

3-5. 动态窗口范围

示例数据：

sale_datesale_amount
2023-08-01100
2023-08-02150
2023-08-03200
……

示例SQL：

SELECT
    sale_date,
    sale_amount,
    AVG(sale_amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS dynamic_moving_avg
FROM
    daily_sales;

示例结果：

sale_datesale_amountdynamic_moving_avg
2023-08-01100125
2023-08-02150150
2023-08-03200175
………

通过这些具体的SQL示例、样例数据和结果，可以更生动地理解高级窗口函数技巧的实际应用和效果，从而更好地利用它们来分析数据并获得洞察。

4. 性能优化与注意事项

窗口函数在SQL查询中提供了强大的分析和聚合能力，但在使用窗口函数时，也需要注意性能方面的优化和注意事项，以确保查询的效率和性能。以下是一些窗口函数的性能优化建议和注意事项：

性能优化建议：

  1. 适当选择窗口函数： 窗口函数提供了多种功能，包括排序、分组、聚合等。选择适当的窗口函数来实现你的需求，避免不必要的计算。

  2. 合理使用分区： 使用 PARTITION BY 子句进行分区，以将数据划分为更小的分组。然而，分区也可能导致性能问题，因此要根据实际情况权衡。

  3. 选择适当的排序顺序： 在窗口函数中，使用 ORDER BY 子句来指定排序顺序。选择合适的排序列和顺序，以减少排序操作的开销。

  4. 限制结果集大小： 在使用窗口函数时，结果集的大小可能会增加。使用 ROWS 或 RANGE 子句来限制窗口大小，以避免不必要的计算。

  5. 考虑查询计划： 数据库系统可能会使用不同的查询计划来执行窗口函数，取决于数据量、索引和其他因素。了解查询计划并进行性能分析是优化的一部分。

注意事项：

  1. 数据量和内存消耗： 窗口函数在处理大数据集时可能会占用大量内存。在设计查询时要考虑数据量，以避免内存问题。

  2. 排序操作开销： 窗口函数通常涉及排序操作，这可能是性能瓶颈之一。确保选择适当的索引来支持排序，以提高性能。

  3. 分区开销： 使用 PARTITION BY 进行分区会增加计算和内存开销。小心过多的分区，以免影响性能。

  4. 数据库版本： 不同的数据库系统对窗口函数的支持和优化方式可能有所不同。了解所使用的数据库系统的特性和最佳实践。

  5. 索引优化： 窗口函数使用的排序可能会受到索引的影响。确保表上的索引能够支持窗口函数所需的排序操作。

  6. 频繁使用窗口函数： 虽然窗口函数功能强大，但频繁使用可能会导致查询变得复杂，降低可读性。在不同的查询中，合理选择是否使用窗口函数。

  7. 监控性能： 在使用窗口函数时，要监控查询性能，并进行性能测试。如果发现性能问题，可以尝试调整查询、索引和分区策略。

总之，窗口函数是强大的分析工具，但在使用时需要根据查询需求和数据量权衡性能优化和注意事项。合理选择窗口函数、设计适当的查询和索引，并进行性能测试和监控，以确保查询在性能和效率方面都能达到预期。

5.窗口函数不同行业示例

5-1. 零售业 - 销售分析

  • 业务场景： 在零售业中，分析每个产品的销售情况、排名以及销售变化。
  • 示例数据： 假设有一个名为 “sales” 的表，包含以下字段：product_id、sale_date、sale_amount。
CREATE TABLE sales (
    product_id INT,
    sale_date DATE,
    sale_amount DECIMAL(10, 2)
);

INSERT INTO sales VALUES
    (1, '2023-08-01', 100),
    (1, '2023-08-02', 150),
    (2, '2023-08-01', 80),
    (2, '2023-08-02', 120),
    (2, '2023-08-03', 90);
  • 示例查询： 计算每个产品的销售排名、累计销售额和与上一周期的销售变化。
SELECT
    product_id,
    sale_date,
    sale_amount,
    RANK() OVER (PARTITION BY product_id ORDER BY sale_amount DESC) AS sales_rank,
    SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS cumulative_sales,
    sale_amount - LAG(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS sales_change
FROM
    sales;

5-2. 金融业 - 客户交易分析

  • 业务场景： 在金融业中，分析客户的交易行为，了解其投资组合和交易历史。
  • 示例数据： 假设有一个名为 “transactions” 的表，包含以下字段：customer_id、transaction_date、amount.
CREATE TABLE transactions (
    customer_id INT,
    transaction_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO transactions VALUES
    (1, '2023-08-01', 1000),
    (1, '2023-08-05', -500),
    (2, '2023-08-02', 2000),
    (2, '2023-08-03', -800),
    (2, '2023-08-07', 1500);
  • 示例查询： 计算每个客户的累计交易额、分析交易趋势以及比较不同时间段的投资组合变化。
SELECT
    customer_id,
    transaction_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS cumulative_amount,
    amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS transaction_change
FROM
    transactions;

示例演示了窗口函数在不同行业中的使用情景，并通过样例数据展示了实际的查询语句。根据实际业务需求，可以针对不同行业和数据设计更复杂的查询，以获得更深入的分析和洞察。

当窗口函数应用于大数据数仓时，以下是一些具体的SQL示例，可以帮助更好地了解它们的使用方法。请注意，以下示例可能包含简化的示例数据，实际情况可能会更复杂。

除了零售业、金融业以外，在大数据常用的使用技巧中，有如下一些常用案例：

5-3. Top N 和分区排名

获取每个部门销售额排名前两的员工：

SELECT
    department,
    employee_id,
    sale_amount,
    RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rank
FROM
    sales
WHERE
    rank <= 2;

5-4. 移动平均和趋势分析

计算每日销售额的7天移动平均：

SELECT
    sale_date,
    sale_amount,
    AVG(sale_amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
    daily_sales;

5-5. 时间序列分析

计算每周产品订购量的变化趋势：

SELECT
    product_id,
    order_date,
    order_quantity,
    order_quantity - LAG(order_quantity) OVER (PARTITION BY product_id ORDER BY order_date) AS order_change
FROM
    order_history;

5-6. 累计计算和增长率

计算每月销售额的累计和及增长率：

SELECT
    sale_month,
    total_sales,
    SUM(total_sales) OVER (ORDER BY sale_month) AS cumulative_sales,
    (total_sales - LAG(total_sales) OVER (ORDER BY sale_month)) / LAG(total_sales) OVER (ORDER BY sale_month) AS growth_rate
FROM
    monthly_sales;

5-7. 比例和百分比

计算每个地区销售额在总销售额中的比例：

SELECT
    region,
    sale_amount,
    sale_amount / SUM(sale_amount) OVER () AS sales_proportion
FROM
    sales_by_region;

5-8. 窗口范围分析

计算每个员工前后3个月的销售总额：

SELECT
    employee_id,
    sale_month,
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_month RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS total_sales
FROM
    monthly_sales;

5-9. 分位数和异常值分析

计算产品销售额的第75百分位数和第90百分位数：

SELECT
    product_id,
    sale_amount,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sale_amount) OVER () AS percentile_75,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY sale_amount) OVER () AS percentile_90
FROM
    product_sales;

5-10. 前后值对比和差异分析

计算每个地区连续两年的销售额差异：

SELECT
    region,
    sale_year,
    sale_amount,
    sale_amount - LAG(sale_amount, 1) OVER (PARTITION BY region ORDER BY sale_year) AS sales_change
FROM
    yearly_sales;

以上示例提供了在大数据数仓中应用窗口函数的实际情况。具体的使用方法可能会因数据结构、查询需求和性能考虑而有所不同，但这些示例可作为起点，根据实际情况进行调整和扩展。

6. 窗口函数开发中容易犯错点总结

当容易犯错的情况结合具体示例进行说明，能够更加清晰地理解。以下是几个容易犯错的情况，每个情况都附带有相关的示例：

6-1. 未正确排序

示例数据：

product_idsale_datesale_amount
12023-08-011000
12023-08-021200
12023-08-031300
………

错误示例：

-- 错误：未指定正确的排序键
SELECT
    product_id,
    sale_date,
    SUM(sale_amount) OVER (PARTITION BY product_id) AS cumulative_sales
FROM
    sales;

正确示例：

-- 正确：根据销售日期排序
SELECT
    product_id,
    sale_date,
    SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS cumulative_sales
FROM
    sales;

6-2. 分区错误

示例数据：

department_idemployee_idsalary
110150000
110260000
220155000
………

错误示例：

-- 错误：未正确分区
SELECT
    department_id,
    employee_id,
    salary,
    AVG(salary) OVER (ORDER BY salary DESC) AS avg_salary
FROM
    employees;

正确示例：

-- 正确：按部门分区计算平均工资
SELECT
    department_id,
    employee_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM
    employees;

6-3. 混淆聚合函数和窗口函数

示例数据：

department_idemployee_idsalary
110150000
110260000
220155000
………

错误示例：

-- 错误：错误地使用聚合函数
SELECT
    department_id,
    AVG(salary) AS avg_salary
FROM
    employees
GROUP BY
    department_id;

正确示例：

-- 正确：使用窗口函数计算平均工资
SELECT
    department_id,
    employee_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM
    employees;

6-4. 忽略 NULL 值

示例数据：

product_idprice
1100
2NULL
1120
……

错误示例：

-- 错误：默认情况下忽略 NULL 值
SELECT
    product_id,
    AVG(price) OVER (PARTITION BY product_id) AS avg_price
FROM
    product_prices;

正确示例：

-- 正确：使用 COALESCE 处理 NULL 值
SELECT
    product_id,
    AVG(COALESCE(price, 0)) OVER (PARTITION BY product_id) AS avg_price
FROM
    product_prices;

6-5. 错误的窗口范围

示例数据：

sale_datesale_amount
2023-08-01100
2023-08-02150
2023-08-03200
……

错误示例：

-- 错误：错误的窗口范围，计算范围不正确
SELECT
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS incorrect_range_sum
FROM
    daily_sales;

正确示例：

-- 正确：使用正确的窗口范围
SELECT
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS correct_range_sum
FROM
    daily_sales;

​ 通过这些示例，你可以更好地理解在使用窗口函数时容易犯的错误，以及如何避免这些错误。确保在编写窗口函数查询时，仔细考虑数据的分布、排序键、分区和窗口范围等因素，同时进行适当的性能优化。

7. Hive与Mysql中窗口函数使用区别

Hive和MySQL虽然都支持窗口函数，但它们在语法和关键词上存在一些区别。以下是Hive和MySQL中窗口函数语法和关键词上的主要区别：

7-1.Hive中窗口函数的语法和关键词

在Hive中，窗口函数的语法和关键词如下：

<窗口函数> OVER (
    [PARTITION BY <分区列>]
    [ORDER BY <排序列>]
    [ROWS BETWEEN <开始行> AND <结束行>]
)
  • <窗口函数>：代表要执行的窗口函数，如SUM、AVG、ROW_NUMBER等。
  • PARTITION BY子句：用于将数据分成不同的分区，窗口函数将在每个分区内执行。
  • ORDER BY子句：定义数据的排序方式，决定窗口函数的计算顺序。
  • ROWS BETWEEN子句：指定窗口的范围，可以是行数、区间等。

7-2. MySQL中窗口函数的语法和关键词

在MySQL中，窗口函数的语法和关键词如下：

<窗口函数> OVER (
    [PARTITION BY <分区列>]
    <ORDER BY <排序列>>
    [ROWS <ROWS类型>]
)
  • <窗口函数>：代表要执行的窗口函数，如SUM、AVG、ROW_NUMBER等。
  • PARTITION BY子句：用于将数据分成不同的分区，窗口函数将在每个分区内执行。
  • <ORDER BY>：定义数据的排序方式，决定窗口函数的计算顺序。
  • ROWS <ROWS类型>：指定窗口的范围，可以是UNBOUNDED、CURRENT ROW、BETWEEN等。

注意以下一些具体差异：

  1. 关键词：Hive使用ROWS BETWEEN，而MySQL使用ROWS。
  2. <ROWS类型>：在Hive中，<ROWS类型>可以是UNBOUNDED、CURRENT ROW、BETWEEN x PRECEDING AND y FOLLOWING等。在MySQL中，<ROWS类型>可以是类似UNBOUNDED、CURRENT ROW、BETWEEN x PRECEDING AND y FOLLOWING的形式，但语法有细微差异。

总之，虽然Hive和MySQL的窗口函数语法有些许不同，但基本的概念和用法是相似的。根据具体的数据库和语法要求，您可以适当调整语法来使用窗口函数。

王亭_666
关注
  • 5
    点赞
  • 踩
  • 18
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
深入VC回调函数的使用详解
01-20
回调函数说白了就是事件响应程序，Windows的每个消息可以理解为一个事件，事件的响应代码要由用户自己来定义。用户定义了事件响应的代码，但还要Windows知道这段代码的位置（要不然Windows就不知道如何去调用，这也没有用），于是用户需要将回调函数的指针告诉Windows，最典型的例子是在窗口类的结构(WNDCLASS)中给lpfnWndProc分量赋回调函数指针值。回调函数的参数格式是由回调函数的调用者（一般是Windows）来定义的，而回调函数的实现者必须遵循这种格式。Windows程序是以事件驱动模型为基础的，这就必然要用到回调函数这种机制。要透彻了解回调函数，多看看SDK Samp
窗口函数概述
hsabrina的博客
08-04 7472
窗口函数，也叫OLAP(Online Anallytical Processing,联机分析处理）,可以对数据库数据进行实时分析处理窗口函数有以下功能：1）同时具有分组（partiton by）和排序（order by） 的功能2）不减少原表的行数，所有经常用来在每组内排名注意事项：窗口函数原则上只能写在select 子句中窗口函数使用场景：1）经典top N问题，比如找出每个部门排名前N的员工进行奖励2）经典排名问题，比如每个部门按业绩来排名。...
窗口函数简介
最新发布
我是一名保安
02-22 1026
窗口函数（Window Function）是一种在关系型数据库中用于。窗口函数可以对查询结果集中的行进行分组、排序，并在每个分组内进行聚合、排名、计算等操作，而不会改变原始查询结果的行数或顺序。
窗口函数简介与总结
kiritobryant的博客
11-24 8768
窗口函数是 SQL 中一类特别的函数。和聚合函数相似，窗口函数的输入也是多行记录。不 同的是，聚合函数的作用于由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口， 这里，窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。一些聚合函 数，如 sum, max, min, avg,count 等也可以当作窗口函数使用。
含泪整理的超全窗口函数：数据开发必备
涤生大数据
11-08 1011
我们可以使用last_value(col,true/false) over()，作用是：取分组内排序后，截止到当前行，最后一个值。所以，如果使用 order by 排序的时候，想要取最后一个值，需要与 rows between unbounded preceding and unbounded following 连用。1.当第二个参数为 true 的时候，会跳过空值；2.当 over() 中不指定排序的时候，会默认使用表中数据的原排序。
常用的窗口函数
简之的通向技术之路
07-11 2万+
1、什么是窗口函数 含义：窗口函数也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据进行实时分析处理。 作用： 解决排名问题，e.g.每个班级按成绩排名 解决TOPN问题，e.g.每个班级前两名的学生 语法： select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名 分类： 排名函数：row_number()，rank()，dense_rank() 聚合函数：max()，min()，count
MySQL窗口函数（MySQL Window Functions）
Vincent的博客
05-17 8516
百分比排序，返回当前行在组内的百分比位置，返回值范围为[0, 1]，可以用 当前行排序/(行数-1) 计算得出，但与rank不同，这里排序是从0开始而不是从1（相当于rank-1），因此第1行的百分比位置是0%，相当于：（rank-1）/(rows-1)。没有参数，返回当前行在组内的位置编号，从1开始，order by子句会影响行的编号顺序，如果没有order by，那么行的编号是不确定的。另外，即使行完全相同，它们的编号也是不同的，这点和后面的 rank() 和 dense_rank() 不同。
Python使用统计函数绘制简单图形实例代码
01-20
用matplotlib绘制一些大家比较熟悉又经常混淆的统计图形，掌握这些统计图形可以对数据可视化有一个深入理解。 Windows 系统安装 Matplotlib 进入到 cmd 窗口下，执行以下命令： python -m pip install -U pip ...
MFC中的InitApplication和InitInstance函数解析
04-02
简要的介绍了在MFC中对窗口初始化其决定性作用的两个函数InitApplication和InitInstance。通过掌握这两个函数的基本原理，来进一步深入了解MFC的内部机制。
MFC程序执行过程深入剖析
01-01
1）我们知道在WIN32API程序当中，程序的入口为WinMain函数，在这个函数当中我们完成注册窗口类，创建窗口，进入消息循环，最后由操作系统根据发送到程序窗口的消息调用程序的窗口函数。而在MFC程序当中我们不在能...
深入解析MFC
07-13
本书是了解MFC内幕的向导，提供了关于那些没有文档记录的MFC类、实用函数和数据成员的独一无二并且透彻的信息，介绍了有用的编码技巧，并对MFC各个类之间的协作方式进行了重要的分析。 本书的第一部分包含了核心的...
【必看】最全开窗函数讲解和实战指南
赵英超的博客
03-20 3845
窗口函数
SQL窗口函数
热门推荐
每天学习一点点
04-18 4万+
SQL窗口函数为在线分析处理（OLAP）和商业智能（BI）提供了复杂分析和报表统计的功能，例如产品的累计销售额统计、分类排名、同比/环比分析等。这些功能通常很难通过聚合函数和分组操作来实现。 窗口函数（Window Function）可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回一个结果。聚合函数和窗口函数的区别如下图所示。 以SUM函数为例演示这两种函数的差异，以下语句中的SUM()是一个聚合函数： S.
还不会窗口函数？进来看看
weixin_46111235的博客
11-12 247
窗口函数（Window Function）是一种在数据库中执行实时分析处理的函数。它可以用于排序并生成序列号，对一组相关的数据行（称为窗口）进行计算，并生成每个窗口内的聚合值。窗口函数允许用户对数据进行更灵活的分析和处理，例如计算排名、分组和计算移动平均等。窗口函数的使用需要使用OVER子句来定义窗口的规则，并可以结合聚合函数（如sum、avg、count、max、min等）对数据进行计算。常见的窗口函数包括RANK()NTILE()LAG()和LEAD()等。
窗口函数
kkill_youokk_的博客
05-17 3348
窗口函数一、窗口函数二、hive窗口函数1.over()窗口函数的语法结构1.1 over()函数中的三个函数讲解1.order by2.partition by3.rows between 开始位置 and 结束位置2.常与over()一起使用的分析函数2.1 聚合类2.2 排名类2.3 其他 一、窗口函数 窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。 窗口函数的基本语法如下： <窗口函数> over
3.5 Window Functions
在路上
12-16 437
3.5窗口函数 窗口函数针对当前行，对一组表行进行计算。这类似于使用聚合函数进行的计算。然而，窗口函数不会像非窗口函数那样将行分组聚合为一行输出，而是这些行保留各自的属性。而且，窗口函数能够不仅仅访问查询结果的当前行。 以下示例，展示了雇员如何与本部门的平均工资进行比较： mydb=# create table empsalary(depname varchar(20),empno int,salary numeric); CREATE TABLE mydb=# insert int...
SQL 中的窗口函数
qq_43430343的博客
02-17 2929
SQL 中的窗口函数（Window Functions）是一种特殊的函数，它可以在查询结果的某个区间内执行计算，而不仅仅是对单个行进行计算。
ezplot函数介绍与用法
09-09
### 回答1： ezplot函数是MATLAB中的一种函数，用于绘制二维函数图像。它可以快速地绘制各种函数，同时还能自动调整坐标轴和图像比例，使绘图效果更佳。使用ezplot函数时，需要指定要绘制的函数表达式，并可以指定其自变量的范围。例如，可以使用如下代码绘制x^2的图像： ezplot('x^2', [-5, 5]) 其中，第一个参数为函数表达式，第二个参数指定了x的取值范围为-5到5。这样就会在MATLAB的绘图窗口中绘制出x^2的图像。 ### 回答2： ezplot函数是MATLAB中的一个绘图函数，用于绘制一元函数的图像。其用法非常简单，只需提供一个函数表达式作为参数，并设置绘图的范围。 例如，我们要绘制函数y = sin(x)在区间[-2π, 2π]上的图像，可以使用以下代码： ezplot('sin(x)', [-2*pi, 2*pi]); ezplot函数会自动计算区间内的数据点，并绘制出对应的曲线。其返回的图像对象可以进一步进行修改和个性化设置。 此外，ezplot函数还支持添加多个函数，以及对每个函数进行不同颜色和线型的设置。例如，我们要同时绘制函数y = sin(x)和y = cos(x)的图像，可以使用以下代码： figure; % 创建一个新的图窗口 ezplot('sin(x)', [-2*pi, 2*pi]); hold on; % 启用图形对象的叠加功能 ezplot('cos(x)', [-2*pi, 2*pi]); hold off; % 关闭叠加功能 使用ezplot函数可以快速绘制一元函数的图像，不需要编写循环来计算数据点，使得绘图更加便捷和高效。同时，ezplot函数还支持交互式地进行放大和缩小、旋转、平移等操作，方便用户对图像进行观察和分析。 需要注意的是，ezplot函数是针对一元函数的，如果要绘制多元函数的图像，需要使用其他的绘图函数，如plot3和meshgrid等。 总而言之，ezplot函数是MATLAB中一个简单、方便的绘图函数，适用于一元函数的图像绘制，提供了丰富的功能和个性化设置选项，可以帮助用户进行科学研究和数据分析。 ### 回答3： ezplot是MATLAB中一个用于绘制函数图像的函数。该函数可以绘制一元函数或者二元函数的图像，并且使用简单方便。 ezplot函数的用法如下： ezplot(fun)：绘制一元函数 y = fun(x) 的图像。其中fun是一个MATLAB函数句柄或者一个字符串，表示函数关系。 ezplot(fun,[xmin,xmax])：绘制一元函数在给定区间[xmin,xmax]上的图像。 ezplot(fun,[xmin,xmax,ymin,ymax])：绘制二元函数 z = fun(x,y) 的图像，其中fun是一个函数句柄或者字符串。 ezplot(fun1,fun2)：绘制两个一元函数 fun1(x) 和 fun2(x) 的图像，其中fun1和fun2是函数句柄或者字符串。 ezplot(fun1,fun2,[xmin,xmax])：绘制两个一元函数在给定区间[xmin,xmax]上的图像。 ezplot(fun1,fun2,[xmin,xmax,ymin,ymax])：绘制二维空间中两个函数 fun1(x,y) 和 fun2(x,y) 的图像。 ezplot(..., LineSpec)：指定图像的线条样式，例如颜色、线型、标记等。 通过使用ezplot函数，我们可以轻松地绘制各种函数的图像，从而更好地理解函数的性质和特征。例如，我们可以使用ezplot函数来绘制多项式函数、三角函数、指数函数等的图像。该函数还可以用于绘制函数族或者方程的解曲线等。 总之，ezplot函数是MATLAB中一个方便且功能强大的函数，能够帮助我们更好地可视化函数图像，从而更深入地研究函数的性质。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

王亭_666

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值