跳至正文

深刻理解聚合函数及其抽象程度

标签:

最近几周,我见了多家客户,涉及到航空、仓储、制造多个行业,在与他们交流生产交期预测、安全库存设置,以及价格预估的过程中,我猛然想到了一个诠释“分析即聚合”以及“高级分析”的绝佳视角,得以把业务的需求与技术的实现方式深度结合起来。

这将是《业务分析通识》极其具有启发性的内容,本文将结合《数据可视化分析(第2版)》的内容先做诠释。

一、理解分析的本质:抽象与聚合

在之前的多篇文章中,我用多个视角诠释了分析的本质过程。在这里不再赘述类似内容。读者可以先观看如下的两篇文章:

概括而言,

  • 数据查询和数据分析的关键差异在于:分析是对业务及数据的高度抽象过程
  • 最重要的数据抽象方式是聚合函数及其计算,因此说“聚合是分析的本质”,分析即聚合
  • 整个计算的体系,可以围绕聚合展开:行级别与聚合,聚合前筛选与聚合后筛选,聚合前预先聚合与聚合后二次聚合等

本文聚焦业务中的指标分类,不同抽象程度的指标借助于不同形式的聚合函数完成。

二、从问题分类,到指标的分类体系

在问题的基本结构中,问题答案是关键,对应技术上的聚合计算,另外的两个构成部分都围绕问题答案而有价值——问题详细级别是答案的分组依据,分析范围则决定答案的样本范围。

在业务分析中,哪些管理中普遍使用的聚合计算常常被称之为“指标”并约定俗称,成为公司管理者探究业务变化、跟踪业务决策的重要抓手。这些指标无一例外的使用了聚合计算,我们可以根据聚合计算的复杂性,把它们划分为如下的几个类型:

  • 一类指标:以直接聚合及其四则运算为实现方式,比如销售额总和、平均销售数量
  • 二类指标:基于直接聚合的二次计算,比如合计销售均价、利润率等,一些常见的计算会进一步固化为函数,比如标准差
  • 三类指标:指标计算中包含不同的范围,比如YTD金额、去年同期金额,以及建立在此基础上的二次计算,比如年度同比、月度环比、客户迁徙M0-M1%、合计百分比等
  • 四类指标:包含排序、累计等行间计算的指标,比如TOP10产品占比、物料集中度(单一客户占比超过50%的物料数量,在所有物料中的占比)。

三、理解聚合的实现形式:聚合函数

甚至可以按照计算的复杂性,把聚合函数分为不同的阶段:

  • 1阶函数:sum和count,代表总体规模
  • 2阶函数:avg平均值,代表抽象特征,是sum和count的组合形式
  • 3阶段函数:var方差,代表更抽象特征,描述波动情况,是数据值及avg和count的组合形式
  • 4阶段函数:max、min、percentile等百分位函数,在聚合过程中包含了相互之间的比较,性能上运算更慢,用个体描述总体特征。 为了方便理解,可以把MAX等包含排序过程的函数,理解为self-join的自连接过程。这将有助于理解。


下文摘自《数据可视化分析(第二版):分析原理与Tableau、SQL实践》第八章

8.5  分析函数:从明细到问题的“直接聚合”

行级别计算是为问题分析而做的准备。分析必然包含聚合,“聚合就是从数据表的行级别到问题级别的由多变少的过程”,为了和后续的表计算等间接聚合相区别,这里介绍的聚合称之为“直接聚合”

常见的直接聚合函数包括总和SUM、平均值AVG、最大值MAX、最小值MIN、重复计数COUNT、不重复计数COUNTD(COUNT DISTINCT)、中位数MEDIAN等。大数据分析中,还有衡量离散程度的方差、体现个体位置的百分位等函数,这里分类介绍。

8.5.1  描述规模:总和、计数、平均值

最常见的分析需求是衡量数据值有多少(规模),代表函数是求和SUM、计数COUNT,分别对应数据表中数字、字符串数据类型的字段列。其中,计数又可以分为重复计数(COUNT)、不重复计数(COUNT DISTINCT)两类。举例:

  • 数字型字段求和聚合:销售额总和-SUM([销售额]) 、 利润总和-SUM([利润])
  • 字符串字段计数(重复计数):客流量-COUNT([客户ID])
  • 字符串字段计数(不重复计数):客户数-COUNTD([会员ID])、订单数COUNTD([订单ID])

数字默认聚合,默认聚合方式是SUM求和,在问题中,默认聚合方式常被忽略,影响了初学者理解“聚合是分析的本质”,比如“各类别的销售额(总和)”。本书中,笔者尽可能补全聚合方式。

