偏移

⚠️ Offset 函数目前不适用于 MySQL/MariaDB、ClickHouse、MongoDB 和 Druid。

Offset 函数返回不同行中表达式的值。Offset 只能在查询构建器的“汇总”步骤中使用(您不能使用 Offset 来创建自定义列)。

语法:Offset(expression, rowOffset)

expression 是要从不同行获取的值。

rowOffset 是相对于当前行的数字。例如,-1 表示上一行,或 1 表示下一行。

示例:Offset(Sum([Total]), -1) 将从上一行获取 Sum([Total]) 的值。

分组的顺序很重要

由于 Offset 引用其他行,因此分组(即“汇总”步骤中“按分组”部分中的组)的顺序很重要。Metabase 将首先按第一个组排序,然后按任何其他分组进行分区。例如,如果您想查看按产品类别随时间变化的订单计数,以及上一个期间按产品类别的计数,您应该首先按 Created At 分组,然后按产品类别分组。

Offset 不会处理缺失数据

Offset 只能引用您拥有的行,这意味着它可能产生正确但意想不到的结果。例如,假设您想将每天的计数与前一天进行比较。

以下表为例,其中 Previous 列由表达式 Offset(Count, -1) 创建。

日期 计数 上一行
1-Oct-22 6  
2-Oct-22 3 6
4-Oct-22 2 3

10月4日 Previous 列中的值为 3,这是上一行的值。虽然根据数据来说是正确的,但上一行是10月2日;没有前一天(10月3日)的数据。如果您想使用偏移量来比较前几天(或几周,或其他任何时间),您需要确保您的数据包含您想比较的每个数据点的行。在这种情况下,您的数据需要包含每一天的行,包括计数为零的天。如果您的数据中缺少日期,您可以将日历表连接到您的数据中,以确保每一天都有对应的行。

数据类型

Offset 函数返回偏移行中的任何值。

数据类型 Offset 返回
字符串
数字
时间戳
布尔
JSON

使用 Offset 进行同比(YoY)时间序列比较示例

在示例数据库中,您可以使用 Offset 比较订单的同比(YoY)计数。

首先,按“总计之和”进行汇总。然后再次汇总该总和,这次使用 Offset 来获取上一行的值。

Offset(Sum([Total]), -1)

然后按年份将结果按 Created At 分组

Comparing year over year

结果如下

Year over year order sum of order totals

通过这些偏移量(“上一期间”列中的总和),我们可以创建 自定义列 来计算年度总和之间的差异等。

[Sum of total] - [Previous period]

以及逐年百分比变化

[Difference] / [Previous period] * 100

Difference and percentage change

使用 Offset 计算滚动平均值示例

您可以使用包含 Offset 的自定义表达式来计算滚动平均值。

例如,假设您想计算过去三个月订单总额的滚动平均值。您可以创建一个自定义表达式来计算这些滚动平均值

(Sum([Total]) + Offset(Sum([Total]), -1) + Offset(Sum([Total]), -2)) / 3

上述表达式将当前期间的总额加上前两个期间的总额(偏移量为 -1-2),然后除以三,以获得这些期间的平均值。

Rolling average

SQL

Offset 函数与 SQL 的 LAGLEAD 窗口函数进行比较。

例如,如果您尝试创建一个包含两个系列的折线图,以比较 (a) 本月的订单计数和 (b) 上月的订单计数,您将 count 本月的订单,然后使用 offset 表达式计算上月的订单,如下所示:

Offset(count, -1)

在内部,Metabase 会将 Offset 表达式转换为 LAG 窗口函数,就像这个查询一样:

SELECT
  "source"."CREATED_AT" AS "CREATED_AT",
  COUNT(*) AS "count",
  LAG(COUNT(*), 1) OVER (

ORDER BY
      "source"."CREATED_AT" ASC
  ) AS "Order count previous period"
FROM
  (
    SELECT
      DATE_TRUNC('month', "PUBLIC"."ORDERS"."CREATED_AT") AS "CREATED_AT"
    FROM
      "PUBLIC"."ORDERS"
  ) AS "source"
GROUP BY
  "source"."CREATED_AT"
ORDER BY
  "source"."CREATED_AT" ASC

进一步阅读

阅读其他 Metabase版本 的文档。

© . All rights reserved.