时间序列的跨期比较

本教程使用了偏移函数，目前MySQL/MariaDB不支持此函数。

介绍

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

我们将为您提供逐步说明，您可以在您的Metabase中跟随。

设置

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

我们将使用一个问题来计算按月计算的收益——订单总金额。

创建问题

1. 从Orders表开始创建新问题；
2. 添加总结：计算Total列的总和，按Created At: Month分组；
3. 保存问题。

使用趋势图比较最近时间段

如果您只想跟踪最新时间段与之前一个（或几个之前时间段）的指标表现，趋势图是最佳选择。趋势图看起来像这样

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

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

   由于分解变量是日期，Metabase将默认创建时间序列图表。让我们将可视化更改为趋势图。

2. 将可视化更改为趋势

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

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

3. 添加对12个月前值的比较

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

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

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

年度对比

通常，您不仅想看最新月份的表现，还想看今年所有月份的表现，以及它们与去年所有月份的比较。我们不会制作12个趋势图表，而是将此信息汇总在一个像这样的条形图中

使用偏移函数获取之前时间段

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

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

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

1. 在查询构建器中，在汇总部分添加一个新的偏移量表达式

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

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

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

2. 通过点击汇总块右侧的播放按钮来预览数据。

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

   

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

将年度数据可视化为柱状图

如果我们想将每个月的数据与上一年同月进行比较，我们可以使用 Offset 函数通过指定 -12 偏移量来返回 12 个月前的数据。我们还可以将数据以柱状图的形式展示，而不是表格，以便于更直观的比较。

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

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

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

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

   对于每个月，这个表达式将返回从当前月份偏移 12 个月的总和，即，从一年前的那个月。

2. 在汇总块后添加一个当前年份的过滤器.

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

   • 在汇总块后添加一个 Created At 过滤器
   • 使用 相对日期 过滤选项并选择 Current > Year。

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

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. 以表格形式可视化结果.

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

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

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

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

💡 提示：您可以在表格上使用条件格式，以便人们更容易阅读您的图表。例如，您可以颜色正变化为绿色，负变化为红色，并根据变化的大小使用不同的强度。有关更多信息，请参阅条件格式。

SQL专家的注意事项

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

您可以通过单击查询构建器右上角的“查看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())) );

进一步阅读

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