WIINDOW函数

一、 语法

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

 

Window ( <起始位置>,<起始位置类型>,<结束位置>,<结束位置类型>, [<关系>], [<OrderBy>],[空白],[PartitionBy] )

 

 

 

备注：

OrderBy、PartitionBy中出现的列，需要先在“关系表”中引用。

 

二、数据准备

2.1  SQL中的数据

 

2.2  PB中的数据

Dim_Product

 

 Fact_Sales

 Relationship

 

 

三、 代码

3.1 SQL 代码

 

SELECT Brand,Product,[num]
,SUM([num]) OVER (ORDER BY num ) as sum_window  
,SUM([num]) OVER ()  as sum_total  
,SUM([num]) OVER (PARTITION BY Brand  ORDER BY num ) as sum_window_partition 
,SUM([num]) OVER (PARTITION BY Brand )  as sum_total_partition 
FROM
(
SELECT  B.Brand,B.Product,SUM([num]) AS num   
FROM Test.Func.Fact_Sales AS A
LEFT JOIN Test.Func.Dim_Product AS B
ON A.[Key] = B.[Key]
GROUP BY  B.Brand,B.Product
)  t1
order by Brand,num

 

 

3.2 POWER BI 代码

EVALUATE
VAR t1 =
    SUMMARIZE (
        Fact_Sales,
        Dim_Product[Brand],
        Dim_Product[Product],
        "@num", SUM ( Fact_Sales[num] )
    )
VAR t2 =
    ADDCOLUMNS (
        t1,
        "sum_window", SUMX ( WINDOW ( 1, ABS, 0, REL, t1 , ORDERBY( [@num] ),  ), [@num] ),
        "sum_total", SUMX ( WINDOW ( 1, ABS, -1, ABS, t1 , ORDERBY([@num] )), [@num] ),
        "sum_window_partition", SUMX ( WINDOW ( 1, ABS, 0, REL, t1, ORDERBY([@num] ),,PARTITIONBY(Dim_Product[Brand]) ), [@num] ),
        "sum_total_partition ", SUMX ( WINDOW ( 1, ABS, -1, ABS, t1, ORDERBY([@num] ),,PARTITIONBY(Dim_Product[Brand]) ), [@num] )
    )
RETURN
    t2
ORDER BY Dim_Product[Brand],[@num]