很多字段的求和没有分析意义,比如年龄、发货间隔等,这种属性称之为“不可加性”(non-additive),此时平均值AVG聚合通常就是首选的聚合方式,用以代表年龄、发货周期的总体特征。

在Tableau中,数字型字段的默认聚合都是求和SUM,分析师可以更改字段的默认聚合方式,或者在视图中临时调整。如图8-32左侧所示,在字段【发货间隔】上右键,在弹出的下拉菜单中,选择“默认属性→聚合→平均值”,以后双击该字段,默认都是“平均值AVG”。也可以在视图中,临时调整字段聚合方式,如图8-32右侧所示,点击聚合的字段右键,在弹出的下拉菜单中,从“度量”列表中切换聚合方式即可。

图8-32  为字段设置默认聚合方式,或者在视图中更改

正如8.1小节所述,不同的工具聚合语法略有不同,但聚合过程都一致。图8-33中展示了Excel、SQL和Python使用SUM函数求和的语法。

Tableau和SQL相比,差异较大的是不重复计数。SQL不重复计数是COUNT(DISTINCT [字段]),对应Tableau的COUNTD函数。

图8-33  不同工具中的聚合语法(注意明细行的多值相加和跨行相加的区别)

在业务分析中,衡量业绩绝对规模是基本的出发点。SUM、AVG、COUNT、COUNTD函数是构建“绝对规模”指标的聚合方式。商业仪表板内容的首要内容,基本都会被销售额总和、利润总和、平均交付周期、订单数等字段占据。

8.5.2  描述数据的波动程度:方差和标准差

总和、平均值、计数聚合常用于描述宏观特征,不考虑个体差异及其分布特征,因此容易受极大值、极小值等特殊值影响。在宏观指标之外,分析还要关注样本中个体的波动性(离散程度)及关键个体(如最大值、中位数、众数、最小值等)。描述波动程度的聚合方式是方差和标准差。

波动分析在质量分析领域应用广泛,质量控制图、西格玛分布图与此有关。

方差(variance)用于衡量数据的离散程度。总体方差( )是总体数据中各样本数据和总体平均数( )之差的平方和的平均数,公式如下所示:

注: 为为总体方差,x为每个数据值,μ为总体均值,N为数据数量

方差相当于以总体的平均数( )为基准,计算每个数据的偏移。相同的多个数据,方差就是0;随着数据围绕基准波动,方差就会增大。为避免上下完全相反的波动相互抵消,比如{4,5,5,6},方差使用了2次方计算偏离。

举例:

  • {5,5,5,5}  =0/4=0 (无波动)
  • {4,5,5,6}  =2/4=0.5 (出现了波动)
  • {3,4,6,7}  =10/4=2.5 (波动进一步增大)

为了更形象地说明,这里用一组身高数据计算方差。如图8-34所示,4个人的身高数据为{190,170,165,160}(单位cm),平均数为170,方差为131.25。如果身高全部减少10厘米,平均数变化,但方差不变。

图8-34  4个人身高的离散程度——使用方差量化

可见,方差虽然建立在平均值基础上,但是又截然不同。方差越大,代表离散程度越高,但不代表总体的平均身高情况。

由于方差使用“差异”的平方来计算,难以将方差和数据值直接比较。为了保持与样本数据单位的一致性,就有了它的平方根形式——标准差(standard deviation)。在于数据值比较时,标准差比方差更直观、易于理解。

比如,4个人身高的方差是131.25,对应标准差是11.45,这是量化后的平均波动范围。如图8-35所示,有3个人的身高在平均值上下1个标准差范围之内(即170 11.45),即“1个标准差”范围( 1σ西格玛)。

图8-35  使用标准差有助于更好地衡量分布

如图8-35右侧所示,在标准的正态分布中,1西格玛的概率是68.3%,3西格玛是99.7%。精益质量管理中常用的“六西格玛”方法,就是将质量缺陷控制在3.4ppm(百万分之3.4)之内。

这里以超市数据为例。图8-36展示了“2020年12月各地区交易的利润分析指标”。其中西南和华东区域交易的利润波动最大,借助它们的利润最小值和最大值也能部分佐证。

图8-36  2020年12月各地区交易的利润分析指标[1]

相比求和、平均值,方差的应用较少,集中在质量分析、采购价格波动分析等少数场景。简单的分析可以直接使用方差计算,复杂的应用都是基于聚合值的二次分析,常见的二次聚合则被简化到“分析”窗格中。在本书第5章5.4.3小节(参考区间、盒须图、标准差分布),质量控制图(一个标准差区间)就是基于标准差计算而生成的可视化。

8.5.3  关注个体、走向分布:百分位函数及最大值、最小值、中位数

