時間序列的期間與期間比較

本教學課程使用 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。

例如，以下是我們在將 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())) );

延伸閱讀

• Offset 函數
• 視覺化趨勢
• SQL 中的日期
• 計算 LTV