跳至正文

分析常识:业务、分析角度看计算的基本分类(初级)

标签:

注:摘自喜乐君《数据可视化分析2.0》一书,作为整个系列中一部分。

8.1.1  计算的本质及其与业务过程的关系

计算是“从无到有”的过程,因此说计算即抽象。不同计算的抽象化程度有所不同。

比如从订单日期中计算“年”,对于每一行都是确定性的,结果在明细上有意义、在业务过程中有对应,因此说抽象化程度较低。而从多行中聚合计算获得“销售额总和”(Excel称之为“求和项”),甚至两个总和进一步获得“利润率”(利润总和/销售额总和),这些聚合在明细表上无意义、在业务过程中无对应,而且随着问题变化结果自动变化,因此说聚合计算的抽象化水平更高。

本书“分析是对业务和数据表的抽象、升华”,指的是包含聚合的高级抽象。

高级分析师务必要从技术、业务多重角度理解计算的分类及其关系。既要理解对应的计算逻辑、功能语法,更要理解彼此的关系、组合。如图8-1所示,勾勒了第3篇的计算分类体系及关键要点。

图8-1  计算的分类体系和计算逻辑

从计算的功能和抽象化水平看,计算可分为两种基本类型。

  • 数据准备(Preparation:在数据明细表中,借助计算、数据表关联合并等方式对原始数据的清理、转换、分组等处理,类型多样,抽象化水平较低,数据在明细上有意义。
  • 聚合分析(Analysis:以问题为引导,从数据表明细分组聚合为少数数据、并可进一步比较计算回答问题的过程,聚合可以是一次聚合、多次聚合,从而实现高级抽象。聚合结果仅在指定问题上有意义,问题是对业务的抽象。

如果站在数据表的角度,数据准备都是对每一行数据而言的,计算不会跨行[1];聚合分析是相对于问题而言的,计算一定会跨行,因此,笔者常把计算分为“行级别计算”和“跨行计算”。

  • 行级别计算(Row Level Calculation:在单一明细行(record)中实现的计算,比如YEAR([订单日期])和[销售额]-[成本额]计算,单行的计算是低级抽象的计算。
  • 跨行计算(Multi-row Calculation):跨多个明细行(record)的计算,如SUM([销售额]),RANK( SUM([销售额]) ),前者是明细表的聚合,后者是聚合表的排序计算。

本章中,除非特别强调,聚合均指SUM、AVG为代表的直接聚合,基于聚合值的二次聚合、相对于指定详细级别的预先聚合,则是后续章节的任务。

充分地理解“计算即抽象”,再从业务和抽象程度角度理解计算的分类,是业务用户进入大数据分析的关键。本书借Tableau和SQL解读分析背后的原理,帮助读者跨工具地掌握业务分析。

8.1.2  以Excel理解详细级别与计算的两大分类

以超市数据为数据源,用Excel[2]完成如下简单、代表性的分析题目:

“2021年,各类别、各品牌的销售额(总和)、(合计)利润率”

业务过程映射为数据表,在数据表中完成计算分析。整个思考过程如下:

首先,理解数据表及其对应的业务过程。

数据表明细行是业务过程的反映,是分析聚合的起点。在超市数据中,「销售明细表」的每一行代表一笔完整的业务交易,描述“谁、在何时、何地、给谁、以何种方式、提供了什么产品(交易过程),及该交易的度量属性(数字记录)”,数据明细表的唯一性用“订单ID*产品ID”表示。

如表8-1所示,展示了数据表的部分关键字段。

其次,确认分析需求,分解每个问题的构成

业务需求分解为问题;每个问题必然包含三个部分(筛选范围、问题描述和问题答案)。数据合并或计算弥补数据表或问题中的字段不足。解析如下:

  • 筛选范围:“2021年”代表问题的分析范围,对应【订单日期】字段,它是“订单日期在2021年”的简化形式。
  • 问题描述:维度描述问题,这里的“类别、品牌”构成“问题详细级别”(Viz LOD),问题详细级别是答案聚合的分组依据。缺少的“品牌”字段需要借助计算弥补。
  • 聚合答案:聚合是分析的本质。聚合的数据值从明细中计算而来,对应一个虚拟的聚合表。“销售额总和”对应SUM求和聚合函数,合计利润率则是两个聚合值的直接比较。

再次,使用计算弥补数据表中字段的不足,而后完成汇总分析。

  • 筛选范围:数据表中【订单日期】对应类似“2021/6/15”的精确日期,并没有年度字段“2021”。年月日要先转化为年,而后完成筛选,这个过程就是“提取日期部分函数”和逻辑判断的计算过程,即YEAR([订单日期])= 2021
  • 问题描述:问题中缺失的“品牌”可以从字段【产品名称】中拆分计算,也可以借助其他数据表合并而来(比如从“产品信息表”中匹配获得品牌信息),计算的方法优先于数据合并。这里使用SPLIT拆分函数或LEFT函数完成。
  • 聚合答案:问题中的“销售额(总和)”来自数据表中的【销售额】字段直接聚合。“利润率”则需要利润总和、销售额总和的聚合比值,是聚合函数和算术计算的组合,可以理解为包含聚合函数的表达式(SUM([利润])/SUM([销售额]))。

在这个过程中,每个部分都需要借助于计算,计算肩负两大功能:数据准备(弥补数据表字段不足)、聚合分析(抽象概括,弥补问题中字段不足)。

“品牌”从【产品名称】字段拆分而来,可以使用LEFT函数取第一个空格左侧的部分(暂不考虑其他特殊情况)。如图8-2,在明细行后面增加辅助列字段“品牌”,完成计算。

= LEFT(N2,FIND(” “,N2))

图8-2  Excel数据明细中增加计算,补充“品牌”

由于每个产品名称必然对应一个品牌,计算是在每一行上执行的,绝对不会跨行(即N3单元格的产品名称,必然对应R3单元格的品牌),这种计算称之为“行级别计算”(row level calculation)。

相比之下,“销售额(总和)”和“利润率”是完全不同的计算逻辑,要在透视表中完成。

如图8-3所示,在WPS Excel中插入“数据透视表”,默认打开新工作表,将【销售额】和【利润】度量字段拖入到“值”,将【类别】和【品牌】拖入到“行”,就会生成如下的聚合。以“技术”下的“贝尔金”为例,它的销售额(2632)是两个值相加而来的(1195+1137),是典型的纵向跨行计算,而非水平的单行计算。其他聚合同理(这里样本小,部分是一个值自己相加)。

图8-3  Excel数据透视表实现两个度量的聚合

计算的关键是如何计算“利润率”。

准确的说,这里的“利润率”是“各类别、品牌的利润率”,是各类别、品牌的利润总和与销售额总和的比值,而非“每一行的利润率”。聚合在“透视表”完成。

如图8-4所示,在菜单选项“分析-字段、项目”选择“计算字段”命令(见图8-4位置①),弹出“插入计算字段”对话框,在“公式”中选择字段并插入,完成计算公式“=利润/销售额”,③计算字段【透视表-利润率】会添加到数据透视表中,增加了第三个度量列(见图8-4位置③)。

图8-4  在Excel数据透视表中增加聚合的计算字段

注意,利润率计算看似没有“求和项”或SUM等聚合方式,其实是默认聚合的,后面详解。

Excel透视表中自动增加“小计”和“总计”,13.5%的总计利润率,是1899/14040的求和项比值。熟悉Excel的读者也可以调整透视表中的行列字段,实现不同层次的求和项计算,如图8-5所示,在不同详细级别的透视表中,合计利润率都是相同的(13.5%)。数据透视表实现了同一份数据、不同视角的层次分析,不管是最高详细级别分析(见图8-5位置①),还是每个类别(见图8-5位置②)。

图8-5  在Excel中对不同层次问题的“透视分析”

这里重点说一下“透视”。中文的“透视”本是一种绘画手法,“在平面上表现物体的空间位置距离和轮廓投影”,也被称为“远近法”,与业务探索、钻取分析(roll up and drill down)的精神相契合,也许这就是中文翻译“透视”的来源。业务分析需要在众多的维度字段中切换分析视角,分析层次有高低,如同视角有远近;问题之间的钻取变化,如同相机拍摄中镜头之远近。

不过,“数据透视表”的英文是Pivot Table,pivot的本意是“支点、中心”(名词)、“使旋转”(动词),后来延伸出了“核心任务”“改进以符合特定需求”等抽象含义[3]。具体到数据领域,pivot本意是“转置”,在数据存储还在软盘阶段的早期年代,Pivot Table用于交换明细中的列字段位置;随着数据量的增加,透视表的功能从“明细行转置”升级为“先按需聚合再转置”。大数据时代,透视表功能受限,它无法完成多表合并,特别是先聚合再合并等高级场景,于是微软开发了Power Pivot,泛指数据建模技术,用于创建数据模型、建立关系,以及创建计算,中文翻译“超级透视表”。

从转置、按需聚合,到多数据模型处理,pivot的范围越来越广泛,英文pivot含义足以概括这一切,中文的“透视表”和“超级透视”都很难代表如此广泛的功能。甚至《新华字典》都没有收录透视的数据转置、聚合含义,“表哥表姐”要当心莫被“透视”的翻译影响了对聚合的理解。

说这么多,旨在帮助大家理解透视背后的本质——聚合,并理解聚合的过程。

Excel透视是从数据明细表到数据透视表的由多变少的过程,透视即聚合,聚合回答问题。

Excel是很多分析师进入数据分析世界的第一站,借助于Excel的明细表和透视表,分析师可以构建如图8-6所示的计算分类。“数据明细表”用于记录业务运营过程,而“数据透视表”的分析则用于辅助业务决策。熟悉它们的分类和逻辑对应关系,是进一步理解SQL和Tableau逻辑的基础。

图8-6  清晰区分Excel的两个数据位置、两类计算

接下来,从Excel的局限出发理解SQL,再从SQL局限去理解Tableau。


[3] 《欧陆字典》在线版 Pivot n. (1) axis consisting of a short shaft that supports something that turns 支点、枢纽(2) the person in a rank around whom the others wheel and maneuver 核心,最重要的人;v. turn on a pivot(使)在枢轴上旋转;(以脚为支点)转身;(为机械装置)提供枢轴

[2]  本书Excel使用金山公司的WPS Excel,逻辑与MS Excel基本一致。

[1]  这里的数据准备,指狭义的字符串拆分、查找等计算,不涉及先聚合再合并的复杂情形。