分析常识-工具篇：从问题结构看SQL、DAX的共同性

May 23, 2023 V2.3

工具服务于分析，分析的关键是问题，问题的核心是聚合度量（业务中称之为指标）。基于这样的常识理解，本文从问题的基本结构出发，帮助初学者理解SQL的常见语法，并进而推及到DAX的对应语法。

在简单问题的解决上，SQL比DAX要简单的多；但在涉及到中间表、聚合中间表的复杂问题时，DAX就能显示它的优势——毕竟，它为分析而生，而SQL本为“数据库增删改查”而生，二者使命不同。

一、问题分析的普适性结构

May 23, 2023 补充，更多内容可以参考图书讲解。

在喜乐君的《数据可视化分析》《业务可视化分析》一书中，作者特别强调所有问题背后的普适性结构，即：

• 所有问题必然包含筛选条件、问题描述、问题答案三个部分构成；
• 聚合回答问题答案，聚合是分析的本质。

从问题分析的角度看，SQL和DAX就有相通之处。这里引用《数据可视化分析2.0》一书的典型案例，介绍二者的“同中之异”和“异中之同”。

案例：2022年，各个产品类别、各个品牌 的 销售额总和、利润率

首先，这里借助于颜色把问题区分为了三个构成部分，逗号之前、橙色部分是“筛选条件”，“的”左侧、蓝色部分是“问题分类”，“的”右侧、绿色部分是问题的指标、“问题答案”。在分析中，问题分类字段和问题答案字段抽象为维度Dimension和Measure度量，这是分析中最最重要的概念之一。

问题的关键是问题分类（维度）和问题答案（度量），以及它们和筛选之间的关系。在关系的背后，是逻辑的真相。

其次，在看得见的背后，聚合是分析的本质，聚合是从数据明细表到最终问题结果表的、由多变少的过程。

接下来，本文重点介绍SQL和DAX在解决相同问题时的实现逻辑。

二、由浅入深理解SQL和DAX背后的分析本质

Mar 12, 2023 补充，完全重写第一部分；May 18, 2023 修正第一部分 @喜乐君

DAX（Data Analysis Expression）是一种专门面向分析的语言，相比之下，SQL（Structured Query Language）是专门面向数据库查询的语言；因此DAX在分析方面更加强大，甚至以可以在分析中同步完成很多ETL解决的问题（比如聚合中间表及其转化）。不得不说，它很强大——强大到超过了大部分业务用户的逻辑理解范畴，强大到了门槛高到望而生畏。

接下来，喜乐君先用SQL介绍分析的过程和计算方式，然后再把类似方法推及到DAX中。

1、从简入深，理解SQL的功能构成和分析计算

为了一步一步地触及分析的本质，可以先建立假设简化计算，然后逐步增加复杂性。

1）假设有一个交叉表，已经包含了问题中所需要的所有字段（包括利润率），此时分析就仅仅是明细查询

数据仓库中，常常会有很多预先聚合的数据明细，假设有一个“各年、各产品类别、品牌的销售金额、利润率”数据表，那么这里的分析就仅仅是明细查询了。不管是SQL，还是DAX，甚至于Excel都能轻松处理。

年度	类别	品牌编码	品牌	销售额总和	利润率
2022	办公用品	A001	Tableau	10031	0.31
2022	家具	A002	一家	903100	0.25
2021	家具	A002	一家	2001	0.30

SQL发明之处的首要任务不是分析，而是查询（Query），即从数据表中查询所需要的行或列——保留特定行称之为Filter筛选、保留特定列称之为Project投影，二者合称“查找Select”。在这里，可以使用如下的查询获得问题所需的数据：

SELECT 年度, 类别, 品牌, 销售额总和，利润率
FROM  summarize_table
Where 年度 = 2022

当然，这个假设过于理想，绝不会实现，也不能实现，这也是很多公司在数据仓库、BI项目建设中常见的问题之一——希望在数据仓库中把数据表聚合到如此高度，既不现实也不推荐的。

