本頁面由 Cloud Translation API 翻譯而成。

使用日期篩選器搭配 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) 查詢時，記錄檔會顯示世界標準時間

有時，即使您在東部或太平洋時區查詢，Looker 記錄檔仍會以世界標準時間儲存。這個問題可能會導致記錄檔已在查詢的當地時區中，捲動至明天的日期，進而導致部分資料遺失。

解決方法是在日期篩選器的結束日期中多加一天，確保系統會擷取世界標準時間午夜過後的記錄項目。

下列範例使用公開的 [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
  }
}

由於這項陳述式會提供 WHERE 限制，將結果集縮減至使用者最初在查詢中指定的日期範圍，因此需要額外的 SELECT 陳述式。

透過字串以「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') 用於將時間戳記轉換為字串，並採用正確格式。
如果使用者輸入 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 中檢查這項 LookML 產生的查詢輸出內容 (按一下「Explore」頁面「Data」分頁中的「SQL」部分即可存取)，您會看到類似下列內容： EXPLAIN

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 和列出的分區鍵範圍表示系統只會掃描這些分區資料欄。

使用日期篩選器搭配 date_start 和 date_end 透過集合功能整理內容 你可以依據偏好儲存及分類內容。

語法附註

使用須知

應用實例範例

每月分區資料欄 (在 BigQuery 中)

範例 1：依附於 always_filter 的 LookML

範例 2：不依附 always_filter 的 LookML

在美洲時區 (BigQuery) 查詢時，記錄檔會顯示世界標準時間

範例 1：依附於 always_filter 的 LookML

範例 2：不依附 always_filter 的 LookML

後 N 天的窗口函式 (在 BigQuery 中)

透過字串以「YYYY-MM-DD」格式按日期分割的資料表 (在 Presto 中)

範例 1：使用通用資料表運算式篩選資料表的 LookML

範例 2：直接在述詞中篩選的 LookML

使用日期篩選器搭配 date_start 和 date_end

範例 1：依附於 `always_filter` 的 LookML

範例 2：不依附 `always_filter` 的 LookML

範例 1：依附於 `always_filter` 的 LookML

範例 2：不依附 `always_filter` 的 LookML