爱学it学无止境

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

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

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

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

--根据需求建测试表
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表中的数据进行分组和排序，并计算每个部门相对于其公司内部其他部门的销售业绩指标。下面是对这些窗口函数的详细解释：

  1. 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字段的降序进行排序。
  1. 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。
  1. MAX(sales) OVER (...) - sales AS total_behind:
  • MAX(sales) OVER (...) 是一个窗口函数，用于计算分区内sales字段的最大值。由于这里使用了相同的分区和排序方式（PARTITION BY comp_name），因此它将为每个部门提供该公司内部最高的销售业绩。
  • 然后，从这个最大值中减去当前行的sales值，得到total_behind，即当前部门的销售业绩与该公司内部最高销售业绩之间的差距。
  1. 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实现的不同而略有差异（例如，在某些数据库中，并列的排名可能会以它们在表中的原始顺序显示）。