SQL踩坑经验 | 用SQL实现帕累托累计和滚动平均值

分享一个最近做得比较复杂的东西，总的来说对专业的ETL可能不难，但对我而言稍微有点麻烦的SQL取数需求。

至于为什么这种分析类的数据处理要用SQL处理而不是其他的工具，纯粹就是因为公司内部工具的限制这个需求只能用SQL处理。

这是一个对销售数据做SQL取数的需求，原始数据是一个by天的销售数据，最终需要的字段大致是这样的：

#brand，源表字段

#bu，源表字段

#yearmonth，源表字段

#workday_of_month，源表字段

#daily_sales，源表字段，当日销售金额，和value一致

#Accumulated Sales，帕累托累计的销售额

#Accumulated Ratio，帕累托累计的销售额占比，展示为百分数形式，保留两位小数

#Objective，将target进行发散，每一天都展示月度总target，保留两位小数

#Accumulated Achi%，按照帕累托累计的销售额计算target达成率，展示为百分数形式，保留两位小数

#Achi%全月销售达成，每一天都展示月度总达成率，展示为百分数形式，保留两位小数

#L6Achi%，回滚过去六个月平均达成率（不含统计月），展示为百分数形式，保留两位小数，平均值算法为加权平均

#L12Ach%，回滚过去六个月平均达成率（不含统计月），展示为百分数形式，保留两位小数，平均值算法为加权平均

手里的原始数据基本是这个样子：

#数据有下面字段：brand ，yearmonth，workday_of_month，value，target

#target信息仅在workday of month=1的时候有值，其他为空白 

是一个很典型的用SQL做纯粹的统计计算的需求，为了方便描述，这里我们请DeepSeek帮忙造一个数据集：

图片

大概就是如上图所示的这么一个数据源。

首先对于这种需求，第一反应就是使用CTE来分段处理。

又来一个可能大家都在用但是不知道怎么表述的东西（哈哈，其实我之前也不知道）。CTE 是 公用表表达式的缩写，全称是 Common Table Expression。你可以把它理解为 SQL 查询中定义的一个临时的、命名的结果集。这个结果集只存在于它所属的单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行期间。

简单来说就是with临时表，日常都在用，但是我猜很多人和我一样之前都不知道CTE这个专业的名称。所以就在这里多讲一点纯理论的内容吧。我们可以从这么几个方面来理解CTE:

1,临时结果集： CTE 定义了一个查询，这个查询的结果就像一个临时的表或视图。

2.命名： 你给这个临时结果集起一个名字。

3.作用域有限： CTE 只在定义它的那条 SQL 语句（通常是紧跟在 WITH 子句后面的那条主查询）的范围内有效。语句执行完毕后，CTE 就消失了。

4.可被引用： 在主查询（以及 CTE 自身内部，如果是递归 CTE）中，你可以像引用普通表或视图一样，多次引用这个 CTE 的名称。

5.使用 WITH 子句定义： CTE 的语法以关键字 WITH 开始。

使用CTE的优点有很多

1. 提高复杂查询的可读性和可维护性：

• 将大型复杂的查询分解成更小的、逻辑清晰的模块（即多个 CTE）。
• 每个 CTE 可以专注于完成一个特定的子任务（例如，计算一个中间汇总、过滤特定数据、连接几个表得到一个中间视图）。
• 主查询通过引用这些命名的 CTE 来组合最终结果，使得主查询的逻辑变得非常简洁明了。这比写一个包含多层嵌套子查询的巨型语句要容易理解和修改得多。

2.避免重复：

如果主查询中需要在多个地方使用相同的子查询结果，可以在 CTE 中定义一次，然后在主查询中多次引用该 CTE。这比多次复制粘贴相同的子查询更高效（数据库可能优化得更好）且不易出错。

3.实现递归查询：

也就是类似下面内容的写法

WITHRECURSIVE SubItems AS (
    -- 锚点成员：选取根节点
    SELECT ItemID, ItemName, ParentID
    FROM Items
    WHERE ParentID ISNULL  -- 假设根节点的ParentID为NULL
    UNIONALL
    -- 递归成员：连接父项和子项
    SELECT i.ItemID, i.ItemName, i.ParentID
    FROM Items i
    INNERJOIN SubItems s ON i.ParentID = s.ItemID -- 关键：引用自身(SubItems)
)
SELECT*FROM SubItems;

