表示されているのは 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
  }
}

米国のタイムゾーンでクエリする場合、ログファイルは UTC になります（BigQuery）

東部または太平洋のタイムゾーンでクエリを実行している場合でも、Looker のログファイルが UTC に保存されることがあります。この問題により、ログファイルがクエリのローカルタイムゾーンで明日の日付にすでにロールされている場合で、データが欠落する問題が発生する可能性があります。

この問題を解決するには、日付フィルタの終了日に日付を追加して、UTC の午前 0 時を過ぎてもそれらのログエントリが取得されるようにします。

次の例では、一般公開の [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 に追加する必要があります。

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 行前まで遡るウィンドウ関数を実装します。ただし、日付で分割されたテーブルを使用する場合のジレンマがあります。クエリが計算のために追加の履歴テーブルを必要とする場合でも、まず、クエリを実行するテーブルのセットを指定する必要があります。

解決策: 開始日を、日付フィルタで指定された日付よりも前に設定できるようにします。さらに 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
  }
}

ユーザーがクエリに最初に指定した期間に結果セットを切り詰める 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') は、タイムスタンプを文字列に変換して適切な形式にするために使用されます。
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

米国のタイムゾーンでクエリする場合、ログファイルは UTC になります（BigQuery）

例 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