时间序列的周期性比较

简介

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

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

设置

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

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

创建问题

1. 从 Orders 表中开始一个新问题；
2. 添加一个汇总：总计... 列，按 创建于：月份 分组；
3. 保存问题。

使用趋势图比较最新时期

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

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

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

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

2. 将可视化更改为趋势图

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

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

3. 添加与 12 个月前的数值进行比较

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

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

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

同比比较

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

使用 Offset 函数获取前一时期

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

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

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

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