具体来说，其一，“利润率”字段依赖于“利润”和“销售额”字段，它是典型的分析字段，仅在分析中有意义，而在明细行中无意义，它不应该出现在物理表中。其二，过高的聚合表，会让整个分析体系变得复杂。临时的问题应该用临时计算来解决，而不能预先加载到明细表中。预先计算应该用于更加明细的数据加载，而非在问题层面。

更合理的假设，应该是如下：

2）假设明细表中，已经包含了问题中所需要的“业务字段”（年度、类别、品牌、销售额、利润），但是分析过程中的聚合（总和）和聚合比值（“利润率”）需要自定义聚合。

这个假设更靠近业务实际场景，体现了数据明细表和问题表的分离——二者之间的距离，就是分析所要实现的本质。

参考Tableau的“超市数据”，数据表示“每天（年月日）、每个订单、每个产品的销售额、利润明细”，基本结构如下所示：

通常，明细中包含所有的业务字段——也就是在业务过程中直接有所🈯️的字段，比如日期、品牌、销售额等（这里把利润也暂且视为业务字段之一）。基于这样的数据，分析计算所要实现的，就是明细表中过于详细的业务事实（business fact）和问题中高度抽象的业务理解（business abstract）之间的距离。

迈过距离的关键桥梁是聚合计算，即把过于详细的多行数据按需相加计算，获得问题中的求和项。在SQL中，可以使用SUM函数完成聚合，而聚合到的详细级别则要结合GROUP BY来明确。如下所示：

SELECT 年, 类别, 品牌, 
SUM(销售额)，
SUM(利润)/SUM(销售额) AS 利润率 
FROM  summarize_table 
Where 年 = 2022
GROUP BY 年, 类别, 品牌

在这里，喜乐君就要再次强调：“分析的本质是聚合”，或者说“分析计算的本质是分组聚合”。在SQL中，分组聚合需要聚合函数和GROUP BY配合方可实现，这影响了大家理解背后的抽象；后续介绍DAX时会发现，DAX把二者整合在一起推出了SUMMARIZE函数，更好地体现了它面向分析、而非面向查询的工具特征。

不过，假设“数据表中包含所有业务字段”也是过于理想的，在大数据场景下，按照数据库的规范要求（减少冗余、减少依赖等），数据表中通常没有“年”字段（日期），类别、品牌字段也会保存在单独的数据表中（减少重复），更不会有“利润”（利润依赖于销售和费用、成本）。

为此，计算就要增加更复杂的一个类型：多表合并计算，以及从字段到字段的数据准备转换处理。

3、假设“交易明细表”中，仅有关键的业务字段（比如订单日期、产品编号、销售额），产品的属性信息（类别、品牌）单独存放在“产品信息表”中，“销售额”则需要从“数量”和“单价”中计算而来。此时，就需要引入数据表合并或者关系模型。

在SQL中，可以使用JOIN实现数据表的合并，在DAX中对应多表关系。在这里，年、销售额、利润字段需要计算，类别、品牌字段需要数据表合并；前者是字段计算，后者是表计算，一起构成了更复杂的计算体系。

如果用SQL，那么过程可以如下所示：

SELECT  t1.Category, t1.Brand,
SUM(t0.Quantity * t0.Unit Price) as 销售额,
SUM(t0.Quantity * (t0.Unit Price - t0.Unit Cost) )/SUM(t0.Quantity * t0.Unit Price)  as 利润率  
FROM  Sales t0
JOIN Product t1 on t0.Productkey=t1.Productkey
Where YEAR(t0.order date) = 2022 
GROUP BY  t1.Category, t1.Brand

在上述过程中，YEAR函数完成「年月日」到「年」的转换，背后是从字符串中提取特定部分的截取计算；t0.Quantity * t0.Unit Price则实现数据明细中每一行的“销售额”计算，这两个字段是“销售额”赖以存在的业务字段——分析师应该理解，事实表中只记录业务对象，不记录抽象转换。

难点在于JOIN的表计算，from之后的 (Sales t0 JOIN Product t1 on t0.Productkey=t1.Productkey )可以视为一个整体，实现两个数据表的合并，类似于Excel中把Vlookup函数的字段追加。

