Power BI WINDOW函数的一些个人理解

很久没有写文了，最近学习了一下 Power BI 的窗口函数，为了加强理解与 sqlserver 的窗口函数对比来学习，使用 DAX 函数 WINDOW 为例进行介绍。需要注意的是以下演示使用的 Power BI 版本是 2023年3月 

1 函数语法

WINDOW ( <From> [, <FromType>], <To> [, <ToType>] [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] )

<From>、 <To>

参数输入的标量值表示按照对应行标从 <Relation> 表即窗口中返回对应区间的行。

<FromType>、 <ToType>

参数缺省值为 ABS ：表示按绝对位置定位，正数为在窗口中从前往后，负数为在窗口从后往前。

当参数输入为 REL 时,表示从相对位置定位，此时需要考虑从外部输入到窗口的值在窗口中行的位置，经过测试这个外部输入的值可以由行上下文提供(如在ADDCOLUMNS中每一行使用 WINDOW 函数)，也可以由筛选上下文提供(如在SUMMARIZECOLUMNS中的每一行使用 WINDOW 函数)，且与外部输入值的列名无关，是按照输入值列的数据沿袭计算位置。(即使用SELECTCOLUMNS修改窗口中的列列名，只要不修改数据沿袭，表达式依然可以正常计算。)，参数为0表示外部输入值所在窗口行，负数为之前行，正数为之后行。

<Relation>

表示形成窗口的表，即所谓的窗口。

需要注意的是如果<Relation>指定了，排序列(<OrderBy>)、分组列( <PartitionBy>)需要从窗口表中进行选取，而且分组列是在WINDOW函数迭代前已存在窗口表中列。排序列则可以是在迭代<Relation>过程中的行上下文计算的标量值表达式(但这种使用方式在2023年3月版本还是会有报错提示，所以不建议用这种方式，最好还是将这个标量值表达式新增变量表中一列，如本例)。即分组列只能直接使用模型原生表中的原生列或者计算列，不能直接引用扩展表中的相关列。如果窗口中需要引用扩展表中的相关列，则需要构建虚拟计算表将扩展的过程定义成结果表的形式(如本例)作为<Relation>参数才可以用这些列分组。

如果<Relation>没有指定，缺省状态下是将所有排序列和分组列作为ALLSELED()函数的参数。

 <OrderBy>

表示每个分组中(由 <PartitionBy>指定)的排序方式

不能与<Relation>参数同时缺省，默认情况下是将<Relation>中除了<PartitionBy>选取的列以外的所有列使用 ASC 方式排序。

需要注意的是，尽管用户已经显式定义 <OrderBy> 的排序列以及升降序，但是如果引擎在根据用户定义的 <OrderBy> 表达式时不能将窗口中的所有行计算成具有唯一行标的窗口表时，即存在行标相同的情况下，引擎会额外以 ASC 的形式添加用户没有定义在 <OrderBy> 指定的列进行排序，如果有多个其他列时可供引擎选择时，至于引擎会额外添加的排序列是不确定的，如果直到引擎将所有除分组列以外的列进行排序仍不能实现唯一行标这个行为时，则会报错。

<Blanks> 

处理空值时排名操作，暂时没发现具体的用处。

<PartitionBy> 

表示用于分组的具体列，影响的是窗口在每个分组的行标。

如果省略则默认整个窗口为一个分组。

2 数据源-粘贴到 Power BI 模型中去，并将两表产品键值构建关系。

Dim_Product

品牌	产品名称	产品键值
品牌A	产品1	1
品牌A	产品2	2
品牌A	产品3	3
品牌A	产品4	4
品牌B	产品5	5
品牌B	产品6	6
品牌B	产品7	7
品牌B	产品8	8
品牌B	产品9	9

Fact_Sales

产品键值	数量
1	62
3	90
7	31
5	88
2	92
7	62
6	24
3	54
2	79
3	70
3	56
6	73
3	29
5	46
9	55
4	61
1	96
2	97
8	77
5	55
3	20
5	69

3 DAX 表达式 - 需要使用 DAX STUDIO

EVALUATE
VAR OriginalQuery =
    SUMMARIZE (
        Fact_Sales,
        Dim_Product[品牌],
        Dim_Product[产品名称],
        "数量", SUM ( Fact_Sales[数量] )
    )
VAR WindowsQuery =
    ADDCOLUMNS (
        OriginalQuery,
        "窗口累加",
        sumx (
            WINDOW ( 1, ABS, 0, REL, OriginalQuery, ORDERBY ( [数量], ASC ) ),
            [数量]
        ),
        "窗口合计", 
        sumx (
	        WINDOW ( 1, ABS, -1, ABS, OriginalQuery ) ,
            [数量]
        ),
        "组内累加",
        sumx (
            WINDOW ( 1, ABS, 0, REL, OriginalQuery, 
            ORDERBY ( [数量], ASC ), ,
            PARTITIONBY ( [品牌] ) ),
            [数量]
        ),
        "组内合计",
        sumx (
            WINDOW ( 1, ABS, -1, ABS, OriginalQuery, , ,PARTITIONBY ( [品牌] ) ),
            [数量]
        )
    )
RETURN
    WindowsQuery
ORDER BY
    [品牌] ASC,
    [组内累加] ASC

DAX 结果 

4 sqlserver 查询

SELECT
  OriginalQuery.品牌,
  OriginalQuery.产品名称,
  OriginalQuery.数量,
  Sum(OriginalQuery.数量) 
	OVER( ORDER BY OriginalQuery.数量 ASC ) 窗口累加,
  Sum(OriginalQuery.数量) 
	OVER() 窗口合计,
  Sum(OriginalQuery.数量) 
	OVER( PARTITION BY OriginalQuery.品牌 ORDER BY OriginalQuery.数量 ASC ) 组内累加,
  Sum(OriginalQuery.数量) 
	OVER( PARTITION BY OriginalQuery.品牌) 组内合计
FROM
  (
    SELECT
    Dim_Product.品牌,
    Dim_Product.产品名称,
    Sum(Fact_Sales.数量) 数量
  FROM
    Fact_Sales
    LEFT JOIN Dim_Product ON Dim_Product.产品键值 = Fact_Sales.产品键值
  GROUP BY
		Dim_Product.品牌,
		Dim_Product.产品名称
  ) OriginalQuery
ORDER BY
  品牌 ASC,
  组内累加 ASC

 SQL 结果