将 date_start 和 date_end 与日期过滤条件搭配使用

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

语法注释

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

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

使用说明

  • 如果没有为 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) 存储。此问题可能会导致日志文件回滚到查询本地时区“明天”的日期，进而导致一些数据丢失。

解决方法是为日期过滤条件的结束日期额外增加一天，以确保在超过零点（世界协调时间）时提取这些日志条目。

以下示例使用公共 [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 以及列出的分区键范围表明它仅扫描这些分区列。