用窗口函数作累计计算
创建于 2022-11-01 / 最近更新于 2022-12-08 / 7230
字体:
[默认]
[大]
[更大]
案例一:月累计销售额计算
需求背景
用户有一张销售明细表,想计算出每月累计销售额,来衡量年度目标完成度。
实现方法
ETL 和卡片里都可以实现,以下以卡片实现为例。
1. 在卡片里新建计算字段:
「年」: year([日期]) --以年为分组累计求和,需要提取“年”; 以月为分组计算日累计,需要提取“年月”。 「累计销售额」:sum([销售金额])over(partition by [大区],[年] order by [日期]) --使用开窗函数来根据特定维度计算累计销售金额。
2. 把开窗函数里partition by 后面的维度字段按照顺序加进维度栏,日期可用需要的日期颗粒度,把「累计销售额」加到数值栏,聚合方式改为“最大值”,对日期字段设置升序排序。如果有总计行,则该字段“小计/总计”需单独设置「以原始数据计算」或者「以聚合数据计算-最大值」(可参考下图)。
扩展场景:用「累计销售额」计算年度目标完成率。
1. 依次新建字段:
「月」: month([日期]) 「月累计销售额」: max([累计销售额])over(partition by [大区],[年],[月]) 「年度目标完成率」:[月累计销售额]/[年度目标]
2. 把「年度目标完成率」加入数值栏,聚合方式选“最大值”,“小计/总计“”设置「以原始数据计算」或者「以聚合数据计算--最大值」。最终效果如下图。
案例二:按销量排名计算商品小类的累计销量占比
需求背景
用一张门店日交易明细表,计算商品小类的累计销量占比。累计销量占比=累计销量/总销量。
累计销量是按照商品小类的销量从高到低排列后依次累加得到。例如商品小类C1在本年销量为4081,在所有小类中排名第1,累计销量就是C1的销量;商品小类A3本年销量在所有小类中排名第2,累计销量就是C1+A3的销量;以此类推。
总销量为所有商品销量的合计销量。
业务意义
计算商品销量的累计占比后,可以对商品进行分类。例如根据二八原则,将累计占比前80%的商品分为一类。
实现方法
1. 在卡片里依次新建以下计算字段:
「商品小类销量」:sum([销量]) over(partition by [商品大类],[商品小类]) --因为数据集中还有商品、日期、会员等维度,需要使用窗口函数计算商品小类的销量,用于下一步对聚合后的商品小类销量做排序。 「商品小类销量排名」:dense_rank() over(partition by 1 order by [商品小类销量] desc) --因为数据集中还有商品、日期、会员等维度,实际计算层级不只是卡片上显示的商品大类和商品小类,所以使用dense_rank来排名。 「累计销量」:sum([销量]) over(partition by 1 order by [商品小类销量排名]) 「总销量」: sum([销量]) over(partition by 1) 「累计占比」:[累计销量]/[总销量]
2. 将新建的字段中需要展示的字段添加到数值栏,并选择聚合方式为“最大值”;把「销量」拖入排序栏,降序排列即可。
72 人点赞过