Legacy SQL 구문, 함수, 연산자

이 문서에서는 legacy SQL 쿼리 구문, 함수, 연산자에 대해 설명합니다. BigQuery의 기본 쿼리 문법은 GoogleSQL입니다. GoogleSQL에 대한 자세한 내용은 GoogleSQL 쿼리 구문을 참조하세요.

쿼리 구문

참고: 키워드는 대소문자를 구분하지 않습니다. 이 문서에서 SELECT와 같은 키워드는 설명을 위해 대문자로 표시됩니다.

SELECT 절

SELECT 절은 계산할 표현식 목록을 지정합니다. SELECT 절의 표현식에는 필드 이름, 리터럴, 함수 호출(집계 함수 및 윈도우 함수 포함) 또는 이 세 가지 항목의 조합이 포함될 수 있습니다. 표현식 목록은 쉼표로 구분됩니다.

각 표현식에는 표현식 다음에 공백 문자와 식별자를 추가하여 별칭을 지정할 수 있습니다. 가독성 향상을 위해 표현식과 별칭 사이에 AS 키워드를 추가할 수도 있습니다. SELECT 절에 정의된 별칭은 쿼리의 GROUP BY, HAVING, ORDER BY 절에서 참조할 수 있지만, FROM, WHERE, OMIT RECORD IF 절이나 동일한 SELECT 절에 있는 다른 표현식에서 참조할 수 없습니다.

참고:

• SELECT 절에서 집계 함수를 사용할 경우 모든 표현식에서 집계 함수를 사용해야 합니다. 아니면 SELECT 절에 있는 집계가 아닌 모든 필드를 포함하는 GROUP BY 절이 쿼리에 그룹화 키로 있어야 합니다. 예를 들면 다음과 같습니다.

  #legacySQL
  SELECT
    word,
    corpus,
    COUNT(word)
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th"
  GROUP BY
    word,
    corpus; /* Succeeds because all non-aggregated fields are group keys. */

  #legacySQL
  SELECT
    word,
    corpus,
    COUNT(word)
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th"
  GROUP BY
    word; /* Fails because corpus is not aggregated nor is it a group key. */

• 대괄호를 사용하여 예약어를 이스케이프 처리하면 필드 이름 및 별칭으로 사용할 수 있습니다. 예를 들어 BigQuery 구문에 예약어인 'partition'이라는 열이 있는 경우 이 필드를 참조하는 쿼리는 대괄호로 이스케이프 처리되지 않으면 모호한 오류 메시지와 함께 실패합니다.

  SELECT [partition] FROM ...

예

이 예시에서는 SELECT 절의 별칭을 정의하며 ORDER BY 절의 별칭 중 하나를 참조합니다. WHERE 절에서 word_alias를 사용해서는 word 열을 참조할 수 없습니다. 이름으로 참조해야 합니다. len 별칭도 WHERE 절에 표시되지 않습니다. 이 별칭은 HAVING 절에 표시됩니다.

#legacySQL
SELECT
  word AS word_alias,
  LENGTH(word) AS len
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS 'th'
ORDER BY
  len;

집계 함수의 WITHIN 한정자

aggregate_function WITHIN RECORD [ [ AS ] alias ]

WITHIN 키워드는 집계 함수가 각 레코드 내에서 반복되는 값을 집계하도록 만듭니다. 각 입력 레코드에 정확히 하나의 집계된 출력이 생성됩니다. 이 유형의 집계를 범위가 지정된 집계라고 부릅니다. 범위가 지정된 집계는 레코드마다 출력을 생성하므로, GROUP BY 절을 사용하지 않고도 범위가 지정된 집계 표현식과 집계가 아닌 표현식을 함께 선택할 수 있습니다.

대부분의 경우에는 범위가 지정된 집계를 사용할 때 RECORD 범위를 사용합니다. 매우 복잡하게 중첩되어 있고 반복되는 스키마가 있는 경우, 하위 레코드 범위 내에서 집계를 수행해야 할 수 있습니다. 이렇게 하려면 위 문법에서 RECORD 키워드를 집계를 수행하려는 스키마에 있는 노드 이름으로 바꾸면 됩니다. 이러한 고급 동작에 대한 자세한 내용은 데이터 취급을 참조하세요.

예

이 예에서는 범위가 지정된 COUNT 집계를 수행하고 집계된 값으로 레코드를 필터링 및 정렬합니다.

#legacySQL
SELECT
  repository.url,
  COUNT(payload.pages.page_name) WITHIN RECORD AS page_count
FROM
  [bigquery-public-data:samples.github_nested]
HAVING
  page_count > 80
ORDER BY
  page_count DESC;

FROM 절

FROM
  [project_name:]datasetId.tableId [ [ AS ] alias ] |
  (subquery) [ [ AS ] alias ] |
  JOIN clause |
  FLATTEN clause |
  table wildcard function

FROM 절은 쿼리할 소스 데이터를 지정합니다. BigQuery 쿼리는 테이블, 하위 쿼리, 조인된 테이블, 아래 설명된 특수 목적의 연산자로 수정된 테이블에 직접 실행할 수 있습니다. BigQuery에서 UNION ALL 연산자인 쉼표를 사용하여 이러한 데이터 소스 조합을 쿼리할 수 있습니다.

테이블 참조

테이블을 참조할 경우 datasetId 및 tableId를 모두 지정해야 합니다. project_name은 선택사항입니다. project_name을 지정하지 않으면 BigQuery에서 현재 프로젝트가 기본적으로 사용됩니다. 프로젝트 이름에 대시가 포함되어 있으면 전체 테이블 참조를 대괄호로 묶어야 합니다.

예

[my-dashed-project:dataset1.tableName]

테이블 이름 다음에 공백 문자와 식별자를 추가하여 테이블에 별칭을 지정할 수 있습니다. 가독성을 향상시키기 위해 tableId와 별칭 사이에 AS 키워드를 추가할 수도 있습니다.

테이블에서 열을 참조할 때는 단순히 열 이름을 사용하거나, 별칭을 지정한 경우 열 이름 앞에 별칭을 프리픽스로 사용하거나, datasetId 및 tableId를 프리픽스로 사용할 수 있습니다(project_name이 지정되지 않은 경우). 필드 이름에는 콜론 문자가 허용되지 않기 때문에 열 프리픽스에 project_name을 포함시킬 수 없습니다.

예시

이 예에서는 테이블 접두사 없이 열을 참조합니다.

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare];

이 예에서는 열 이름에 datasetId 및 tableId를 접두사로 사용합니다. 이 예시에서는 project_name을 포함시킬 수 없습니다. 이 방법은 데이터 세트가 사용자의 현재 기본 프로젝트에 있는 경우에만 작동합니다.

#legacySQL
SELECT
  samples.shakespeare.word
FROM
  samples.shakespeare;

이 예시에서는 열 이름에 테이블 별칭을 프리픽스로 사용합니다.

#legacySQL
SELECT
  t.word
FROM
  [bigquery-public-data:samples.shakespeare] AS t;

정수 범위로 파티션을 나눈 테이블

legacy SQL은 테이블 데코레이터를 사용하여 정수 범위로 파티션을 나눈 테이블에서 특정 파티션을 처리할 수 있습니다. 범위 파티션을 처리하는 핵심은 범위의 시작입니다.

다음 예시에서는 30으로 시작하는 범위 파티션을 쿼리합니다.

#legacySQL
SELECT
  *
FROM
  dataset.table$30;

legacy SQL를 사용해서는 정수 범위로 파티션을 나눈 테이블 전체에서 쿼리를 수행할 수 없습니다. 대신 쿼리가 다음과 같은 오류를 반환합니다.

서브 쿼리 사용

서브 쿼리는 괄호로 묶인 중첩된 SELECT 문입니다. 서브 쿼리의 SELECT 절에서 계산되는 표현식은 테이블의 열이 제공될 때와 같이 외부 쿼리에 제공됩니다.

서브 쿼리를 사용해 집계 및 기타 표현식을 계산할 수 있습니다. 하위 쿼리에서는 모든 범위의 SQL 연산자를 사용할 수 있습니다. 즉, 서브 쿼리 자체도 다른 서브 쿼리를 포함할 수 있으며 조인 및 그룹화 집계 등을 할 수 있습니다.

UNION ALL 쉼표

GoogleSQL과 달리 legacy SQL은 쉼표를 CROSS JOIN 연산자 대신 UNION ALL 연산자로 사용합니다. 이는 발전된 기본 동작입니다. 이전까지 BigQuery에서는 CROSS JOIN이 지원되지 않았으며 BigQuery 사용자가 일반적으로 UNION ALL 쿼리를 작성해야 했기 때문입니다. GoogleSQL에서 union을 수행하는 쿼리는 아주 상세합니다 이때 union 연산자로 쉼표를 사용하면 그러한 쿼리를 상당히 효율적으로 작성할 수 있습니다. 예를 들어 다음 쿼리를 사용하면 여러 날에 걸친 로그에 단일 쿼리를 실행할 수 있습니다.

#legacySQL
SELECT
  FORMAT_UTC_USEC(event.timestamp_in_usec) AS time,
  request_url
FROM
  [applogs.events_20120501],
  [applogs.events_20120502],
  [applogs.events_20120503]
WHERE
  event.username = 'root' AND
  NOT event.source_ip.is_internal;

많은 수의 테이블을 통합하는 쿼리는 데이터 양이 같더라도 단일 테이블의 데이터를 처리하는 쿼리보다 일반적으로 느리게 실행됩니다. 이러한 성능 차이는 테이블이 추가될 때마다 최대 50밀리초까지 늘어날 수 있습니다. 단일 쿼리는 테이블을 1,000개까지 통합할 수 있습니다.

테이블 와일드 카드 함수

테이블 와일드 카드 함수는 BigQuery에 고유한 특별한 유형의 함수입니다. 이러한 함수는 FROM 절에서 여러 유형의 필터 중 하나를 사용하여 테이블 이름 컬렉션에서 비교를 수행하기 위해 사용됩니다. 예를 들어 TABLE_DATE_RANGE 함수를 사용하면 특정 일별 테이블 집합만 쿼리할 수 있습니다. 이러한 함수에 대한 자세한 내용은 테이블 와일드 카드 함수를 참조하세요.

FLATTEN 연산자

(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened))
(FLATTEN((subquery), field_to_be_flattened))

일반적인 SQL 처리 시스템과 달리, BigQuery는 반복되는 데이터를 처리하도록 디자인되었습니다. 따라서 경우에 따라 BigQuery 사용자는 반복되는 레코드 구조를 조작하는 쿼리를 작성할 필요가 있습니다. 이렇게 하는 방법 중 하나는 FLATTEN 연산자를 사용하는 것입니다.

FLATTEN은 스키마의 한 노드를 반복 노드에서 선택사항 노드로 변환합니다. 반복 필드에 하나 이상의 값이 포함된 레코드가 있을 때, FLATTEN은 반복 필드에 있는 값마다 하나씩 여러 개의 레코드를 만듭니다. 레코드에서 선택된 다른 모든 필드는 새로운 각 출력 레코드에 복사됩니다. 여러 번 반복된 데이터를 삭제하고자 한다면 FLATTEN을 반복해서 적용할 수 있습니다.

이에 대한 자세한 내용과 예시를 보려면 데이터 취급을 참조하세요.

JOIN 연산자

BigQuery는 각 FROM 절에서 여러 개의 JOIN 연산자를 지원합니다. 후속 JOIN 연산에서는 이전 JOIN 연산의 결과가 왼쪽 JOIN 입력으로 사용됩니다. 이전 JOIN 입력의 필드는 후속 JOIN 연산자의 ON 절에서 키로 사용될 수 있습니다.

JOIN 유형

BigQuery는 INNER, [FULL|RIGHT|LEFT] OUTER, CROSS JOIN 연산자를 지원합니다. 지정되지 않은 상태로 둘 경우 기본값은 INNER입니다.

CROSS JOIN 연산자는 ON 절을 허용하지 않습니다. CROSS JOIN은 많은 양의 데이터를 반환할 수 있으며, 쿼리를 느리고 비효율적으로 만들거나 쿼리가 허용되는 최대 쿼리별 리소스를 초과하도록 만들 수 있습니다. 이러한 쿼리는 실패하고 오류가 발생합니다. 가능하면 CROSS JOIN을 사용하지 않는 쿼리를 사용하는 것이 좋습니다. 예를 들어 CROSS JOIN 대신 윈도우 함수를 사용하는 것이 보다 효율적인 경우가 많습니다.

EACH 한정자

EACH 한정자는 BigQuery가 여러 파티션을 사용해서 JOIN를 실행하도록 지정합니다. 이 기능은 JOIN의 양쪽이 모두 크다는 것을 알고 있을 때 특히 유용합니다. EACH 한정자는 CROSS JOIN 절에서 사용할 수 없습니다.

