分析常识-2：分析的本质过程、问题的基本结构_SQL视角

本文属于“分析常识”系列文章之一：

1-分析常识：数据库、数据表、数据查询 ——所有工具背后的基础
2- 分析常识：分析的本质过程、问题的基本结构（本文）——理解分析和问题结构
3- 分析常识：分析中的计算分类（高难度） ——分析中的计算体系
3- Power BI：全新角度理解DAX计算 ——基于新的框架理解DAX计算
4- SQL：SQL的计算体系（待定）

Mar 11, 2023 修改
Mar 19, 2023 修改

上一篇文章，喜乐君介绍了数据库、数据表、数据查询的基本知识。其中，数据分析是特殊的查询过程，它的特殊性体现在分组聚合（aggregation）上，以聚合为中心，可以构建完整的函数和计算体系。

本文介绍分析的聚合过程，并分析影响聚合的多种要素，从而帮助任何人逐渐理解分析的精髓。

一、查询Query和分析Analysis的“边界”：分组聚合是关键

广义的角度看，数据库和计算机（computer）中所有的操作都是计算（calculation，computation）。在数据分析中，所有的查询都是计算，准确的说是“表查询”——分析必然是table 到table的 table query过程。

“数据查询”（query）是广义的概念，“数据分析”（analysis）是其特殊的形式，其特殊之处是包含聚合，也就在数据明细查询基础上，增加了抽象化的聚合计算，比如SUM、AVG等。

1、分析的本质过程是聚合

数据分析的过程，是从数据表查询数据并根据问题处理、转换、归纳展现的过程，分析的重点是对数据的抽象归纳。

比如，用“100万”描述公司昨日的“销售额总和”，它代表了公司的营业规模，而不关心背后是多少笔订单、多少位客户。再比如，用“25.32%”描述公司开业以来的“合计利润率”，代表了公司每增加1元销售带来的边际利润贡献。这个用极少数据，概括极多数据的背后，是对数据库中记录的几百万数据行的抽象概括。

在数据分析中，最基本的数据抽象方式是聚合函数及其计算，比如SUM、AVG、MAX，及其比值计算。对应常见的指标：销售额总和、利润率（SUM利润/SUM销售额）。

从技术的角度看，分析的过程是从“数据明细表”（detail table）到“问题结果表”（result table）的聚合查询过程。这个过程必然伴随分组、聚合——这是不可省略的部分，可能夹杂着筛选、排序、可视化，它们都是对数据的抽象方式（abstraction）。由于“聚合”是分析的关键特征，因此“结果表”常常被称为“聚合表”（aggregate table）。

举例而言，假设数据明细表是“每位作者的图书信息”，问题是“每位作者的图书数量（计数）”。

在Excel中，聚合的起点和终点，可以对应明细表和透视表，如下所示：

而在 SQL中，聚合的起点和终点对应FROM后的“底表”（base table）和SELECT最终生成的查询结果（query table)。假设数据库的数据表为books，那么“各作者的图书数”可以借助于如下的查询实现：

SELECT author, COUNT( bookname ) -- 分组和聚合
FROM books        -- 查询明细表
GROUP BY author;  -- 分组依据

在可视化BI工具中，数据查询和聚合的过程会被拖拉拽“掩盖”，但并没有改变分组透视、分组聚合的本质。在Tableau中，可以借助于“性能记录器”查看拖拽背后生成的SQL过程；而在Power BI中，每个可视化也可以用DAX代码实现完全对应的聚合表效果。

可见，不管是传统的Excel，还是SQL，或者可视化的Tableau、Power BI，聚合都是构成问题分析的核心。也正因此，只有包含聚合的查询才能称之为分析（analysis），否则仅能称之为查询（query）。

“你叫什么名字”只是查询，“你去过多少地方”才叫分析，它实现了数据的抽象转换。

因此，我们可以把“聚合”（aggregation）视为分析的本质过程。

Data aggregation is a process where data is collected and expressed briefly in a summarised format.

Aggregation is the core process of analysis , which is high level abstraction of data to answer specified questions.

这有点像《小王子》中的话，“看不见的才是最重要的”，如何表达抽象，正是分析通识的关键之一。既然学习可视化，喜乐君推荐使用如下的可视化方式理解这个过程：

