结合使用 date_start 和 date_end 与日期过滤条件

您可以使用模板化过滤条件来引用日期，只需在日期过滤条件（分别是 {% date_start date_filter %} 和 {% date_end date_filter %}）中选择开始日期和结束日期即可。本页将向您介绍一些用例示例以及实现这些示例的步骤。

语法说明

以下语法适用于大多数方言，但某些方言有其特定的用例。示例：

  • 在使用 TABLE_DATE_RANGE 和 TABLE_QUERY 等表通配符函数时，BigQuery 允许精细控制，因此使用 {% table_date_range prefix date_filter %} 不足以指定日期过滤条件。
  • Hadoop 允许处理日期分区列，无论列的类型（string、date）或格式 (YYYY-MM-DD) 如何。

使用说明

  • 如果没有为 date_filter 指定值，则 {% date_start date_filter %} 和 {% date_end date_filter %} 的计算结果均为 NULL。
  • 对于开放式 date_filter（例如 before 2016-01-01 或 after 2016-01-01），{% date_start date_filter %} 或 {% date_end date_filter %} 过滤条件之一将为 NULL。

    • 为了确保这两种情况都不会导致无效的 SQL，您可以在 LookML 中使用 IFNULL 或 COALESCE。

使用场景示例

每月分区列（在 BigQuery 中）

在某些 BigQuery 数据集中，表按月份整理，表 ID 以年/月组合为后缀。例如，公共 BigQuery 维基百科 [fh-bigquery:wikipedia] 数据集内包含名为 pagecounts_201601、pagecounts_201602、pagecounts_201603 等等的表。

示例 1：依赖于 always_filter 的 LookML

下面的派生表使用 TABLE_QUERY([dataset], [expr]) 获取要查询的一组正确表： 

