202304 Power BI 更新了两个窗口函数(RANK 和 ROWNUM),为了加强理解继续与 sqlserver 的窗口函数对比来学习。需要注意的是以下演示使用的 Power BI 版本是 2023年4月
1 函数语法
RANK ( [<Ties>] [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] )
ROWNUMBER ( [<Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] )
这两个新的窗口函数参数语法基本与 WINDOW 函数一致,有兴趣的可以翻看 WINDOW函数 ,值得注意就是RANK中的 <Ties> 默认是 SKIP, 简单理解就是窗口中用来排名的值去不去重,默认是不去重。DESC 就是去重。举个例子,5个人3个成绩,分别是99,99,98,98,97。不去重就是以这5个成绩进行排名,结果是1,1,3,3,5。去重就是以去重的结果进行排名1,1,2,2,3。
2 数据源-粘贴到 Power BI 模型中去,并将两表产品键值构建关系。
品牌 | 产品名称 | 产品键值 |
品牌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 |
产品键值 | 数量 |
---|---|
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,
"窗口SKIP排名",
RANK ( SKIP, OriginalQuery, ORDERBY ( [数量], ASC ) ),
"组内SKIP排名",
RANK ( SKIP, OriginalQuery, ORDERBY ( [数量], ASC ), PARTITIONBY ( [品牌] ) ),
"窗口DENSC排名",
RANK ( DENSE, OriginalQuery, ORDERBY ( [数量], ASC ) ),
"组内DENSC排名",
RANK ( DENSE, OriginalQuery, ORDERBY ( [数量], ASC ), PARTITIONBY ( [品牌] ) ),
"排序后窗口行号",
ROWNUMBER ( OriginalQuery, ORDERBY ( [数量], ASC ) ),
"排序后组内行号",
ROWNUMBER ( OriginalQuery, ORDERBY ( [数量], ASC ), PARTITIONBY ( [品牌] ) )
)
RETURN
WindowsQuery
ORDER BY
[数量] ASC
DAX 结果
4 sqlserver 查询
SELECT
OriginalQuery.品牌
,OriginalQuery.产品名称
,数量
,Rank()
OVER(
ORDER BY OriginalQuery.数量 ASC) 窗口SKIP排名
,Rank()
OVER(
PARTITION BY OriginalQuery.品牌
ORDER BY OriginalQuery.数量 ASC) 组内SKIP排名
,Dense_rank()
OVER(
ORDER BY OriginalQuery.数量 ASC) 窗口DENSE排名
,Dense_rank()
OVER(
PARTITION BY OriginalQuery.品牌
ORDER BY OriginalQuery.数量 ASC) 组内DENSE排名
,Row_number()
OVER(
ORDER BY OriginalQuery.数量 ASC) 排序后窗口行号
,Row_number()
OVER(
PARTITION BY OriginalQuery.品牌
ORDER BY OriginalQuery.数量 ASC) 排序后组内行号
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
SQL 结果