在 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