时间序列的期间对比

本教程使用Offset 函数，该函数目前在 MySQL/MariaDB 中不可用。

简介

本教程将向您展示如何比较两个或多个时间段的数据。以下是我们将要制作的图表

我们将为您提供分步说明，您可以在 Metabase 中按照这些说明进行操作。

设置

我们将使用每个新的 Metabase 实例随附的示例数据库中的 Orders 表。

我们将使用一个问题来计算收入 - 订单总额的总和 - 按月。

要创建问题

1. 从 Orders 表开始一个新问题；
2. 添加摘要：Sum of... Total 列，按 Created At: Month 分组；
3. 保存问题。

使用趋势图比较最新期间

如果您只想跟踪指标在最新时间段与上一个期间（或几个以前的期间）的绩效，则趋势图是最佳选择。趋势图如下所示

要构建此图表，请从您在设置部分中创建的“按月收入”问题开始

1. 如果您在查询构建器中，点击“可视化”以创建图表。

   Metabase 默认情况下会创建时间序列图表，因为细分变量是日期。让我们将可视化更改为趋势图。

2. 将可视化更改为趋势

   • 点击屏幕左下角的可视化按钮；
   • 选择“趋势”。

   Metabase 将显示数据中的最新值，以及该值与上一个期间同一指标的比较情况。您还可以选择与静态值（例如您设置的目标）或多个期间进行比较。

3. 添加与 12 个月前的值的另一个比较

   • 从趋势图中，点击左下角的齿轮图标打开可视化设置；
   • 在数据选项卡中，点击添加比较；
   • 选择12 个月前。

您的趋势图现在将包含两个比较：一个与上个月的比较，另一个与一年前同月的比较。

💡 提示：查看“显示”选项卡中的其他趋势可视化设置。例如，您可以向收入显示添加 $ 符号，或更改用于比较的颜色。

同比比较

通常，您不仅要查看最近一个月，还要查看今年所有月份的绩效，以及它们与去年所有月份的比较情况。与其制作 12 个趋势图，我们不如将此信息收集在一个条形图中，如下所示

使用 Offset 函数获取之前的期间

我们将使用方便的自定义表达式函数Offset，它返回不同行中的值，由偏移量指定（例如，后 1 行或前 5 行）。如果您以前从未使用过自定义表达式，您可以查看我们的教程笔记本编辑器中的自定义表达式。

我们将再次从设置中的“每月订单总收入”问题开始。

首先，我们将复制趋势图所做的操作 - 将该月的结果与上个月进行比较 - 但适用于数据中的所有月份，而不仅仅是最后一个月。

1. 在查询构建器中，在“汇总”部分中添加新的 Offset 表达式

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

   您可以将列命名为类似 "上个月" 的名称（您的数据仍应按 Created At: Month 分组）。

   对于每个月，此表达式将返回上个月（偏移 -1）的总和。

2. 点击“汇总”块右侧的播放按钮预览数据。

   您应该看到三列：月份、该月的总和以及上个月的总和。

   

   使用 Offset，您可以通过查看单行轻松地比较每个月的月度绩效与上个月的月度绩效。

将 YoY 数据可视化为条形图

如果我们想将每个月的数据与上一年的同月进行比较，我们可以使用 Offset 函数通过指定 -12 偏移量来返回 12 个月前的数据。我们也可以将数据呈现为条形图，以便更轻松地进行视觉比较。

从上一节中的问题或设置问题

1. 在查询构建器中，在“汇总”部分中添加新的 Offset 自定义表达式（或更改现有表达式）

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

   您可以将新列命名为 "1 年前"。如果您正在编辑上一节中的列，请记住重命名列以反映新的时间段！

   对于每个月，此表达式将返回当前月份的总和，偏移量为 12 个月 - 因此，来自一年前的月份。

2. 在“汇总”块之后添加当前年份的过滤器.

   您的结果包括自时间开始以来的所有数据。在 YoY 图表中，我们只想查看当前年份的月份以及它们与上一年同月的比较情况，因此我们需要过滤数据。

   • 在“汇总”块之后为 Created At 添加过滤器
   • 使用相对日期过滤器选项并选择当前 > 年。

   重要的是在汇总数据之后添加过滤器，而不是之前。如果您在计算总和之前为当前年份添加过滤器，则结果中将不会包含去年的数据，因此您将无法偏移它。

3. 预览数据.

   现在您应该只看到当前年份的月份。