• 递归 CTE 是 CTE 一个非常强大且独特的特性。它允许 CTE 在其自身的定义中引用自己。
• 这使得查询层次结构或树形结构数据（例如组织结构图、文件目录、物料清单 BOM）变得非常简单和高效，而这是标准 SQL 难以完成的。
• 递归 CTE 通常包含两部分：一个“锚点成员”和一个“递归成员”，通过 UNION ALL 连接。

4.替代视图：

• 当你只需要在一个查询中临时使用一个视图逻辑，而不想实际在数据库中创建永久视图时，CTE 是一个完美的选择。它避免了创建和管理视图对象的开销。

5.简化窗口函数的使用：

• 在定义 CTE 中使用窗口函数进行计算（如排名、累计求和等），然后在主查询中对这些计算结果进行过滤或进一步处理，会使逻辑更清晰。

总的来说，在做这个需求的SQL的时候前后遇到了各种各样的麻烦，这里总结一下这些小麻烦的解决方案。

第一个小问题就是帕累托累计到底怎么用SQL表达的问题。

 select
        b.brand,
        b.yearmonth,
        b.workday_of_month,
        b.value as daily_sales,
        b.target,
        date_parse(b.yearmonth, '%Y%m') as yearmonth_date,
        sum(b.value) over (partition by b.brand,  b.yearmonth) as monthly_total_sales,
        sum(b.value) over (partition by b.brand,  b.yearmonth, order by b.workday_of_month) as accumulated_sales,
        max(case when b.workday_of_month = 1 then b.target end) over (partition by b.brand,  b.yearmonth) as monthly_target
    from your_table b

乍一想挺复杂的，但是这个写法比预想要简单，就是使用了我们在开窗函数应用的过程中的一个知识点：当未指定窗口范围的时候，默认使用RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是从分区第一行开始，累加到当前行。

所以，解题思路是先分区：将数据按 brand（品牌）、yearmonth（年月）、分成独立组。然后分区内按 workday_of_month（当月工作日序号）从低到高排序（如第1天、第2天...）。接下来使用默认窗口帧的方式进行累计求和就可以了。

顺便就复习一下开窗函数的相关内容吧。

开窗函数（Window Functions）也叫分析函数（这个名字也有点冷门），是 SQL 中极其强大且实用的功能，它允许你在不折叠原始行的情况下，对与当前行相关的一组行执行计算。简单来说，它让你在查询结果中同时看到原始数据和基于某个“窗口”计算的聚合值或排名。

它与普通的聚合函数（如 SUM(), AVG(), COUNT()）的关键区别在于：

不折叠结果集： 普通聚合函数配合 GROUP BY 会把多行数据聚合成一行摘要。开窗函数则为每一行原始数据都计算一个值，结果集的行数通常与原始查询的行数相同。

定义“窗口”： 开窗函数通过 OVER() 子句定义一个“窗口”，这个窗口指定了计算所基于的行集合以及这些行在窗口内的顺序。

主要有点和应用场景如下：

• 同时查看明细和聚合数据： 无需自连接或子查询就能在原始行旁边显示汇总值（如累计和、排名、移动平均）；
• 高效计算排名和序号，比如： ROW_NUMBER, RANK, DENSE_RANK 是处理分页、Top N、唯一排名等需求的利器。
• 访问前后行数据： LAG 和 LEAD 非常适用于计算环比、同比、差值等时间序列分析；
• 计算复杂的聚合： 在定义好的窗口内进行聚合，比用多个子查询或自连接更简洁高效；
• 分区计算： 在数据的不同子集（分区）内独立执行相同的计算（如每个部门、每个地区、每个客户等）。

语法规则如下：

window_function(column3): 

这是实际执行计算的函数。它可以是：

• 聚合类：

 SUM(), AVG(), MIN(), MAX(), COUNT()。这些函数在窗口函数上下文中使用时，会为每一行计算基于其窗口的聚合值。

• 排名类：

 ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(n)。用于为窗口内的行分配排名、序号或分组。

• 偏移类： 

LAG(column, offset), LEAD(column, offset), FIRST_VALUE(column), LAST_VALUE(column), NTH_VALUE(column, n)。用于访问窗口内相对于当前行的前面、后面或指定位置行的值。

OVER(): 

这是定义窗口的关键字。括号内的子句决定了窗口的范围。

PARTITION BY ... (可选)：

将整个结果集划分成更小的组（分区），开窗函数的计算会独立地在每个分区内进行。

相当于在分区内部重新开始计算，如果省略 PARTITION BY，则整个结果集被视为一个分区。

ORDER BY ... (可选)：

指定分区内行的排序顺序。

对于排名函数 

(ROW_NUMBER, RANK, DENSE_RANK) 是必须的，因为它们需要明确的顺序来分配排名。