上述过程，可以用如下的图示展示：

• 分组聚合之前的部分，可以称之为数据准备：包含数据表的join合并，和行级别的计算准备
• 分组、聚合，是分析本身，是分析的本质过程
• 简单的筛选条件，可以视为是数据准备，但是由于它随着问题随时变化，更推荐视为分析的一部分

随着问题的复杂性提高，筛选还可能出现更多类型，比如分组聚合之后的筛选，甚至基于聚合的筛选，这些都暂且不提，它们不影响分析的本质，只是影响聚合表的值大小。

总结一下，

基于上述的SQL介绍，分析师可以更好地理解分析的本质过程和计算类型，概括如下：

• 分析的本质是分组聚合，体现在SQL中是GROUP BY和SUM的结合
• 分组聚合之前的部分，都可以视为数据准备，常见形式有多表JOIN合并，和表中行级别计算
• 筛选影响数据值的大小，可以在明细表中，也可以在聚合之后

2、入门即高端——DAX的解决方案和计算

在SQL中看似简单的过程，在DAX中则略显复杂。究其原因，是因为它为了适应更加复杂的问题，增加了更多的逻辑层次，反而把简单问题也略微复杂化了，从而提高了学习的门槛。这个“复杂”是庞大计算体系升级所付出的必要代价、合理代价。

首先，为了更好地完成复杂问题分析，DAX区分了两个阶段：数据准备和问题分析。

日期计算、销售额计算应该在数据明细中完成，分组聚合、利润率计算应该在问题中完成。不同阶段的计算，在DAX中对应计算列（calculated Columns）、度量值（Measure）。简单的说，前者是数据准备，后者是问题分析。

比如，YEAR(Sales[Order date]) 可以从完整的日期字段中获得年度值，鉴于它在筛选-分组-聚合之前完成，因此可以视为问题分析的事先准备。可以考虑用DEFINE COLUMN定义一个计算列完成，或者直接嵌套放在其他计算中（毕竟它太简单了）。

相比之下，销售额的总和、利润率计算就略显复杂，特别是销售额还需要预先行级别计算（单价*数量）。

假设数据表中有【销售额】字段直接可用，SQL中的SUM(销售额) 可以转换为DAX中的 SUM(销售额)或者SUMX( table_name, sales_field) ——SUM是SUMX的特殊简化形式，完成指定工作表下的指导字段聚合。

假设数据表中没有【销售额】，只有它依赖的数量Quantity和单价Unit Price，此时SQL中可以使用嵌套组合完成，而DAX中必须使用SUMX指定数据表、指定计算来完成，如下所示 （to和sales指代数据表名称）：

SQL表达式：SUM(t0.Quantity * t0.Unit Price) as 销售额,
DAX表达式：SUMX( Sales, Sales[Quantity]*Sales[Unit Price]) 

这个看似简单的计算，其实是行级别计算（[Quantity]*[Unit Price]）和聚合计算的组合形式，在DAX中有一个更复杂的概念：迭代计算——先完成每一行的行级别计算，而后逐一相加完成聚合。

特别特别注意的是，数据准备、问题分析并不直接对应行级别计算、聚合计算，二者是交叉的体系。也就是说，数据准备不一定都是行级别计算，问题分析也不一定都是聚合，这些复杂场景，以后择时介绍，这也是DAX最难理解的部分。为了理解这个内容，就不得不引入一个更加抽象的内容：两类计算对应的Row Context、Filter Context环境。

其次，DAX需要实现建立两个表的关系，在多表关系模型中处理跨表计算。

SQL使用JOIN指定表，结合ON指定合并条件；而在DAX中，合并条件需要在模型中事先匹配，函数中使用RELATED或者RELATEDTABLE直接引用指定数据表的字段值——也就是它把建模和引用分为两个环节来完成。

• 在data model中，预先设置多表之间的匹配关系，包括字段匹配、基数、引用完整性
• 在分析过程中，使用RELATED引用表和字段值，比如RELATED ( ‘Product'[Category] )和RELATED ( ‘Product'[Brand])

