在 Looker 中使用自訂日曆

如果方言支援，Looker 的自訂日曆功能可讓您在資料庫中定義自訂日曆 (例如特定會計或零售日曆)，然後將日曆套用至 LookML 模型中以日期為基礎的維度群組。使用者就能使用自訂時間範圍 (例如 custom_week、custom_period) 建立「探索」查詢，就像使用標準時間範圍一樣。

必要條件

使用自訂日曆前，請確認符合下列必要條件：

建立自訂日曆

如要實作自訂日曆，請按照下列各節所述的一般步驟操作：

  1. 在資料庫中建立日曆資料表
  2. 在 LookML 中定義自訂日曆檢視畫面
  3. 建立自訂日曆維度群組

在資料庫中建立日曆資料表

如要計算自訂日曆的日期，Looker 需要資料庫中有專屬的日曆資料表，用來定義自訂時間範圍。資料表必須有參照日期欄，且使用標準日曆日期。Looker 會根據參考日期欄，將日曆資料表彙整至資料表 (例如 orders 資料表)。

日曆資料表的資料欄名稱可彈性調整。在 LookML 中定義自訂日曆檢視畫面時，您會使用 calendar_definition 區塊，將資料庫中的資料欄對應至標準自訂時間範圍名稱。

以下是名為 fiscal_calendar_table 的日曆資料表結構定義範例。

資料欄名稱 資料類型 說明
reference_date DATE 標準日曆日期 (例如「2023-01-01」)。用於加入。必須是專屬或主鍵。
fiscal_day_num INTEGER 自訂日期或一年中的序數日 (例如 1)。
fiscal_year VARCHAR 會計年度 (例如「FY2023」)。
fiscal_year_num INTEGER 會計年度 (數字，例如 2023)。
fiscal_quarter_of_year VARCHAR 會計季度 (例如「FQ1」)。
fiscal_quarter_of_year_num INTEGER 會計季度 (以數字表示，例如 1)。
fiscal_week_of_year VARCHAR 一年的財務週 (例如「Week01」、「FW01」)。
fiscal_week_of_year_num INTEGER 一年中的會計週次 (例如 1)。
fiscal_period_of_year VARCHAR 自訂期間名稱 (例如「P01」)。
fiscal_period_of_year_num INTEGER 自訂週期的數字 (例如 1)。
season VARCHAR 自訂季節名稱 (例如「冬季」)。
season_num INTEGER 自訂季別的數字 (例如 1)。
prev_week_num INTEGER 前一週的序數值 (例如 52 或 53)。如果當週是第 1 週，而日曆中的前一週不是第 52 週，Looker 就需要這個值來計算逐期測量指標。只有在您想搭配自訂日曆使用期別比較測量指標，且自訂日曆不含 52 週時，才需要使用 prev_week_num。
prev_day_num INTEGER 前一天的序數值 (例如 364 或 371)。如果當天是 1，而日曆中的前一天不是 364，Looker 就需要這個值來計算前期比較指標。只有在您想使用自訂日曆進行期別比較，且自訂日曆的天數不是 364 天時，才需要使用 prev_day_num。

以下列舉 fiscal_calendar_table 資料表中的幾個資料列範例：

reference_date fiscal_day_num fiscal_year fiscal_year_num fiscal_period fiscal_period_num prev_week_num prev_day_num
2023-12-25 359 FY2024 2024 P01 1 51 358
2023-12-26 360 FY2024 2024 P01 1 51 359
2024-01-01 1 FY2024 2024 P02 2 52 364
2024-01-02 2 FY2024 2024 P02 2 52 1

在 LookML 中定義自訂日曆檢視畫面

在資料庫中建立日曆資料表後，您需要建立 LookML 檢視表，為資料庫日曆資料表建立模型。

自訂日曆檢視畫面檔案必須包含下列內容：

  • sql_table_name 參數，指向資料庫中的自訂日曆表格。
  • calendar_definition 區塊，用於將資料表資料欄對應至 Looker 自訂時間範圍類型。
  • dimension 參數，為資料庫中自訂日曆資料表的資料欄建立模型。

以下是名為 fiscal_calendar.view.lkml 的檢視表檔案範例，用於模擬 fiscal_calendar_table：

view: fiscal_calendar {
  sql_table_name: fiscal_calendar_table ;;
  calendar_definition: {
    reference_date: reference_date
    timeframe_mapping: {
      custom_year: fiscal_year
      custom_quarter: fiscal_quarter_of_year
      custom_date: fiscal_date
      custom_week: fiscal_week_of_year
      custom_period: fiscal_period_of_year
      custom_season: season
    }
    timeframe_ordinal_mapping: {
      custom_year: fiscal_year_num
      custom_quarter: fiscal_quarter_of_year_num
      custom_date: fiscal_day_num
      custom_week: fiscal_week_of_year_num
      custom_period: fiscal_period_of_year_num
      custom_season: season_num
    }
    previous_ordinal_mapping: {
      custom_week: prev_week_num
      custom_date: prev_day_num
    }
  }

  dimension: reference_date {
    type: date
    primary_key: yes
    sql: ${TABLE}.reference_date ;; # Assuming column name is reference_date
  }

  dimension: fiscal_date {
    type: string
    sql: FORMAT_TIMESTAMP('%Y-%m-%d', ${TABLE}.reference_date) ;;
  }

  dimension: fiscal_day_num {
    type: number
    sql: ${TABLE}.fiscal_day_num ;;
  }

  dimension: fiscal_year {
    type: string
    sql: ${TABLE}.fiscal_year ;;
  }

  dimension: fiscal_year_num {
    type: number
    sql: ${TABLE}.fiscal_year_num ;;
  }

  # ... other dimensions for quarters, weeks, periods, seasons, etc. ...

  # Example placeholder dimensions for unused timeframes
  dimension: season {
    type: string
    sql: 'N/A' ;;
    hidden: yes
  }
  dimension: season_num {
    type: number
    sql: 0 ;;
    hidden: yes
  }
}