많은 경우에 EACH 사용이 권장되지만, 더 이상 그럴 필요는 없습니다. 가능하다면 성능 향상을 위해서는 EACH 한정자 없이 JOIN을 사용하세요. JOIN EACH는 해당 쿼리가 리소스 초과 오류 메시지와 함께 실패했을 때 사용하세요.

세미 조인 및 안티 조인

FROM 절에서 JOIN을 지원하는 것 외에도 BigQuery는 WHERE 절에서 세미 조인과 안티 세미 조인이라는 두 가지 유형의 조인을 지원합니다. 세미 조인은 서브 쿼리에 IN 키워드를 사용하여 지정되고, 안티 조인은 NOT IN 키워드를 사용하여 지정됩니다.

예시

다음 쿼리는 세미 조인을 사용하여 ngram에 있는 첫 번째 단어가 다른 ngram에 있는 두 번째 단어이고 해당 ngram에서 세 번째 단어로 'AND'가 포함된 ngram을 찾습니다.

#legacySQL
SELECT
  ngram
FROM
  [bigquery-public-data:samples.trigrams]
WHERE
  first IN (SELECT
              second
            FROM
              [bigquery-public-data:samples.trigrams]
            WHERE
              third = "AND")
LIMIT 10;

다음 쿼리는 세미 조인을 사용해서 출생자 수가 가장 많은 10개 주에서 출산을 한 50세 이상 여성 수를 반환합니다.

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state IN (SELECT
              state
            FROM
              (SELECT
                 state,
                 COUNT(state) total
               FROM
                 [bigquery-public-data:samples.natality]
               GROUP BY
                 state
               ORDER BY
                 total DESC
               LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

다른 40개 주에서도 이 수치를 보려면 안티 조인을 사용할 수 있습니다. 다음 쿼리는 이전 예시와 거의 동일하지만 IN 대신 NOT IN을 사용하여 출생자 수가 가장 적은 40개 주에서 출산을 한 50세 이상의 여성 수를 반환합니다.

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state NOT IN (SELECT
                  state
                FROM
                  (SELECT
                     state,
                     COUNT(state) total
                   FROM
                     [bigquery-public-data:samples.natality]
                   GROUP BY
                     state
                   ORDER BY
                     total DESC
                   LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

참고:

• BigQuery는 상관된 세미 조인 또는 안티 세미 조인을 지원하지 않습니다. 이 하위 쿼리는 외부 쿼리에서 어떤 필드도 참조할 수 없습니다.
• 세미 조인 또는 안티 세미 조인에서 사용되는 서브 쿼리는 정확히 하나의 필드를 선택해야 합니다.
• 선택된 필드의 유형과 WHERE 절의 다른 쿼리로부터 사용되고 있는 필드의 유형은 정확하게 일치해야 합니다. BigQuery는 세미 조인 또는 안티 세미 조인에 대해 어떤 유형의 강제 변환도 수행하지 않습니다.

WHERE 절

조건자라고도 부르는 WHERE 절은 부울 표현식을 사용하여 FROM 절로 생성된 레코드를 필터링합니다. 부울 AND 및 OR 절을 사용하고 선택적으로 괄호 ()를 사용해서 이를 그룹화하면 여러 조건을 조인할 수 있습니다. WHERE 절에 나열된 필드는 해당 SELECT 절에서 선택할 필요가 없으며, WHERE 절 표현식은 WHERE 절이 속하는 쿼리의 SELECT 절에서 계산되는 표현식을 참조할 수 없습니다.

참고: WHERE 절에서는 집계 함수를 사용할 수 없습니다. 집계 함수의 출력을 필터링해야 하는 경우에는 HAVING 절과 외부 쿼리를 사용하세요.

예

다음 쿼리는 WHERE 절에서 부울 표현식의 논리합(두 표현식이 OR 연산자로 조인됨)을 사용합니다. 표현식 중 어느 하나라도 true를 반환하면 입력 레코드가 WHERE 필터를 통과합니다.

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  (word CONTAINS 'prais' AND word CONTAINS 'ing') OR
  (word CONTAINS 'laugh' AND word CONTAINS 'ed');

OMIT RECORD IF 절

OMIT RECORD IF 절은 BigQuery에 고유한 구조체입니다. 이 기능은 반복되고 중첩된 스키마를 다룰 때 특히 유용합니다. 이 절은 WHERE 절과 비슷하지만 두 가지 중요한 방식에서 차이가 있습니다. 첫째, 이 절에는 배타적 조건이 사용됩니다. 즉, 표현식이 true를 반환할 경우 레코드가 생략되지만, 표현식이 false 또는 null을 반환할 경우 레코드가 보존됩니다. 둘째, OMIT RECORD IF 절은 해당 조건에서 범위가 지정된 집계 함수를 사용할 수 있으며, 일반적으로 이를 사용합니다.

전체 레코드 필터링 이외에도 OMIT...IF를 사용하면 보다 좁은 범위를 지정해서 레코드의 일부만 필터링할 수 있습니다. 이를 위해서는 OMIT...IF 절의 RECORD 대신 스키마에 있는 리프가 아닌 노드 이름을 사용합니다. 이 기능은 BigQuery 사용자들이 거의 사용하지 않는 기능입니다. 이러한 고급 동작에 대한 자세한 내용은 위의 WITHIN 문서에 연결된 문서를 참조하세요.

OMIT...IF를 사용하여 반복 필드에 있는 레코드의 일부를 제외하고 쿼리에서도 독립적으로 반복되는 다른 필드를 선택한 경우, BigQuery는 쿼리에서 반복되는 다른 레코드의 일부를 생략합니다. Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, 오류가 표시되면 GoogleSQL로 전환하는 것이 좋습니다. OMIT...IF 문을 GoogleSQL로 마이그레이션하는 방법에 대한 자세한 내용은 GoogleSQL로 마이그레이션을 참조하세요.

예

WITHIN 한정자에 사용된 예시로 다시 돌아가서, OMIT RECORD IF 를 사용하면 해당 예시에서 WITHIN 및 HAVING을 사용한 것과 동일한 결과를 얻을 수 있습니다.

#legacySQL
SELECT
  repository.url
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

GROUP BY 절

GROUP BY 절을 사용하면 특정 필드 또는 필드 집합에 동일한 값을 가진 행을 그룹화하여 관련 필드의 집계를 계산할 수 있습니다. 그룹화는 WHERE 절에서 필터링이 수행되고 SELECT 절의 표현식이 계산되기 전에 수행됩니다. 표현식 결과는 GROUP BY 절에서 그룹 키로 사용될 수 없습니다.

예

이 쿼리는 트라이그램 표본 데이터 세트에서 가장 일반적으로 사용되는 상위 10개 첫 단어를 찾습니다. 이 쿼리는 GROUP BY 절 사용을 보여줄 뿐만 아니라 GROUP BY 및 ORDER BY 절의 필드 이름 대신 위치 색인을 사용하는 방법을 보여줍니다.

#legacySQL
SELECT
  first,
  COUNT(ngram)
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 10;

GROUP BY 절을 사용하여 수행되는 집계를 그룹화된 집계라고 합니다. 범위가 지정된 집계와 다르게, 그룹화된 집계는 대부분의 SQL 처리 시스템에서 일반적으로 사용됩니다.

EACH 한정자

EACH 한정자는 BigQuery가 여러 파티션을 사용해서 GROUP BY를 실행하도록 지정합니다. 이 기능은 데이터세트에 그룹 키에 대해 많은 고유 값이 포함된다는 것을 알고 있을 때 특히 유용합니다.

많은 경우에 EACH 사용이 권장되지만, 더 이상 그럴 필요는 없습니다. EACH 한정자 없이 GROUP BY를 사용하는 것만으로도 일반적으로 더 나은 성능을 제공합니다. GROUP EACH BY는 해당 쿼리가 리소스 초과 오류 메시지와 함께 실패했을 때 사용하세요.

ROLLUP 함수

ROLLUP 함수를 사용하면 BigQuery는 롤업된 집계를 나타내는 행을 쿼리 결과에 추가합니다. ROLLUP 다음에 나열되는 모든 필드는 단일 괄호 세트로 묶어야 합니다. ROLLUP 함수로 인해 추가된 행에서 NULL은 집계가 롤업되는 열을 나타냅니다.

예

이 쿼리는 natality 표본 데이터세트에서 연도별 남아 및 여아 출생 수를 생성합니다.

#legacySQL
SELECT
  year,
  is_male,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

다음은 이 쿼리의 결과입니다. 여기에서 그룹 키 중 하나 또는 모두가 NULL인 행이 있음을 알 수 있습니다. 이러한 행이 롤업 행입니다.

+------+---------+----------+
| year | is_male |  count   |
+------+---------+----------+
| NULL |    NULL | 12122730 |
| 2000 |    NULL |  4063823 |
| 2000 |   false |  1984255 |
| 2000 |    true |  2079568 |
| 2001 |    NULL |  4031531 |
| 2001 |   false |  1970770 |
| 2001 |    true |  2060761 |
| 2002 |    NULL |  4027376 |
| 2002 |   false |  1966519 |
| 2002 |    true |  2060857 |
+------+---------+----------+

ROLLUP 함수를 사용할 때는 GROUPING 함수를 사용하여 ROLLUP 함수로 인해 추가된 행과 실제로 그룹 키에 대해 NULL 값을 포함하는 행을 구분할 수 있습니다.

예

이 쿼리는 ROLLUP 함수로 인해 추가된 행을 더 쉽게 식별할 수 있도록 이전 예에 GROUPING 함수를 추가합니다.

#legacySQL
SELECT
  year,
  GROUPING(year) as rollup_year,
  is_male,
  GROUPING(is_male) as rollup_gender,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

다음은 새로운 쿼리로 반환되는 결과입니다.

+------+-------------+---------+---------------+----------+
| year | rollup_year | is_male | rollup_gender |  count   |
+------+-------------+---------+---------------+----------+
| NULL |           1 |    NULL |             1 | 12122730 |
| 2000 |           0 |    NULL |             1 |  4063823 |
| 2000 |           0 |   false |             0 |  1984255 |
| 2000 |           0 |    true |             0 |  2079568 |
| 2001 |           0 |    NULL |             1 |  4031531 |
| 2001 |           0 |   false |             0 |  1970770 |
| 2001 |           0 |    true |             0 |  2060761 |
| 2002 |           0 |    NULL |             1 |  4027376 |
| 2002 |           0 |   false |             0 |  1966519 |
| 2002 |           0 |    true |             0 |  2060857 |
+------+-------------+---------+---------------+----------+

참고:

• SELECT 절에서 집계되지 않은 필드는 GROUP BY 절에 나열되어야 합니다.

  #legacySQL
  SELECT
    word,
    corpus,
    COUNT(word)
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th"
  GROUP BY
    word,
    corpus; /* Succeeds because all non-aggregated fields are group keys. */

  #legacySQL
  SELECT
    word,
    corpus,
    COUNT(word)
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th"
  GROUP BY
    word;  /* Fails because corpus is not aggregated nor is it a group key. */

• SELECT 절에서 계산된 표현식은 해당 GROUP BY 절에서 사용할 수 없습니다.

  #legacySQL
  SELECT
    word,
    corpus,
    COUNT(word) word_count
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th"
  GROUP BY
    word,
    corpus,
    word_count;  /* Fails because word_count is not visible to this GROUP BY clause. */

• float 및 double 값을 사용한 그룹화는 해당 유형에 대한 같음 기능이 잘 정의되지 않기 때문에 지원되지 않습니다.
• 시스템이 대화형으로 작동하므로 다수의 그룹을 생성하는 쿼리는 실패할 수 있습니다. GROUP BY 대신 TOP 함수를 사용하면 일부 확장 문제를 해결할 수 있습니다.

HAVING 절

HAVING 절은 WHERE 절과 거의 같지만 SELECT 절 다음에 평가되므로 모든 계산된 표현식의 결과는 HAVING 절에 표시됩니다. HAVING 절은 해당 SELECT 절의 출력만 참조할 수 있습니다.

예

이 쿼리는 ngram 표본 데이터세트에서 문자 a를 포함하고 최대 10,000회 발생한 가장 일반적으로 사용되는 first 단어를 계산합니다.

#legacySQL
SELECT
  first,
  COUNT(ngram) ngram_count
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
HAVING
  first contains "a"
  AND ngram_count < 10000
ORDER BY
  2 DESC
LIMIT 10;

ORDER BY 절

ORDER BY 절은 하나 이상의 키 필드를 사용하여 오름차순 또는 내림차순으로 쿼리 결과를 정렬합니다. 여러 필드 또는 별칭으로 정렬하려면 쉼표로 구분된 목록으로 입력합니다. 결과는 항목이 나열된 순서에 따라 필드에 정렬됩니다. DESC(내림차순) 또는 ASC(오름차순)를 사용하여 정렬 방향을 지정합니다. 기본값은 ASC입니다. 각 정렬 키에 대해 다른 정렬 방향을 지정할 수 있습니다.

ORDER BY 절은 SELECT 절 다음에 평가되므로, SELECT에서 계산된 표현식의 결과를 참조할 수 있습니다. SELECT 절에서 필드에 별칭이 지정된 경우, 이 별칭을 ORDER BY 절에서 사용해야 합니다.

LIMIT 절

LIMIT 절은 반환된 결과 집합에 포함되는 행 수를 제한합니다. BigQuery 쿼리는 일반적으로 매우 많은 수의 행에 대해 작동하기 때문에, LIMIT를 사용하면 행의 일부만 처리함으로써 쿼리 실행 시간이 오래 걸리는 것을 효과적으로 방지할 수 있습니다.

참고:

• 요구사항이 충족되면 LIMIT 절이 처리를 중지하고 결과를 반환합니다. 이러한 방식은 일부 쿼리의 처리 시간을 줄여줄 수 있지만 사용자가 COUNT 또는 ORDER BY 절과 같은 집계 함수를 지정할 경우, 결과를 반환하기 전에 여전히 전체 결과 집합을 처리해야 합니다. LIMIT 절은 마지막으로 평가됩니다.
• LIMIT 절이 포함된 쿼리는 출력 결과 집합의 순서를 보장하는 연산자가 쿼리에 없을 경우 여전히 비확정적일 수 있습니다. 이것은 BigQuery가 많은 수의 병렬 작업자를 사용하여 실행되기 때문입니다. 병렬 작업이 반환되는 순서는 보장되지 않습니다.
• LIMIT 절은 함수를 포함할 수 없으며, 숫자 상수만 사용합니다.
• LIMIT 절이 사용되면 동일한 쿼리에서 처리된 총 바이트 수와 청구된 바이트 수가 다를 수 있습니다.

쿼리 문법

위에서는 BigQuery SELECT 문의 절을 개별적으로 자세히 설명했습니다. 여기에서는 개별 섹션에 대한 링크가 포함된 축약 형태로 SELECT 문의 전체 문법을 보여줍니다.

query:
    SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ]
    [ FROM from_body
      [ WHERE bool_expression ]
      [ OMIT RECORD IF bool_expression]
      [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ]
      [ HAVING bool_expression ]
      [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ]
      [ LIMIT n ]
    ];

from_body:
    {
      from_item [, ...] |  # Warning: Comma means UNION ALL here
      from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] |
      (FLATTEN({ table_name | (query) }, field_name_or_alias)) |
      table_wildcard_function
    }

from_item:
    { table_name | (query) } [ [ AS ] alias ]

join_type:
    { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS }

join_predicate:
    field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...]

expression:
    {
      literal_value |
      field_name_or_alias |
      function_call
    }

bool_expression:
    {
      expression_which_results_in_a_boolean_value |
      bool_expression AND bool_expression |
      bool_expression OR bool_expression |
      NOT bool_expression
    }

표기법:

• 대괄호 '[ ]'는 절(선택사항)을 의미합니다.
• 중괄호 '{ }'는 선택사항 조합을 둘러쌉니다.
• 세로 막대 '|'는 논리 OR를 의미합니다.
• 대괄호 '[, ...]' 안에서 줄임표 앞에 있는 쉼표 또는 키워드는 앞의 항목이 지정된 구분자를 사용한 목록으로 반복될 수 있음을 나타냅니다.
• 괄호 '( )'는 리터럴 괄호를 의미합니다.

지원되는 함수 및 연산자

SELECT 문의 절 대부분에 함수가 지원됩니다. 함수에서 참조되는 필드를 SELECT 절에 나열할 필요가 없습니다. 따라서 다음 쿼리는 clicks 필드가 직접 표시되지 않았더라도 유효합니다.

#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;

집계 함수

집계 함수는 큰 데이터세트의 요약을 나타내는 값을 반환하므로, 로그를 분석할 때 특히 유용합니다. 집계 함수는 값의 컬렉션을 대상으로 연산하고, 테이블, 그룹 또는 범위별 단일 값을 반환합니다.

• 테이블 집계

  집계 함수를 사용하여 테이블에 있는 모든 적격한 행을 요약합니다. 예를 들면 다음과 같습니다.

  SELECT COUNT(f1) FROM ds.Table;

• 그룹 집계

  집계 함수 및 집계되지 않는 필드를 지정하는 GROUP BY 절을 사용하여 그룹별로 행을 요약합니다. 예를 들면 다음과 같습니다.

  SELECT COUNT(f1) FROM ds.Table GROUP BY b1;

  TOP 함수는 그룹 집계의 특수한 경우를 나타냅니다.

• 범위가 지정된 집계

  이 기능은 중첩된 필드를 포함하는 테이블에만 적용됩니다.
  정의된 범위 내에 있는 반복되는 값을 집계하려면 집계 함수와 WITHIN 키워드를 사용합니다. 예를 들면 다음과 같습니다.

  SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;

  범위는 전체 행에 해당하는 RECORD이거나 노드(행에서 반복되는 필드)일 수 있습니다. 집계 함수는 범위 내에 있는 값에 대해서만 작동하고, 각 레코드 또는 노드에 대해 집계된 결과를 반환합니다.

다음 옵션 중 하나를 사용하여 집계 함수에 제한을 적용할 수 있습니다.

• subselect 쿼리의 별칭. 이 제한은 외부 WHERE 절에서 지정됩니다.

  #legacySQL
  SELECT corpus, count_corpus_words
  FROM
    (SELECT corpus, count(word) AS count_corpus_words
    FROM [bigquery-public-data:samples.shakespeare]
    GROUP BY corpus) AS sub_shakespeare
  WHERE count_corpus_words > 4000

• HAVING 절의 별칭.

  #legacySQL
  SELECT corpus, count(word) AS count_corpus_words
  FROM [bigquery-public-data:samples.shakespeare]
  GROUP BY corpus
  HAVING count_corpus_words > 4000;

또한 GROUP BY 또는 ORDER BY 절에서 별칭을 참조할 수 있습니다.

구문

AVG(numeric_expr)

numeric_expr로 계산된 행 그룹의 값 평균을 반환합니다. NULL 값을 포함한 행은 계산에 포함되지 않습니다.

BIT_AND(numeric_expr)

모든 행에서 각 numeric_expr 인스턴스 간에 비트 AND 연산을 수행한 결과를 반환합니다. NULL 값은 무시됩니다. numeric_expr의 모든 인스턴스가 NULL로 평가되면 NULL을 반환합니다.

BIT_OR(numeric_expr)

모든 행에서 각 numeric_expr 인스턴스 간에 비트 OR 연산을 수행한 결과를 반환합니다. NULL 값은 무시됩니다. numeric_expr의 모든 인스턴스가 NULL로 평가되면 NULL을 반환합니다.

BIT_XOR(numeric_expr)

모든 행에서 각 numeric_expr 인스턴스 간에 비트 XOR 연산을 수행한 결과를 반환합니다. NULL 값은 무시됩니다. numeric_expr의 모든 인스턴스가 NULL로 평가되면 NULL을 반환합니다.

CORR(numeric_expr, numeric_expr)

숫자 쌍 집합의 피어슨 상관 계수를 반환합니다.

COUNT(*)

함수의 범위에 있는 전체 값 수(NULL 값과 NULL이 아닌 값)를 반환합니다. TOP 함수와 함께 COUNT(*)를 사용하지 않는 한, 계산할 필드 수를 명시적으로 지정하는 것이 더 좋습니다.

COUNT([DISTINCT] field [, n])

함수의 범위에 있는 NULL이 아닌 값의 전체 개수를 반환합니다.

DISTINCT 키워드를 사용하는 경우 이 함수는 지정된 필드의 고유 값 수를 반환합니다. DISTINCT에 반환된 값은 통계적 근사치이며 정확성을 보장하지 않습니다.

정확한 답을 원하면 EXACT_COUNT_DISTINCT()를 사용합니다.

COUNT(DISTINCT)에서 더욱 높은 정확도가 필요한 경우 정확한 결과가 보장되는 기준점을 지정하는 두 번째 매개변수인 n을 지정하면 됩니다. 기본적으로 n은 1,000이지만 더 큰 n을 지정하면 최대 n 값까지 COUNT(DISTINCT)에 정확한 결과를 가져올 수 있습니다. 하지만 n에 대해 더 큰 값을 지정하면 이 연산자의 확장성이 줄어들고, 쿼리 실행 시간이 크게 늘어나거나 쿼리가 실패할 수 있습니다.

정확한 고유 값 수를 계산하려면 EXACT_COUNT_DISTINCT를 사용합니다. 또는 보다 확장 가능한 접근 방식을 위해 관련 필드에서 GROUP EACH BY를 사용하고 COUNT(*)를 적용할 수 있습니다. GROUP EACH BY 접근 방식은 보다 확장 가능하지만, 약간의 초기 성능 저하가 발생할 수 있습니다.

COVAR_POP(numeric_expr1, numeric_expr2)

numeric_expr1 및 numeric_expr2로 계산된 값의 모집단 공분산을 계산합니다.

COVAR_SAMP(numeric_expr1, numeric_expr2)

numeric_expr1 및 numeric_expr2로 계산된 값의 표본 집단 공분산을 계산합니다.

EXACT_COUNT_DISTINCT(field)

지정된 필드에서 NULL이 아닌 고유 값의 정확한 개수를 반환합니다. 확장성과 성능을 개선하려면 COUNT(DISTINCT 필드)를 사용합니다.

FIRST(expr)

함수 범위에서 첫 번째 순차 값을 반환합니다.

GROUP_CONCAT('str' [, separator])

여러 개의 문자열을 단일 문자열에 연결합니다. 여기서 각 값은 선택적 separator 매개변수로 구분됩니다. separator가 생략되면 BigQuery는 쉼표로 구분된 문자열을 반환합니다.

소스 데이터의 문자열에 큰따옴표 문자가 포함된 경우, GROUP_CONCAT는 큰따옴표가 추가된 상태로 문자열을 반환합니다. 예를 들어 a"b 문자열은 "a""b"로 반환됩니다. 이러한 문자열이 큰따옴표가 추가된 상태로 반환되지 않도록 하려면 GROUP_CONCAT_UNQUOTED를 사용합니다.

예시:

#legacySQL
SELECT
  GROUP_CONCAT(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);

GROUP_CONCAT_UNQUOTED('str' [, separator])

여러 개의 문자열을 단일 문자열에 연결합니다. 여기서 각 값은 선택적 separator 매개변수로 구분됩니다. separator가 생략되면 BigQuery는 쉼표로 구분된 문자열을 반환합니다.

GROUP_CONCAT와 다르게, 이 함수는 큰따옴표 문자를 포함하는 반환된 값에 큰따옴표를 추가하지 않습니다. 예를 들어 a"b 문자열은 a"b로 반환됩니다.

예시:

#legacySQL
SELECT
  GROUP_CONCAT_UNQUOTED(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);

LAST(field)

함수 범위에서 마지막 순차 값을 반환합니다.

MAX(field)

함수 범위에서 최댓값을 반환합니다.

MIN(field)

함수 범위에서 최솟값을 반환합니다.

NEST(expr)

현재 집계 범위의 모든 값을 반복 필드로 집계합니다. 예를 들어 "SELECT x, NEST(y) FROM ... GROUP BY x" 쿼리는 각 고유한 x 값마다 출력 레코드를 한 개 반환하고 쿼리 입력에서 x 값과 쌍을 이루는 모든 y 값의 반복 필드를 포함합니다. NEST 함수에는 GROUP BY 절이 필요합니다.

BigQuery는 쿼리 결과를 자동으로 평면화하므로 최상위 쿼리에서 NEST 함수를 사용할 경우, 결과에 반복 필드가 포함되지 않습니다. 같은 쿼리에서 즉시 사용할 수 있도록 중간 결과를 생성하는 subselect를 사용할 때 NEST 함수를 사용합니다.

NTH(n, field)

함수 범위에서 n번째 순차 값을 반환합니다. 여기서 n은 상수입니다. NTH 함수는 1부터 계산을 시작하므로 0번째 항이 없습니다. 함수 범위에 n개 미만의 값이 포함된 경우, 함수가 NULL을 반환합니다.

QUANTILES(expr[, buckets])

입력 표현식의 대략적인 최솟값, 최댓값, 백분위수를 계산합니다. NULL 입력 값은 무시됩니다. 비어 있거나 배타적인 NULL 입력은 NULL 출력을 가져옵니다. 계산된 백분위수는 계산 결과에 최솟값 및 최댓값을 포함하는 선택적인 buckets 매개변수로 제어됩니다. 대략적인 N 백분위수를 계산하려면 N+1 buckets를 사용합니다. buckets의 기본값은 100입니다. (참고: 기본값 100은 백분위수를 추정하지 않습니다. 백분위수를 추정하려면 최소한 101 buckets를 사용합니다.) 명시적으로 지정된 경우, buckets는 최소한 2 이상이어야 합니다.

백분위수당 분수 오차는 epsilon = 1 / buckets입니다. 즉, 버킷 수가 늘수록 오차가 줄어듭니다. 예를 들면 다음과 같습니다.

QUANTILES(<expr>, 2) # computes min and max with 50% error.
QUANTILES(<expr>, 3) # computes min, median, and max with 33% error.
QUANTILES(<expr>, 5) # computes quartiles with 25% error.
QUANTILES(<expr>, 11) # computes deciles with 10% error.
QUANTILES(<expr>, 21) # computes vigintiles with 5% error.
QUANTILES(<expr>, 101) # computes percentiles with 1% error.

NTH 함수를 사용하여 특정 백분위수를 선택할 수 있지만, NTH는 1부터 시작하며, QUANTILES는 첫 번째 위치의 최솟값('0번째' 백분위수)과 마지막 위치의 최댓값('100번째' 백분위수 또는 'N번째' N 백분위수)을 반환합니다. 예를 들어 NTH(11, QUANTILES(expr, 21))는 expr의 중앙값을 추정하고, 여기서 NTH(20, QUANTILES(expr, 21))는 expr의 19번째 20분위수(95번째 백분위수)를 추정합니다. 두 함수 모두 오차 범위는 5%입니다.

정확도를 높이려면 버킷을 더 많이 사용합니다. 예를 들어 이전 계산의 오차 범위를 5%에서 0.1%로 줄이려면 버킷을 21개 대신 1,001개 사용하고, 그에 따라 NTH 함수에 대해 인수를 조정합니다. 0.1% 오차로 중앙값을 계산하려면 NTH(501, QUANTILES(expr, 1001))를 사용하고, 0.1% 오차로 95번째 백분위수를 구하려면 NTH(951, QUANTILES(expr, 1001))를 사용합니다.

STDDEV(numeric_expr)

numeric_expr로 계산된 값의 표준 편차를 반환합니다. NULL 값이 있는 행은 계산에 포함되지 않습니다. STDDEV 함수는 STDDEV_SAMP의 별칭입니다.

STDDEV_POP(numeric_expr)

numeric_expr로 계산된 값의 모집단 표준 편차를 계산합니다. 전체 관심 대상 모집단이 포함된 데이터세트의 표준 편차를 계산하려면 STDDEV_POP()를 사용합니다. 데이터세트가 모집단의 대표 표본으로만 구성된 경우, 대신 STDDEV_SAMP()를 사용합니다. 모집단 및 표본 표준 편차 비교에 대한 자세한 내용은 위키백과에서 표준 편차를 참조하세요.

STDDEV_SAMP(numeric_expr)

numeric_expr로 계산된 값의 표본 집단 표준 편차를 계산합니다. 모집단의 대표 표본을 기반으로 전체 모집단의 표준 편차를 계산하려면 STDDEV_SAMP()를 사용합니다. 데이터세트가 전체 모집단으로 구성된 경우, 대신 STDDEV_POP()를 사용합니다. 모집단 및 표본 표준 편차 비교에 대한 자세한 내용은 위키백과에서 표준 편차를 참조하세요.

SUM(field)

함수 범위에서 값의 전체 합계를 반환합니다. 수치 데이터 유형에서만 사용합니다.

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

빈도별 최상위 max_records 레코드를 반환합니다. 자세한 내용은 아래의 TOP 설명을 참조하세요.

UNIQUE(expr)

함수 범위에서 NULL이 아닌 고유 값 집합을 정의되지 않은 순서로 반환합니다. EACH 키워드가 없는 큰 GROUP BY 절과 비슷하게, 고유한 값이 너무 많으면 이 쿼리가 '리소스 초과' 오류와 함께 실패합니다. 하지만 GROUP BY와 다르게, UNIQUE 함수는 범위가 지정된 집계에 적용할 수 있으며, 값 개수가 제한된 중첩 필드에서 연산을 효율적으로 수행할 수 있습니다.

VARIANCE(numeric_expr)

numeric_expr로 계산된 값의 분산을 계산합니다. NULL 값이 있는 행은 계산에 포함되지 않습니다. VARIANCE 함수는 VAR_SAMP의 별칭입니다.

VAR_POP(numeric_expr)

numeric_expr로 계산된 값의 모집단 분산을 계산합니다. 모집단 및 표본 표준 편차 비교에 대한 자세한 내용은 위키백과에서 표준 편차를 참조하세요.

VAR_SAMP(numeric_expr)

numeric_expr로 계산된 값의 표본 집단 분산을 계산합니다. 모집단 및 표본 표준 편차 비교에 대한 자세한 내용은 위키백과에서 표준 편차를 참조하세요.

TOP() 함수

TOP는 GROUP BY 절의 대안 함수입니다. 이 함수는 GROUP BY ... ORDER BY ... LIMIT ...의 단순 문법으로 사용됩니다. 일반적으로 TOP 함수는 전체 ... GROUP BY ... ORDER BY ... LIMIT ... 쿼리보다 수행 속도가 빠르지만, 대략적인 결과만 반환할 수 있습니다. 다음은 TOP 함수의 문법입니다.

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

SELECT 절에서 TOP를 사용할 때는 필드 중 하나로 COUNT(*)를 포함해야 합니다.

TOP() 함수를 사용하는 쿼리는 TOP 필드 및 COUNT(*) 값의 두 가지 필드만 반환할 수 있습니다.

field|alias

반환할 필드 또는 별칭입니다.

max_values

[선택사항] 반환할 결과의 최대 개수입니다. 기본값은 20입니다.

multiplier

COUNT(*)로 반환되는 값을 지정된 배수만큼 늘려주는 양의 정수입니다.

TOP() 예시

• TOP()를 사용하는 기본 쿼리 예시

  다음 쿼리는 TOP()를 사용하여 10개 행을 반환합니다.

  예시 1:

  #legacySQL
  SELECT
    TOP(word, 10) as word, COUNT(*) as cnt
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th";

  예시 2:

  #legacySQL
  SELECT
    word, left(word, 3)
  FROM
    (SELECT TOP(word, 10) AS word, COUNT(*)
       FROM [bigquery-public-data:samples.shakespeare]
       WHERE word CONTAINS "th");

• TOP() 및 GROUP BY...ORDER BY...LIMIT의 비교

  이 쿼리는 'th'가 포함된 가장 자주 사용되는 최상위 10개 단어와 해당 단어가 사용된 문서 수를 순서대로 반환합니다. TOP 쿼리가 더 빠르게 실행됩니다.

  TOP() 제외 예시:

  #legacySQL
  SELECT
    word, COUNT(*) AS cnt
  FROM
    ds.Table
  WHERE
    word CONTAINS 'th'
  GROUP BY
    word
  ORDER BY
    cnt DESC LIMIT 10;

  TOP()의 예시:

  #legacySQL
  SELECT
    TOP(word, 10), COUNT(*)
  FROM
    ds.Table
  WHERE
    word contains 'th';

• multiplier 매개변수 사용

  다음 쿼리는 쿼리 결과에 영향을 주는 multiplier 매개변수를 보여줍니다. 첫 번째 쿼리는 Wyoming에서의 월별 출생 수를 반환합니다. 두 번째 쿼리는 multiplier 매개변수를 사용하여 cnt 값에 100을 곱합니다.

  multiplier 매개변수 제외 예시:

  #legacySQL
  SELECT
    TOP(month,3) as month, COUNT(*) as cnt
  FROM
    [bigquery-public-data:samples.natality]
  WHERE
    state = "WY";

  반환:

  +-------+-------+
  | month |  cnt  |
  +-------+-------+
  |   7   | 19594 |
  |   5   | 19038 |
  |   8   | 19030 |
  +-------+-------+
  

  multiplier 매개변수 포함 예시:

  #legacySQL
  SELECT
    TOP(month,3,100) as month, COUNT(*) as cnt
  FROM
    [bigquery-public-data:samples.natality]
  WHERE
    state = "WY";

  반환:

  +-------+---------+
  | month |   cnt   |
  +-------+---------+
  |   7   | 1959400 |
  |   5   | 1903800 |
  |   8   | 1903000 |
  +-------+---------+
  

참고: TOP를 사용하려면 SELECT 절에 COUNT(*)가 있어야 합니다.

고급 예시

• 조건별로 그룹화된 평균 및 표준 편차

  다음 쿼리는 2003년 Ohio에서 흡연자 또는 비흡연자 산모별로 그룹화된 출산 가중치의 평균과 표준 편차를 반환합니다.

  예시:

  #legacySQL
  SELECT
    cigarette_use,
    /* Finds average and standard deviation */
    AVG(weight_pounds) baby_weight,
    STDDEV(weight_pounds) baby_weight_stdev,
    AVG(mother_age) mother_age
  FROM
    [bigquery-public-data:samples.natality]
  WHERE
    year=2003 AND state='OH'
  /* Group the result values by those */
  /* who smoked and those who didn't.  */
  GROUP BY
    cigarette_use;

• 집계 값을 사용하여 쿼리 결과 필터링

  집계 값을 사용하여 쿼리 결과를 필터링하려면(예: SUM의 값으로 필터링), HAVING 함수를 사용합니다. HAVING은 집계 전 각 행에서 작동하는 WHERE와 반대로, 집계 함수로 확인된 결과와 값을 비교합니다.

  예시:

  #legacySQL
  SELECT
    state,
    /* If 'is_male' is True, return 'Male', */
    /* otherwise return 'Female' */
    IF (is_male, 'Male', 'Female') AS sex,
    /* The count value is aliased as 'cnt' */
    /* and used in the HAVING clause below. */
    COUNT(*) AS cnt
  FROM
    [bigquery-public-data:samples.natality]
  WHERE
    state != ''
  GROUP BY
    state, sex
  HAVING
    cnt > 3000000
  ORDER BY
    cnt DESC

  반환:

  +-------+--------+---------+
  | state |  sex   |   cnt   |
  +-------+--------+---------+
  | CA    | Male   | 7060826 |
  | CA    | Female | 6733288 |
  | TX    | Male   | 5107542 |
  | TX    | Female | 4879247 |
  | NY    | Male   | 4442246 |
  | NY    | Female | 4227891 |
  | IL    | Male   | 3089555 |
  +-------+--------+---------+
  

산술 연산자

산술 연산자는 숫자 인수를 사용하고 숫자 결과를 반환합니다. 각 인수는 숫자 리터럴이거나 쿼리로 반환된 숫자 값일 수 있습니다. 산술 연산 값이 정의되지 않은 결과로 평가될 경우 연산에서 NULL이 반환됩니다.

구문

비트 함수

비트 함수는 개별 비트 수준에서 작동하며 숫자 인수가 필요합니다. 비트 함수에 대한 자세한 내용은 비트 연산을 참조하세요.

세 가지 추가 비트 함수(BIT_AND, BIT_OR, BIT_XOR)는 집계 함수를 참조하세요.

구문

Cast 변환 함수

Cast 변환 함수는 숫자 표현식의 데이터 유형을 변환합니다. Cast 변환 함수는 비교 함수에 사용되는 인수가 동일한 데이터 유형을 갖도록 보장하는 데 특히 유용합니다.

구문

BOOLEAN(<numeric_expr>)

• <numeric_expr>이 0이 아니고 NULL도 아니면 true를 반환합니다.
• <numeric_expr>이 0인 경우 false를 반환합니다.
• <numeric_expr>이 NULL이면 NULL을 반환합니다.

BYTES(string_expr)

string_expr을 bytes 유형의 값으로 반환합니다.

CAST(expr AS type)

expr을 type 유형의 변수로 변환합니다.

FLOAT(expr)

expr을 double로 반환합니다. expr은 '45.78'과 같은 문자열일 수 있습니다. 하지만 값이 숫자가 아니면 이 함수는 NULL을 반환합니다.

HEX_STRING(numeric_expr)

numeric_expr을 16진수 문자열로 반환합니다.

INTEGER(expr)

expr을 64비트 정수로 변환합니다.

• expr이 정수 값에 해당되지 않는 문자열이면 NULL을 반환합니다.
• expr이 타임스탬프이면 Unix epoch 이후의 마이크로초 수를 반환합니다.

STRING(numeric_expr)

numeric_expr을 문자열로 반환합니다.

비교 함수

비교 함수는 다음과 같은 비교 유형에 따라 true 또는 false를 반환합니다.

• 두 표현식 비교
• 지정된 목록에 포함되는지 여부, NULL인지 여부 또는 기본값이 아닌 선택적 값인지 여부 등과 같은 특정 기준에 대해 표현식 또는 표현식 집합 비교

아래에 나열된 함수 중 일부에서는 true 또는 false가 아닌 다른 값이 반환되지만, 비교 연산을 기준으로 해당 함수에서 값이 반환됩니다.

숫자 또는 문자열 표현식을 비교 함수의 인수로 사용할 수 있습니다. (문자열 상수는 작은 따옴표 또는 큰따옴표로 묶어야 합니다.) 표현식은 쿼리로 가져온 리터럴 또는 값일 수 있습니다. 비교 함수는 WHERE 절에서 필터링 조건으로 가장 자주 사용되지만, 다른 절에서 사용될 수도 있습니다.

구문

expr1 = expr2

두 표현식이 같으면 true를 반환합니다.

expr1 != expr2
expr1 <> expr2

두 표현식이 같지 않으면 true를 반환합니다.

expr1 > expr2

expr1이 expr2보다 크면 true를 반환합니다.

expr1 < expr2

expr1이 expr2보다 작으면 true를 반환합니다.

expr1 >= expr2

expr1이 expr2 이상이면 true를 반환합니다.

expr1 <= expr2

expr1이 expr2 이하이면 true를 반환합니다.

expr1 BETWEEN expr2 AND expr3

expr1 값이 expr2 이상이고 expr3 이하이면 true를 반환합니다.

expr IS NULL

expr이 NULL이면 true를 반환합니다.

expr IN(expr1, expr2, ...)

expr이 expr1, expr2 또는 괄호 안에 있는 값과 일치하면 true를 반환합니다. IN 키워드는 (expr = expr1 || expr = expr2 || ...)의 약식 표현입니다. IN 키워드를 사용한 표현식은 상수여야 하고 expr의 데이터 유형과 일치해야 합니다. IN 절은 세미 조인과 안티 조인을 만드는 데에도 사용할 수 있습니다. 자세한 내용은 세미 조인 및 안티 조인을 참조하세요.

COALESCE(<expr1>, <expr2>, ...)

NULL이 아닌 첫 번째 인수를 반환합니다.

GREATEST(numeric_expr1, numeric_expr2, ...)

가장 큰 numeric_expr 매개변수를 반환합니다. 모든 매개변수는 숫자여야 하고, 모두 동일한 유형이어야 합니다. 매개변수 중 하나가 NULL이면 이 함수는 NULL을 반환합니다.

NULL 값을 무시하려면 IFNULL 함수를 사용하여 NULL 값을 비교에 영향을 주지 않는 값으로 변경합니다. 다음 코드 예시에서는 IFNULL 함수를 사용하여 NULL 값을 양의 숫자 간 비교에 영향을 주지 않는 -1로 변경합니다.

SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);

