时间序列的期对期比较

简介

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

我们将为您提供可在 Metabase 中跟随操作的分步说明。

设置

我们将使用随每个全新 Metabase 实例附带的示例数据库中的“订单”表。

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

创建问题

1. 从 订单 表开始一个新问题；
2. 添加汇总：对 总计 列进行 求和...，按 创建时间：月份 分组；
3. 保存问题。

使用趋势图比较最新期间

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

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

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

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

2. 将可视化更改为趋势

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

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

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

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

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

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

同比比较

您通常不仅想查看最新月份，还想查看今年 *所有月份* 的表现，以及它们与去年 *所有月份* 的比较。我们不会制作 12 个趋势图，而是将这些信息收集到如下所示的条形图中

使用 Offset 函数获取先前期间

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

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

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

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

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

   您可以将列命名为“上月”（您的数据仍应按 创建时间：月份 分组）。

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

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

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

   

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

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

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

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

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

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

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

   对于每个月，此表达式将返回当前月份总计之和，并偏移 12 个月——即，来自一年前的那个月。

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

   您的结果包含了所有历史数据。在同比图表中，我们只想看到当前年份的月份，以及它们与去年同月份的比较，因此我们需要过滤数据。

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

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

3. 预览数据.

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

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

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

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

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

   查看可视化时

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

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

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

   查看可视化时

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

您的图表应该看起来像这样

添加与 2 年前的比较

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

您的图表应该看起来像这样

衡量差异和变化

您可以使用 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. 将结果可视化为表格.

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

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