分析是不同抽象程度的数据转换，自下而上，代表数据的抽象过程、分析过程
分析抽象主要以聚合实现；分析是从“数据明细表”到“问题聚合表”的、由多变少的聚合过程
不同问题的抽象程度不同，即“聚合度”不同；聚合度是衡量多个问题抽象程度的“公共尺度”

二、以聚合为中心的分析计算体系

既然“聚合”是分析的本质，接下来，就可以以此为中心构建整个分析的框架了。考虑到聚合的起点和终点，分析计算可以分为两类：

数据明细表中的计算：在分组聚合之前完成的所有准备性计算
生成问题聚合表的计算：伴随聚合过程，以及聚合之后而来的计算

在这里，我们先假设数据表非常完整，已经包含了分析中可能用到的所有字段，不管是年、月、日等日期部分，还是销售额、发货间隔，甚至“客户获客日期”这样的特殊字段——问题分析中数据明细表中应该出现的字段，都可以假设有一位“神妖”立刻帮你解决。这样，接下来就可以专注于聚合本身，理解影响聚合的要素及其关系。

站在问题的角度看，影响聚合值多少、大小的因素有很多，这里按照难易程度依次介绍：

1、分组聚合——分析的本质和必然部分

最简单的问题似乎是“公司的销售额总和”，它只需要一个数据值即可回答。

Tableau中，可以直接双击聚合值生成最高聚合；而在一些BI工具中，专门为此设计了“指标卡”，如下所示。单一的销售额聚合值，技术上可以视为“单行单列”的数据表，业务上可以视为“最高抽象程度”的分组聚合——分组依据只有一个字段，即“公司”，它甚至于无需出现。

随着问题分类维度变化，分析就从抽象走向具体，比如“各个类别的销售额总和”“各个类别、子类别的销售额总和”。和之前问题相比，它们具有完全相同的聚合度量（销售额总和），但是分组依据截然不同，这就让结果的数量有了明显差异。如下图所示，使用Tableau Desktop展现了上述两个问题的可视化结果。

正因为此，聚合可以视为“分组聚合”的简称——分析中“聚合”必然相对于“分组依据”而有业务意义。

在SQL为代表的代码语言中，分组、聚合视为两个独立的部分、独立的计算，并且，分组计算（GROUP BY）优先于聚合计算（SUM）。上述的销售额分组聚合，可以用SQL如下完成：

SELECT  类别, 子类别,  SUM(销售额)
FROM superstore
GROUP BY 类别, 子类别;

在SQL中，计算按照“FROM——GROUP BY——SUM ——SELECT”的优先级执行。

2、筛选并分组聚合

上述的聚合结构最为简单，但在业务中不够典型。典型的业务问题由三个部分构成，在分组、聚合基础上，增加筛选范围，如下所示。

从不同的角度，问题的构成对应不同的含义。

从业务角度看，典型的问题是由分析范围scope、分析对象object、分析指标metrics构成的；

从技术角度看，典型的问题是由筛选条件filter condition、分组依据group（详细级别）、聚合度量measure构成。

在不同的工具中，上述用词各不相同，这也是阻碍分析成为一门专业、阻碍构建分析标准方法的原因。这里重点是理解三者之间的关系。

“分组依据”主要决定“聚合度量”的数量，比如类别的销售额有3个值，子类别的销售额有15个值
“筛选条件”主要影响“聚合度量”的大小，比如2020年和2021年的聚合值就截然不同。
某些情况下，筛选也会影响分组和聚合值的数量，比如筛选字段和分组字段有依赖关系，或者筛选范围过小。不过这种关系通常是间接的、非主流的。

同时包含上述三个部分的典型SQL如下：

SELECT  类别, 子类别,  SUM(销售额) 
FROM superstore 
WHERE 年度 = "2021" AND 地区 = "东北"
GROUP BY 类别, 子类别;

在计算机、数据库的技术实现中，基于计算性能的考虑，筛选具有优先性，它可以有效地减少后续计算的数据量。正因为此，SQL的WHERE子句先于GROUP BY和聚合函数执行。

3、筛选、分组聚合，并再次筛选

