表示されているのは Looker のドキュメントです。Looker Studio のドキュメントについては、https://support.google.com/looker-studio をご覧ください。

日付フィルタでの date_start と date_end の使用

テンプレートフィルタを使用すると、日付フィルタ（{% date_start date_filter %} と {% date_end date_filter %}）ごとに開始日と終了日を選択して日付を参照できます。このページでは、ユースケースの例とその実現手順について説明します。

構文の注意事項

次の構文はほとんどの言語に対応していますが、特定の構文には特定のユースケースがあります。例:

BigQuery では、TABLE_DATE_RANGE や TABLE_QUERY などのテーブルワイルドカード関数を使用する場合に、きめ細かい制御が可能です。そのため、日付フィルタを指定するには {% 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 になります。

この 2 つのケースで無効な SQL が発生しないようにするには、LookML で IFNULL または COALESCE を使用します。

ユースケースの例

月別パーティション分割テーブル（BigQuery 内）

一部の BigQuery データセットでは、テーブルが月単位で整理され、テーブル ID には接尾辞として年と月の組み合わせが含まれます。たとえば、BigQuery の一般公開 Wikipedia [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 の部分に置き換えられます。この問題を回避するには、Explore に always_filter が必要です。

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

ただし、{% date_start date_filter %} は NULL と評価されるため、データセット内で最も早い日付より前の日付のフィルタは引き続き失敗します。

例 2: `always_filter` に依存しない LookML

また、COALESCE または IFNULL を使用して、クエリするデフォルトのテーブルセットをエンコードすることもできます。次の例では、過去 2 か月が使用されます。

下限: 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

クエリが UTC 形式で保存される場合もありますが、クエリが Eastn や太平洋時間のタイムゾーンで処理されている場合もあります。この問題は、ログファイルがクエリのローカルタイムゾーンで明日の日付にすでにロールアウトされており、一部のデータが欠落している問題の原因となる可能性があります。

日付フィルタの終了日にさらに 1 日を追加して、UTC の午前 0 時を過ぎている場合にこれらのログエントリが取得されるようにしてください。

以下の例では、GitHub 情報が毎日分割されている、一般公開の [githubarchive:day] データセットを使用しています。

例 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 が失敗するため、Explore に always_filter を追加する必要があります。

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

例 2: `always_filter` に依存しない LookML

この例では、デフォルトの日付範囲が LookML でエンコードされています。COALESCE は unknown 型を返したため、最終的に SQL を機能させるには IFNULL を使用する必要がありました。

下限: 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 数の行を返すウィンドウ関数を実装します。ただし、日付パーティション分割テーブルを使用する場合には catch-22 があります。クエリで実際には計算のために追加の履歴テーブルが必要な場合でも、最初にクエリを実行するテーブルのセットを決定する必要があります。

解決策: 開始日を日付フィルタで指定した日付より早くすることができます。以下に、1 週間分の延長をリクエストする例を示します。

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 に YYYY-MM-DD 形式の文字列 dt があり、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 にも適用することをおすすめします。

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 ;;
  }
}

この LookML によって生成されたクエリについて SQL Runner の EXPLAIN の出力をチェックすることで、テーブルパーティションが実際に使用されていることを確認でき（データ探索ページの [Data] タブの [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 と一覧表示されたパーティションキーの範囲は、分割された列のみをスキャンしていることを示します。

日付フィルタでの date_start と date_end の使用

構文の注意事項

使用上の注意:

ユースケースの例

月別パーティション分割テーブル（BigQuery 内）

例 1: always_filter に依存する LookML

例 2: always_filter に依存しない LookML

ログファイルがアメリカのタイムゾーン（BigQuery の場合）のクエリでは UTC

例 1: always_filter に依存する LookML

例 2: always_filter に依存しない LookML

末尾の N 日間のウィンドウ関数（BigQuery 内）

「YYYY-MM-DD」形式の文字列（Presto）で日付で分割されたテーブル

例 1: 共通テーブル式を使用してテーブルをフィルタリングする LookML

例 2: 述語で直接フィルタリングする LookML

例 1: `always_filter` に依存する LookML

例 2: `always_filter` に依存しない LookML

例 1: `always_filter` に依存する LookML

例 2: `always_filter` に依存しない LookML