IFNULL(expr, null_default)

expr이 null이 아니면 expr을 반환하고, 그렇지 않으면 null_default를 반환합니다.

IS_INF(numeric_expr)

numeric_expr이 양의 무한대이거나 음의 무한대이면 true를 반환합니다.

IS_NAN(numeric_expr)

numeric_expr이 특수 NaN 숫자 값이면 true를 반환합니다.

IS_EXPLICITLY_DEFINED(expr)

이 함수는 지원 중단되었습니다. 대신 expr IS NOT NULL을 사용하세요.

LEAST(numeric_expr1, numeric_expr2, ...)

가장 작은 numeric_expr 매개변수를 반환합니다. 모든 매개변수는 숫자여야 하고, 모두 동일한 유형이어야 합니다. 매개변수 중 하나가 NULL이면 이 함수는 NULL을 반환합니다.

NVL(expr, null_default)

expr이 null이 아니면 expr을 반환하고, 그렇지 않으면 null_default를 반환합니다. NVL 함수는 IFNULL의 별칭입니다.

날짜 및 시간 함수

다음 함수를 사용하면 UNIX 타임스탬프, 날짜 문자열, TIMESTAMP 데이터 유형에 대한 날짜 및 시간을 조작할 수 있습니다. TIMESTAMP 데이터 유형 사용에 대한 자세한 내용은 TIMESTAMP 사용을 참조하세요.