在上述经典的三分类基础上，还可以进一步增加一些差异化筛选条件，比如在聚合前增加包含聚合的筛选（比如销售总和TOP10的产品），或者在聚合后进一步筛选。由于前者可以视为数据合并的特殊形式，这里不做展开。

聚合后的筛选是常见的筛选类型，它可以视为“聚合表”上的明细判断，影响聚合表最终展现的行数；在SQL中，对应having子句部分。聚合前的明细筛选对应Where子句，比如如下的案例：

SELECT  类别, 子类别,  
SUM(销售额) 
FROM superstore  
WHERE 年度 = "2021" AND 地区 = "东北" 
GROUP BY 类别, 子类别
HAVING SUM(销售额)>1000 ;

在这样的略微复杂的问题面前，使用者必须清晰地理解它们的不同：

聚合前的明细筛选，在SQL中对应Where子句，它是基于FROM引用数据明细表的筛选，筛选条件中不能包含聚合

聚合后的明细筛选，对应SQL的having子句，它是基于SELECT结果表明细行的筛选，筛选条件可以包含聚合

前者可以称之为“明细筛选”，筛选对象是base table；后者可以称之为“结果筛选”，筛选对象是result table。虽然筛选都是明细行的筛选，但二者筛选的对象是不同的，前者是物理表，后者是逻辑表。

4、在分组聚合基础上，添加延伸计算

业务分析的很多指标都是建立在聚合之后的计算，比如利润率、平均票价、YTD同比等，因此聚合值的二次计算，是完成业务分析指标的必由之路。

按照二次计算是否跨行、是否聚合，又可以分为几种类型：

不跨行的计算，比如SUM利润/SUM销售额
跨行、非聚合计算，比如聚合值的排序计算，RANK（SUM利润）
跨行、聚合计算，比如SUM（SUM利润）

其中，第三类代表了更高抽象层次的分析过程——多个详细级别的问题合并，它的背后可以视为数据表的合并计算。鉴于抽象难度，后续单独阐述。

如下的SQL实例中，喜乐君在SELECT之后增加了“利润率”、“类别内销售排名”两个延伸计算，它们都是在分组聚合之后的，因此必须位于SELECT之中。

SELECT  类别, 子类别,  
SUM(销售额) ,
SUM(利润)/SUM(销售额)  AS 利润率,
RANK(SUM(销售额)) over (partition by 类别) 类别内销售排名
FROM superstore  
WHERE 年度 = "2021" AND 地区 = "东北" 
GROUP BY 类别, 子类别
HAVING SUM(销售额)>1000 ;

更复杂的计算可以完成更高级的业务指标，比如复购率、会员状态迁徙等。深入理解相关内容，需要系统地掌握聚合、度量、指标、KPI的关系，喜乐君也单独成文。

>>> 分析常识：度量、事实、聚合、指标、KPI （后续补充）

4、在当前问题中引入其他问题的结构分析

上述的所有计算，都是围绕一个聚合而来的，高级问题中可以包含多个分组聚合过程，此时就会出现技术上的嵌套合并。这一内容，需要在掌握聚合度、详细级别、数据合并等更高级的分析方法后方可展开。

技术上看，影响聚合值大小的筛选、分组，以及问题中的聚合部分，都可以引用其他问题的聚合，构成结构性关系，典型代表是购物篮关联分析、会员RFM分析和客户购买力分析。

聚合前包含聚合的筛选（FROM中增加嵌套引用）
问题中引用其他详细级别的聚合（比如不同频次的客户数量）
聚合中引入其他详细级别的聚合（比如各省份的客户数量、客户购买力）

》〉》分析常识：聚合度、详细级别、结构分析

至此，本文就从计算的角度，介绍了分析查询的本质过程（聚合），再围绕聚合介绍了问题的构成及其计算。

只是，本文建立在一个非常理想的假设之上——数据表中已经包含了问题中可能用到的所有字段。这在实际业务中明显是不现实的，为此，就需要在分析的同时，借助于计算弥补数据表字段的不足，如同在问题之后计算“利润率”“类别内销售排序”一样。这将是一个更大的计算话题。

沿着这条路，就能理解计算的两类阶段、两类目的，并构建整个分析计算和函数的大厦。

喜乐君
Feb 26, 2023
Feb 28, 2023 修改第一部分