分析通常是“分析宏观特征、不关注个体差异”,因此总和、平均值、计数是使用最频繁的聚合方式。业务探索分析的趋势是把宏观分析和微观分析紧密结合在一起,因此需要“关注个体”的分析指标,其中的典型代表是百分位数(percentile,符号为P),而百分位数的代表就是最小值、中位数和最大值,分别可以用P0、P50和P100代表。

关于“百分位数”的理解和计算,虽然没有统一,但不影响大家使用。Excel和Tableau都可以使用PERCENTILE()函数计算一组数据的百分位数,如图8-37所示。

图8-37  使用Excel和Tableau计算一组数据的百分位

相对于此前的求和、求平均值,标准差和百分位数目前在业务分析中尚未普及,它们是分布分析的基础。有以下几个地方需要强调。

1.百分位数与常见聚合的关系

最大值MAX、最小值MIN、中位数MEDIAN是特殊的百分位数聚合,对应如下。

  • 最大值MAX       = 百分位数P100对应的数据  = PERCENTILE([销售额],1)
  • 中位数MEDIAN  = 百分位数P50对应的数据   = PERCENTILE([销售额],0.5)
    (奇数序列即中间值,偶数序列则为中间两个数的平均值)
  • 最小值MIN        =百分位数P0对应的数据      = PERCENTILE([销售额],0)

另外两个使用最广泛的是P25百分位和P75百分位,它们是“箱线图”分布的基础。图8-38展示了“2021年12月,各地区的(所有交易)利润分布”,使用百分位数展示了多个指标的计算。

图8-38  2020年12月各地区所有交易明细的利润分布

上述文本表聚合难以对比,可视化的魅力在于借助位置、大小、形状等方式直观展现,图8-39展示了不同区域的利润箱线图分布(延伸到最大最小值)。可见华东地区的最小值P0最低(-6771),西南地区的最大值P100最高(9153);按照最大值和最小值的范围估计,华北地区的离散程度明显低于其他各地区。

图8-39  2020年12月各地区的利润交易明细箱线图

取消默认的“聚合度量”,相当于从交易明细中构建了箱线图分布。业务中常见的箱线图分布是已有聚合值的二次抽象聚合,因此需要使用表计算完成,本书将在第9章9.7.4小节介绍。

2.百分位数、百分比与累计百分比的差异

百分比(percent,符号%)是相对于单一静态数值的算术计算,而百分位数(percentile,符号P)则是相对于多个数据点的聚合分布,如图8-40所示。

图8-40  百分比与百分位数

除了百分比、百分位,高级分析时常还会用到“累计百分比”,最典型的是帕累托分析中的“前20%的客户累计贡献了前80%的销售额总和”场景,和这里“百分比”不同,它是聚合值的二次计算,是第9章表计算的典型场景,本书将在第9章9.9小节介绍。

8.5.4  ATTR属性——针对维度字段的聚合判断

在Tableau分中,还有一个特殊的聚合方式:属性ATTR()函数。专门用于在视图中对字符串做唯一性判断。做出聚合字段,它受视图详细级别影响。

在Tableau中,“属性”ATTRIBUTION被用来代表视图详细级别对应的唯一值,比如男/女、户籍地是员工ID的性别属性、出生地属性。如果一个人有多个手机号,手机号就不是一个人的属性,此时ATTR函数就会返回星号“*”,都在该值不唯一。

如图8-41所示,左侧“客户名称”构成了问题详细级别的一部分,而在右侧,“客户名称”从维度改为“属性”,此时每个订单ID成为了问题详细级别,对应唯一的行。如果一个订单ID对应多个客户(这通常是数据采集的问题),那么就会返回“*”星号。

图8-41  ATTR函数返回离散维度的唯一值

初学者可以把ATTR([订单ID])理解为一个判断,这个判断有多种写法,旨在判断是否重复:

IF MAX([订单ID])=MIN([订单ID]) THEN MIN([订单ID]) ELSE '*' END

在分析过程中,经常遇到“聚合和非聚合不能直接比较”的报错提醒,一个解决方案就是把明细判断改为聚合判断,或者聚合判断改为行级别,比如:

IF ATTR([子类别])= "桌子"  THEN  SUM([销售额])<1000 END
SUM(IF [子类别]= "桌子"  THEN  [销售额] END )  <1000

使用ATTR聚合的前提是视图中有【子类别】字段,性能好但不易于控制;使用行级别判断把计算改到明细上完成,易于理解,但性能非常差。选择时要注意。


[1]  此处的“群体方差”就是“总体方差”,属于软件的翻译bug,已经申请修改,正在陆续调整。