在 Looker 中使用自定义日历

对于支持自定义日历的方言，Looker 中的自定义日历功能可让您在数据库中定义自定义日历（例如特定的财年日历或零售日历），然后将该日历应用于 LookML 模型中基于日期的维度组。这样，您的用户就可以使用自定义时间范围（例如 custom_week、custom_period）创建探索查询，就像使用标准时间范围一样。

前提条件

在使用自定义日历之前，请确保满足以下前提条件：

  • 您必须拥有与支持自定义日历的方言的 Looker 连接。
  • 您必须在数据库中创建一个日历表，然后可以在 Looker 中将其建模为视图。Looker 使用数据库中的日历表来计算自定义时间范围。如需了解详情，请参阅在数据库中创建日历表部分。
  • 您的 LookML 项目必须使用新的 LookML 运行时。如果您的实例启用了 **使用旧版 LookML 运行时** 旧版功能，则必须将 new_lookml_runtime: yes 语句添加到项目的清单文件中。

创建自定义日历

如需实现自定义日历，您需要执行以下各部分中介绍的这些常规步骤：

  1. 在数据库中创建日历表
  2. 在 LookML 中定义自定义日历视图
  3. 创建自定义日历维度组

在数据库中创建日历表

为了计算自定义日历的日期，Looker 需要在数据库中创建一个专用日历表，用于定义自定义时间范围。您的表必须有一个使用标准日历日期的参考日期列。Looker 会根据参考日期列将日历表与数据表（例如 orders 表）联接起来。

日历表中的列名称是灵活的。在 LookML 中定义自定义日历视图时，您将使用 calendar_definition 块将数据库中的列映射到标准自定义时间范围名称。

以下是名为 fiscal_calendar_table 的日历表的示例表架构。

列名 数据类型 说明
reference_date DATE 标准日历日期（例如“2023-01-01”）。用于联接。应为唯一键或主键。
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 自定义季节名称（例如“Winter”）。
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_year fiscal_year_num fiscal_period fiscal_period_num prev_week_num prev_day_num
2023-12-25 FY2024 2024 P01 1 51 358
2023-12-26 FY2024 2024 P01 1 51 359
2024-01-01 FY2024 2024 P02 2 52 364
2024-01-02 FY2024 2024 P02 2 52 1

在 LookML 中定义自定义日历视图

在数据库中创建日历表后，您需要创建一个 LookML 视图来对数据库日历表进行建模。

自定义日历视图文件必须包含以下内容：

  • 指向数据库中自定义日历表的 sql_table_name 参数。
  • 用于将表的列映射到 Looker 自定义时间范围类型的 calendar_definition 块。
  • 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_date_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_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 维度组创建一组新的自定义时间范围（例如“Created Custom Year”“Created Custom Week”）。然后，用户可以使用这些字段进行探索、报告和过滤。

如需详细了解如何为自定义日历创建维度组，请参阅 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
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+
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
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