迭代计算、聚合函数与详细级别 2.1

V1 Jan 9, 2023 喜乐君

V2 Jan 14, 2023 增加排序等

“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),计算到最后一行为止。

Iteration: repetition of a mathematical or computational procedure

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

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

COUNTROWS([table name])

计数,这就是最简单的迭代——只需要一遍即可完成,因此也可以称之为“一次性迭代”。

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

for value in  ts_code:
    print(value)

【新增】循环迭代

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

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

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

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

排序之选择算法

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

不管是一次性的计数,还是多遍的排序,每次都需要依次访问数据表的每一行,能完成上述操作的函数,常称之为迭代函数(iteration function),或者迭代器(iterator)。

在wikipedia中,迭代器的解释如下:

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

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

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

在DAX中,可以直接使用这些函数完成单一字段列的计算。比如:

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这些典型的工具中,也可以用这样的逻辑。

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

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

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

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

正因为此,SUM和SUM X为例的函数语法如下所示:

  • SUM ( <ColumnName> )
  • SUMX ( <Table>, <Expression> )

这里提供一个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]
)

上述逻辑结果如下所示:

三、用详细级别的方式诠释

上述迭代计算过程,也可以换一个角度解释:详细级别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计算为例,假设视图中没有分类字段,也就是计算“全公司的销售额总和”,此时的过程可以如下图所示:

添加图片注释,不超过 140 字(可选)

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

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

添加图片注释,不超过 140 字(可选)

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

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

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

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

添加图片注释,不超过 140 字(可选)

在《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

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

您正在使用您的 WordPress.com 账号评论。 注销 /  更改 )

Twitter picture

您正在使用您的 Twitter 账号评论。 注销 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 注销 /  更改 )

Connecting to %s

网站通过 WordPress.com 打造.

向上 ↑

%d 博主赞过: