Power BI 新函数 RANK 和 ROWNUM 的一些个人理解

原创已于 2023-04-17 20:39:58 修改 · 2.2k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#microsoft #开发语言 #sqlserver

于 2023-04-17 20:37:11 首次发布

文章介绍了PowerBI在2023年4月的更新，新增了RANK和ROWNUM两个窗口函数，并与SQLServer的窗口函数进行了对比。通过示例解释了RANK的<Ties>参数默认处理重复值的方式，以及如何使用DAX和SQL查询实现窗口函数的功能，展示了相关DAX表达式和SQL查询语句。

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 模型中去，并将两表产品键值构建关系。

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,
      "窗口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 结果