UNIX 타임스탬프에서 작동하는 날짜 및 시간 함수는 UNIX 시간에서 작동합니다. 날짜 및 시간 함수는 UTC 시간대를 기준으로 값을 반환합니다.

구문

CURRENT_DATE()

현재 날짜를 인간이 읽을 수 있는 문자열인 %Y-%m-%d 형식으로 반환합니다.

예시:

SELECT CURRENT_DATE();

반환: 2013-02-01

CURRENT_TIME()

서버의 현재 시간을 인간이 읽을 수 있는 %H:%M:%S 형식으로 반환합니다.

예시:

SELECT CURRENT_TIME();

반환: 01:32:56

CURRENT_TIMESTAMP()

서버 현재 시간의 TIMESTAMP 데이터 유형을 %Y-%m-%d %H:%M:%S 형식으로 반환합니다.

예시:

SELECT CURRENT_TIMESTAMP();

반환: 2013-02-01 01:33:35 UTC

DATE(<timestamp>)

TIMESTAMP 데이터 유형을 인간이 읽을 수 있는 문자열인 %Y-%m-%d 형식으로 반환합니다.

예시:

SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));

반환: 2012-10-01

DATE_ADD(<timestamp>,<interval>,
                 <interval_units>)

지정된 간격을 TIMESTAMP 데이터 유형에 추가합니다. 가능한 interval_units 값에는 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND가 포함됩니다. interval이 음의 숫자인 경우, TIMESTAMP 데이터 유형에서 해당 간격을 뺍니다.

