将 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 以年份和月份组合作为后缀。以下数据集就是一个示例，其中包含许多名称类似于 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) 格式存储。此问题可能会导致日志文件已滚动到查询的本地时区的明天，从而导致部分数据丢失。

解决方案是在日期过滤条件的结束日期中添加一天，以确保即使已过午夜（世界协调时间），系统也能提取这些日志条目。

以下示例使用公开的 [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（格式为 YYYY-MM-DD 的字符串），Presto 会搜索该列。

首次运行生成器时，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') 用于将时间戳转换为字符串并设置为正确的格式。
  • coalesce 用于处理有人输入 before 2010-01-01 或 after 2012-12-31 等过滤条件时出现 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 中由相应 LookML 生成的查询的 EXPLAIN 输出，验证是否实际使用了表分区（您可以通过点击“探索”页面“数据”标签页中的“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 以及列出的分区键范围表明，系统仅扫描这些分区列。