多维度占比分析是数据分析中的常见需求，比如我们需要同时计算全量维度的总占比，以及某个子维度下的分组内占比，这时候单纯使用单个窗口函数无法同时满足需求，就需要用到窗口函数的多重嵌套写法。
多维度占比分析场景说明
假设我们有一张销售数据表sales_data，表结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| category | varchar | 商品品类 |
| product | varchar | 商品名称 |
| sales_amount | int | 销售金额 |
我们的需求是：计算每个商品在全量销售中的占比，同时计算每个商品在其所属品类中的销售占比。这两个占比的维度不同，前者是全表维度，后者是品类分组维度。
单窗口函数实现的局限
如果先用单个窗口函数计算全量占比：
-- 计算全量占比的单窗口函数写法
SELECT
category,
product,
sales_amount,
sales_amount * 1.0 / SUM(sales_amount) OVER() AS total_ratio
FROM sales_data;
上述语句只能得到每个商品的全量占比，无法得到品类内的占比。如果再加一个窗口函数计算品类内占比：
-- 尝试加品类内占比的单窗口函数写法
SELECT
category,
product,
sales_amount,
sales_amount * 1.0 / SUM(sales_amount) OVER() AS total_ratio,
sales_amount * 1.0 / SUM(sales_amount) OVER(PARTITION BY category) AS category_ratio
FROM sales_data;
这种方式看起来可行，但如果后续还需要基于品类内占比再做其他计算，比如筛选品类内占比超过20%的商品，就需要把上面的查询作为子查询，此时如果嵌套层级更多，单层的多窗口函数写法会让代码变得难以维护，这时候就需要用到窗口函数多重嵌套。
窗口函数多重嵌套实现多维度占比
窗口函数多重嵌套的核心思路是，先通过内层窗口函数计算出分组维度的聚合值，再在外层窗口函数中基于内层的结果计算更上层的聚合值，或者直接基于内层结果计算最终占比。
实现示例
下面的SQL通过窗口函数多重嵌套，同时计算全量占比和品类内占比：
SELECT
category,
product,
sales_amount,
-- 计算全量占比：用当前商品销售额除以全表总销售额
sales_amount * 1.0 / total_sales AS total_ratio,
-- 计算品类内占比：用当前商品销售额除以所属品类总销售额
sales_amount * 1.0 / category_sales AS category_ratio
FROM (
SELECT
category,
product,
sales_amount,
-- 内层窗口函数：计算全表总销售额
SUM(sales_amount) OVER() AS total_sales,
-- 内层窗口函数：计算各品类总销售额
SUM(sales_amount) OVER(PARTITION BY category) AS category_sales
FROM sales_data
) t;
代码逻辑说明
- 内层子查询中，使用
SUM(sales_amount) OVER()计算全表销售总额，结果赋值给total_sales字段，这个值是所有行都相同的。 - 内层子查询中，使用
SUM(sales_amount) OVER(PARTITION BY category)计算每个品类的总销售额，结果赋值给category_sales字段，同一个品类的行这个值相同。 - 外层查询中，直接用
sales_amount除以total_sales得到全量占比，除以category_sales得到品类内占比，逻辑清晰，后续如果需要扩展更多维度，只需要在内层子查询中增加对应的窗口函数即可。
扩展：三层维度占比分析
如果有三层维度需求，比如还要计算各品类在不同销售区域的占比，只需要在内层子查询中再增加一个对应分区的窗口函数：
SELECT
region,
category,
product,
sales_amount,
sales_amount * 1.0 / total_sales AS total_ratio,
sales_amount * 1.0 / category_sales AS category_ratio,
sales_amount * 1.0 / region_category_sales AS region_category_ratio
FROM (
SELECT
region,
category,
product,
sales_amount,
SUM(sales_amount) OVER() AS total_sales,
SUM(sales_amount) OVER(PARTITION BY category) AS category_sales,
SUM(sales_amount) OVER(PARTITION BY region, category) AS region_category_sales
FROM sales_data
) t;
注意事项
- 窗口函数嵌套时，内层的窗口函数会先执行，外层的窗口函数基于内层的结果进行计算，注意区分不同层级的计算逻辑。
- 计算占比时，要注意数值类型转换，避免整数除法导致结果错误，比如示例中用
sales_amount * 1.0将整数转换为浮点数。 - 如果数据量较大，窗口函数的计算会消耗一定的资源，需要根据实际情况评估性能，必要时可以对分区字段建立索引提升计算效率。