예시:

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");

반환: 2017-10-01 02:03:04 UTC

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");

반환: 2007-10-01 02:03:04 UTC

DATEDIFF(<timestamp1>,<timestamp2>)

2개의 TIMESTAMP 데이터 유형 사이의 일 수를 반환합니다. 두 번째 TIMESTAMP 데이터 유형 다음에 첫 번째 TIMESTAMP 데이터 유형이 올 경우 결과가 양수이고 그렇지 않으면 결과가 음수입니다.

예시:

SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));

반환: 466

예시:

SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));

반환: -466

DAY(<timestamp>)

TIMESTAMP 데이터 유형의 월중 일을 1에서 31(포함) 사이의 정수로 반환합니다.

예시:

SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));

반환: 2

DAYOFWEEK(<timestamp>)

TIMESTAMP 데이터 유형의 주중 요일을 1(일요일)에서 7(토요일)(포함) 사이의 정수로 반환합니다.

예시:

SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));

반환: 2

DAYOFYEAR(<timestamp>)

TIMESTAMP 데이터 유형의 연중 일을 1에서 366(포함) 사이의 정수로 반환합니다. 정수 1은 1월 1일을 의미합니다.

예시:

SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));

반환: 275

FORMAT_UTC_USEC(<unix_timestamp>)

UNIX 타임스탬프를 인간이 읽을 수 있는 문자열 표현인 YYYY-MM-DD HH:MM:SS.uuuuuu 형식으로 반환합니다.

예시:

SELECT FORMAT_UTC_USEC(1274259481071200);

반환: 2010-05-19 08:58:01.071200

HOUR(<timestamp>)

TIMESTAMP 데이터 유형의 시간을 0에서 23(포함) 사이의 정수로 반환합니다.

예시:

SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));

반환: 5

MINUTE(<timestamp>)

TIMESTAMP 데이터 유형의 분을 0에서 59(포함) 사이의 정수로 반환합니다.

예시:

SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));

반환: 23

MONTH(<timestamp>)

TIMESTAMP 데이터 유형의 월을 1에서 12(포함) 사이의 정수로 반환합니다.

예시:

SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));

반환: 10

MSEC_TO_TIMESTAMP(<expr>)

밀리초 단위의 UNIX 타임스탬프를 TIMESTAMP 데이터 유형으로 변환합니다.

예시:

SELECT MSEC_TO_TIMESTAMP(1349053323000);

반환: 2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

반환: 2012-10-01 01:02:04 UTC

NOW()

현재 UNIX 타임스탬프를 마이크로초 단위로 반환합니다.

예시:

SELECT NOW();

반환: 1359685811687920

PARSE_UTC_USEC(<date_string>)

날짜 문자열을 마이크로초 단위의 UNIX 타임스탬프로 변환합니다. date_string은 YYYY-MM-DD HH:MM:SS[.uuuuuu] 형식이어야 합니다. 초의 소수점 이하 부분은 최대 6자리 숫자까지 가능하거나, 생략할 수 있습니다.

TIMESTAMP_TO_USEC는 날짜 문자열 대신 TIMESTAMP 데이터 유형 인수를 변환하는 동일한 기능의 함수입니다.

예시:

SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");

반환: 1349056984000000

QUARTER(<timestamp>)

TIMESTAMP 데이터 유형의 연중 분기를 1에서 4(포함) 사이의 정수로 반환합니다.

예시:

SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));

반환: 4

SEC_TO_TIMESTAMP(<expr>)

초 단위의 UNIX 타임스탬프를 TIMESTAMP 데이터 유형으로 변환합니다.

예시:

SELECT SEC_TO_TIMESTAMP(1355968987);

반환: 2012-12-20 02:03:07 UTC

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

반환: 2012-12-20 02:03:07 UTC

SECOND(<timestamp>)

TIMESTAMP 데이터 유형의 초를 0에서 59(포함) 사이의 정수로 반환합니다.

윤초 중에는 정수 범위가 0에서 60(포함) 사이입니다.

예시:

SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));

반환: 48

STRFTIME_UTC_USEC(<unix_timestamp>,
                  <date_format_str>)

날짜 문자열을 인간이 읽을 수 있는 date_format_str 형식으로 반환합니다. date_format_str에는 날짜 관련 구두점 문자(예: / 및 -)와 C++의 strftime 함수에서 허용되는 특수 문자(예: 개월을 나타내는 %d)가 포함될 수 있습니다.

특정 월의 모든 데이터를 가져올 때와 같이 시간 간격에 따라 쿼리 데이터를 그룹화하려면 UTC_USEC_TO_<function_name> 함수를 사용하는 것이 효율적입니다.

예시:

SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");

반환: 2010-05-19

TIME(<timestamp>)

TIMESTAMP 데이터 유형을 인간이 읽을 수 있는 문자열인 %H:%M:%S 형식으로 반환합니다.

예시:

SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));

반환: 02:03:04

TIMESTAMP(<date_string>)

날짜 문자열을 TIMESTAMP 데이터 유형으로 변환합니다.

예시:

SELECT TIMESTAMP("2012-10-01 01:02:03");

반환: 2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC(<timestamp>)

TIMESTAMP 데이터 유형을 밀리초 단위의 UNIX 타임스탬프로 변환합니다.

예시:

SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));

반환: 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)

TIMESTAMP 데이터 유형을 초 단위의 UNIX 타임스탬프로 변환합니다.

예시:

SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));

반환: 1349053323

TIMESTAMP_TO_USEC(<timestamp>)

TIMESTAMP 데이터 유형을 마이크로초 단위의 UNIX 타임스탬프로 변환합니다.

PARSE_UTC_USEC는 TIMESTAMP 데이터 유형 대신 날짜 문자열 인수를 변환하는 동일한 기능의 함수입니다.

예시:

SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));

반환: 1349053323000000

USEC_TO_TIMESTAMP(<expr>)

밀리초 단위의 UNIX 타임스탬프를 TIMESTAMP 데이터 유형으로 변환합니다.

예시:

SELECT USEC_TO_TIMESTAMP(1349053323000000);

반환: 2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

반환: 2012-10-01 01:02:04 UTC

UTC_USEC_TO_DAY(<unix_timestamp>)

마이크로초 단위의 UNIX 타임스탬프를 발생 일의 시작 부분으로 이동합니다.

예를 들어 unix_timestamp가 5월 19일 08:58에 발생한 경우, 이 함수는 5월 19일 00:00(자정)에 대한 UNIX 타임스탬프를 반환합니다.

예시:

SELECT UTC_USEC_TO_DAY(1274259481071200);

반환: 1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

마이크로초 단위의 UNIX 타임스탬프를 발생 시간의 시작 부분으로 이동합니다.

예를 들어 unix_timestamp가 08:58에 발생한 경우, 이 함수는 같은 날 08:00에 대한 UNIX 타임스탬프를 반환합니다.

예시:

SELECT UTC_USEC_TO_HOUR(1274259481071200);

반환: 1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

마이크로초 단위의 UNIX 타임스탬프를 발생 월의 시작 부분으로 이동합니다.

