迭代计算（上）：迭代、X聚合与详细级别

V1 Jan 9, 2023 喜乐君
V2 Jan 14, 2023 增加排序等
V2.1 Mar 7, 2023 更正错误
V2.2 Sep 3, 2023 补充修改

• 迭代计算（上）：迭代、X聚合与详细级别 2023/1，2023/9修改
• 迭代计算（中）：DAX迭代计算的分类 2023/2
• 迭代计算（下）：DAX“迭代”最后一次说明：“是”与“不是” 2023/3

“SQL中没有赋值或者循环的处理，数据也不以记录为单位进行处理，而以集合为单位进行处理。SQL和关系数据库的思维方式更像是一种整体论的思维方式。” ——SQL进阶教程

在学习Power Bi的过程中，会发现很多常见于编程的概念，比如迭代iteration、赋值（var）、定义define等，这也是它不同于Tableau、SQL的关键特征。而要完全理解其中的DAX语法，就先要理解这些关键概念。

本书先说迭代（iteration），对应迭代器（iterator）就是各种迭代函数（iteration function），比如SUMX和filter。

一、迭代iteration和迭代器入门：两种主要类型

SUMX、AVERAGEX等X函数（X functions）是DAX的特殊函数，它们和filter函数同属于迭代函数（iteration function）。

何为迭代？指按照某种顺序逐个访问列表中的每一项。iteration迭代的关键是重复执行同一个指令，直至遇到中止条件。在数据库中，就是逐行访问（row by row），计算到最后一行为止。（补充：有没有可能不计算到最后一行，而是是指定范围？有，存在于多个筛选器的优先级过程中，属于难点，后续补充。Sep 3, 2023 喜乐君）

Iteration： repetition of a mathematical or computational procedure

在wikipedia中，迭代器的解释如下：

迭代器（iterator），是确使用户可在容器对象（container，例如链表或数组）上遍访的对象，设计人员使用此接口无需关心容器对象的内存分配的实现细节。其行为很像数据库技术中的光标（cursor），迭代器最早出现在1974年设计的CLU编程语言中。

——维基百科

1、迭代入门：数据表计数

举个形象的例子，体育课时20人站为一对，从第一名开始依次报数，1、2、3、4……19、20。 报数自动止于最后一人，这个就是迭代。

数据库中也是如此，比如对数据库的某个数据表执行“计数计算”，计算引擎就会从第一行开始，1、2、3、4、…… 99、100…… ，直至计算到最后一行，最后返回“一个数据值”，称之为“标量”（scalar number）。由于这个计算场景属于高频的业务场景，所以最优算法就会被转化为特定的函数，也就是熟知的COUNT、COUNTX函数或者COUNTROWS函数。

