跳至正文

迭代计算（上）：“迭代类函数”的类型与应用场景

标签:

V1 Jan 9, 2023 喜乐君
V2 Jan 14, 2023 增加排序等
V2.1 Mar 7, 2023 更正错误
V2.2 Sep 3, 2023 补充修改
V3.0 2024/11/17 调整
V3.1 2026/2/25

“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函数。

SQL
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）。从算法的角度，排序可以视为FIlter 和 SUM 或 COUNT 函数的结合，即指定条件计算数量（比当前行更大的数据有几个？）。

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

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

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

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

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

【2024/11/17 补充】当然，RANK 排序作为特殊的迭代计算，它既可以在明细行中完成，也可以在聚合表中完成。这个逻辑和 SQL 中的窗口函数基本一致。如下图所示，展示了在一个聚合表上，使用 RANKX 增加了“不同品牌的销售数量排序”。对于每一个产品而言，为了证明它是第1或者第2，不考虑算法优化的情况下，可以粗暴的理解为需要11行*11行的计算——每一个产品都要和其他所有产品比较。

【2024/11/17】这种多遍迭代，用表合并的方式更容易理解，就像两个数据表可以生成笛卡尔积一样。在这种场景下，我是不准备用迭代这个词的，它遮挡了计算的逻辑，过于晦涩、难以讲授。

3、迭代的常见类型

在上面，这里用通俗的话描述了何为迭代（逐行计算的过程），比如计数和排序。它们分别代表一次性迭代和多遍迭代两种场景。

在 PowerBI 中，任何一个在数据表明细行中，针对所有行完成逐一计算的函数，都可以称之为“迭代函数”，比如 YEAR 日期函数、LEFT 截取函数，以及前面讲到的 COUNTROWS 函数等。这个过程可以用 Excel 的明细表直观理解，如下所示。

难点在于，只有在明细行中（或者说在行上下文）中的聚合函数才是迭代函数。视图中的聚合不算。这就需要详细介绍一下 X 函数。

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

2024/11/17 补充，强调 X 函数仅仅在行上下文中才是迭代。

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

1、行级别的 SUMX 聚合（计算列）

迭代有两个必备条件，其一，全表为范围，其二，逐一计算、不能有例外。

比如，在 Sales 明细表之后，增加一个 SUM(Quantity) 的计算列（必须是计算列），从而在每一行之后、计算整个表的数量总和。

这个过程，可以在其他工具中完成。比如在 Excel 的明细行中（注意不是透视表）增加整列聚合，或者在 SQL 中使用子查询增加每一行的聚合值，都可以理解为是全表、逐行计算而来的迭代计算。

但是，视图中的SUMX 不能称之为迭代，因为它是在分区中的计算，而非全表的计算。

2、视图中的SUMX 聚合

在分析中，聚合更常见的是出现在视图阶段，此时聚合会被维度分组，因此无法一次性迭代整个表——可见，视图阶段的聚合函数，不属于“迭代函数”的范围。比如：

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

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

比如计算“每个dim分类的 销售额总和”，对应的计算过程就是如下所示：

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

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

在这里，看似 SUMX 是迭代函数，但并没有一个全表迭代的过程。视图中聚合是在每个分组之中完成的，不是迭代。

可见，聚合只是迭代的一种类型（而且还要是计算列、行级别的聚合），反之迭代也不一定都是聚合。

3、行级别迭代和视图聚合的结合

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

SUMX (
    table,
    expression
)

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

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

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

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

【喜乐君20230114/2024/11/17】
我们可以把这个过程理解为两次计算过程：一次是逐行的行内迭代（相乘计算 iterate the table row by row），这个迭代一定是整个表所有行全部计算的；另一次是跨行的聚合计算(aggregate)、聚合仅仅在分组范围中完成，不能理解为聚合是一次性迭代到表的尽头。
所以说，measure 度量中的上述计算，Sales[Quantity] * Sales[Net Price] 是迭代，但 SUMX 却不见得。

“聚合函数不是迭代计算片”，迭代不一定是聚合，也可以是筛选（filter）或者行级别计算（Sales[Quantity]* Sales[Net Price]）。

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

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

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

在Excel中实现聚合和逐行迭代的组合

也许，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

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

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

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

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

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

喜乐君

那就是说，迭代函数必须在行级别对整个表逐行计算（iterate the table）；而聚合的迭代函数只是以问题详细级别为依据，汇总为单一数据值，这个过程跨行计算仅仅在特定group by范围内完成，因此不理解为迭代。

Jan 14, 2023 Revised
Mar 20, 2023 准备删除第三部分（详细级别）略显不协调
Sep 3, 2023 补充修订
2024-11-17 大面积修订，修改之前错误。
2026-2-25 修改：基于视图详细级别的聚合不是迭代！

《迭代计算（上）：“迭代类函数”的类型与应用场景》有2个想法

  1. Pingback： 迭代计算（中）：DAX迭代计算的分类 – 喜乐君

  2. Pingback： 迭代计算（下）：DAX“迭代”最后一次说明 – 喜乐君

评论已关闭。