예를 들어 unix_timestamp가 3월 19일에 발생한 경우, 이 함수는 같은 연도의 3월 1일에 대한 UNIX 타임스탬프를 반환합니다.

예시:

SELECT UTC_USEC_TO_MONTH(1274259481071200);

반환: 1272672000000000

UTC_USEC_TO_WEEK(<unix_timestamp>,
                 <day_of_week>)

unix_timestamp 인수의 요일을 나타내는 마이크로초 단위의 UNIX 타임스탬프를 반환합니다. 이 함수는 마이크로초 단위의 UNIX 타임스탬프와 0(일요일)에서 6(토요일) 사이의 주중 요일을 나타내는 두 개의 인수를 사용합니다.

예를 들어 unix_timestamp가 2008-04-11 금요일에 발생하고, 사용자가 day_of_week를 2(화요일)로 설정한 경우, 이 함수는 2008-04-08 화요일에 대한 UNIX 타임스탬프를 반환합니다.

예시:

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

반환: 1207612800000000

UTC_USEC_TO_YEAR(<unix_timestamp>)

unix_timestamp 인수의 연도를 나타내는 마이크로초 단위의 UNIX 타임스탬프를 반환합니다.

예를 들어 unix_timestamp가 2010에 발생한 경우, 이 함수는 2010-01-01 00:00의 마이크로초 표현인 1274259481071200을 반환합니다.

예시:

SELECT UTC_USEC_TO_YEAR(1274259481071200);

반환: 1262304000000000

WEEK(<timestamp>)

TIMESTAMP 데이터 유형의 주를 1에서 53(포함) 사이의 정수로 반환합니다.

주는 일요일부터 시작되므로, 1월 1일이 일요일이 아닌 다른 요일인 경우, 첫 번째 주는 요일 수가 7일 미만이고, 해당 연도의 첫 번째 일요일은 두 번째 주의 첫 번째 요일입니다.

예시:

SELECT WEEK(TIMESTAMP('2014-12-31'));

반환: 53

YEAR(<timestamp>)

TIMESTAMP 데이터 유형의 연도를 반환합니다.

예시:

SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));

반환: 2012

고급 예시

• 정수 타임스탬프 결과를 인간이 읽을 수 있는 형식으로 변환

  다음 쿼리는 대부분의 위키백과 개정이 수행된 시간 중 최상위 5개 순간을 찾습니다. 인간이 읽을 수 있는 형식으로 결과를 표시하려면 마이크로초 단위의 타임스탬프를 입력으로 사용하는 BigQuery의 FORMAT_UTC_USEC() 함수를 사용합니다. 이 쿼리는 초 단위로 된 위키백과의 POSIX 형식 타임스탬프에 1,000,000을 곱해서 값을 마이크로초 단위로 변환합니다.

  예시:

  #legacySQL
  SELECT
    /* Multiply timestamp by 1000000 and convert */
    /* into a more human-readable format. */
    TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5)
      AS top_revision_time,
    COUNT (*) AS revision_count
  FROM
    [bigquery-public-data:samples.wikipedia];

  반환:

  +----------------------------+----------------+
  |     top_revision_time      | revision_count |
  +----------------------------+----------------+
  | 2002-02-25 15:51:15.000000 |          20976 |
  | 2002-02-25 15:43:11.000000 |          15974 |
  | 2010-02-02 03:34:51.000000 |              3 |
  | 2010-02-02 01:04:59.000000 |              3 |
  | 2010-02-01 23:55:05.000000 |              3 |
  +----------------------------+----------------+
  

• 타임스탬프로 결과 묶기

  쿼리 결과를 특정 연도, 월 또는 일에 따라 버킷으로 그룹화하려면 날짜 및 시간 함수를 사용하는 것이 유용합니다. 다음 예시에서는 UTC_USEC_TO_MONTH() 함수를 사용하여 각각의 위키백과 기여자가 자신의 항목 설명을 수정하기 위해 사용한 월별 문자 수를 보여줍니다.

  예시:

  #legacySQL
  SELECT
    contributor_username,
    /* Return the timestamp shifted to the
     * start of the month, formatted in
     * a human-readable format. Uses the
     * 'LEFT()' string function to return only
     * the first 7 characters of the formatted timestamp.
     */
    LEFT (FORMAT_UTC_USEC(
      UTC_USEC_TO_MONTH(timestamp * 1000000)),7)
      AS month,
    SUM(LENGTH(comment)) as total_chars_used
  FROM
    [bigquery-public-data:samples.wikipedia]
  WHERE
    (contributor_username != '' AND
     contributor_username IS NOT NULL)
    AND timestamp > 1133395200
    AND timestamp < 1157068800
  GROUP BY
    contributor_username, month
  ORDER BY
    total_chars_used DESC;

  반환(잘림):

  +--------------------------------+---------+-----------------------+
  |      contributor_username      |  month  | total_chars_used      |
  +--------------------------------+---------+-----------------------+
  | Kingbotk                       | 2006-08 |              18015066 |
  | SmackBot                       | 2006-03 |               7838365 |
  | SmackBot                       | 2006-05 |               5148863 |
  | Tawkerbot2                     | 2006-05 |               4434348 |
  | Cydebot                        | 2006-06 |               3380577 |
  etc ...
  

IP 함수

IP 함수는 IP 주소를 인간이 읽을 수 있는 형식으로 또는 그 반대로 변환합니다.

구문

FORMAT_IP(integer_value)

integer_value의 최하위 32개 비트를 인간이 읽을 수 있는 IPv4 주소 문자열로 변환합니다. 예를 들어 FORMAT_IP(1)는 문자열 '0.0.0.1'을 반환합니다.

PARSE_IP(readable_ip)

IPv4 주소를 나타내는 문자열을 부호 없는 정수 값으로 변환합니다. 예를 들어 PARSE_IP('0.0.0.1')는 1을 반환합니다. 문자열이 올바른 IPv4 주소가 아닌 경우 PARSE_IP가 NULL을 반환합니다.

BigQuery는 네트워크 바이트 순서의 4바이트 또는 16바이트 바이너리 데이터와 같이 저장된 문자열에 IPv4 및 IPv6 주소 쓰기를 지원합니다. 아래 설명된 함수들에서는 주소를 인간이 읽을 수 있는 형식으로 또는 그 반대로 파싱할 수 있습니다. 이러한 함수는 IP를 포함한 문자열 필드에서만 작동합니다.

구문

FORMAT_PACKED_IP(packed_ip)

인간이 읽을 수 있는 형식의 IP 주소를 10.1.5.23 또는 2620:0:1009:1:216:36ff:feef:3f 형식으로 반환합니다. 예를 들면 다음과 같습니다.

• FORMAT_PACKED_IP('0123456789@ABCDE')는 '3031:3233:3435:3637:3839:4041:4243:4445'를 반환합니다.
• FORMAT_PACKED_IP('0123')는 '48.49.50.51'을 반환합니다.

PARSE_PACKED_IP(readable_ip)

IP 주소를 BYTES로 반환합니다. 입력 문자열이 올바른 IPv4 또는 IPv6 주소가 아니면 PARSE_PACKED_IP는 NULL을 반환합니다. 예를 들면 다음과 같습니다.

• PARSE_PACKED_IP('48.49.50.51')는 'MDEyMw=='를 반환합니다.
• PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445')는 'MDEyMzQ1Njc4OUBBQkNERQ=='를 반환합니다.

JSON 함수

BigQuery의 JSON 함수를 사용하면 JSONPath와 비슷한 표현식을 사용하여, 저장된 JSON 데이터 내에서 값을 찾을 수 있습니다.

모든 개별 필드를 테이블 스키마에서 선언하는 것보다는 JSON 데이터를 저장하는 것이 더 유연할 수 있지만, 비용이 높아질 수 있습니다. JSON 문자열에서 데이터를 선택할 때는 전체 문자열을 스캔하는 비용이 부과됩니다. 이는 각 필드가 개별 열에 있을 때보다 높은 비용입니다. 또한 쿼리 시간에 전체 문자열을 파싱해야 하므로 쿼리가 더 느립니다. 하지만 임시 스키마 또는 빠르게 바뀌는 스키마의 경우에는 추가 비용보다 JSON의 유연성이 더 필요할 수 있습니다.

JSON 함수를 사용하는 것이 더 쉬우므로, 구조화된 데이터를 사용할 때는 BigQuery의 정규 표현식 함수 대신 JSON 함수를 사용하세요.

구문

JSON_EXTRACT(json, json_path)

JSONPath 표현식 json_path에 따라 json의 값을 반환합니다. json_path는 문자열 상수여야 합니다. 값을 JSON 문자열 형식으로 반환합니다.

JSON_EXTRACT_SCALAR(json, json_path)

JSONPath 표현식 json_path에 따라 json의 값을 반환합니다. json_path는 문자열 상수여야 합니다. 스칼라 JSON 값을 반환합니다.

논리 연산자

논리 연산자는 표현식에서 바이너리 또는 3항 논리를 수행합니다. 바이너리 논리는 true 또는 false를 반환합니다. 3항 논리는 NULL 값을 사용하고 true, false 또는 NULL을 반환합니다.

구문

expr AND expr

• 두 표현식이 true이면 true를 반환합니다.
• 두 표현식 중 하나 이상이 false이면 false를 반환합니다.
• 두 표현식이 NULL이거나 하나의 표현식 true이고 다른 표현식이 NULL이면 NULL을 반환합니다.

expr OR expr

• 두 표현식 중 하나 이상이 true이면 true를 반환합니다.
• 두 표현식이 false이면 false를 반환합니다.
• 두 표현식이 NULL이거나 하나의 표현식 false이고 다른 표현식이 NULL이면 NULL을 반환합니다.

NOT expr

• 표현식이 false이면 true를 반환합니다.
• 표현식이 true이면 false를 반환합니다.
• 표현식이 NULL이면 NULL을 반환합니다.

NOT은 다른 함수에서 부정 연산자로 사용할 수 있습니다. 예를 들면 NOT IN(expr1, expr2) 또는 IS NOT NULL입니다.

수학 함수

수학 함수는 숫자 인수를 사용하고 숫자 결과를 반환합니다. 각 인수는 숫자 리터럴이거나 쿼리로 반환된 숫자 값일 수 있습니다. 수학 함수가 정의되지 않은 결과로 평가될 경우 연산이 NULL을 반환합니다.

구문

ABS(numeric_expr)

인수의 절댓값을 반환합니다.

ACOS(numeric_expr)

인수의 아크 코사인을 반환합니다.

ACOSH(numeric_expr)

인수의 아크 쌍곡선 코사인을 반환합니다.

ASIN(numeric_expr)

인수의 아크 사인을 반환합니다.

ASINH(numeric_expr)

인수의 아크 쌍곡선 사인을 반환합니다.

ATAN(numeric_expr)

인수의 아크 탄젠트를 반환합니다.

ATANH(numeric_expr)

인수의 아크 쌍곡선 탄젠트를 반환합니다.

ATAN2(numeric_expr1, numeric_expr2)

두 인수의 아크 탄젠트를 반환합니다.

CEIL(numeric_expr)

인수를 가까운 정수로 올림하고 올림된 값을 반환합니다.

COS(numeric_expr)

인수의 코사인을 반환합니다.

COSH(numeric_expr)

인수의 쌍곡선 코사인을 반환합니다.

DEGREES(numeric_expr)

라디안에서 도로 변환된 numeric_expr을 반환합니다.

EXP(numeric_expr)

자연 로그의 밑에서 상수 'e'를 numeric_expr의 승수로 올린 결과를 반환합니다.

FLOOR(numeric_expr)

인수를 가까운 정수로 반내림하고 반내림된 값을 반환합니다.

LN(numeric_expr)
LOG(numeric_expr)

인수의 자연 로그를 반환합니다.

LOG2(numeric_expr)

인수의 Base-2 로그를 반환합니다.

LOG10(numeric_expr)

인수의 Base-10 로그를 반환합니다.

PI()

상수 π를 반환합니다. PI() 함수는 함수임을 나타내는 괄호가 필요하지만, 괄호 안에 인수를 포함하지 않습니다. 수학 함수 및 산술 함수에서 상수로 PI()를 사용할 수 있습니다.

POW(numeric_expr1, numeric_expr2)

numeric_expr1을 numeric_expr2로 거듭제곱한 결과를 반환합니다.

RADIANS(numeric_expr)

도에서 라디안으로 변환된 numeric_expr을 반환합니다. (π 라디안은 180도입니다.)

RAND([int32_seed])

0.0 이상 1.0 미만의 무작위 부동 소수점 값을 반환합니다. 사용자가 LIMIT 절을 사용하지 않는다면 각 int32_seed 값은 지정된 쿼리 내에서 항상 동일한 순서로 난수를 생성합니다. int32_seed가 지정되지 않으면 BigQuery는 현재 타임스탬프를 시드 값으로 사용합니다.

