偏移
⚠️
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
分组
结果如下
通过这些偏移量(“上一期间”列中的总和),我们可以创建 自定义列 来计算年度总和之间的差异等。
[Sum of total] - [Previous period]
以及逐年百分比变化
[Difference] / [Previous period] * 100
使用 Offset
计算滚动平均值示例
您可以使用包含 Offset
的自定义表达式来计算滚动平均值。
例如,假设您想计算过去三个月订单总额的滚动平均值。您可以创建一个自定义表达式来计算这些滚动平均值
(Sum([Total]) + Offset(Sum([Total]), -1) + Offset(Sum([Total]), -2)) / 3
上述表达式将当前期间的总额加上前两个期间的总额(偏移量为 -1
和 -2
),然后除以三,以获得这些期间的平均值。
相关函数
SQL
Offset
函数与 SQL 的 LAG
和 LEAD
窗口函数进行比较。
例如,如果您尝试创建一个包含两个系列的折线图,以比较 (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版本 的文档。