【SQL】Lag/Rank/Over窗口函数揭秘，数据分析之旅

2024-07-22 08:04 由 lanedm 发表于 #数据库

七月的夏日，阳光如火，但小悦的心中却是一片清凉与激情。在数据分析项目组的新岗位上，她仿佛找到了自己新的舞台，这里让她得以将深厚的后端技术实力与数据分析的精髓深度融合。每天，她都沉浸在业务需求的分析与数据驱动的决策之中，与业务、产品等多部门紧密合作，共同揭开数据背后的秘密，为企业的发展贡献自己的力量。

正当她全身心投入到新环境的学习与探索中时，微信工作群的一则消息如同夏日里的一阵清风，为她带来了新的机遇。逸尘，作为某项目微信群中的甲方代表，通过该群发布了紧急的数据分析任务，而他当时并未意识到是小悦将会被指定来负责处理这项任务。虽然两人上周在咖啡馆的偶遇只是匆匆一瞥，但那次偶遇似乎为这次合作埋下了一丝伏笔。

面对这份突如其来的挑战，小悦没有丝毫的慌乱与犹豫。她深知，无论是熟悉的伙伴还是陌生的合作者，工作中的每一次挑战都是自我提升与成长的宝贵机会。于是，她迅速调整心态，仔细阅读任务要求，以专业的态度和严谨的精神回复了逸尘。

小悦接到的任务，是根据原始数据表分析每个公司内部不同部门的销售业绩，并计算了每个部门销售业绩的排名、与下一名销售业绩的差距、与本部门最高销售业绩的差距、以及与所有部门销售平均值的差异。

--根据需求建测试表
create table temp1 (
comp_name varchar(100),
dept_name varchar(100),
sales numeric(18,4)
)
--写入测试数据：
INSERT INTO temp1  VALUES ('CompanyA', 'Dept 1', 100);
INSERT INTO temp1  VALUES ('CompanyA', 'Dept 3', 80);
INSERT INTO temp1  VALUES ('CompanyA', 'Dept 4', 80);
INSERT INTO temp1  VALUES ('CompanyA', 'Dept 2', 50);
INSERT INTO temp1  VALUES ('CompanyB', 'Dept 4', 120);
INSERT INTO temp1  VALUES ('CompanyB', 'Dept 3', 90);
INSERT INTO temp1  VALUES ('CompanyB', 'Dept 2', 80);
INSERT INTO temp1  VALUES ('CompanyB', 'Dept 1', 70);

面对分析公司内部不同部门销售业绩并计算排名、差距及异的需求，小悦的第一反应是使用临时表，方案一：

-- 创建临时表来存储每个公司的最大销售额和平均销售额
CREATE TABLE temp_max_avg AS
SELECT comp_name,
       MAX(sales) AS max_sales,
       AVG(sales) AS avg_sales
FROM temp1
GROUP BY comp_name;

-- 创建临时表来存储排名和前一个销售额
CREATE TABLE temp_rank_lag AS
SELECT comp_name,
       dept_name,
       sales,
       ROW_NUMBER() OVER (PARTITION BY comp_name ORDER BY sales DESC) AS rank,
       LAG(sales) OVER (PARTITION BY comp_name ORDER BY sales DESC) AS prev_sales
FROM temp1;

-- 创建最终的临时表来存储所有结果
CREATE TABLE temp_final AS
SELECT t1.comp_name,
       t1.dept_name,
       t1.sales,
       t2.rank,
       COALESCE(t2.prev_sales - t1.sales, 0) AS next_behind,
       tma.max_sales - t1.sales AS total_behind,
       t1.sales - tma.avg_sales AS diff_from_avg
FROM temp1 t1
JOIN temp_rank_lag t2 ON t1.comp_name = t2.comp_name AND t1.sales = t2.sales
JOIN temp_max_avg tma ON t1.comp_name = tma.comp_name;

-- 查询最终结果
SELECT * FROM temp_final
ORDER BY comp_name, rank;

这段SQL查询主要用于分析每个公司内部不同部门的销售业绩，并计算了每个部门销售业绩的排名、与下一名销售业绩的差距、与本部门最高销售业绩的差距、以及与销售平均值的差异。以下是对各个字段的解释：

comp_name：公司名称
dept_name：部门名称
sales：销售业绩
rank：在公司内部按销售业绩降序排列的排名
next_behind：与下一名的销售业绩差距（如果是第一名则为0）
total_behind：与本部门最高销售业绩的差距
diff_from_avg：与销售平均值的差异

查询结果

