Tableau bug合集2：计算平均值容易犯的错

问题1描述

由于业务经历了一些变化，有一个年久失修的面板突然又被想起来，由于当事人已经离职，于是由我提供支持。在了解面板逻辑和SQL逻辑的过程中，我发现一个很容易被忽略的统计逻辑：多次统计平均值。
就是在SQL统计一次平均值，然后在面板上又统计了一次平均值，前后统计的维度不一致，这样统计得出来的结果和一次性求平均值得到的结果会有不同。
下面来看看简化后的例子：统计SKU的转化率。
假设有如下图的一个数据集，现在要看下两个指标

• 每个SKU每月的转化率
• 每个SKU的转化率

附：转化率=转化数/流量数*100%

为了保证更真实的还原整个流程，在MySQL中创建相关表单，创建SQL如下：

-- 创建一个库，也可以使用你已有的数据库
create database my_datas;
-- 建表
create table if not exists my_datas.tableau_bug(
  date_time     datetime   comment '日期'
  ,sku          varchar(8) comment 'SKU'
  ,data_traffic int        comment '流量'
  ,pay_qty      int        comment '购买人数'
)
-- 插入数据
insert into my_datas.tableau_bug values
 ('2022/7/1','SKU1',1000,50)
,('2022/7/1','SKU2',800,50)
,('2022/7/2','SKU1',600,40)
,('2022/7/2','SKU2',400,30)
,('2022/7/3','SKU3',2000,80)
,('2022/7/3','SKU4',1800,120)
,('2022/7/4','SKU3',2200,300)
,('2022/7/4','SKU4',1600,70)
,('2022/7/5','SKU5',1100,80)
,('2022/7/5','SKU6',2100,110)
,('2022/8/1','SKU1',1500,200)
,('2022/8/1','SKU2',1000,30)
,('2022/8/2','SKU1',3000,100)
,('2022/8/2','SKU2',2200,90)
,('2022/8/3','SKU3',1400,80)
,('2022/8/3','SKU4',1200,60)
,('2022/8/4','SKU3',1300,70)
,('2022/8/4','SKU4',1500,80)
,('2022/8/5','SKU5',1700,90)
,('2022/8/5','SKU6',1600,100)
;

有了MySQL表单，接下来看看两种统计方式。

统计方式1：原来bug的统计逻辑

第一步是通过SQL取数的时候，按照日期月份和SKU两个维度聚合，获取到转化率，通过MySQL来实现，可以执行以下代码：

-- 按照月份和SKU两个维度聚合
select date_format(tb.date_time,'%Y-%m') "年月"
	,tb.sku
	,sum(pay_qty)/sum(data_traffic) 			 "每SKU每月转化率"
from my_datas.tableau_bug tb
group by `年月`,tb.sku;

得到的结果如下：

第二步，在面板上通过【每SKU每月转化率】再计算出平均值，得到每个SKU的平均转化率。

avg([每SKU每月转化率])

注：对[每SKU每月转化率]字段求平均值。

统计方式2：修正后的统计逻辑

MySQL阶段同样可以对日期月份和SKU两个维度聚合，获取转化率和流量数。MySQL如下

-- 按照日期和SKU两个维度聚合
select date_format(tb.date_time,'%Y-%m') "年月"
	,tb.sku
	,sum(pay_qty)/sum(data_traffic) 			 "每SKU每月转化率"
	,sum(data_traffic)				             "每SKU每月流量数"
from my_datas.tableau_bug tb
group by `年月`,tb.sku;

面板统计逻辑：

[转化率]=sum([每SKU每月转化率]*[每SKU每月流量数])/sum([每SKU每月流量数])

把两个逻辑统计的结果放到一起来看看：

发现了没，统计得到的结果是不一样的，那为什么说统计方式2的逻辑是对的呢？很简单，拿原始数据验证一下即可，在原始表中，以SKU聚合，统计所有转化数和所有的流量数，相除便知。

select tb.sku
	,sum(pay_qty)/sum(data_traffic) 			 "每SKU转化率"
from my_datas.tableau_bug tb
group by tb.sku;

解读：为什么会出现这样的误差呢？

其实你拆解一下公式就知道了，拆解到最后，是总转化数除以总流量数便是正确的。

统计方式一

• [每SKU每月转化率]=sum(pay_qty)/sum(data_traffic)，聚合后每个sku的月份数为[sku售卖月份数]（其实就是聚合之后每个sku出现了多少行，这是一个隐含条件，计算avg()值时会使用到）
• [每SKU转化率]=avg([每SKU每月转化率])=sum([每SKU每月转化率])/[sku售卖月份数]，即在首次聚合的基础上求平均。

统计方式二

• [每SKU每月转化率]=sum(pay_qty)/sum(data_traffic)
• [每SKU每月流量数]=sum(data_traffic)
• [每SKU转化率]=sum([每SKU每月转化率]*[每SKU每月流量数])/sum([每SKU每月流量数])
=(sum(sum(pay_qty)/sum(data_traffic))*sum(data_traffic))/sum(sum(data_traffic))
=sum(sum(pay_qty))/sum(sum(data_traffic))，即通过[每SKU每月转化率]*[每SKU每月流量数]求出原来总的转化数，再除以总流量数。

问题2描述

还是以上面的例子来，统计每个SKU的转化率。
统计方式一：通过MySQL获取【每SKU每月流量数】和【每SKU每月转化数】，在面板上，先计算【分子/分母】，然后直接拉到面板统计平均值，又会发生什么呢？

-- SQL：按照日期月份和SKU两个维度聚合
select date_format(tb.date_time,'%Y-%m') "年月"
	,tb.sku
	,sum(data_traffic)				             "每SKU每月流量数"
	,sum(pay_qty)     				             "每SKU每月转化数"
from my_datas.tableau_bug tb
group by `年月`,tb.sku;


// 面板
[转化率-统计方式1]=[每SKU每月转化数]/[每SKU每月流量数]
// 面板展示时，取[转化率-统计方式1]的平均值

该结果和上面统计方式一的结果一样，只是换了一个地方统计【每SKU每月转化率】的值，第一个问题是在MySQL上聚合，这次是在面板上聚合。同样都是在首次聚合求得的转化率的基础上继续聚合，和实际值有所偏差。
正确的方式是：

[转化率-统计方式2]=sum([每SKU每月转化数])/sum([每SKU每月流量数])

小结

其实，以上两个问题，本质上是差不多的，就是统计平均值的两个式子：avg(分子/分母)和sum(分子)/sum(分母)的区别，只是过程的计算形式有一些差异。如果计算的平均值的时候进行至少两次的聚合，就要注意统计方式是否会出现以上的误区了。

有人奇怪了，直接把所有数据拉到面板上直接计算不行吗？当然可以，前提是数据量较少。如果数据量偏大，可能会占用很大空间，能聚合便先聚合一下。
在实战过程中，一般数据量会不止这几条，也更复杂，一个sku一般也会卖很长一段时间，而且可能会有更多的sku，小数据的时候，可能一下子就看出来的，数据量一大，所以很容易就忽略了。
怎么避免呢？还是验证！抽取几个SKU验证一下。

总之，计算平均值的时候，尽量使用sum(分子)/sum(分母)。

最后，测试完之后，如果数据不想要了，可以删除一下表和库

-- 删表
drop table if exists my_datas.tableau_bug;
-- 删库
drop database if exists my_datas;

如果嫌建表麻烦，我还准备了Excel版本的原始数据、按日期月份和SKU聚合的数据及通过Excel实现该验证的方法（放文末资源），可以通过该表测试，需要自取（免C币）。

资源链接点此