현재 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이 됩니다.

이 두 가지 사례 모두 잘못된 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 기준으로 자정을 넘는 경우 해당 로그 항목이 선택되도록 하는 것입니다.

아래 예시에서는 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
  }
}

이 SQL은 NULL이 날짜로 대체되면 실패하므로 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개 행에 도달하는 윈도우 함수를 구현합니다. 그러나 날짜로 파티션을 나눈 테이블을 사용하는 경우 문제가 발생할 수 있습니다. 쿼리에 실제로 계산을 위해 추가 기록 테이블이 필요한 경우에도 쿼리를 실행할 테이블 집합을 먼저 지정해야 합니다.

해결 방법: 시작일이 날짜 필터에 제공된 날짜보다 이전이어야 합니다. 다음은 일주일을 더 연장하는 예입니다.

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에는 Presto에서 검색되는 YYYY-MM-DD 형식의 문자열인 파티션을 나눈 열 dt가 있습니다.

생성기가 처음 실행되면 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의 출력을 확인하여 테이블 파티션이 실제로 사용 중인지 확인할 수 있습니다(Explore 페이지의 데이터 탭에서 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