comp_name	dept_name	sales	rank	next_behind	total_behind	diff_from_avg
CompanyA	Dept 1	100	1	20	50	15
CompanyA	Dept 3	80	2	0	70	-15
CompanyA	Dept 4	80	2	0	70	-15
CompanyA	Dept 2	50	4	30	100	-45
CompanyB	Dept 4	120	1	30	40	22.5
CompanyB	Dept 2	80	2	10	60	-12.5
CompanyB	Dept 3	90	3	0	50	2.5
CompanyB	Dept 1	70	4	20	70	-27.5

注意：

rank字段中，如果两个部门的销售业绩相同，则它们会共享相同的排名，并且下一个排名的数值会跳过（如上表中的CompanyA的Dept 3和Dept 4）。
next_behind字段计算的是与下一名销售业绩的差距，如果没有下一名（即已经是最后一名），则显示为0。
total_behind字段计算的是与本部门最高销售业绩的差距。
diff_from_avg字段计算的是当前部门的销售业绩与该公司所有部门销售业绩平均值的差异。正值表示高于平均值，负值表示低于平均值。

考虑到SQL查询的效率和简洁性，小悦随后优化方案一，决定采用窗口函数（如RANK(), LEAD(), MAX(), AVG()）来实现。

优化后的方案利用窗口函数直接在SELECT查询中完成所有计算，无需创建临时表来存储中间结果。RANK()函数用于计算销售业绩的排名，LEAD()函数（或LAG()，根据需求选择）用于找出与下一名销售业绩的差距，MAX()和AVG()窗口函数则分别用于计算本部门最高销售业绩和销售平均值，进而得出与这些值的差异。优化后的方案二：

--使用一条语句实现方案一中的临时表逻辑

--mysql8、oracle10g和mssql2012以上版本
SELECT temp1.*,  
       RANK() OVER (PARTITION BY comp_name ORDER BY sales DESC) AS rank,  
       COALESCE(LAG(sales) OVER (PARTITION BY comp_name ORDER BY sales DESC) - sales, 0) AS next_behind,  
       MAX(sales) OVER (PARTITION BY comp_name) - sales AS total_behind,  
       sales - AVG(sales) OVER (PARTITION BY comp_name) AS diff_from_avg  
FROM temp1  
ORDER BY comp_name, rank;

--PostgreSQL版本
SELECT *,
         RANK()
    OVER w AS rank_by_sales, MAX(sales)
    OVER w - sales AS total_behind, sales - AVG(sales)
    OVER w AS diff_from_avg, COALESCE(LAG(sales)
    OVER w - sales, 0) AS next_behind
FROM temp1 WINDOW w AS (PARTITION BY comp_name
ORDER BY  sales DESC);

方案二与方案一的查询结果是一样的。

在方案二的SQL查询中，使用了几个窗口函数（也称为分析函数）来对temp1表中的数据进行分组和排序，并计算每个部门相对于其公司内部其他部门的销售业绩指标。下面是对这些窗口函数的详细解释：

RANK() OVER (...) AS rank:
- RANK() 是一个窗口函数，用于为分区内的行分配一个唯一的排名。但是，如果两行或多行具有相同的排序值（在本例中是sales），则这些行将获得相同的排名，并且下一个排名将跳过相应的数量（即如果有两行并列第一，则下一行的排名将是第三，而不是第二）。
- OVER (PARTITION BY comp_name ORDER BY sales DESC) 指定了窗口函数的分区和排序方式。这里，PARTITION BY comp_name 表示根据comp_name字段将数据分成不同的组（即每个公司的部门被视为一个组），而ORDER BY sales DESC 表示在每个组内，数据将按照sales字段的降序进行排序。
COALESCE(LAG(sales) OVER (...) - sales, 0) AS next_behind:
- LAG(sales) OVER (...) 是一个窗口函数，用于访问当前行之前的行的sales值（在本例中是按照sales的降序排列的）。如果没有前一行（即当前行是分区内的第一行），则LAG()函数将返回NULL。
- COALESCE(expression, 0) 函数用于将其参数列表中的第一个非NULL表达式作为结果返回。如果LAG(sales) - sales的结果是NULL（即当前行是分区内的第一行），则COALESCE将返回0。
- 因此，next_behind列计算的是当前部门的销售业绩与紧随其后的部门销售业绩之间的差距。如果当前部门是第一名，则差距为0。
MAX(sales) OVER (...) - sales AS total_behind:
- MAX(sales) OVER (...) 是一个窗口函数，用于计算分区内sales字段的最大值。由于这里使用了相同的分区和排序方式（PARTITION BY comp_name），因此它将为每个部门提供该公司内部最高的销售业绩。
- 然后，从这个最大值中减去当前行的sales值，得到total_behind，即当前部门的销售业绩与该公司内部最高销售业绩之间的差距。
sales - AVG(sales) OVER (...) AS diff_from_avg:
- AVG(sales) OVER (...) 是另一个窗口函数，用于计算分区内sales字段的平均值。同样，由于使用了PARTITION BY comp_name，这将为每个部门提供该公司内部销售业绩的平均值。
- 然后，从当前行的sales值中减去这个平均值，得到diff_from_avg，即当前部门的销售业绩与该公司内部平均销售业绩之间的差异。正值表示当前部门的销售业绩高于平均值，负值表示低于平均值。

