分析通识-5：分析查询的“表操作”_SQL为例（高难度）

本系列第1篇文章，喜樂君介绍了数据查询的两个视角：table到table的“表操作”过程，及其中间穿插的“字段转换”。后者普遍称之为“字段计算”，典型代表是LEFT、DATEPART等字段函数。在上一篇文章（第4），喜樂君介绍了字段计算的分类方式和阶段，总结了字段计算的“分类矩阵”（分析通识-4：分析的“字段计算”分类（高难度））；本文将进一步回归table数据表本身，深入介绍“表操作”的原理和计算类型。

一、从数据查询的方式，理解计算的过程和转换

数据查询的过程，都是从table到table的过程，因此，查询的本质都是“表操作”（table manipulation）。

1）表的直接查询和“赋名”

在SQL中，应用最为广泛的查询当然是SELECT和FROM，它的结果就是指定列名（column）的临时数据表，起点是FROM对应的表。查询过程中，常常辅以明细行的过滤筛选（filter records），因此 SELECT- FROM- WHERE是最常见的组合。

如下图所示，描述了一个典型的查询过程，我们从superstor-en数据表中，查询了三个字段/列（columns/fields），并增加了WHERE Product Name= “Newell 322″的明细行筛选。SELECT的查询默认是没有名称的，是临时表；借助于CREATE VIEW可以创建一个“视图名”，虽然还是临时表，但是有了方便检索的名字。这就像神仙还是那个虚构的神仙，但是有了“二郎神”的名号。

在数据查询中，可以使用AS为“数据表”和“字段计算”赋予名称，这种把逻辑对象具体化的方式，是让问题简单的关键手法；同时，“别名”并没有改变虚拟对象的本质。这就好比父母给孩子随机编了一个长长的故事，为了方便孩子理解，把这个故事赋予了名称，就叫“爷爷的电视被老鼠偷走了”，并且为带头的老鼠取名叫“阿黄”，而把它的爸爸取名为“阿嚏”。命名没有改变故事是虚构的基本特征（哪怕故事的主人公叫“喜乐君”），只是让一切更加清晰。

分析师既要理解逻辑的重要性，又不能因为有了特定“名称”而把逻辑对象视为“真实”，对于理解后续更复杂的逻辑过程非常重要。

2）表查询过程中的“聚合转换”

当然，上面的查询只有明细的查询，而无结构上的抽象处理，这就导致很多重复行——一个产品名称会对应很多很多的Quantity数值。

为此，数据查询之后总是要有很多结构上的“转换操作”（transform），最经典的就是把“每个Product Name的多行Quantity累加起来”，从而获得“每个Product Name的Quantity求和”。

如下所示，在明细查询基础上，使用SUM+GROUP BY的组合，SQL可以轻松完成分组聚合。我们可以把这个转换过程（transform process）视为“表操作”——从2121行的明细表变成了4行的聚合表。可见，聚合是对数据表的整体转换过程。

对于初学者而言，为了更加具体地理解上述过程，可以使用SQL工具查询每个阶段的数据表，对照每一步的查询结果，从而理解计算和表转换之间的对应关系，如下所示：

在这里，可以把聚合过程理解为“数据表的分组聚合”。

分组聚合可以理解为两个字段的操作：维度字段分类、度量字段聚合。换句话说，表操作的起点和终点虽然是table（数据表，二维数组），实现上也以字段（或者说一维数组）为基本操作对象的，只是每一步字段操作，都会引起整个表的变化。

高阶的计算通常可以用基本计算及其组合来理解，就像AVG是SUM和COUNT的组合，方差又是AVG、COUNT的进一步组合。在分析过程中，面对复杂的“表操作”计算，可以用字段来简化理解。

上述只是介绍了“表操作”的两个典型案例，接下来，喜乐君先介绍数据查询、数据合并的经典“表操作”，而后介绍查询基础上的聚合、筛选、排序等转换处理。

二、“表操作”类型详解（上）：单表查询和跨表合并

在保持关系数据结构不变的前提下，表操作可以分为“单表操作”和“跨表合并”两大类，前者称之为查询（query），后者称之为合并（combine）。根据行列两个方向的特征，又可以进一步细分如下：