如要進一步瞭解 calendar_definition 參數，請參閱 calendar_definition 參數頁面。

建立自訂日曆維度群組

在資料庫中建立日曆資料表，並在 LookML 中建立資料庫日曆資料表的模型後，即可根據自訂日曆檢視畫面建立 type: custom_calendar 的維度群組。

舉例來說，以下是名為 orders.view.lkml 的檢視表檔案，定義了自訂日曆維度群組。

include: "/views/fiscal_calendar.view"

view: orders {
  sql_table_name: public.orders ;;

  dimension_group: created {
    type: custom_calendar

    # Optional list of allowed timeframes
    custom_timeframes: [
      custom_date,
      custom_week,
      custom_year
    ]
    sql: ${TABLE}.created_at ;;
    based_on_calendar: fiscal_calendar  # This links to your calendar view
  }
}

有了這個 LookML，Looker 會自動為 created 維度群組建立一組新的自訂時間範圍 (例如「會計年度」、「會計週」)。使用者可以運用這些欄位探索、回報及篩選資料。

如要瞭解如何為自訂日曆建立維度群組，請參閱 dimension_group 說明文件頁面。

注意事項

請注意，自訂日曆有下列限制：

  • 篩選測量指標：篩選測量指標不支援自訂日曆：您無法在測量指標的 filters 參數中參照自訂日曆維度。
  • 進階篩選器：進階篩選器不支援自訂日曆，因此您無法在 Looker 篩選運算式中參照自訂日曆維度。
  • 對稱式匯總函式：在某些複雜的彙整情境中，涉及自訂日曆維度的查詢可能無法完全支援對稱式匯總函式。
  • 維度填補：Looker 無法填補自訂日曆維度缺少的日期。
  • 篩選條件超出日曆範圍：如果篩選條件套用至自訂時間範圍，導致日期不在日曆中，系統可能會傳回非預期的結果。這與用來計算週期開始時間的序數算術有關。
  • OR 篩選器： OR 篩選器不支援自訂日曆查詢。
  • 時區轉換行為：Looker 會遵循現有的時區轉換語意。也就是說，如果資料庫和查詢時區相符，就不會套用時區轉換。如果兩者不一致，Looker 會套用時區轉換。如要覆寫這項行為，請在自訂日曆維度群組中指定 convert_tz: no。
  • 預設排序：選取自訂日曆欄位時，Looker 會對第一個日期欄位套用預設排序。使用者可以指定不同的資料排序方式。
  • 自訂日曆篩選器：自訂日曆維度支援特定篩選器選項，例如「過去 3 個自訂年」或「這個自訂期間」。詳情請參閱「篩選及限制資料」說明文件頁面的「自訂日曆篩選器」一節。

自訂日曆支援的資料庫方言

下表列出最新版 Looker 中，支援自訂日曆的方言：

方言 是否支援？
Actian Avalanche
Amazon Athena
Amazon Aurora MySQL
Amazon Redshift
Amazon Redshift 2.1+
Amazon Redshift Serverless 2.1+
Apache Druid
Apache Druid 0.13.x - 0.17.x
Apache Druid 0.18+
Apache Hive 2.3+
Apache Hive 3.1.2+
Apache Spark 3+
ClickHouse
ClickHouse 26+
Cloudera Impala 3.1+
Cloudera Impala 3.1+ with Native Driver
Cloudera Impala with Native Driver
DataVirtuality
Databricks
Denodo 7
Denodo 8 & 9
Dremio
Dremio 11+
Dremio 26+ with Arrow Flight SQL
Exasol
Google BigQuery Legacy SQL
Google BigQuery Standard SQL
Google Cloud AlloyDB for PostgreSQL
Google Cloud PostgreSQL
Google Cloud SQL
Google Spanner
Greenplum
HyperSQL
IBM Netezza
MariaDB
Microsoft Azure PostgreSQL
Microsoft Azure SQL Database
Microsoft Azure Synapse Analytics
Microsoft SQL Server 2008+
Microsoft SQL Server 2012+
Microsoft SQL Server 2016
Microsoft SQL Server 2017+
MongoBI
MongoSQL
MySQL
MySQL 8.0.12+
Oracle
Oracle ADWC
PostgreSQL 9.5+
PostgreSQL pre-9.5
PrestoDB
PrestoSQL
SAP HANA
SAP HANA 2+
SingleStore
SingleStore 7+
Snowflake
Teradata
Trino
Vector
Vertica