ROUND(numeric_expr [, digits])

인수를 가까운 정수(숫자가 지정된 경우, 지정된 숫자의 자릿수)로 반올림 또는 반내림하고 반올림된 또는 반내림된 값을 반환합니다.

SIN(numeric_expr)

인수의 사인을 반환합니다.

SINH(numeric_expr)

인수의 쌍곡선 사인을 반환합니다.

SQRT(numeric_expr)

표현식의 제곱근을 반환합니다.

TAN(numeric_expr)

인수의 탄젠트를 반환합니다.

TANH(numeric_expr)

인수의 쌍곡선 탄젠트를 반환합니다.

고급 예시

• 경계 상자 쿼리

  다음 쿼리는 가운데에 San Francisco(37.46, -122.50)가 있는 직사각형 경계 상자 내에 있는 지점들의 모음을 반환합니다.

  예시:

  #legacySQL
  SELECT
    year, month,
    AVG(mean_temp) avg_temp,
    MIN(min_temperature) min_temp,
    MAX(max_temperature) max_temp
  FROM
    [weather_geo.table]
  WHERE
    /* Return values between a pair of */
    /* latitude and longitude coordinates */
    lat / 1000 > 37.46 AND
    lat / 1000 < 37.65 AND
    long / 1000 > -122.50 AND
    long / 1000 < -122.30
  GROUP BY
    year, month
  ORDER BY
    year, month ASC;

• 대략적인 경계 원 쿼리

  가운데에 Denver Colorado(39.73, -104.98)가 있고 구면 코사인 법칙을 통해 확인된 대략적인 원 내에 있는 최대 100개 지점들의 모음을 반환합니다. 이 쿼리는 PI(), SIN(), COS()과 같은 BigQuery의 수학 함수 및 삼각 함수를 사용합니다.

  지구가 완전한 구형이 아니고 경도와 위도가 양극에서 모이므로 이 쿼리는 많은 유형의 데이터에 대해 유용할 수 있는 근사치를 반환합니다.

  예시:

  #legacySQL
  SELECT
    distance, lat, long, temp
  FROM
    (SELECT
      ((ACOS(SIN(39.73756700 * PI() / 180) *
             SIN((lat/1000) * PI() / 180) +
             COS(39.73756700 * PI() / 180) *
             COS((lat/1000) * PI() / 180) *
             COS((-104.98471790 -
             (long/1000)) * PI() / 180)) *
             180 / PI()) * 60 * 1.1515)
        AS distance,
       AVG(mean_temp) AS temp,
       AVG(lat/1000) lat, AVG(long/1000) long
  FROM
    [weather_geo.table]
  WHERE
    month=1 GROUP BY distance)
  WHERE
    distance < 100
  ORDER BY
    distance ASC
  LIMIT 100;

정규 표현식 함수

BigQuery에서는 re2 라이브러리를 사용하여 정규 표현식을 지원합니다. 정규 표현식 문법 관련 문서를 참조하세요.

정규 표현식은 전역 일치입니다. 단어의 시작 부분에서 일치를 시작하려면 ^ 문자를 사용해야 합니다.

구문

REGEXP_MATCH('str', 'reg_exp')

str이 정규 표현식과 일치하면 true를 반환합니다. 정규 표현식 없이 문자열 일치를 수행하려면 REGEXP_MATCH 대신 CONTAINS를 사용합니다.

예시:

#legacySQL
SELECT
   word,
   COUNT(word) AS count
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   (REGEXP_MATCH(word,r'\w\w\'\w\w'))
GROUP BY word
ORDER BY count DESC
LIMIT 3;

반환:

+-------+-------+
| word  | count |
+-------+-------+
| ne'er |    42 |
| we'll |    35 |
| We'll |    33 |
+-------+-------+

REGEXP_EXTRACT('str', 'reg_exp')

정규 표현식 내에서 캡처 그룹과 일치하는 str 부분을 반환합니다.

예시:

#legacySQL
SELECT
   REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment
FROM
   [bigquery-public-data:samples.shakespeare]
GROUP BY fragment
ORDER BY fragment
LIMIT 3;

반환:

+----------+
| fragment |
+----------+
| NULL     |
| Al'ce    |
| As'es    |
+----------+

REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')

reg_exp와 일치하는 orig_str의 하위 문자열이 replace_str로 바뀌는 문자열을 반환합니다. 예를 들어 REGEXP_REPLACE ('Hello', 'lo', 'p')는 Help를 반환합니다.

예시:

#legacySQL
SELECT
  REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  REGEXP_MATCH(word, r'ne\'er')
GROUP BY expanded_word
ORDER BY expanded_word
LIMIT 5;

반환:

+---------------+
| expanded_word |
+---------------+
| Whenever      |
| never         |
| nevertheless  |
| whenever      |
+---------------+

고급 예시

• 정규 표현식 일치로 설정된 결과 필터링

  BigQuery의 정규 표현식 함수를 사용하면 SELECT로 결과를 표시할 뿐만 아니라 WHERE 절로 결과를 필터링할 수 있습니다. 다음 예시는 이러한 두 가지 정규 표현식 사용 사례를 단일 쿼리로 조합해서 보여줍니다.

  예시:

  #legacySQL
  SELECT
    /* Replace white spaces in the title with underscores. */
    REGEXP_REPLACE(title, r'\s+', '_') AS regexp_title, revisions
  FROM
    (SELECT title, COUNT(revision_id) as revisions
    FROM
      [bigquery-public-data:samples.wikipedia]
    WHERE
      wp_namespace=0
      /* Match titles that start with 'G', end with
       * 'e', and contain at least two 'o's.
       */
      AND REGEXP_MATCH(title, r'^G.*o.*o.*e$')
    GROUP BY
      title
    ORDER BY
      revisions DESC
    LIMIT 100);

• 정수 또는 float 데이터에서 정규 표현식 사용

  BigQuery의 정규 표현식 함수는 문자열 데이터에서만 작동하지만, STRING() 함수를 사용하여 정수 또는 float 데이터를 문자열 형식으로 변환할 수 있습니다. 이 예시에서는 STRING()을 사용하여 정수 값 corpus_date를 문자열로 변환하고, 이를 다시 REGEXP_REPLACE로 수정합니다.

  예시:

  #legacySQL
  SELECT
    corpus_date,
    /* Cast the corpus_date to a string value  */
    REGEXP_REPLACE(STRING(corpus_date),
      '^16',
      'Written in the sixteen hundreds, in the year \''
      ) AS date_string
  FROM [bigquery-public-data:samples.shakespeare]
  /* Cast the corpus_date to string, */
  /* match values that begin with '16' */
  WHERE
    REGEXP_MATCH(STRING(corpus_date), '^16')
  GROUP BY
    corpus_date, date_string
  ORDER BY
    date_string DESC
  LIMIT 5;

문자열 함수

문자열 함수는 문자열 데이터에서 작동합니다. 문자열 상수는 작은 따옴표 또는 큰따옴표로 묶어야 합니다. 문자열 함수는 기본적으로 대소문자를 구분합니다. IGNORE CASE를 쿼리 끝에 추가하면 대소문자를 구분하지 않는 일치가 수행됩니다. IGNORE CASE는 ASCII 문자에서만 작동하고, 쿼리 최상위에서만 작동합니다.

이러한 함수에서는 와일드 카드가 지원되지 않습니다. 정규 표현식 기능을 위해서는 정규 표현식 함수를 사용합니다.

구문

CONCAT('str1', 'str2', '...')
str1 + str2 + ...

2개 이상의 연결된 문자열을 반환하거나 값 중 NULL이 있는 경우 NULL을 반환합니다. 예를 들어 str1이 Java고 str2가 Script면 CONCAT은 JavaScript를 반환합니다.

expr CONTAINS 'str'

expr에 지정된 문자열 인수가 있으면 true를 반환합니다. 이 비교에서는 대소문자가 구분됩니다.

INSTR('str1', 'str2')

str1에서 str2 첫 어커런스의 1로 시작하는 색인을 반환하거나, str2가 str1에 없으면 0을 반환합니다.

LEFT('str', numeric_expr)

str의 왼쪽 끝에서부터 numeric_expr자의 문자를 반환합니다. 숫자가 str보다 길면 전체 문자열이 반환됩니다. 예를 들어 LEFT('seattle', 3)는 sea를 반환합니다.

LENGTH('str')

문자열 길이의 숫자 값을 반환합니다. 예를 들어 str이 '123456'이면 LENGTH는 6을 반환합니다.

LOWER('str')

원본 문자열의 모든 문자를 소문자로 변환하여 반환합니다.

LPAD('str1', numeric_expr, 'str2')

str1 왼쪽에 str2를 채우고 결과 문자열이 정확하게 numeric_expr자가 될 때까지 str2 채우기를 반복합니다. 예를 들어 LPAD('1', 7, '?')는 ??????1을 반환합니다.

LTRIM('str1' [, str2])

str1 왼쪽에서 문자를 삭제합니다. str2가 없으면 LTRIM은 str1 왼쪽에서 공백을 삭제합니다. 그렇지 않으면 LTRIM은 str2 왼쪽부터 str2에 있는 모든 문자를 삭제합니다(대소문자 구분).

예:

SELECT LTRIM("Say hello", "yaS")은 " hello"를 반환합니다.

SELECT LTRIM("Say hello", " ySa")은 "hello"를 반환합니다.

REPLACE('str1', 'str2', 'str3')

str1 내에서 발견된 모든 str2를 str3로 바꿉니다.

RIGHT('str', numeric_expr)

str의 오른쪽 끝에서부터 numeric_expr자의 문자를 반환합니다. 숫자가 문자열보다 길면 전체 문자열을 반환합니다. 예를 들어 RIGHT('kirkland', 4)는 land를 반환합니다.

RPAD('str1', numeric_expr, 'str2')

str1 오른쪽에 str2를 채우고 결과 문자열이 정확하게 numeric_expr자가 될 때까지 str2 채우기를 반복합니다. 예를 들어 RPAD('1', 7, '?')는 1??????를 반환합니다.

RTRIM('str1' [, str2])

str1의 오른쪽부터 후행 문자를 삭제합니다. str2가 없으면 RTRIM은 str1에서 후행 공백을 삭제합니다. 그렇지 않으면 RTRIM은 str2 오른쪽부터 str2에 있는 모든 문자를 삭제합니다(대소문자 구분).

예:

SELECT RTRIM("Say hello", "leo")은 "Say h"를 반환합니다.

SELECT RTRIM("Say hello ", " hloe")은 "Say"를 반환합니다.

SPLIT('str' [, 'delimiter'])

문자열을 반복되는 하위 문자열로 분할합니다. delimiter가 지정된 경우 SPLIT 함수는 delimiter를 구분자로 사용하여 str을 하위 문자열로 분할합니다.

SUBSTR('str', index [, max_len])

index에서 시작하는 str의 하위 문자열을 반환합니다. 선택사항인 max_len 매개변수를 사용하는 경우 반환 문자열의 최대 길이는 max_len자입니다. 계산은 1부터 시작되므로 문자열의 첫 번째 문자는 1번 위치에 있습니다(0이 아님). index가 5이면 str의 왼쪽 5번째 문자부터 하위 문자열이 시작됩니다. index가 -4이면 str의 오른쪽 4번째 문자부터 하위 문자열이 시작됩니다. 예를 들어 SUBSTR('awesome', -4, 4)은 some 하위 문자열을 반환합니다.

UPPER('str')

원본 문자열의 모든 문자를 대문자로 변환하여 반환합니다.

'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported

테이블 와일드 카드 함수

테이블 와일드 카드 함수를 사용하면 특정 테이블 집합에서 데이터를 편리하게 쿼리할 수 있습니다. 테이블 와일드 카드 함수는 와일드 카드 함수로 일치한 모든 테이블의 쉼표로 구분된 합집합과 동일합니다. 테이블 와일드 카드 함수를 사용할 때, BigQuery는 와일드 카드와 일치하는 테이블만 액세스하고 비용을 부과합니다. 테이블 와일드 카드 함수는 쿼리의 FROM 절에 지정됩니다.

쿼리에 테이블 와일드 카드 함수를 사용할 경우, 해당 함수는 더 이상 괄호로 묶을 필요가 없습니다. 예를 들어 다음 예시 중 일부에는 괄호가 사용되지만, 다른 일부에는 괄호가 사용되지 않습니다.

캐싱된 결과 사용 옵션을 선택하더라도 와일드 카드 함수를 사용하는 여러 테이블에 대한 쿼리에서는 캐시된 결과가 지원되지 않습니다. 동일한 와일드 카드 쿼리를 여러 번 실행하면, 각 쿼리에 대해 요금이 청구됩니다.