4. 将结果可视化为堆叠条形图。

   您可能需要更改可视化类型：点击屏幕左下角的“可视化”按钮，然后选择“条形图”。

5. 关闭拆分 y 轴以在同一尺度上比较数据

   根据您的数据，Metabase 可能会为条形图创建拆分 Y 轴。因为我们想在同一尺度上比较年度结果，所以我们的图表应该只有一个 y 轴。

   在查看可视化时

   • 点击屏幕左下角的“齿轮”图标
   • 切换到“轴”选项卡
   • 关闭“必要时拆分 y 轴”

6. 更改条形顺序，使上一年的条形位于当前年份的条形左侧。

   Metabase 将使用“汇总”块中表达式的顺序对堆叠条形图中的条形进行排序，因此上一年的条形将位于当前年份条形的右侧。让我们改为按时间顺序排列条形。

   在查看可视化时

   • 点击屏幕左下角的“齿轮”图标
   • 在“数据”选项卡中，拖动系列的行以按正确的顺序排列它们。

您的图表应如下所示

添加与 2 年前的比较

现在自己试用一下：按照相同的步骤添加与当前年份2 年前的数据的另一个比较。

您的图表应如下所示

衡量差异和变化

您可以将 Offset 函数与一些数学运算结合使用，以计算从一个期间到另一个期间的变化 - 以值或百分比表示，以获得如下数据

假设您已按照上一节中的说明构建了 YoY 图表

1. 在查询构建器中，为 YoY 收入变化添加新的摘要

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

   对于每个月，此表达式将计算该月在 Sum([Total]) 中的收入，然后减去上个月在 Offset(Sum([Total]), -12) 中的收入。

   您可以预览数据以查看结果。

2. 为收入变化添加新的摘要（以百分比表示）:

   要计算 YoY 变化占上一年价值的百分比，请添加自定义表达式

   ( Sum([Total]) - Offset(Sum([Total]), -12) ) / Offset(Sum([Total]), -12)
   

   这里我们将当前年份和上一年价值之间的差异除以上一年价值。

3. 将结果可视化为表格.

   如果您从 YoY 条形图开始，请将可视化类型更改为表格：点击屏幕左下角的“可视化”按钮，然后选择“表格”。

4. 将百分比变化列格式化为百分比.

   默认情况下，Metabase 会将列显示为小数，但您可以更改列格式以将其显示为百分比

   • 点击列标题以打开列操作菜单
   • 点击齿轮图标以打开列格式设置
   • 选择 样式 > 百分比

💡 提示：您可以在表格上使用条件格式，以便人们更轻松地阅读您的图表。例如，您可以将正向变化用绿色表示，负向变化用红色表示，并根据变化幅度使用不同的强度。了解更多关于条件格式的信息。

SQL 专家须知

Metabase 会将查询构建器中创建的所有查询转换为 SQL。Offset 自定义表达式（我们用它来创建期间与期间的比较）会转换为 LAG 和 LEAD SQL 窗口函数。

您可以通过点击查询构建器右上角的“查看 SQL”按钮来查看 Metabase 生成的 SQL。

例如，这是我们在 将同比数据可视化为条形图 中创建的问题的 SQL

SELECT
  "source"."CREATED_AT" AS "CREATED_AT",
  "source"."sum" AS "sum",
  "source"."1 year ago" AS "1 year ago"
FROM
  ( SELECT
      "source"."CREATED_AT" AS "CREATED_AT",
      SUM("source"."TOTAL") AS "sum",

      LAG(SUM("source"."TOTAL"), 12) OVER (
         ORDER BY "source"."CREATED_AT" ASC
      ) AS "1 year ago"

    FROM
      ( SELECT
          DATE_TRUNC('month', "PUBLIC"."ORDERS"."CREATED_AT") AS "CREATED_AT",
          "PUBLIC"."ORDERS"."TOTAL" AS "TOTAL"
        FROM
          "PUBLIC"."ORDERS"
      ) AS "source"
    GROUP BY
      "source"."CREATED_AT"
    ORDER BY
      "source"."CREATED_AT" ASC
  ) AS "source"
WHERE
  ("source"."CREATED_AT" >= DATE_TRUNC('year', NOW()))
   AND (
    "source"."CREATED_AT" < DATE_TRUNC('year', DATEADD('year', 1, NOW())) );

延伸阅读

• Offset 函数
• 可视化趋势
• SQL 中的日期
• 计算 LTV