view: pagecounts {
  derived_table: {
    sql: SELECT * FROM
    TABLE_QUERY([fh-bigquery:wikipedia],
    "length(table_id) = 17 AND
    table_id >= CONCAT( 'pagecounts_' , STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') ) AND
    table_id <= CONCAT('pagecounts_' , STRFTIME_UTC_USEC({% date_end date_filter %},'%Y%m') )";
    )
    ;;
  }
  filter: date_filter {
    type: date
  }
}

关于表达式中代码的一些说明：

  • table_id 是指数据集中表的名称。
  • length(table_id) = 17 可确保它忽略其他名称类似于 pagecounts_201407_en_top64k 的表。
  • STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') 将仅输出开始日期的 YYYYmm 部分。

NULL 将替换为 date_filter 部分。若要解决此问题，您需要在“探索”中使用 always_filter： 

explore: pagecounts {
  always_filter: {
    filters: [date_filter: "2 months ago"]
  }
}

请注意，对于数据集中最早日期之前的日期，这仍然会失败，因为 {% date_start date_filter %} 的计算结果为 NULL。

示例 2：不依赖于 always_filter 的 LookML

您也可以使用 COALESCE 或 IFNULL 对要查询的一组默认表进行编码。下面的示例使用的是过去两个月的数据：

  • 下限：COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH'))
  • 上限：COALESCE({% date_end date_filter %},CURRENT_TIMESTAMP()) 
view: pagecounts {
  derived_table: {
    sql: SELECT * FROM
    TABLE_QUERY([fh-bigquery:wikipedia],
    "length(table_id) = 17 AND
    table_id >= CONCAT( 'pagecounts_'; , STRFTIME_UTC_USEC(COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH')),'%Y%m') ) AND
    table_id <= CONCAT( 'pagecounts_' , STRFTIME_UTC_USEC(COALESCE({% date_end date_filter %},CURRENT_TIMESTAMP()),'%Y%m') )"
    )
    ;;
  }
  filter: date_filter {
    type: date
  }
}

按美国时区（在 BigQuery 中）查询时，日志文件采用世界协调时间 (UTC)

有时，即使您按照东部或太平洋时区进行查询，您的 Looker 日志文件也会以世界协调时间 (UTC) 存储。此问题可能会导致出现以下问题：日志文件已滚动至查询本地时区的“明天”的日期，导致一些数据丢失。

解决方案是向日期过滤条件的结束日期额外添加一天，以确保在世界协调时间 (UTC) 零点之后，系统会提取这些日志条目。

以下示例使用的是公共 [githubarchive:day] 数据集，其中包含 GitHub 信息的每日分区。

示例 1：依赖于 always_filter 的 LookML

view: githubarchive {
  derived_table: {
    sql: SELECT * FROM
    TABLE_DATE_RANGE([githubarchive:day.],
    {% date_start date_filter %},
    DATE_ADD({% date_end date_filter %},1,"DAY")
    )
    ;;
  }

  filter: date_filter {
    type: date
  }
}

由于如果将日期替换为 NULL，此 SQL 将失败，因此需要向“探索”中添加 always_filter： 

explore: githubarchive {
  always_filter: {
    filters: [date_filter: "2 days ago"]
  }
}

示例 2：不依赖于 always_filter 的 LookML

在此示例中，默认日期范围在 LookML 中进行编码。由于 COALESCE 返回了 unknown 类型，我最终必须使用 IFNULL 才能使 SQL 正常运行。

  • 下限：IFNULL({% date_start date_filter %},CURRENT_DATE())
  • 上限：IFNULL({% date_end date_filter %},CURRENT_DATE()) + 1 天 
view: githubarchive {
  derived_table: {
    sql: SELECT * FROM
    TABLE_DATE_RANGE([githubarchive:day.],
    IFNULL({% date_start date_filter %},CURRENT_TIMESTAMP()),
    DATE_ADD(IFNULL({% date_end date_filter %},CURRENT_TIMESTAMP()),1,"DAY")
    )
    ;;
  }
  filter: date_filter {
    type: date
  }
}

跟踪 N 天窗口函数（在 BigQuery 中）

执行某些分析时，应以某种汇总形式对历史时间范围内的数据进行计算。要在 SQL 中执行此操作，通常应实现一个窗口函数，该函数为一个按日期唯一返回的表返回 n 行数据。然而，在使用日期分区表时，会遇到一个问题：一个必须首先指定运行查询所针对的表集，即使查询实际上需要额外的历史表进行计算也是如此。

解决方案：允许开始日期早于日期过滤条件中提供的日期。以下是重新联系客户的示例： 

view: githubarchive {
  derived_table: {
    sql:  SELECT y._date,
                y.foo,
                y.bar
            FROM (
              SELECT _date,
                    SUM(foo) OVER (ORDER BY _date RANGE BETWEEN x PRECEDING AND CURRENT ROW),
                    COUNT(DISTINCT(bar)) OVER (ORDER BY _date RANGE BETWEEN x PRECEDING AND CURRENT ROW)
                FROM (
                    SELECT _date,
                          foo,
                          bar
                      FROM TABLE_DATE_RANGE([something:something_else.], DATE_ADD(IFNULL({% date_start date_filter %},CURRENT_TIMESTAMP()), -7, "DAY"), IFNULL({% date_end date_filter %},CURRENT_TIMESTAMP()))
                      ) x
            ) y
          WHERE {% condition date_filter %} y._date {% endcondition %};;
  }
  filter: date_filter {
    type: date
  }
}

需要额外的 SELECT 语句，因为它提供 WHERE 约束条件，以将结果集缩小到用户最初在查询中指定的日期范围。

表按日期分区，通过字符串格式“YYYY-MM-DD”（在 Presto 中）

Hadoop 表中的一种常见模式是使用分区列来缩短搜索常用列（尤其是日期）的速度。日期列的格式可以任意设置，但 YYYY-MM-DD 和 YYYYMMDD 最常见。日期列的类型可以是字符串、日期或数字。

在此示例中，Hive 表 table_part_by_yyyy_mm_dd 有一个分区列 dt，这是 Presto 搜索的 YYYY-MM-DD 字符串格式。

首次运行生成器时，LookML 如下所示：

view: table_part_by_yyyy_mm_dd {
  sql_table_name: hive.taxi. table_part_by_yyyy_mm_dd ;;
  suggestions: no
  dimension: dt {
    type: string
    sql: ${TABLE}.dt ;;
  }
}

关于以下两个示例表达式中代码的一些说明：

  • date_start 和 date_end 的输出为 type: timestamp。
  • date_format( <expr>, '%Y-%m-%d') 用于将时间戳转换为字符串并转换为正确的格式。
  • 如果用户输入 before 2010-01-01 或 after 2012-12-31 等过滤条件，coalesce 将处理 NULL 的情况。
  • 这是 Presto 方言代码，因此 Hive 在格式字符串 (yyyy-MM-dd) 方面存在一些差异，并且 date_format 不能接受 NULL 值，因此 coalesce 必须使用某种默认值进行迁移。

示例 1：使用通用表表达式过滤表的 LookML

此示例使用派生表来过滤该表。

view: table_part_by_yyyy_mm_dd {
  # sql_table_name: hive.taxi. table_part_by_yyyy_mm_dd
  suggestions: no
  derived_table: {
    sql: SELECT * FROM hive.taxi. table_part_by_yyyy_mm_dd
      WHERE ( coalesce( dt >= date_format({% date_start date_filter %}, '%Y-%m-%d'), TRUE) )
      AND ( coalesce( dt <= date_format({% date_end date_filter %}, '%Y-%m-%d'), TRUE) )
      ;;
  }
  filter: date_filter {
    type: date
  }
  dimension: dt {
    type: string
    sql: ${TABLE}.dt ;;
  }
}

通常情况下，分区表执行全表扫描所花费的时间太长（并且会消耗过多的集群资源），因此最好也在“探索”中为此视图设置默认过滤器：

explore: table_part_by_yyyy_mm_dd {
  always_filter: {
    filters: [date_filter: "2013-01"]
  }
}

示例 2：直接在谓词中过滤的 LookML

此示例直接对表执行谓词过滤，没有子查询或通用表表达式。 

view: table_part_by_yyyy_mm_dd {
  sql_table_name: hive.taxi.table_part_by_yyyy_mm_dd ;;
  filter: date_filter {
    type: date
    sql: ( coalesce( ${dt} >= date_format({% date_start date_filter %}, '%Y-%m-%d'), TRUE) )
    AND ( coalesce( ${dt} <= date_format({% date_end date_filter %}, '%Y-%m-%'), TRUE) );;
  }
  dimension: dt {
    type: string
    sql: ${TABLE}.dt ;;
  }
}

我们可以检查 SQL Runner 中 EXPLAIN 的输出是否通过此 LookML 生成的查询来验证表分区是否确实在使用（点击“探索”页面的“数据”标签页中的 SQL 部分即可访问该查询），您会看到如下内容： 

output[table_part_by_yyyy_mm_dd.count] => [count:bigint]
table_part_by_yyyy_mm_dd.count := count
  TopN[500 by (count DESC_NULLS_LAST)] => [count:bigint]
  Aggregate(FINAL) => [count:bigint]
  count := "count"("count_4")
  RemoteExchange[GATHER] => count_4:bigint
  Aggregate(PARTIAL) => [count_4:bigint]
  count_4 := "count"(*)
  Filter[(COALESCE(("dt" >= CAST('2013-04-01' AS VARCHAR)), true) AND COALESCE(("dt" <= CAST('2016-08-01' AS VARCHAR)), true))] => [dt:varchar]
  TableScan[hive:hive:taxi: table_part_by_yyyy_mm_dd, originalConstraint = (COALESCE(("dt" >= CAST('2013-04-01' AS VARCHAR)), true) AND COALESCE(("dt" <= CAST('2016-08-01' AS VARCHAR)), true))] => [dt:varchar]
  LAYOUT: hive dt := HiveColumnHandle{clientId=hive, name=dt, hiveType=string, hiveColumnIndex=-1, partitionKey=true}
  :: [[2013-04-01, 2013-12-31]]

partitionKey=true 以及列出的分区键范围表明它仅扫描这些分区列。