时间序列的周期性比较

简介

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

我们将为您提供逐步说明，您可以在 Metabase 中进行操作。

设置

我们将使用 Metabase 实例附带的示例数据库中的 Orders 表。

我们将使用一个问题来计算每月收入（订单总额之和）。

创建问题

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

使用趋势图比较最新时期

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

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

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

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

2. 将可视化更改为趋势

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

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

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

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

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

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

同比增长比较

通常，您不仅希望查看最新的月份，还希望查看今年所有月份的业绩，以及它们与去年所有月份的比较。与其制作 12 个趋势图，不如将这些信息收集在一个如下所示的条形图中

使用 Offset 函数获取前一个时期

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

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

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

1. 在查询生成器中，在“摘要”部分添加一个新的 Offset 表达式

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

   您可以将该列命名为 "Previous month"（您的数据应仍按 Created At: Month 分组）。

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

2. 通过点击“摘要”块右侧的播放按钮预览数据。

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

   

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

将同比增长数据可视化为条形图

如果我们要将每月数据与上一年同月的数据进行比较，我们可以使用 Offset 函数通过指定 -12 偏移量来返回 12 个月前的数据。我们还可以将数据呈现为条形图而不是表格，以便于视觉比较。

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

1. 在查询生成器中，在“摘要”部分添加一个新的 Offset 自定义表达式（或更改现有的表达式）

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

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

   对于每个月，此表达式将返回当前月份的总和（偏移 12 个月）——即一年前的月份的总和。

2. 在“汇总”块后添加当前年份的筛选器.

   您的结果包含从一开始的所有数据。在 YoY 图表中，我们只想看到当前年份的月份以及它们与上一年相同月份的比较，因此我们需要过滤数据。

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

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

3. 预览数据.

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

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

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

5. 关闭拆分 Y 轴以在相同比例下比较数据

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

   在查看可视化时

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

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

   Metabase 将使用“摘要”块中表达式的顺序来排列堆积条形图中的条形，因此上一年的条形将位于当前年份的条形右侧。让我们以时间顺序排列条形。

   在查看可视化时

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

您的图表应如下所示

增加与两年前的对比

现在自己尝试一下：按照相同的步骤，增加与当前年份两年前数据的另一个对比。

您的图表应如下所示

衡量差异和变化

您可以使用 Offset 函数结合一些数学运算来计算一个时期到另一个时期的变化——无论是值还是百分比，以获得如下数据

假设您已经按照上一节中的说明构建了同比增长图表

1. 在查询生成器中，添加一个新的摘要，用于计算同比增长的收入变化

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

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

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

2. 添加一个新的摘要，以百分比形式显示收入变化:

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

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

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

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

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

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

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

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

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

SQL 专家注意事项

Metabase 将查询生成器中创建的所有查询转换为 SQL。我们用于创建同期比较的 Offset 自定义表达式转换为 LAG 和 LEAD SQL 窗口函数。

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

例如，这是我们在将 YoY 数据可视化为条形图中创建的问题的 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())) );

延伸阅读

• 偏移函数
• 可视化趋势
• SQL 中的日期
• 计算客户生命周期价值 (LTV)