相比前面的字段字段，这里的表操作计算更加抽象，这里不展开。

在完成数据表合并和字段计算之后，问题中需要的所有元素才算准备齐备，接下来才是分组聚合过程。

再次，创建聚合表，其中至少要有分组依据（类别、品牌）和聚合度量（销售额），必要时还要有FILTER筛选条件。

分析的本质是分组聚合，SQL中使用 GROUP BY分组和SUM聚合结合实现，而在DAX中，分组聚合把进一步封装为一系列的表操作函数（table manilulation functions），比如SUMMARIZECOLUMNS。在函数内部进一步指明聚合字段、分组字段。比如：

SUMMARIZECOLUMNS (
'Product'[Category],
"Sales Amount", [Sales Amount]
)

和SQL采用的字段操作逻辑不同，DAX采用了表操作的逻辑，即从数据表table到数据表table，这增加了理解复杂性，但在处理复杂问题时又更加容易理解，可谓有得有失。最常用的表操作函数有SUMMARIZE函数和SUMMARIZECOLUMNS 函数，它们可以完成分组、聚合，只是SUMMARIZE不能“无中生有”添加新字段，所有后者使用更加普遍。

类似的表操作函数还有ADDCOLUMNS（在已有表基础上，添加字段列），以及CALCULATETABLE。初学者可以把它们视为SQL查询的二次封装。

SUMMARIZE——The GroupBy_ColumnName must be either in table or in a related table to Table. SUMMARIZE should not be used to add columns. As an alternative, use SUMMARIZECOLUMNS or ADDCOLUMNS / SUMMARIZE.

SUMMARIZE does not preserve the data lineage of the columns used in ROLLUP or ROLLUPGROUP, raising an error if such columns are later used in the filter context.

DAX Guide

基于上述的层层分析，最后借助于DAX的DEFINE和EVALUTE结构可以完成如下分析：

-- RELATED 用于获得另一个数据表的指定字段
-- 
DEFINE
-- COLUMN  year_dt= YEAR(Sales[Order Date])
MEASURE Sales[Sales Amount] =
SUMX ( Sales, Sales[Quantity] * Sales[Unite Price] )
MEASURE Sales[Gross Sales] =
SUMX ( Sales, Sales[Quantity] * (Sales[Unit Price] - Sales[Unit Cost]  ) )

EVALUATE
SUMMARIZECOLUMNS (
 Related('Product'[Category]),
 Related('Product'[Brand]),
 "Sales Amount", Calculate ([Sales Amount], YEAR(Sales[Order Date])=2022)
 "Gross Sales%", Calculate ( Sales[Gross Sales]/[Sales Amount], YEAR(Sales[Order Date])=2022)
 )

幸好DAX有DEFINE定义，让原本复杂的DAX表达式有了清晰、简洁的表达方式。

当然，在Power BI Desktop中，可以借助拖拽操作简化这里的DAX难度，拖拽转化为DAX语言，然后与数据库查询交互。不过，倘若不能理解DAX 背后的逻辑，拖拽也无法解决复杂问题，反而容易迷失在拖拽的简单表象之中。

借助于上述层层递进的案例解读，读者应该可以理解SQL和DAX背后相同的计算体系——左手边是数据准备，右手边是聚合分析。区别在于DAX“极端强调”表模型和计算中的“表引用” ，不像SQL中表计算、字段计算相对分开，所以初学者的入门异常陡峭。

沿着这样的框架，我们可以进一步开展开计算的阶段、分类，最终构建完整的体系。

下文：

分析常识-工具篇：DAX计算体系——计算的分类矩阵

喜乐君

V1 Mar 7, 2023
V1.1 Mar 9, 2023 修改
V2.0 Mar 12, 2023 重写第一部分
V2.1 Mar 20, 2023 更新图片
V2.2 May 18, 2023 细致修改第一部分内容，单独发布知乎
V2.3 May 23, 2023 原文第三部分（DAX计算）单独拆分成为