DEFINE
    MEASURE Customer[# Individuals 1] = COUNT ( Customer[Customer Name] )
    MEASURE Customer[# Individuals 2] = COUNTX ( Customer, Customer[Customer Name] )

计数，就是最简单的迭代——只需要一遍即可完成，因此也可以称之为“一次性迭代”。当然，DAX把“计数”函数COUNTX、SUMX、AVGX等以X为后缀的函数才称之为“迭代函数”。它们的第一个参数都是table（数据表），然后就可以逐行计数、或逐行相加，或逐行相加后平均。（Sep 3, 2023 补充）

如下所示，这里使用COUNTX迭代计算了数据表的行数，并追加到了明细表Sales之后。

在python或者其他编程语言中，会有很多种语言实现计数迭代的功能，比如for循环。我在查询公募基金数据时，常常使用这样的方法。如下的for循环，就会把ts-code中每一个数据值依次打印一遍，逐个访问，而且仅仅打印一次，类似于上述计数的过程。

j=0
for value in  ts_code:
    print(value)
    j=j+1

2、【新增】循环迭代：排序

相比排序、for的一次性迭代，还有一些算法会涉及到多次的迭代，比如排序（rank）。

排序有很多中算法，比如冒泡算法、选择算法、插入算法、归并算法、哈希算法等，不同的算法各有优劣。简要入门可以参考vivia：[算法总结] 十大排序算法，喜乐君正在阅读宫崎修一、石田保辉的《我的第一本算法书》，也介绍了类似的过程。

举例而言，这里有简单的四个数字，可以通过多次选择MIN最小值并交换位置的方式实现排序。

上面的VIVA文章中有一个动画，更加生动地表达了这个排序过程。

相比之前的计数COUNT，这里的排序需要多次迭代过程，因此可以称之为“多遍迭代”；由于每次的算法都相同，因此也可以称之为“循环”（loop）。

不管是一次性的计数，还是多遍的排序，每次都需要依次访问数据表的每一行，能完成上述操作的函数，常称之为迭代函数（iteration function），或者迭代器（iterator）。在SQL和DAX中，一次性迭代可以用聚合函数快速完成，多遍迭代则要借助于JOIN匹配或者嵌套的X函数来完成，因此后者的难度更高。在Tableau这类敏捷BI工具中，多遍迭代需要借助于Prep等专门的ETL工具完成。（补充Sep 3, 2023 ）

二、DAX中的迭代函数：X函数

按照上述的定义，Excel中的SUM、AVERAGE、MAX等函数都是迭代函数，它们会迭代数据表的每一行，然后透视表的分组字段分组聚合，并返回一个计算值。

在DAX中，迭代函数特指能完整迭代整个数据表的函数，比如SUMX、FILTER函数。按照这个理解，常见的聚合函数都可以视为最简单的迭代函数，比如SUM、AVG、MAX等，它们都是SUMX、AVGX、MAXX的简化形式。

但是，有时候SUM函数也会出现了Report报表（视图）中，此时聚合会被维度分组，因此无法一次性迭代整个表。比如：

DEFINE
MEASURE Sales[# Quantity 1] =
      SUM ( Sales[Quantity] ) 

上述的分组、聚合过程是普适性的，是分析函数的基础，体现了抽象聚合的灵魂。所以在Excel透视表、SQL group by、tableau拖拉拽，乃至Python等，无一例外的存在SUM、MIN这样的聚合函数。它们是业务需求、最优算法到程序设计的典型代表。

当然，不同的工具又有很多不同。比如在DAX中，如果被聚合的对象不是单一字段，而是多个字段构成的表达式（expression），此时，聚合就叠加了逐行计算需求，DAX引入了完整的迭代函数SUMX。它的语法更加完整，既指明了迭代的数据表，又指明了逐行计算的计算逻辑（expression），如下所示：

SUMX (
    table,
    expression
)

比如，在数据表每一行中先把 数量 和 单价 相乘，然后再依次相加SUM，这里的expression就引用了两个字段。先逐行迭代计算（必然是row by row的每一行的计算），再多行累加聚合迭代。如下所示：

MEASURE Sales[Sales Amount] =
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

这里，SUMX函数的第一个参数是数据表名称（table name），既可以是实体表，也可以是逻辑表；第二个参数是迭代计算的表达式，既可以是多个字段的计算，也可以是单一字段（极端简化形式）。完整的意思是：对sales数据表中每一行，依次计算quantity和net price的乘积，最后再计算求和。

【喜乐君20230114】我们可以把这个过程理解为两次迭代：一次是逐行的行内迭代（相乘计算 iterate the table row by row），另一次是跨行的聚合迭代(aggregate)。理解这个是后续理解“FILTER也是迭代函数”的关键。聚合函数一定是迭代函数，但迭代不一定是聚合，也可以是筛选（filter）或者行级别计算（Sales[Quantity]* Sales[Net Price]）。

很多人会和我一样提出这样的疑问：为什么不直接用已有的SUM函数嵌套表达式呢？比如：

Sales[Sales Amount] = 
SUM（ Sales[Quantity]* Sales[Net Price]）。

站在Excel的角度，上述逻辑并非不可行。事实上，Excel中有很多方法实现类似的过程，仅需要sum和嵌套即可完成。而在tableau、SQL这些典型的工具中，也可以用这样的逻辑。

也许，DAX的工程师觉得这种方式不够严谨，不足以体现逐行计算的优先性，而且在无法“所见即所得”的代码世界中，没有Excel的视图和明细界面，需要更清晰地指出迭代的数据表对象，因此设计了参数更加齐全的X函数表达迭代、聚合的过程。

有了X函数，已有SUM函数就变成了SUMX函数的特例。简而言之：

• SUM、AVERAGE等函数，只能以单一字段列为参数，可以认为跳过行内迭代，直接跨行聚合。
• SUMX、AVERAGEX等迭代函数，既要指定迭代数据表，也可以指定多个字段构成的表达式。先行内迭代（row by row），再跨行聚合。

这里提供一个DAX的函数实例如下（来自dax.guide），大家可以感受一下SUM和SUMX的区别（可以先忽略SUMMARIZECOLUMNS部分）：

--  SUM is the short version of SUMX, when used with one column only
--  SUMX is required to evaluate formulas, instead of columns
DEFINE
    MEASURE Sales[# Quantity 1] = SUM ( Sales[Quantity] )
    MEASURE Sales[# Quantity 2] = SUMX ( Sales, Sales[Quantity] )
    MEASURE Sales[Sales Amount] =
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Color],
    "Quantity 1", [# Quantity 1],
    "Quantity 2", [# Quantity 2],
    "Sales Amount", [Sales Amount]
)

上述逻辑结果如下所示：

三、用详细级别的方式诠释SUMX迭代计算

本小节更详细的阐述，参考：迭代过程iteration及迭代函数分类（下）

上述迭代计算过程，也可以换一个角度解释：详细级别level of detail。

1

详细级别，即Level of detail，指的是数据的详细程度，或者说抽象程度（level of abstraction）。分析的过程，就是抽象度提高的过程，因此喜乐君常言“分析即抽象”。抽象的最典型方式就是聚合，聚合的典型代表是SUM求和。

聚合必然是由多变少、由详细到抽象。从数据表的角度看，聚合的起点是数据表的明细行，我们可以用数据表详细级别，或者行级别（row level）指定；聚合的终点是问题、可视化、视图，所以可以用视图详细级别（viz level of detail），或问题详细级别来指定。

也就是说，我们可以用两类“详细级别”来代表不同的数据抽象程度，这两类详细级别，也是两个位置的计算的背景（context）。在DAX中，数据表明细上的计算环境称之为 row context，对应的就是数据表详细级别；而在聚合表，或者相对于问题上计算环境称之为filter context，它对应问题详细级别。

喜乐君个人认为filter context是一个被滥用乃至引起众多误解的概念，它的关键是视图中的分类字段和筛选条件，filter context包含了分类和过滤两个计算条件，但是字面意思上，filter只是过滤条件。而对于聚合而言，分组才是最重要的依据，决定了返回值的多少。

2

以最简单的sum计算为例，假设视图中没有分类字段，也就是计算“全公司的销售额总和”，此时的过程可以如下图所示：

迭代是逐行计算的过程。在上述的聚合过程中，迭代过程就是逐行相加，返回一个唯一值。

不过，大多数分析场景是返回多个值——此时需要一个分组条件，分析中称之为“维度”。比如计算“每个dim分类的 销售额总和”，对应的计算过程就是如下所示：

在这里，聚合之前增加了一个分组（group by）阶段，这个阶段数据并没有结构性的变化，只是分组。或者说一个数据表被拆分为了两个虚拟的数据表过程。这个过程也存在迭代——逐行判断dim的数据值，归入相同的分组中。而分组后的聚合，则发生了结构性的变化，不相关字段被忽略，分组的明细被逐行累加，获得一个值。

在这里，迭代有两个作用：分组和聚合。

可见，聚合是迭代的一种类型，但迭代不一定都是聚合。

上述的两种迭代，分别依赖于不同的环境（environment），可以称之为“context”。其中，明细上的迭代环境称之为 row context；聚合的迭代环境称之为 filter context，或者aggregation context。前者对应数据表详细级别，后者对应视图/问题详细级别。详细级别是迭代计算的依据。

在《DAX权威指南》中有句话，如下：

“Row context iterates the table, whereas filter context filter data.”

row context是行级别计算的依据、环境，它用于迭代整个表；filter context是聚合计算的依据、环境，它用于过滤数据。这里的“filter”要从极其广义的角度理解才能行得通，不仅包含狭义的数据过滤、筛选，还包含分组聚合有多变少的过程。

参考这个样式，喜乐君总结如下的一句话，供大家勘误：

Iteration functions iterates the table by row context, and aggregate iteration functions summarize forward to a result by filter context （ viz level of detail and filter conditions）.

喜乐君

那就是说，迭代函数先在行级别逐行计算（iterate the table）；而聚合的迭代函数进而以详细级别为依据，汇总为单一数据值。

Jan 14, 2023 Revised
Mar 20, 2023 准备删除第三部分（详细级别）略显不协调
Sep 3, 2023 补充修订