구문

TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

<timestamp1> 및 <timestamp2> 사이의 시간 범위와 겹치는 일별 테이블을 쿼리합니다.

테이블 이름은 <prefix><day> 형식이어야 합니다. 여기서 <day>는 YYYYMMDD 형식입니다.

날짜 및 시간 함수를 사용하여 타임스탬프 매개변수를 생성할 수 있습니다. 예를 들면 다음과 같습니다.

• TIMESTAMP('2012-10-01 02:03:04')
• DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

예시: 2일 동안의 테이블 가져오기

이 예시에서는 다음 테이블이 있다고 가정합니다.

• mydata.people20140325
• mydata.people20140326
• mydata.people20140327

#legacySQL
SELECT
  name
FROM
  TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27'))
WHERE
  age >= 35

일치하는 테이블은 다음과 같습니다.

• mydata.people20140325
• mydata.people20140326
• mydata.people20140327

예시: '지금'까지 2일 동안의 테이블 가져오기

이 예시에서는 이름이 myproject-1234인 프로젝트에 다음 테이블이 있다고 가정합니다.

• mydata.people20140323
• mydata.people20140324
• mydata.people20140325

#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'),
                    CURRENT_TIMESTAMP()))
WHERE
  age >= 35

일치하는 테이블은 다음과 같습니다.

• mydata.people20140323
• mydata.people20140324
• mydata.people20140325

TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)

이 함수는 TABLE_DATE_RANGE와 동일합니다. 유일한 차이점은 시퀀스에서 일별 테이블이 누락되면 TABLE_DATE_RANGE_STRICT가 실패하고 Not Found: Table <table_name> 오류가 반환된다는 점입니다.

예시: 테이블 누락 오류

이 예시에서는 다음 테이블이 있다고 가정합니다.

• people20140325
• people20140327

#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27')))
WHERE age >= 35

위 예시는 'people20140326' 테이블에 대해 '찾을 수 없음' 오류를 반환합니다.

TABLE_QUERY(dataset, expr)

제공된 expr과 이름이 일치하는 테이블을 쿼리합니다. expr 매개변수는 문자열로 표현되어야 하고 평가할 표현식을 포함해야 합니다. 예를 들면 'length(table_id) < 3'입니다.

예시: 이름에 'oo'가 포함되고 길이가 4자 이상인 테이블 찾기

이 예시에서는 다음 테이블이 있다고 가정합니다.

• mydata.boo
• mydata.fork
• mydata.ooze
• mydata.spoon

#legacySQL
SELECT
  speed
FROM (TABLE_QUERY([myproject-1234:mydata],
                  'table_id CONTAINS "oo" AND length(table_id) >= 4'))

일치하는 테이블은 다음과 같습니다.

• mydata.ooze
• mydata.spoon

예시: 이름이 'boo'로 시작하고 3~5자릿수 숫자가 이어지는 테이블 찾기

이 예시에서는 이름이 myproject-1234인 프로젝트에 다음 테이블이 있다고 가정합니다.

• mydata.book4
• mydata.book418
• mydata.boom12345
• mydata.boom123456789
• mydata.taboo999

#legacySQL
SELECT
  speed
FROM
  TABLE_QUERY([myproject-1234:mydata],
               'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')

일치하는 테이블은 다음과 같습니다.

• mydata.book418
• mydata.boom12345

URL 함수

구문

HOST('url_str')

제공된 URL에 대해 호스트 이름을 문자열로 반환합니다. 예시: HOST('http://www.google.com:80/index.html')는 'www.google.com'을 반환합니다.

DOMAIN('url_str')

제공된 URL에 대해 도메인을 문자열로 반환합니다. 예시: DOMAIN('http://www.google.com:80/index.html')은 'google.com'을 반환합니다.

TLD('url_str')

제공된 URL에 대해 최상위 도메인과 URL에 포함된 국가 도메인을 반환합니다. 예를 들어 TLD('http://www.google.com:80/index.html')는 '.com'을 반환합니다. TLD('http://www.google.co.uk:80/index.html')는 '.co.uk'를 반환합니다.

참고:

• 이러한 함수는 역방향 DNS 조회를 수행하지 않습니다. 따라서 IP 주소를 사용하여 이러한 함수를 호출할 경우, 함수가 호스트 이름 세그먼트 대신 IP 주소 세그먼트를 반환합니다.
• 모든 URL 파싱 함수에는 소문자가 사용됩니다. URL에 대문자가 있으면 NULL 또는 잘못된 결과가 발생합니다. 데이터에 대소문자가 혼합된 경우 LOWER()를 통해 이 함수에 입력을 파싱하세요.

고급 예시

URL 데이터에서 도메인 이름 파싱

이 쿼리는 DOMAIN() 함수를 사용하여 GitHub에서 저장소 홈페이지로 나열된 가장 인기 있는 도메인을 반환합니다. 여기에서는 DOMAIN() 함수 결과를 사용하여 레코드를 필터링할 수 있는 HAVING이 사용되었습니다. 이 함수는 URL 데이터에서 리퍼러 정보를 확인하는 데 유용합니다.

예:

#legacySQL
SELECT
  DOMAIN(repository_homepage) AS user_domain,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_domain
HAVING
  user_domain IS NOT NULL AND user_domain != ''
ORDER BY
  activity_count DESC
LIMIT 5;

반환:

+-----------------+----------------+
|   user_domain   | activity_count |
+-----------------+----------------+
| github.com      |         281879 |
| google.com      |          34769 |
| khanacademy.org |          17316 |
| sourceforge.net |          15103 |
| mozilla.org     |          14091 |
+-----------------+----------------+

TLD 정보를 자세히 보려면 TLD() 함수를 사용합니다. 이 예시에서는 일반 예시 목록에 없는 최상위 TLD를 보여줍니다.

#legacySQL
SELECT
  TLD(repository_homepage) AS user_tld,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_tld
HAVING
  /* Only consider TLDs that are NOT NULL */
  /* or in our list of common TLDs */
  user_tld IS NOT NULL AND NOT user_tld
  IN ('','.com','.net','.org','.info','.edu')
ORDER BY
  activity_count DESC
LIMIT 5;

반환:

+----------+----------------+
| user_tld | activity_count |
+----------+----------------+
| .de      |          22934 |
| .io      |          17528 |
| .me      |          13652 |
| .fr      |          12895 |
| .co.uk   |           9135 |
+----------+----------------+

윈도우 함수

분석 함수라고도 부르는 윈도우 함수를 사용하면 결과 집합의 특정 하위 집합 또는 '구간'에 대한 계산을 수행할 수 있습니다. 윈도우 함수를 사용하면 후행 평균 및 누적 합계와 같은 복잡한 분석이 포함된 보고서를 쉽게 만들 수 있습니다.

각 윈도우 함수에는 윈도우 시작과 끝을 지정하는 OVER 절이 필요합니다. OVER 절의 세 가지 구성요소(파티션 나누기, 정렬, 프레이밍)는 해당 윈도우에 대한 추가적인 제어 방법을 제공합니다. 파티션 나누기는 입력 데이터를 공통 특성을 지닌 논리적 그룹으로 나눌 수 있게 해줍니다. 정렬은 파티션 내에서 결과를 정렬할 수 있게 해줍니다. 프레이밍은 파티션 내에서 현재 행을 기준으로 이동하는 슬라이딩 윈도우 프레임을 만들 수 있게 해줍니다. 이동하는 윈도우 프레임의 크기는 시간 간격과 같은 여러 행 또는 값 범위를 기준으로 구성할 수 있습니다.

#legacySQL
SELECT <window_function>
  OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr> [ASC | DESC]]
      [<window-frame-clause>]
     )

PARTITION BY

이 함수가 작동하는 기본 파티션을 정의합니다. 쉼표로 구분된 하나 이상의 열 이름을 지정합니다. GROUP BY 절과 비슷하게, 이러한 열의 각 고유 값 집합에 대해 하나의 파티션이 생성됩니다. PARTITION BY가 생략된 경우, 기본 파티션은 윈도우 함수의 입력에 포함된 모든 행입니다.

PARTITION BY 절에서는 또한 윈도우 함수가 데이터를 파티션으로 나누고 실행을 동시에 로드할 수 있습니다. allowLargeResults와 함께 윈도우 함수를 사용하거나 윈도우 함수의 출력에 조인 또는 집계를 추가로 적용하려는 경우에는 PARTITION BY를 사용하여 실행을 동시에 로드합니다.

JOIN EACH 및 GROUP EACH BY 절은 윈도우 함수의 출력에 사용할 수 없습니다. 윈도우 함수를 사용할 때 큰 쿼리 결과를 생성하려면 PARTITION BY를 사용해야 합니다.

ORDER BY

파티션을 정렬합니다. ORDER BY가 없으면 기본 정렬 순서를 보장할 수 없습니다. 정렬은 윈도우 프레임 절이 적용되기 전 파티션 수준에서 수행됩니다. RANGE 윈도우를 지정한 경우 ORDER BY 절을 추가해야 합니다. 기본 순서는 ASC입니다.

경우에 따라 ORDER BY는 선택사항이지만 rank() 또는 dense_rank()와 같은 특정 윈도우 함수에서는 이 절이 필수항목입니다.

ROWS 또는 RANGE를 지정하지 않고 ORDER BY를 사용할 경우, ORDER BY는 파티션의 시작 지점에서 현재 행까지 윈도우가 연장된다고 암시적으로 가정합니다. ORDER BY 절이 없으면 전체 파티션이 윈도우입니다.

<window-frame-clause>

{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}

작업을 수행할 파티션의 하위 집합입니다. 하위 집합의 크기는 파티션보다 작거나 같을 수 있습니다. window-frame-clause 없이 ORDER BY를 사용할 경우, 기본 윈도우 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW입니다. ORDER BY와 window-frame-clause를 모두 생략할 경우, 기본 윈도우 프레임은 전체 파티션입니다.

• ROWS - 현재 행을 기준으로 하여 행 위치로 윈도우를 정의합니다. 예를 들어 급여 값 중 이전 5개 행의 합계를 표시하는 열을 추가하려면 SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)를 쿼리합니다. 행 집합에는 일반적으로 현재 행이 포함되지만, 반드시 그럴 필요는 없습니다.
• RANGE - 현재 행에 있는 열의 값에 상대되는, 지정된 열에 있는 값의 범위를 기준으로 구간을 정의합니다. 날짜 값이 단순 정수(기점 이후의 마이크로초)인 숫자 및 날짜에서만 작동합니다. 동일 값을 갖는 인접 행은 피어 행이라고 부릅니다. CURRENT ROW의 피어 행은 CURRENT ROW를 지정하는 윈도우 프레임에 포함됩니다. 예를 들어 윈도우 끝을 CURRENT ROW로 지정하고, 해당 윈도우의 다음 행에 동일 값이 포함될 경우, 해당 피어 행이 함수 계산에 포함됩니다.
• BETWEEN <start> AND <end> - 시작 행과 끝 행을 포함하는 범위입니다. 이 범위는 현재 행을 포함할 필요가 없지만 <start>가 <end>보다 앞에 있거나 동일해야 합니다.
• <start> - 현재 행을 기준으로 이 윈도우의 시작 오프셋을 지정합니다. 다음과 같은 옵션이 지원됩니다.

  {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  여기서 <expr>은 양의 정수이고, PRECEDING은 앞에 오는 행 수 또는 범위 값을 나타내고, FOLLOWING은 뒤에 오는 행 수 또는 범위 값을 나타냅니다. UNBOUNDED PRECEDING은 파티션의 첫 번째 행을 의미합니다. 시작 지점이 해당 윈도우 이전인 경우, 파티션의 첫 번째 행으로 설정됩니다.
• <end> - 현재 행을 기준으로 이 윈도우의 끝 오프셋을 지정합니다. 다음과 같은 옵션이 지원됩니다.

  {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  여기서 <expr>은 양의 정수이고, PRECEDING은 앞에 오는 행 수 또는 범위 값을 나타내고, FOLLOWING은 뒤에 오는 행 수 또는 범위 값을 나타냅니다. UNBOUNDED FOLLOWING은 파티션의 마지막 행을 의미합니다. 끝 지점이 윈도우의 끝을 지나서 있는 경우, 파티션의 마지막 행으로 설정됩니다.

여러 입력 행을 하나의 출력 행으로 축소시키는 집계 함수와 달리, 윈도우 함수는 각 입력 행에 대해 하나의 출력 행을 반환합니다. 이 기능을 사용하면 누적 합계 및 이동 평균을 계산하는 쿼리를 쉽게 만들 수 있습니다. 예를 들어 다음 쿼리는 SELECT 문으로 정의된 5개 행의 작은 데이터 세트에 대한 누적 합계를 반환합니다.

#legacySQL
SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value);