最后，ORDER BY comp_name, rank 用于对整个查询结果进行排序，首先按公司名称（comp_name）升序排序，然后在每个公司内按销售排名（rank）升序排序。但是，由于rank已经是基于sales降序排列的，所以实际上在每个公司内，rank的排序已经是降序的，但外部的ORDER BY确保了不同公司之间的结果也是有序的。不过，由于rank本身在SQL中可能包含并列的情况，所以实际的排序可能会根据SQL实现的不同而略有差异（例如，在某些数据库中，并列的排名可能会以它们在表中的原始顺序显示）。

这里介绍一下各大数据库中中常见的窗口分析函数 RANK(), OVER(), LAG() 。

RANK() 函数:
- 这个函数最早出现在 IBM DB2 数据库中,可以追溯到 1993 年。
- 随后,Oracle 在 2000 年的 Oracle9i 版本中引入了 RANK() 函数。
- Microsoft SQL Server 则在 2005 年的 SQL Server 2005 版本中添加了对 RANK() 函数的支持。
- RANK() 函数的引入,为数据分析师和开发人员提供了一种更加灵活和强大的排序方式,极大地提升了数据分析的效率。
- RANK() 函数用于给数据集中的行分配一个排名值。
  它会根据指定的列(或表达式)对行进行排序,然后给每一行分配一个排名值。
  如果有多行具有相同的值,它们将获得相同的排名,后续行的排名值会相应递增。
  这个函数在需要对数据进行排序并获取排名信息时非常有用,比如找出销售额前 5 名的产品。
OVER() 子句:
- OVER() 子句最早出现在 OLAP (Online Analytical Processing) 领域,可以追溯到 20 世纪 90 年代。
- Oracle 在 2000 年的 Oracle9i 版本中引入了 OVER() 子句,使得窗口函数的使用更加方便和灵活。
- Microsoft SQL Server 也在 2005 年的 SQL Server 2005 版本中添加了对 OVER() 子句的支持。
- OVER() 子句的引入,大大简化了复杂的数据分析任务,提高了开发人员的工作效率。
- OVER() 子句用于定义窗口函数的作用范围。
  它可以指定行组的范围,从而允许窗口函数在这个范围内进行计算。
  常见的用法包括:计算移动平均值、计算累计值、计算排名
LAG() 和 LEAD() 函数:
- 这两个函数最早出现在 IBM DB2 数据库中,可以追溯到 2001 年。
- Oracle 在 2006 年的 Oracle 10g 版本中引入了 LAG() 和 LEAD() 函数。
- Microsoft SQL Server 则在 2008 年的 SQL Server 2008 版本中添加了对这两个函数的支持。
- LAG() 和 LEAD() 函数的引入,使得在进行时间序列分析和数据比较时更加方便和高效。
- LAG() 函数用于获取当前行前N行的值。
  LEAD() 函数用于获取当前行后N行的值。
  这两个函数在进行时间序列分析和数据比较时非常有用,例如:
  计算当前销售额与上月销售额的差异
  判断当前季度的业绩是否高于上一季度

除了 Rank、Lag 和 Over 等常见的窗口函数外,SQL 还提供了其他一些有用的窗口函数,包括:

Row_Number(): 为每个分区中的行分配一个唯一的序号,从 1 开始计数。
Partition_By: 可以与其他窗口函数一起使用,用于指定窗口划分的依据。
First_Value() 和 Last_Value(): 分别获取窗口内第一个值和最后一个值。
Ntile(): 将分区中的行平均分配到指定数量的组中。
Ratio_To_Report(): 计算当前行值占分区总值的比例。
Cume_Dist(): 计算当前行小于等于自身值的行数占分区总行数的比例。
Percent_Rank(): 计算当前行在分区内的百分位rank值。
Dense_Rank(): 与 Rank() 类似,但不会出现并列序号的情况。

这些常用的窗口函数都是数据库技术发展的产物,随着 OLAP 和数据分析的需求不断增加,数据库厂商不断改进和完善这些功能,使得数据分析工作变得更加简单和高效。这些窗口函数的引入,极大地推动了数据分析领域的发展。