观远 BI

用窗口函数作累计计算

创建于 2022-11-01 / 最近更新于 2022-12-08 / 7230
字体: [默认] [大] [更大]

案例一:月累计销售额计算

需求背景

用户有一张销售明细表,想计算出每月累计销售额,来衡量年度目标完成度。

图片.png

实现方法

ETL 和卡片里都可以实现,以下以卡片实现为例。

1. 在卡片里新建计算字段:

「年」: year([日期])
     --以年为分组累计求和,需要提取“年”; 以月为分组计算日累计,需要提取“年月”。
「累计销售额」:sum([销售金额])over(partition by [大区],[年] order by [日期])
     --使用开窗函数来根据特定维度计算累计销售金额。

图片.png

2. 把开窗函数里partition by 后面的维度字段按照顺序加进维度栏,日期可用需要的日期颗粒度,把「累计销售额」加到数值栏,聚合方式改为“最大值”,对日期字段设置升序排序。如果有总计行,则该字段“小计/总计”需单独设置「以原始数据计算」或者「以聚合数据计算-最大值」(可参考下图)。

图片.png

扩展场景:用「累计销售额」计算年度目标完成率。

1.  依次新建字段:

「月」:          month([日期])
「月累计销售额」:  max([累计销售额])over(partition by [大区],[年],[月])
「年度目标完成率」:[月累计销售额]/[年度目标]

2.  把「年度目标完成率」加入数值栏,聚合方式选“最大值”,“小计/总计“”设置「以原始数据计算」或者「以聚合数据计算--最大值」。最终效果如下图。

图片.png

案例二:按销量排名计算商品小类的累计销量占比

需求背景

用一张门店日交易明细表,计算商品小类的累计销量占比。累计销量占比=累计销量/总销量。

累计销量是按照商品小类的销量从高到低排列后依次累加得到。例如商品小类C1在本年销量为4081,在所有小类中排名第1,累计销量就是C1的销量;商品小类A3本年销量在所有小类中排名第2,累计销量就是C1+A3的销量;以此类推。

总销量为所有商品销量的合计销量。

图片.png

业务意义

计算商品销量的累计占比后,可以对商品进行分类。例如根据二八原则,将累计占比前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 人点赞过