1、数据表查询的两种方式

为了方便理解，这里可以用一个极简的数据（喜乐君/喜乐的出行记录和个人信息）举例。如下图所示：

从关系数据表的结构来看，查询有两个基本的类型：查询特定列、查询特定行。我们分别称之为“投影”和“筛选”，在SQL中虽然都是用SELECT完成，但语法上又有明显差异。

1）投影列PROJECT

“投影”首先是一个数学概念，“投影几何”（projective geometry）研究在各种变换背后不变的线性关系。通俗地看，我们也可以把物体由于光源而产生的影子视为一种投影——生活中常用的“倒影”是站在物体角度，“投影”则更像是站在光源的立场。

在关系性数据库中，任何一个数据表都是关系的一个子集，我们也可以想象一只“冥冥之手”在完成这个投影操作。对于数据查询而言，站在操作者角度，“投影”是从关系数据表中保留指定列（specified columns）的操作，是分析中最为高频的操作之一。在SAP等典型的ERP系统中，很多数据表都是几百列字段，指定字段并完成后续的合并、过滤等是高频操作。

在SQL历史早期，投影有专门的查询字符，后来逐渐被SELECT代替。比如如下的查询完成“保留特定字段练”。

SELECT
name, date, days
FROM Table_a

投影只会影响数据表的字段列（自上而下），但不会影响数据表的行。保留特定行是更加复杂的操作，称之为“筛选”。

2）筛选行FILTER- WHERE

筛选的背后是布尔逻辑（真/伪），是保留计算条件为真的数据行。从构成上看，筛选又可以分为筛选对象、筛选条件两个部分——这在复杂的筛选逻辑面前尤为重要。

在SQL中，常见的筛选有WHERE 和having 两种方式，它们的差异在于，前者是对聚合前底表的筛选，后者是对聚合后逻辑表的筛选，这里仅介绍WHERE的查询筛选，HAVING筛选列入后续“转换”操作中。比如：

SELECT *
FROM Table_a
where date='2022-2';

要注意的是，虽然筛选条件是以字段为操作对象，筛选而来的联动作用却是作用到整个表上。这个过程，可以用excel来理解。如下所示，当筛选“装运模式=一级”时，所有符合条件的明细行都会被保留。

也就是说，“筛选的操作面向字段，但筛选的结果是数据表”。理解并把握这个基本逻辑，在使用DAX或者嵌套SQL时，才不会迷失在复杂的抽象过程中，典型的应用是DAX的FILTER筛选函数。

2、数据表合并

1）为当前数据表追加新列 JOIN

2）为当前数据表追加新行Union

3、数据表结构调整——Pivot转置

前面的论述，都建立在“数据表结构不变”的前提下，即查询或合并都不改变关系数据的基本样式。如果不考虑这个大前提，表操作就会出现一个新的操作类型——数据表的结构调整，典型是“转置”Pivot。

不过，由于转置等不属于典型的分析操作，通常是针对脏数据向规范数据的转换，因此这里不做展开。

小结，不管是数据查询，还是数据合并，它们都遵循一些共同的规则，具有类似的特征：

• 数据表中每一行都要逐一参与计算，明细行之间相互独立，没有跨行计算
• 表操作所针对的数据表，都是真实存在的物理表（physical table）

三、“表操作”类型详解（下）：聚合、筛选、排序

在表操作过程中，出现了跨行的计算，比如求和SUM、排序RANK、分片Tile等。它们的背后，都包含数据分组过程。

1、分组聚合SUMMARIZE

分步骤理解聚合：

聚合是中心，其他都是围绕聚合而来。

2、虚拟表的筛选

和此前的明细表筛选一样，这筛选条件都是作用在字段上，但是对关联的数据表起作用。

区别在于，这里的字段是“逻辑字段”（比如sum(days)），对应的表是“逻辑表”，也就是并不真实存在的数据表。

V1.0 Mar 21,2023
V1.2 Mar 23,2023 升级第二部分
V1.3 Mar 28, 2023 调整第二部分，补充表计算