对于聚合函数和偏移函数，它决定了窗口框架（如果指定了）的起点和方向，也决定了 FIRST_VALUE/LAST_VALUE/NTH_VALUE 的取值依据。

如果没有 ORDER BY 且没有指定窗口框架，默认的窗口框架通常是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（对于聚合函数），但这可能因数据库而异（特别是偏移函数行为可能不同）。

window_frame_clause (可选)：

在定义了 ORDER BY 之后，可以进一步精确定义当前行计算所基于的具体行范围。常见的子句有：

ROWS BETWEEN N PRECEDING AND M FOLLOWING： 从当前行前 N 行到当前行后 M 行。

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW： 从分区第一行到当前行（常用累加）。

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING： 从当前行到分区最后一行。

ROWS UNBOUNDED PRECEDING： 从分区第一行到当前行（很多数据库允许简写）。

RANGE ...： 类似 ROWS，但基于 ORDER BY 列的值范围（例如，计算当前行值 ±10 范围内的所有行）。

虽然帕累托累计的问题解决了，但是这样处理会引发第二个问题，那就是原始数据中的时间可能不是连续的。

我们可以这样理解这个按时间的帕累托累计，最后可能会呈现为一张带有日期的折线图，换句话说，每个日期对应的数据都应该是有值的，当没有销量的日子里应该展示为0而不是缺失。当我们用开窗函数处理的时候会有一个问题，日期会有断点的情况，所以我们需要用一个办法来解决断点的问题。

既然数据断点的产生原因是数据为零，那解决思路就有了，我们先用品牌信息关联一下日期表做一个cross join生成一个list，然后再把销量的数据匹回来，把匹不上的地方置为0就可以了，所以我们需要做一个临时表来处理这个逻辑。

  select distinct 
        cast(dc.year_month as varchar) as year_month, 
        cast(dc.monthlyworkday as int) as monthlyworkday, 
        a.brand, 
        a.bu 
    from calendar dc
    cross join(
        select distinct 
             brand
        from sales ic
    ) a 
    where dc.calendar_dt <= cast(current_date as varchar) 
        and dc.monthlyworkday <> '0';

这样一来，帕累托累计的问题就解决了，接下来就是滚动平均的问题。

思路上是做自连接，然后用case when嵌套求和就可以了，但是写得时候我搞了个小问题，想了半天都没想明白是哪里出的问题。需求是写过去6个月的平均达成率不含当前月，也就是说，需要sum求和的范围其实是：

select
        m.bu,
        m.yearmonth,
        m.yearmonth_date,
        sum(case when m2.yearmonth_date between date_add('month', -7, m.yearmonth_date) and date_add('month', -1, m.yearmonth_date) then m2.monthly_total_sales end) 
            / nullif(sum(case when m2.yearmonth_date between date_add('month', -7, m.yearmonth_date) and date_add('month', -1, m.yearmonth_date) then m2.monthly_target end), 0) as l6_weighted_achi,
        sum(case when m2.yearmonth_date between date_add('month', -13, m.yearmonth_date) and date_add('month', -1, m.yearmonth_date) then m2.monthly_total_sales end) 
            / nullif(sum(case when m2.yearmonth_date between date_add('month', -13, m.yearmonth_date) and date_add('month', -1, m.yearmonth_date) then m2.monthly_target end), 0) as l12_weighted_achi
    from(
        select distinct
            bu,
            yearmonth,
            yearmonth_date,
            full_month_achi,
            monthly_total_sales,
            monthly_target
        from achi_calc
        where workday_of_month = 1
    ) m
    left join(
        select distinct
            bu,
            yearmonth,
            yearmonth_date,
            full_month_achi,
            monthly_total_sales,
            monthly_target
        from achi_calc
        where workday_of_month = 1
    ) m2
        on 
    m.bu = m2.bu
    group by
        m.bu,
        m.yearmonth,
        m.yearmonth_date

这里一开始取值的时候原表是一个层层递进的CTE的临时表，但是出来的结果不太多对，琢磨了很久都没有琢磨明白为啥用CTE出来的结果是错的，最后修改成了从原始表取数

第四个问题是时间范围的问题，我的需求中有时间范围的要求，数据结构也比这里展示的更复杂一些，所以我在开始的时候就写了一个预处理的CTE限制了时间范围，然后后面所有的计算都是基于这个CTE开展。但其实在滚动6个月这个计算的时候不应该卡时间范围的限制要求，时间限制的这个where条件应该写在最后而不是中间位置。