分析常识：业务、分析角度看计算的基本分类（初级）

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

• 1-分析常识：数据库、数据表、数据查询 ——所有工具背后的基础
• 2- 分析常识：分析的本质过程、问题的基本结构——适用于所有工具的分析过程
• 3- 分析常识-工具篇：从问题结构看SQL、DAX的共同性 ——基于新的框架理解SQL和DAX计算
• 4-分析常识-工具篇：DAX计算体系——计算的两大阶段、上下文体系 —— 数据准备与问题分析
• 5-分析常识：业务、分析角度看计算的基本分类（初级） —— 计算的基本分类
• 6- 分析常识：分析中的计算分类（高难度） ——字段计算的分类形式和分类矩阵
• 7-分析常识：分析查询的“表操作”_SQL为例（高难度） –计算的本质过程是“表操作”

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]  这里的数据准备，指狭义的字符串拆分、查找等计算，不涉及先聚合再合并的复杂情形。