Power BI: 对事件进行排序

2023-09-24 20:16:54 浏览数 (2)

文章背景：最近在学习DAX权威指南的第16章，DAX中的高级计算。其中提到了一种相当常见的计算模式：对事件序列进行编号，以便查找第一个、最后一个和上一个事件。

计算实例：我们需要在Contoso数据库中分客户对每个订单进行编号，目的是得到一个新的计算列，其中1代表客户的第一个订单，2代表第二个订单，依次类推。每个客户各自的第一个订单编号都是1。

注意：这里先给出一个性能预警：本节中的一些公式计算速度很慢。我们展示代码的目的是介绍它们的复杂度，以便寻找更好的解决方案。

需要呈现的结果如下图所示：

图1 在同一个客户的所有订单中，Order Position包含每个订单的相对位置

1 计算订单号小于或等于当前订单号的所有订单数量

订单号是唯一的，它的值会随着订单的增加而增加。因此，通过为同一个客户计算订单号小于或等于当前订单号的所有订单数量，可以获得订单序号。

计算列的代码如下：

代码语言：javascript复制
Order Position = 
VAR CurrentOrderNumber = Sales[Order Number]
VAR Position =
    CALCULATE (
        DISTINCTCOUNT ( Sales[Order Number] ),
        Sales[Order Number] <= CurrentOrderNumber,
        ALLEXCEPT (
                            Sales,
                                Sales[CustomerKey]
               )
    )
RETURN
    Position

虽然看起来简单，但是这段代码执行了非常复杂的计算。在CALCULATE中，它使用订单号和由计算列生成的上下文转换作为筛选器。对于Sales表的每一行，引擎必须筛选Sales表本身。因此，它的复杂度就是Sales表行数的平方值。因为Sales表包含10万行，所以总的复杂度是10万乘10万，结果就是100亿。最终的结果就是这个计算列需要花费数小时来计算。在更大的数据集中，它足以让任何服务器奔溃。

有经验的开发人员应该尽量避免在大表中使用上下文转换，否则会带来性能不佳的风险。

2 使用组合表

创建一个包含CustomerKey和Order Number的所有组合的表，以避免使用CALCULATE执行计算开销更大的上下文转换。然后执行相似的计算逻辑，统计同一个客户的小于或等于当前订单号的所有订单数量。

计算列的代码如下：

代码语言：javascript复制
Order Position = 
VAR CurrentCustomerKey = Sales[CustomerKey]
VAR CurrentOrderNumber = Sales[Order Number]

VAR CustomersOrders =
    ALL (
        Sales[CustomerKey],
        Sales[Order Number]
    )
VAR PreviousOrdersCurrentCustomer =
    FILTER (
        CustomersOrders,
        AND (
                    Sales[CustomerKey] = CurrentCustomerKey,
                    Sales[Order Number] <= CurrentOrderNumber
            )
    )
VAR Position =
    COUNTROWS( PreviousOrdersCurrentCustomer )
RETURN
    Position

新公式的计算速度要快得多。首先，CustomerKey和Order Number的唯一组合数量为2.6万，而不是10万。此外，通过避免上下文转换，优化器可以生成更好的执行计划。

这个公式的复杂度还是很高的。基于同样的逻辑，我们还有一种更好的执行方案：使用RANKX函数。

3 使用RANKX函数

RANKX函数可以根据表对值进行排序，使用它可以很容易地计算出订单序号。实际上，一个订单的序号与该订单的编号在同一个客户的所有订单列表中按升序排列时的序号相同。

计算列的代码如下：

代码语言：javascript复制
Order Position = 
VAR CurrentCustomerKey = Sales[CustomerKey]
VAR CustomersOrders =
    ALL (
        Sales[CustomerKey],
        Sales[Order Number]
    )
VAR OrdersCurrentCustomer =
    FILTER (
        CustomersOrders,
        Sales[CustomerKey] = CurrentCustomerKey
     )
VAR Position =
    RANKX (
        OrdersCurrentCustomer,
        Sales[Order Number],
        Sales[Order Number],
        ASC,
        DENSE
    )
RETURN
    Position

RANKX得到了很好的优化。它使用了一个高效的内部排序算法，即使在大数据集上也能快速执行。

延伸阅读：

（1）RANKX函数

代码语言：javascript复制
RANKX(<table>, <expression>, [ <value> ], [ <order> ], [ <ties> ])

RANKX 首先为table的每一行计值表达式expression，将结果临时存储为一个值列表。然后value在当前筛选上下文中计值，将得到的结果与列表中的值进行比较，根据排名规则order和ties的设置，返回最终排名。

  • table: 表或者返回表的表达式；
  • expression: 沿着table每行计值的表达式；
  • value: 可选，需要返回排名的 DAX 表达式，返回标量值。当value省略时，用expression代替；
  • order: 可选，排名依据。可以使用ASC或DESC，默认使用降序（DESC）。
  • ties: 可选，处理相同排名时的依据。skip 代表稀疏排名，下一名的排序等于之前所有排序的数量 1；dense 代表稠密排名，只累加排序，不考虑数量。默认使用 skip。

参考资料：

[1] DAX权威指南(https://item.jd.com/13168782.html)

bi 函数 排序 事件 优化

0 人点赞