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 ] |JOINclause |FLATTENclause | table wildcard function
FROM 절은 쿼리할 소스 데이터를 지정합니다. BigQuery 쿼리는 테이블, 하위 쿼리, 조인된 테이블, 아래 설명된 특수 목적의 연산자로 수정된 테이블에 직접 실행할 수 있습니다. BigQuery에서 UNION ALL 연산자인 쉼표를 사용하여 이러한 데이터 소스 조합을 쿼리할 수 있습니다.
테이블 참조
테이블을 참조할 경우 datasetId 및 datasetId를 모두 지정해야 합니다. datasetId은 선택사항입니다. project_name을 지정하지 않으면 BigQuery에서 현재 프로젝트가 기본적으로 사용됩니다. 프로젝트 이름에 대시가 포함되어 있으면 전체 테이블 참조를 대괄호로 묶어야 합니다.
예시
[my-dashed-project:dataset1.tableName]
테이블 이름 다음에 공백 문자와 식별자를 추가하여 테이블에 별칭을 지정할 수 있습니다. 가독성을 향상시키기 위해 tableId와 별칭 사이에 AS 키워드를 추가할 수도 있습니다.
테이블에서 열을 참조할 때는 단순히 열 이름을 사용하거나, 별칭을 지정한 경우 열 이름 앞에 별칭을 프리픽스로 사용하거나, datasetId 및 datasetId를 프리픽스로 사용할 수 있습니다(datasetId이 지정되지 않은 경우). 필드 이름에는 콜론 문자가 허용되지 않기 때문에 열 프리픽스에 project_name을 포함시킬 수 없습니다.
예시
이 예에서는 테이블 접두사 없이 열을 참조합니다.
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare];
이 예에서는 열 이름에 datasetId 및 datasetId를 접두사로 사용합니다. 이 예시에서는 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를 사용해서는 정수 범위로 파티션을 나눈 테이블 전체에서 쿼리를 수행할 수 없습니다. 대신 쿼리가 다음과 같은 오류를 반환합니다.
Querying tables partitioned on a field is not supported in 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 BYclause. */- 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절은 함수를 포함할 수 없으며, 숫자 상수만 사용합니다.
쿼리 문법
위에서는 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;
| 집계 함수 | |
|---|---|
AVG() |
행 그룹의 값 평균을 반환합니다. |
BIT_AND() |
비트 AND 연산의 결과를 반환합니다. |
BIT_OR() |
비트 OR 연산의 결과를 반환합니다. |
BIT_XOR() |
비트 XOR 연산의 결과를 반환합니다. |
CORR() |
숫자 쌍 집합의 피어슨 상관 계수를 반환합니다. |
COUNT() |
값의 전체 개수를 반환합니다. |
COUNT([DISTINCT]) |
NULL이 아닌 값의 전체 개수를 반환합니다. |
COVAR_POP() |
값의 모집단 공분산을 계산합니다. |
COVAR_SAMP() |
값의 표본 공분산을 계산합니다. |
EXACT_COUNT_DISTINCT() |
지정된 필드에서 NULL이 아닌 고유 값의 정확한 개수를 반환합니다. |
FIRST() |
함수 범위에서 첫 번째 순차 값을 반환합니다. |
GROUP_CONCAT() |
다중 문자열을 단일 문자열로 연결합니다. |
GROUP_CONCAT_UNQUOTED() |
다중 문자열을 단일 문자열로 연결합니다. 큰따옴표를 추가하지 않습니다. |
LAST() |
마지막 순차 값을 반환합니다. |
MAX() |
최댓값을 반환합니다. |
MIN() |
최솟값을 반환합니다. |
NEST() |
현재 집계 범위의 모든 값을 반복 필드로 집계합니다. |
NTH() |
n번째 순차 값을 반환합니다. |
QUANTILES() |
근사 최솟값, 최댓값, 백분위수를 계산합니다. |
STDDEV() |
표준 편차를 반환합니다. |
STDDEV_POP() |
모집단 표준 편차를 계산합니다. |
STDDEV_SAMP() |
표본 표준 편차를 계산합니다. |
SUM() |
값의 총계를 반환합니다. |
TOP() ... COUNT(*) |
빈도별 최상위 max_records 레코드를 반환합니다. |
UNIQUE() |
NULL이 아닌 고유 값의 집합을 반환합니다. |
VARIANCE() |
값의 분산을 계산합니다. |
VAR_POP() |
값의 모집단 분산을 계산합니다. |
VAR_SAMP() |
값의 표본 분산을 계산합니다. |
| 산술 연산자 | |
|---|---|
+ |
더하기 |
- |
빼기 |
* |
곱하기 |
/ |
나누기 |
% |
Modulo |
| 비트 함수 | |
|---|---|
& |
비트 AND |
| |
비트 OR |
^ |
비트 XOR |
<< |
비트 왼쪽 이동 |
>> |
비트 오른쪽 이동 |
~ |
비트 NOT |
BIT_COUNT() |
비트 수를 반환합니다. |
| Cast 변환 함수 | |
|---|---|
BOOLEAN() |
부울로 변환합니다. |
BYTES() |
바이트로 변환합니다. |
CAST(expr AS type) |
expr을 type 유형의 변수로 변환합니다. |
FLOAT() |
double로 변환합니다. |
HEX_STRING() |
16진수 문자열로 변환합니다. |
INTEGER() |
정수로 변환합니다. |
STRING() |
문자열로 변환합니다. |
| 비교 함수 | |
|---|---|
expr1 = expr2 |
두 표현식이 같으면 true를 반환합니다. |
expr1 != expr2expr1 <> 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() |
expr이 expr1, expr2 또는 괄호 안에 있는 값과 일치하면 true를 반환합니다. |
COALESCE() |
NULL이 아닌 첫 번째 인수를 반환합니다. |
GREATEST() |
가장 큰 numeric_expr 매개변수를 반환합니다. |
IFNULL() |
인수가 null이 아니면 인수를 반환합니다. |
IS_INF() |
양의 무한대이거나 음의 무한대이면 true를 반환합니다. |
IS_NAN() |
인수가 NaN이면 true를 반환합니다. |
IS_EXPLICITLY_DEFINED() |
지원 중단됨: 대신 expr IS NOT NULL을 사용하세요. |
LEAST() |
가장 작은 인수 numeric_expr 매개변수를 반환합니다. |
NVL() |
expr이 null이 아니면 expr을 반환하고, 그렇지 않으면 null_default를 반환합니다. |
| 날짜 및 시간 함수 | |
|---|---|
CURRENT_DATE() |
현재 날짜를 %Y-%m-%d 형식으로 반환합니다. |
CURRENT_TIME() |
서버의 현재 시간을 %H:%M:%S 형식으로 반환합니다. |
CURRENT_TIMESTAMP() |
서버의 현재 시간을 %Y-%m-%d %H:%M:%S 형식으로 반환합니다. |
DATE() |
날짜를 %Y-%m-%d 형식으로 반환합니다. |
DATE_ADD() |
지정된 간격을 TIMESTAMP 데이터 유형에 추가합니다. |
DATEDIFF() |
2개의 TIMESTAMP 데이터 유형 사이의 일 수를 반환합니다. |
DAY() |
월중 일을 1에서 31 사이의 정수로 반환합니다. |
DAYOFWEEK() |
주중 일을 1(일요일)에서 7(토요일) 사이의 정수로 반환합니다. |
DAYOFYEAR() |
연중 일을 1에서 366사이의 정수로 반환합니다. |
FORMAT_UTC_USEC() |
UNIX 타임스탬프를 YYYY-MM-DD HH:MM:SS.uuuuuu 형식으로 반환합니다. |
HOUR() |
TIMESTAMP의 시간을 0에서 23 사이의 정수로 반환합니다. |
MINUTE() |
TIMESTAMP의 분을 0에서 59 사이의 정수로 반환합니다. |
MONTH() |
TIMESTAMP의 월을 1에서 12 사이의 정수로 반환합니다. |
MSEC_TO_TIMESTAMP() |
밀리초 단위의 UNIX 타임스탬프를 TIMESTAMP로 변환합니다. |
NOW() |
현재 UNIX 타임스탬프를 마이크로초 단위로 반환합니다. |
PARSE_UTC_USEC() |
날짜 문자열을 마이크로초 단위의 UNIX 타임스탬프로 변환합니다. |
QUARTER() |
TIMESTAMP의 연중 분기를 1에서 4 사이의 정수로 반환합니다. |
SEC_TO_TIMESTAMP() |
초 단위의 UNIX 타임스탬프를 TIMESTAMP로 변환합니다. |
SECOND() |
TIMESTAMP의 초를 0에서 59 사이의 정수로 반환합니다. |
STRFTIME_UTC_USEC() |
날짜 문자열을 date_format_str 형식으로 반환합니다. |
TIME() |
TIMESTAMP를 %H:%M:%S 형식으로 반환합니다. |
TIMESTAMP() |
날짜 문자열을 TIMESTAMP로 변환합니다. |
TIMESTAMP_TO_MSEC() |
TIMESTAMP를 밀리초 단위의 UNIX 타임스탬프로 변환합니다. |
TIMESTAMP_TO_SEC() |
TIMESTAMP를 초 단위의 UNIX 타임스탬프로 변환합니다. |
TIMESTAMP_TO_USEC() |
TIMESTAMP를 마이크로초 단위의 UNIX 타임스탬프로 변환합니다. |
USEC_TO_TIMESTAMP() |
마이크로초 단위의 UNIX 타임스탬프를 TIMESTAMP로 변환합니다. |
UTC_USEC_TO_DAY() |
마이크로초 단위의 UNIX 타임스탬프를 발생 일의 시작 부분으로 이동합니다. |
UTC_USEC_TO_HOUR() |
마이크로초 단위의 UNIX 타임스탬프를 발생 시간의 시작 부분으로 이동합니다. |
UTC_USEC_TO_MONTH() |
마이크로초 단위의 UNIX 타임스탬프를 발생 월의 시작 부분으로 이동합니다. |
UTC_USEC_TO_WEEK() |
요일을 나타내는 마이크로초 단위의 UNIX 타임스탬프를 반환합니다. |
UTC_USEC_TO_YEAR() |
연도를 나타내는 마이크로초 단위의 UNIX 타임스탬프를 반환합니다. |
WEEK() |
TIMESTAMP의 주를 1에서 53 사이의 정수로 반환합니다. |
YEAR() |
TIMESTAMP의 연도를 반환합니다. |
| IP 함수 | |
|---|---|
FORMAT_IP() |
integer_value의 최하위 32개 비트를 인간이 읽을 수 있는 IPv4 주소 문자열로 변환합니다. |
PARSE_IP() |
IPv4 주소를 나타내는 문자열을 부호 없는 정수 값으로 변환합니다. |
FORMAT_PACKED_IP() |
인간이 읽을 수 있는 형식의 IP 주소를 10.1.5.23 또는 2620:0:1009:1:216:36ff:feef:3f 형식으로 반환합니다. |
PARSE_PACKED_IP() |
IP 주소를 BYTES로 반환합니다. |
| JSON 함수 | |
|---|---|
JSON_EXTRACT() |
JSONPath 표현식에 따라 값을 선택하고 JSON 문자열을 반환합니다. |
JSON_EXTRACT_SCALAR() |
JSONPath 표현식에 따라 값을 선택하고 JSON 스칼라를 반환합니다. |
| 논리 연산자 | |
|---|---|
expr AND expr |
두 표현식이 true이면 true를 반환합니다. |
expr OR expr |
두 표현식 중 하나 이상이 true이면 true를 반환합니다. |
NOT expr |
표현식이 false이면 true를 반환합니다. |
| 수학 함수 | |
|---|---|
ABS() |
인수의 절댓값을 반환합니다. |
ACOS() |
인수의 아크 코사인을 반환합니다. |
ACOSH() |
인수의 아크 쌍곡선 코사인을 반환합니다. |
ASIN() |
인수의 아크 사인을 반환합니다. |
ASINH() |
인수의 아크 쌍곡선 사인을 반환합니다. |
ATAN() |
인수의 아크 탄젠트를 반환합니다. |
ATANH() |
인수의 아크 쌍곡선 탄젠트를 반환합니다. |
ATAN2() |
두 인수의 아크 탄젠트를 반환합니다. |
CEIL() |
인수를 가까운 정수로 올림하고 올림된 값을 반환합니다. |
COS() |
인수의 코사인을 반환합니다. |
COSH() |
인수의 쌍곡선 코사인을 반환합니다. |
DEGREES() |
라디안에서 도로 변환합니다. |
EXP() |
인수를 지수로 한 e를 반환합니다. |
FLOOR() |
인수를 가까운 정수로 내림합니다. |
LN()LOG()
|
인수의 자연 로그를 반환합니다. |
LOG2() |
인수의 Base-2 로그를 반환합니다. |
LOG10() |
인수의 Base-10 로그를 반환합니다. |
PI() |
상수 π를 반환합니다. |
POW() |
두 번째 인수의 지수로 한 첫 번째 인수를 반환합니다. |
RADIANS() |
도에서 라디안으로 변환합니다. |
RAND() |
0.0 이상 1.0 미만의의 무작위 부동 소수점 값을 반환합니다. |
ROUND() |
인수를 가까운 정수로 반올림합니다. |
SIN() |
인수의 사인을 반환합니다. |
SINH() |
인수의 쌍곡선 사인을 반환합니다. |
SQRT() |
표현식의 제곱근을 반환합니다. |
TAN() |
인수의 탄젠트를 반환합니다. |
TANH() |
인수의 쌍곡선 탄젠트를 반환합니다. |
| 정규 표현식 함수 | |
|---|---|
REGEXP_MATCH() |
인수가 정규 표현식과 일치하면 true를 반환합니다. |
REGEXP_EXTRACT() |
정규 표현식 내에서 캡처 그룹과 일치하는 인수 부분을 반환합니다. |
REGEXP_REPLACE() |
정규 표현식과 일치하는 하위 문자열을 바꿉니다. |
| 문자열 함수 | |
|---|---|
CONCAT() |
2개 이상의 연결된 문자열을 반환하거나 값 중 NULL이 있는 경우 NULL을 반환합니다. |
expr CONTAINS 'str' |
expr에 지정된 문자열 인수가 있으면 true를 반환합니다. |
INSTR() |
문자열이 처음 발견된 위치에 대해 1부터 시작되는 색인을 반환합니다. |
LEFT() |
문자열의 가장 왼쪽 문자를 반환합니다. |
LENGTH() |
문자열의 길이를 반환합니다. |
LOWER() |
원본 문자열의 모든 문자를 소문자로 변환하여 반환합니다. |
LPAD() |
문자열 왼쪽에 문자를 삽입합니다. |
LTRIM() |
문자열 왼쪽에서 문자를 삭제합니다. |
REPLACE() |
하위 문자열이 발견된 모든 위치를 바꿉니다. |
RIGHT() |
문자열의 가장 오른쪽 문자를 반환합니다. |
RPAD() |
문자열의 오른쪽에 문자를 삽입합니다. |
RTRIM() |
문자열의 오른쪽에서 후행 문자를 삭제합니다. |
SPLIT() |
문자열을 반복되는 하위 문자열로 분할합니다. |
SUBSTR() |
하위 문자열을 반환합니다. |
UPPER() |
원본 문자열의 모든 문자를 대문자로 변환하여 반환합니다. |
| 테이블 와일드 카드 함수 | |
|---|---|
TABLE_DATE_RANGE() |
날짜 범위에 포함된 여러 일별 테이블을 쿼리합니다. |
TABLE_DATE_RANGE_STRICT() |
누락된 날짜가 없는 날짜 범위에 포함된 여러 일별 테이블을 쿼리합니다. |
TABLE_QUERY() |
이름이 지정된 조건자와 일치하는 테이블을 쿼리합니다. |
| URL 함수 | |
|---|---|
HOST() |
제공된 URL에 대해 호스트 이름을 문자열로 반환합니다. |
DOMAIN() |
제공된 URL에 대해 도메인을 문자열로 반환합니다. |
TLD() |
제공된 URL에 대해 최상위 도메인과 URL에 포함된 국가 도메인을 반환합니다. |
| 윈도우 함수 | |
|---|---|
AVG()COUNT(*)COUNT([DISTINCT])MAX()MIN()STDDEV()SUM() |
집계 함수와 동일한 연산을 수행하지만, OVER 절로 정의된 구간만 기준으로 연산합니다. |
CUME_DIST() |
값 그룹에서 특정 값의 누적 분포를 나타내는 double을 반환합니다. |
DENSE_RANK() |
값 그룹에서 특정 값의 정수 순위를 반환합니다. |
FIRST_VALUE() |
구간 내에서 지정된 필드의 첫 번째 값을 반환합니다. |
LAG() |
구간 내에서 이전 행의 데이터를 읽을 수 있습니다. |
LAST_VALUE() |
구간 내에서 지정된 필드의 마지막 값을 반환합니다. |
LEAD() |
구간 내에서 다음 행의 데이터를 읽을 수 있습니다. |
NTH_VALUE() |
윈도우 프레임의 <n> 위치에 있는 <expr> 값을 반환합니다. |
NTILE() |
구간을 지정된 버킷 수로 나눕니다. |
PERCENT_RANK() |
파티션에 있는 다른 행과 비교해 현재 행의 순위를 반환합니다. |
PERCENTILE_CONT() |
구간을 기준으로 백분위수 인수로 매핑되는 보간된 값을 반환합니다. |
PERCENTILE_DISC() |
구간 내에서 인수의 백분위수에 가장 가까운 값을 반환합니다. |
RANK() |
값 그룹에서 특정 값의 정수 순위를 반환합니다. |
RATIO_TO_REPORT() |
값 합계에 대한 각 값의 비율을 반환합니다. |
ROW_NUMBER() |
구간 내에서 쿼리 결과의 현재 행 번호를 반환합니다. |
| 기타 함수 | |
|---|---|
CASE WHEN ... THEN |
CASE를 사용하여 쿼리에 있는 2개 이상의 대체 표현식 중에서 선택합니다. |
CURRENT_USER() |
쿼리를 실행하는 사용자의 이메일 주소를 반환합니다. |
EVERY() |
인수가 모든 입력에 대해 true이면 true를 반환합니다. |
FROM_BASE64() |
base-64로 인코딩된 입력 문자열을 BYTES 형식으로 변환합니다. |
HASH() |
64비트 부호 있는 해시 값을 계산하고 반환합니다. |
FARM_FINGERPRINT() |
64비트 부호 있는 지문 값을 계산하고 반환합니다. |
IF() |
첫 번째 인수가 true이면 두 번째 인수를 반환하고, 그렇지 않으면 세 번째 인수를 반환합니다. |
POSITION() |
인수의 1부터 시작하는 순차적 위치를 반환합니다. |
SHA1() |
SHA1 해시를 BYTES 형식으로 반환합니다. |
SOME() |
인수가 입력 중 적어도 하나에 true이면 true를 반환합니다. |
TO_BASE64() |
BYTES 인수를 base-64로 인코딩된 문자열로 변환합니다. |
집계 함수
집계 함수는 큰 데이터세트의 요약을 나타내는 값을 반환하므로, 로그를 분석할 때 특히 유용합니다. 집계 함수는 값의 컬렉션을 대상으로 연산하고, 테이블, 그룹 또는 범위별 단일 값을 반환합니다.
- 테이블 집계
집계 함수를 사용하여 테이블에 있는 모든 적격한 행을 요약합니다. 예를 들면 다음과 같습니다.
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() |
행 그룹의 값 평균을 반환합니다. |
BIT_AND() |
비트 AND 연산의 결과를 반환합니다. |
BIT_OR() |
비트 OR 연산의 결과를 반환합니다. |
BIT_XOR() |
비트 XOR 연산의 결과를 반환합니다. |
CORR() |
숫자 쌍 집합의 피어슨 상관 계수를 반환합니다. |
COUNT() |
값의 전체 개수를 반환합니다. |
COUNT([DISTINCT]) |
NULL이 아닌 값의 전체 개수를 반환합니다. |
COVAR_POP() |
값의 모집단 공분산을 계산합니다. |
COVAR_SAMP() |
값의 표본 공분산을 계산합니다. |
EXACT_COUNT_DISTINCT() |
지정된 필드에서 NULL이 아닌 고유 값의 정확한 개수를 반환합니다. |
FIRST() |
함수 범위에서 첫 번째 순차 값을 반환합니다. |
GROUP_CONCAT() |
다중 문자열을 단일 문자열로 연결합니다. |
GROUP_CONCAT_UNQUOTED() |
다중 문자열을 단일 문자열로 연결합니다. 큰따옴표를 추가하지 않습니다. |
LAST() |
마지막 순차 값을 반환합니다. |
MAX() |
최댓값을 반환합니다. |
MIN() |
최솟값을 반환합니다. |
NEST() |
현재 집계 범위의 모든 값을 반복 필드로 집계합니다. |
NTH() |
n번째 순차 값을 반환합니다. |
QUANTILES() |
근사 최솟값, 최댓값, 백분위수를 계산합니다. |
STDDEV() |
표준 편차를 반환합니다. |
STDDEV_POP() |
모집단 표준 편차를 계산합니다. |
STDDEV_SAMP() |
표본 표준 편차를 계산합니다. |
SUM() |
값의 총계를 반환합니다. |
TOP() ... COUNT(*) |
빈도별 최상위 max_records 레코드를 반환합니다. |
UNIQUE() |
NULL이 아닌 고유 값의 집합을 반환합니다. |
VARIANCE() |
값의 분산을 계산합니다. |
VAR_POP() |
값의 모집단 분산을 계산합니다. |
VAR_SAMP() |
값의 표본 분산을 계산합니다. |
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+1buckets를 사용합니다.buckets의 기본값은 100입니다. (참고: 기본값 100은 백분위수를 추정하지 않습니다. 백분위수를 추정하려면 최소한 101buckets를 사용합니다.) 명시적으로 지정된 경우,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입니다.
multiplierCOUNT(*)로 반환되는 값을 지정된 배수만큼 늘려주는 양의 정수입니다.
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이 반환됩니다.
구문
| 연산자 | 설명 | 예시 |
|---|---|---|
| + | 더하기 |
반환: 10 |
| - | 빼기 |
반환: 1 |
| * | 곱하기 |
반환: 24 |
| / | 나누기 |
반환: 1.5 |
| % | Modulo |
반환: 2 |
비트 함수
비트 함수는 개별 비트 수준에서 작동하며 숫자 인수가 필요합니다. 비트 함수에 대한 자세한 내용은 비트 연산을 참조하세요.
세 가지 추가 비트 함수(BIT_AND, BIT_OR, BIT_XOR)는 집계 함수를 참조하세요.
구문
| 연산자 | 설명 | 예시 |
|---|---|---|
| & | 비트 AND |
반환: 0 |
| | | 비트 OR |
반환: 28 |
| ^ | 비트 XOR |
반환: 1 |
| << | 비트 왼쪽 이동 |
반환: 16 |
| >> | 비트 오른쪽 이동 |
반환: 2 |
| ~ | 비트 NOT |
반환: -3 |
BIT_COUNT(<numeric_expr>) |
|
반환: 4 |
Cast 변환 함수
Cast 변환 함수는 숫자 표현식의 데이터 유형을 변환합니다. Cast 변환 함수는 비교 함수에 사용되는 인수가 동일한 데이터 유형을 갖도록 보장하는 데 특히 유용합니다.
구문
| Cast 변환 함수 | |
|---|---|
BOOLEAN() |
부울로 변환합니다. |
BYTES() |
바이트로 변환합니다. |
CAST(expr AS type) |
expr을 type 유형의 변수로 변환합니다. |
FLOAT() |
double로 변환합니다. |
HEX_STRING() |
16진수 문자열로 변환합니다. |
INTEGER() |
정수로 변환합니다. |
STRING() |
문자열로 변환합니다. |
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 != expr2expr1 <> 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() |
expr이 expr1, expr2 또는 괄호 안에 있는 값과 일치하면 true를 반환합니다. |
COALESCE() |
NULL이 아닌 첫 번째 인수를 반환합니다. |
GREATEST() |
가장 큰 numeric_expr 매개변수를 반환합니다. |
IFNULL() |
인수가 null이 아니면 인수를 반환합니다. |
IS_INF() |
양의 무한대이거나 음의 무한대이면 true를 반환합니다. |
IS_NAN() |
인수가 NaN이면 true를 반환합니다. |
IS_EXPLICITLY_DEFINED() |
지원 중단됨: 대신 expr IS NOT NULL을 사용하세요. |
LEAST() |
가장 작은 인수 numeric_expr 매개변수를 반환합니다. |
NVL() |
expr이 null이 아니면 expr을 반환하고, 그렇지 않으면 null_default를 반환합니다. |
expr1 = expr2- 두 표현식이 같으면
true를 반환합니다. expr1 != expr2
expr1 <> expr2- 두 표현식이 같지 않으면
true를 반환합니다. expr1 > expr2expr1이expr2보다 크면true를 반환합니다.expr1 < expr2expr1이expr2보다 작으면true를 반환합니다.expr1 >= expr2expr1이expr2이상이면true를 반환합니다.expr1 <= expr2expr1이expr2이하이면true를 반환합니다.expr1 BETWEEN expr2 AND expr3-
expr1값이expr2이상이고expr3이하이면true를 반환합니다. expr IS NULLexpr이 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 형식으로 반환합니다. |
CURRENT_TIME() |
서버의 현재 시간을 %H:%M:%S 형식으로 반환합니다. |
CURRENT_TIMESTAMP() |
서버의 현재 시간을 %Y-%m-%d %H:%M:%S 형식으로 반환합니다. |
DATE() |
날짜를 %Y-%m-%d 형식으로 반환합니다. |
DATE_ADD() |
지정된 간격을 TIMESTAMP 데이터 유형에 추가합니다. |
DATEDIFF() |
2개의 TIMESTAMP 데이터 유형 사이의 일 수를 반환합니다. |
DAY() |
월중 일을 1에서 31 사이의 정수로 반환합니다. |
DAYOFWEEK() |
주중 일을 1(일요일)에서 7(토요일) 사이의 정수로 반환합니다. |
DAYOFYEAR() |
연중 일을 1에서 366사이의 정수로 반환합니다. |
FORMAT_UTC_USEC() |
UNIX 타임스탬프를 YYYY-MM-DD HH:MM:SS.uuuuuu 형식으로 반환합니다. |
HOUR() |
TIMESTAMP의 시간을 0에서 23 사이의 정수로 반환합니다. |
MINUTE() |
TIMESTAMP의 분을 0에서 59 사이의 정수로 반환합니다. |
MONTH() |
TIMESTAMP의 월을 1에서 12 사이의 정수로 반환합니다. |
MSEC_TO_TIMESTAMP() |
밀리초 단위의 UNIX 타임스탬프를 TIMESTAMP로 변환합니다. |
NOW() |
현재 UNIX 타임스탬프를 마이크로초 단위로 반환합니다. |
PARSE_UTC_USEC() |
날짜 문자열을 마이크로초 단위의 UNIX 타임스탬프로 변환합니다. |
QUARTER() |
TIMESTAMP의 연중 분기를 1에서 4 사이의 정수로 반환합니다. |
SEC_TO_TIMESTAMP() |
초 단위의 UNIX 타임스탬프를 TIMESTAMP로 변환합니다. |
SECOND() |
TIMESTAMP의 초를 0에서 59 사이의 정수로 반환합니다. |
STRFTIME_UTC_USEC() |
날짜 문자열을 date_format_str 형식으로 반환합니다. |
TIME() |
TIMESTAMP를 %H:%M:%S 형식으로 반환합니다. |
TIMESTAMP() |
날짜 문자열을 TIMESTAMP로 변환합니다. |
TIMESTAMP_TO_MSEC() |
TIMESTAMP를 밀리초 단위의 UNIX 타임스탬프로 변환합니다. |
TIMESTAMP_TO_SEC() |
TIMESTAMP를 초 단위의 UNIX 타임스탬프로 변환합니다. |
TIMESTAMP_TO_USEC() |
TIMESTAMP를 마이크로초 단위의 UNIX 타임스탬프로 변환합니다. |
USEC_TO_TIMESTAMP() |
마이크로초 단위의 UNIX 타임스탬프를 TIMESTAMP로 변환합니다. |
UTC_USEC_TO_DAY() |
마이크로초 단위의 UNIX 타임스탬프를 발생 일의 시작 부분으로 이동합니다. |
UTC_USEC_TO_HOUR() |
마이크로초 단위의 UNIX 타임스탬프를 발생 시간의 시작 부분으로 이동합니다. |
UTC_USEC_TO_MONTH() |
마이크로초 단위의 UNIX 타임스탬프를 발생 월의 시작 부분으로 이동합니다. |
UTC_USEC_TO_WEEK() |
요일을 나타내는 마이크로초 단위의 UNIX 타임스탬프를 반환합니다. |
UTC_USEC_TO_YEAR() |
연도를 나타내는 마이크로초 단위의 UNIX 타임스탬프를 반환합니다. |
WEEK() |
TIMESTAMP의 주를 1에서 53 사이의 정수로 반환합니다. |
YEAR() |
TIMESTAMP의 연도를 반환합니다. |
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에는 날짜 관련 구두점 문자(예: date_format_str 및 date_format_str)와 C++의 strftime 함수에서 허용되는 특수 문자(예: 개월을 나타내는 date_format_str)가 포함될 수 있습니다.
특정 월의 모든 데이터를 가져올 때와 같이 시간 간격에 따라 쿼리 데이터를 그룹화하려면
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 주소를 인간이 읽을 수 있는 형식으로 또는 그 반대로 변환합니다.
구문
| IP 함수 | |
|---|---|
FORMAT_IP() |
integer_value의 최하위 32개 비트를 인간이 읽을 수 있는 IPv4 주소 문자열로 변환합니다. |
PARSE_IP() |
IPv4 주소를 나타내는 문자열을 부호 없는 정수 값으로 변환합니다. |
FORMAT_PACKED_IP() |
인간이 읽을 수 있는 형식의 IP 주소를 10.1.5.23 또는 2620:0:1009:1:216:36ff:feef:3f 형식으로 반환합니다. |
PARSE_PACKED_IP() |
IP 주소를 BYTES로 반환합니다. |
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 함수 | |
|---|---|
JSON_EXTRACT() |
JSONPath 표현식에 따라 값을 선택하고 JSON 문자열을 반환합니다. |
JSON_EXTRACT_SCALAR() |
JSONPath 표현식에 따라 값을 선택하고 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를 반환합니다. |
expr OR expr |
두 표현식 중 하나 이상이 true이면 true를 반환합니다. |
NOT expr |
표현식이 false이면 true를 반환합니다. |
expr AND expr- 두 표현식이 true이면
true를 반환합니다. - 두 표현식 중 하나 이상이 false이면
false를 반환합니다. - 두 표현식이 NULL이거나 하나의 표현식 true이고 다른 표현식이 NULL이면
NULL을 반환합니다.
- 두 표현식이 true이면
expr OR expr- 두 표현식 중 하나 이상이 true이면
true를 반환합니다. - 두 표현식이 false이면
false를 반환합니다. - 두 표현식이 NULL이거나 하나의 표현식 false이고 다른 표현식이 NULL이면
NULL을 반환합니다.
- 두 표현식 중 하나 이상이 true이면
NOT expr- 표현식이 false이면
true를 반환합니다. - 표현식이 true이면
false를 반환합니다. - 표현식이 NULL이면
NULL을 반환합니다.
NOT은 다른 함수에서 부정 연산자로 사용할 수 있습니다. 예를 들면NOT IN(expr1, expr2)또는IS NOT NULL입니다.- 표현식이 false이면
수학 함수
수학 함수는 숫자 인수를 사용하고 숫자 결과를 반환합니다. 각 인수는 숫자 리터럴이거나 쿼리로 반환된 숫자 값일 수 있습니다. 수학 함수가 정의되지 않은 결과로 평가될 경우 연산이 NULL을 반환합니다.
구문
| 수학 함수 | |
|---|---|
ABS() |
인수의 절댓값을 반환합니다. |
ACOS() |
인수의 아크 코사인을 반환합니다. |
ACOSH() |
인수의 아크 쌍곡선 코사인을 반환합니다. |
ASIN() |
인수의 아크 사인을 반환합니다. |
ASINH() |
인수의 아크 쌍곡선 사인을 반환합니다. |
ATAN() |
인수의 아크 탄젠트를 반환합니다. |
ATANH() |
인수의 아크 쌍곡선 탄젠트를 반환합니다. |
ATAN2() |
두 인수의 아크 탄젠트를 반환합니다. |
CEIL() |
인수를 가까운 정수로 올림하고 올림된 값을 반환합니다. |
COS() |
인수의 코사인을 반환합니다. |
COSH() |
인수의 쌍곡선 코사인을 반환합니다. |
DEGREES() |
라디안에서 도로 변환합니다. |
EXP() |
인수를 지수로 한 e를 반환합니다. |
FLOOR() |
인수를 가까운 정수로 내림합니다. |
LN()LOG()
|
인수의 자연 로그를 반환합니다. |
LOG2() |
인수의 Base-2 로그를 반환합니다. |
LOG10() |
인수의 Base-10 로그를 반환합니다. |
PI() |
상수 π를 반환합니다. |
POW() |
두 번째 인수의 지수로 한 첫 번째 인수를 반환합니다. |
RADIANS() |
도에서 라디안으로 변환합니다. |
RAND() |
0.0 이상 1.0 미만의의 무작위 부동 소수점 값을 반환합니다. |
ROUND() |
인수를 가까운 정수로 반올림합니다. |
SIN() |
인수의 사인을 반환합니다. |
SINH() |
인수의 쌍곡선 사인을 반환합니다. |
SQRT() |
표현식의 제곱근을 반환합니다. |
TAN() |
인수의 탄젠트를 반환합니다. |
TANH() |
인수의 쌍곡선 탄젠트를 반환합니다. |
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() |
인수가 정규 표현식과 일치하면 true를 반환합니다. |
REGEXP_EXTRACT() |
정규 표현식 내에서 캡처 그룹과 일치하는 인수 부분을 반환합니다. |
REGEXP_REPLACE() |
정규 표현식과 일치하는 하위 문자열을 바꿉니다. |
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')orig_str와 일치하는 orig_str의 하위 문자열이 orig_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() |
2개 이상의 연결된 문자열을 반환하거나 값 중 NULL이 있는 경우 NULL을 반환합니다. |
expr CONTAINS 'str' |
expr에 지정된 문자열 인수가 있으면 true를 반환합니다. |
INSTR() |
문자열이 처음 발견된 위치에 대해 1부터 시작되는 색인을 반환합니다. |
LEFT() |
문자열의 가장 왼쪽 문자를 반환합니다. |
LENGTH() |
문자열의 길이를 반환합니다. |
LOWER() |
원본 문자열의 모든 문자를 소문자로 변환하여 반환합니다. |
LPAD() |
문자열 왼쪽에 문자를 삽입합니다. |
LTRIM() |
문자열 왼쪽에서 문자를 삭제합니다. |
REPLACE() |
하위 문자열이 발견된 모든 위치를 바꿉니다. |
RIGHT() |
문자열의 가장 오른쪽 문자를 반환합니다. |
RPAD() |
문자열의 오른쪽에 문자를 삽입합니다. |
RTRIM() |
문자열의 오른쪽에서 후행 문자를 삭제합니다. |
SPLIT() |
문자열을 반복되는 하위 문자열로 분할합니다. |
SUBSTR() |
하위 문자열을 반환합니다. |
UPPER() |
원본 문자열의 모든 문자를 대문자로 변환하여 반환합니다. |
CONCAT('str1', 'str2', '...')
str1 + str2 + ...- 2개 이상의 연결된 문자열을 반환하거나 값 중 NULL이 있는 경우 NULL을 반환합니다. 예를 들어
str1이Java고str2가Script면CONCAT은JavaScript를 반환합니다. expr CONTAINS 'str'expr에 지정된 문자열 인수가 있으면true를 반환합니다. 이 비교에서는 대소문자가 구분됩니다.INSTR('str1', 'str2')- str2에서 str2 첫 어커런스의 1로 시작하는 색인을 반환하거나, str2가 str2에 없으면 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은 str2 왼쪽에서 공백을 삭제합니다. 그렇지 않으면LTRIM은 str2 왼쪽부터 str2에 있는 모든 문자를 삭제합니다(대소문자 구분).예시:
SELECT LTRIM("Say hello", "yaS")은" hello"를 반환합니다.SELECT LTRIM("Say hello", " ySa")은"hello"를 반환합니다. REPLACE('str1', 'str2', 'str3')-
str2 내에서 발견된 모든 str2를 str2로 바꿉니다.
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은 str2에서 후행 공백을 삭제합니다. 그렇지 않으면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')- 원본 문자열의 모든 문자를 대문자로 변환하여 반환합니다.
문자열에서 특수 문자 이스케이프 처리
특수 문자를 이스케이프 처리하려면 다음 방법 중 하나를 사용합니다.
'\xDD'표기법을 사용합니다. 여기서'\x'다음에는 두 자리의 16진수 문자 표현식이 나옵니다.- 슬래시, 작은따옴표, 큰따옴표 앞에 이스케이프 슬래시를 사용합니다.
- 다른 문자의 경우 C 스타일 시퀀스(
'\a', '\b', '\f', '\n', '\r', '\t',,'\v')를 사용합니다.
몇 가지 이스케이프 처리 예시:
'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() |
날짜 범위에 포함된 여러 일별 테이블을 쿼리합니다. |
TABLE_DATE_RANGE_STRICT() |
누락된 날짜가 없는 날짜 범위에 포함된 여러 일별 테이블을 쿼리합니다. |
TABLE_QUERY() |
이름이 지정된 조건자와 일치하는 테이블을 쿼리합니다. |
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 함수
구문
| URL 함수 | |
|---|---|
HOST() |
제공된 URL에 대해 호스트 이름을 문자열로 반환합니다. |
DOMAIN() |
제공된 URL에 대해 도메인을 문자열로 반환합니다. |
TLD() |
제공된 URL에 대해 최상위 도메인과 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);
반환 값:
+------+-------+--------------+ | name | value | RunningTotal | +------+-------+--------------+ | a | 0 | 0 | | b | 1 | 1 | | c | 2 | 3 | | d | 3 | 6 | | e | 4 | 10 | +------+-------+--------------+
다음 예시는 현재 행과 그 이전 행에 있는 값의 이동 평균을 계산합니다. 윈도우 프레임은 현재 행과 함께 이동하는 2개 행으로 구성됩니다.
#legacySQL
SELECT
name,
value,
AVG(value)
OVER (ORDER BY value
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
AS MovingAverage
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);
반환 값:
+------+-------+---------------+ | name | value | MovingAverage | +------+-------+---------------+ | a | 0 | 0.0 | | b | 1 | 0.5 | | c | 2 | 1.5 | | d | 3 | 2.5 | | e | 4 | 3.5 | +------+-------+---------------+
구문
| 윈도우 함수 | |
|---|---|
AVG()COUNT(*)COUNT([DISTINCT])MAX()MIN()STDDEV()SUM() |
집계 함수와 동일한 연산을 수행하지만, OVER 절로 정의된 구간만 기준으로 연산합니다. |
CUME_DIST() |
값 그룹에서 특정 값의 누적 분포를 나타내는 double을 반환합니다. |
DENSE_RANK() |
값 그룹에서 특정 값의 정수 순위를 반환합니다. |
FIRST_VALUE() |
구간 내에서 지정된 필드의 첫 번째 값을 반환합니다. |
LAG() |
구간 내에서 이전 행의 데이터를 읽을 수 있습니다. |
LAST_VALUE() |
구간 내에서 지정된 필드의 마지막 값을 반환합니다. |
LEAD() |
구간 내에서 다음 행의 데이터를 읽을 수 있습니다. |
NTH_VALUE() |
윈도우 프레임의 <n> 위치에 있는 <expr> 값을 반환합니다. |
NTILE() |
구간을 지정된 버킷 수로 나눕니다. |
PERCENT_RANK() |
파티션에 있는 다른 행과 비교해 현재 행의 순위를 반환합니다. |
PERCENTILE_CONT() |
구간을 기준으로 백분위수 인수로 매핑되는 보간된 값을 반환합니다. |
PERCENTILE_DISC() |
구간 내에서 인수의 백분위수에 가장 가까운 값을 반환합니다. |
RANK() |
값 그룹에서 특정 값의 정수 순위를 반환합니다. |
RATIO_TO_REPORT() |
값 합계에 대한 각 값의 비율을 반환합니다. |
ROW_NUMBER() |
구간 내에서 쿼리 결과의 현재 행 번호를 반환합니다. |
-
AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)
- 이러한 윈도우 함수는 해당 집계 함수와 동일한 연산을 수행하지만 OVER 절로 정의된 윈도우에서 계산됩니다.
또한
COUNT([DISTINCT] field)함수는 윈도우 함수로 사용될 때EXACT_COUNT_DISTINCT()집계 함수처럼 정확한 결과를 생성한다는 큰 차이점이 있습니다.쿼리 예시에서
ORDER BY절은 파티션의 시작 지점에서 현재 행까지의 윈도우를 계산하여 해당 연도의 누적 합계를 생성합니다.#legacySQL SELECT corpus_date, corpus, word_count, SUM(word_count) OVER ( PARTITION BY corpus_date ORDER BY word_count) annual_total FROM [bigquery-public-data:samples.shakespeare] WHERE word='love' ORDER BY corpus_date, word_count반환:
corpus_date corpus word_count annual_total 0 various 37 37 0 sonnets 157 194 1590 2kinghenryvi 18 18 1590 1kinghenryvi 24 42 1590 3kinghenryvi 40 82 CUME_DIST()-
<number of rows preceding or tied with the current row> / <total rows>공식을 사용하여 계산된 값 그룹에서 특정 값의 누적 분포를 나타내는 double을 반환합니다. 연결된 값은 동일한 누적 분포 값을 반환합니다.이 윈도우 함수는
OVER절에ORDER BY가 있어야 합니다.#legacySQL SELECT word, word_count, CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:
word word_count cume_dist handkerchief 29 0.2 satisfaction 5 0.4 displeasure 4 0.8 instruments 4 0.8 circumstance 3 1.0 DENSE_RANK()-
값 그룹에서 특정 값의 정수 순위를 반환합니다. 순위는 그룹에 있는 다른 값과 비교하여 계산됩니다.
연결된 값은 동일한 순위로 표시됩니다. 다음 값의 순위는 1씩 증가합니다. 예를 들어 두 값이 순위 2로 연결되면 다음 순위 값은 3입니다. 순위 목록에서 간격이 필요하면 rank()를 사용합니다.
이 윈도우 함수는
OVER절에ORDER BY가 있어야 합니다.#legacySQL SELECT word, word_count, DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:word word_count dense_rank handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 3 circumstance 3 4 FIRST_VALUE(<field_name>)-
구간에서
<field_name>의 첫 번째 값을 반환합니다.#legacySQL SELECT word, word_count, FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
반환:word word_count fv imperfectly 1 imperfectly LAG(<expr>[, <offset>[, <default_value>]])-
구간 내에서 이전 행의 데이터를 읽을 수 있습니다. 특히
LAG()는 현재 행 앞의<offset>행에 위치한 행에 대해<expr>값을 반환합니다. 행이 존재하지 않는 경우<default_value>를 반환합니다.#legacySQL SELECT word, word_count, LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:
word word_count lag handkerchief 29 null satisfaction 5 handkerchief displeasure 4 satisfaction instruments 4 displeasure circumstance 3 instruments LAST_VALUE(<field_name>)-
구간에서
<field_name>의 마지막 값을 반환합니다.#legacySQL SELECT word, word_count, LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
반환:
word word_count lv imperfectly 1 imperfectly LEAD(<expr>[, <offset>[, <default_value>]])-
구간 내에서 다음 행의 데이터를 읽을 수 있습니다. 특히
LEAD()는 현재 행 뒤의<offset>행에 위치한 행에 대해<expr>값을 반환합니다. 행이 존재하지 않는 경우<default_value>를 반환합니다.#legacySQL SELECT word, word_count, LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:word word_count lead handkerchief 29 satisfaction satisfaction 5 displeasure displeasure 4 instruments instruments 4 circumstance circumstance 3 null NTH_VALUE(<expr>, <n>)-
윈도우 프레임의
<n>위치에 있는<expr>값을 반환합니다.<n>은 1부터 시작하는 색인입니다. NTILE(<num_buckets>)-
일련의 행을 버킷
<num_buckets>개로 나누고 각 행에 해당 버킷 수를 정수로 할당합니다.ntile()함수는 버킷 수를 가능한 한 동일하게 할당하고 각 행에 1부터<num_buckets>까지의 값을 반환합니다.#legacySQL SELECT word, word_count, NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:word word_count ntile handkerchief 29 1 satisfaction 5 1 displeasure 4 1 instruments 4 2 circumstance 3 2 PERCENT_RANK()-
파티션에 있는 다른 행에 상대되는 현재 행의 순위를 반환합니다. 반환되는 값의 범위는 0에서 1(포함) 사이입니다. 반환되는 첫 번째 값은 0.0입니다.
이 윈도우 함수는
OVER절에ORDER BY가 있어야 합니다.#legacySQL SELECT word, word_count, PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:word word_count p_rank handkerchief 29 0.0 satisfaction 5 0.25 displeasure 4 0.5 instruments 4 0.5 circumstance 3 1.0 PERCENTILE_CONT(<percentile>)-
해당 구간과 관련해서 백분위수 인수로 매핑되는 보간된 값을
ORDER BY절에 따라 정렬한 후 반환합니다.<percentile>은 0과 1 사이여야 합니다.이 윈도우 함수는
OVER절에ORDER BY가 있어야 합니다.#legacySQL SELECT word, word_count, PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:word word_count p_cont handkerchief 29 4 satisfaction 5 4 displeasure 4 4 instruments 4 4 circumstance 3 4 PERCENTILE_DISC(<percentile>)-
구간 내에서 인수의 백분위수에 가장 가까운 값을 반환합니다.
<percentile>은 0과 1 사이여야 합니다.이 윈도우 함수는
OVER절에ORDER BY가 있어야 합니다.#legacySQL SELECT word, word_count, PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:word word_count p_disc handkerchief 29 4 satisfaction 5 4 displeasure 4 4 instruments 4 4 circumstance 3 4 RANK()-
값 그룹에서 특정 값의 정수 순위를 반환합니다. 순위는 그룹에 있는 다른 값과 비교하여 계산됩니다.
연결된 값은 동일한 순위로 표시됩니다. 다음 값의 순위는 전에 연결된 값의 수에 따라 증가합니다. 예를 들어 두 값이 순위 2로 연결되면 다음 순위 값은 3이 아닌 4입니다. 순위 목록에서 간격이 필요하지 않으면 dense_rank()를 사용합니다.
이 윈도우 함수는
OVER절에ORDER BY가 있어야 합니다.#legacySQL SELECT word, word_count, RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:word word_count rank handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 3 circumstance 3 5 RATIO_TO_REPORT(<column>)-
값의 합계에 대한 각 값의 비율을 0에서 1 사이의 double로 반환합니다.
#legacySQL SELECT word, word_count, RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:word word_count r_to_r handkerchief 29 0.6444444444444445 satisfaction 5 0.1111111111111111 displeasure 4 0.08888888888888889 instruments 4 0.08888888888888889 circumstance 3 0.06666666666666667 ROW_NUMBER()-
해당 구간 동안 1부터 시작하는 쿼리 결과의 현재 행 번호를 반환합니다.
#legacySQL SELECT word, word_count, ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
반환:word word_count row_num handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 4 circumstance 3 5
기타 함수
구문
| 기타 함수 | |
|---|---|
CASE WHEN ... THEN |
CASE를 사용하여 쿼리에 있는 2개 이상의 대체 표현식 중에서 선택합니다. |
CURRENT_USER() |
쿼리를 실행하는 사용자의 이메일 주소를 반환합니다. |
EVERY() |
인수가 모든 입력에 대해 true이면 true를 반환합니다. |
FROM_BASE64() |
base-64로 인코딩된 입력 문자열을 BYTES 형식으로 변환합니다. |
HASH() |
64비트 부호 있는 해시 값을 계산하고 반환합니다. |
FARM_FINGERPRINT() |
64비트 부호 있는 지문 값을 계산하고 반환합니다. |
IF() |
첫 번째 인수가 true이면 두 번째 인수를 반환하고, 그렇지 않으면 세 번째 인수를 반환합니다. |
POSITION() |
인수의 1부터 시작하는 순차적 위치를 반환합니다. |
SHA1() |
SHA1 해시를 BYTES 형식으로 반환합니다. |
SOME() |
인수가 입력 중 적어도 하나에 true이면 true를 반환합니다. |
TO_BASE64() |
BYTES 인수를 base-64로 인코딩된 문자열로 변환합니다. |
CASE WHEN when_expr1 THEN then_expr1
WHEN when_expr2 THEN then_expr2 ...
ELSE else_expr END- CASE를 사용하여 쿼리에 있는 2개 이상의 대체 표현식 중에서 선택합니다. WHEN 표현식이 부울이어야 하고 THEN 절과 ELSE 절의 모든 표현식은 호환되는 유형이어야 합니다.
CURRENT_USER()- 쿼리를 실행하는 사용자의 이메일 주소를 반환합니다.
EVERY(<condition>)condition이 모든 입력에 대해 true이면true를 반환합니다.OMIT IF절과 함께 사용할 경우 이 함수는 반복 필드를 포함하는 쿼리에 유용합니다.FROM_BASE64(<str>)- base-64로 인코딩된
str입력 문자열을 BYTES 형식으로 변환합니다. BYTES를 base64로 인코딩된 문자열로 변환하려면 TO_BASE64()를 사용합니다. HASH(expr)- CityHash 라이브러리(버전 1.0.3)에서 정의된 대로
expr바이트의 64비트 부호 있는 해시 값을 계산하고 반환합니다. 모든 문자열 또는 정수 표현식이 지원되며 이 함수는 문자열에IGNORE CASE를 반영하여 대소문자가 반영되지 않은 값을 반환합니다. FARM_FINGERPRINT(expr)- 오픈소스 FarmHash 라이브러리의
Fingerprint64함수를 사용하여STRING또는BYTES입력의 64비트 부호 있는 지문 값을 계산하고 반환합니다. 특정 입력에 대한 이 함수의 출력은 절대 변경되지 않으며 GoogleSQL을 사용할 때FARM_FINGERPRINT함수의 출력과 일치합니다. 문자열에IGNORE CASE를 반영하여 대소문자가 반영되지 않은 값을 반환합니다. IF(condition, true_return, false_return)-
condition이 true 또는 false인지에 따라true_return또는false_return을 반환합니다. 반환 값은 리터럴 또는 필드 파생 값일 수 있지만 데이터 유형은 동일해야 합니다. 필드 파생 값을SELECT절에 포함할 필요가 없습니다. POSITION(field)- 반복 필드 집합 내에서 1부터 시작하는 필드의 순차 위치를 반환합니다.
SHA1(<str>)- 입력 문자열
str의 SHA1 해시를 BYTES 형식으로 반환합니다. TO_BASE64()를 사용하여 결과를 base64로 변환할 수 있습니다. 예를 들면 다음과 같습니다.#legacySQL SELECT TO_BASE64(SHA1(corpus)) FROM [bigquery-public-data:samples.shakespeare] LIMIT 100;
SOME(<condition>)condition이 입력 중 최소 하나 이상에 true이면true를 반환합니다.OMIT IF절과 함께 사용할 경우 이 함수는 반복 필드를 포함하는 쿼리에 유용합니다.TO_BASE64(<bin_data>)- BYTES 입력
bin_data를 base64로 인코딩된 문자열로 변환합니다. 예를 들면 다음과 같습니다.#legacySQL SELECT TO_BASE64(SHA1(title)) FROM [bigquery-public-data:samples.wikipedia] LIMIT 100;
base64로 인코딩된 문자열을 BYTES로 변환하려면 FROM_BASE64()를 사용하세요.
고급 예시
-
조건을 사용하여 결과를 카테고리로 묶기
다음 쿼리는
CASE/WHEN블록을 사용하여 상태 목록을 기준으로 결과를 'region' 카테고리로 묶습니다. 상태가WHEN문 중 하나의 옵션으로 표시되지 않으면 상태 값은 기본적으로 'None'입니다.예시:
#legacySQL SELECT CASE WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID', 'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM') THEN 'West' WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL', 'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV', 'MD', 'DC', 'DE') THEN 'South' WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA', 'MO', 'WI', 'IL', 'IN', 'MI', 'OH') THEN 'Midwest' WHEN state IN ('NY', 'PA', 'NJ', 'CT', 'RI', 'MA', 'VT', 'NH', 'ME') THEN 'Northeast' ELSE 'None' END as region, average_mother_age, average_father_age, state, year FROM (SELECT year, state, SUM(mother_age)/COUNT(mother_age) as average_mother_age, SUM(father_age)/COUNT(father_age) as average_father_age FROM [bigquery-public-data:samples.natality] WHERE father_age < 99 GROUP BY year, state) ORDER BY year LIMIT 5;반환:
+--------+--------------------+--------------------+-------+------+ | region | average_mother_age | average_father_age | state | year | +--------+--------------------+--------------------+-------+------+ | South | 24.342600163532296 | 27.683769419460344 | AR | 1969 | | West | 25.185041908446163 | 28.268214055448098 | AK | 1969 | | West | 24.780776677578217 | 27.831181063905248 | CA | 1969 | | West | 25.005834769924412 | 27.942978384829598 | AZ | 1969 | | South | 24.541730952905738 | 27.686430093306885 | AL | 1969 | +--------+--------------------+--------------------+-------+------+
-
피벗 테이블 시뮬레이션
조건문을 사용하여 subselect 쿼리 결과를 행과 열로 구성합니다. 아래 예시에서 값이 'Google' 값으로 시작하고 가장 많이 수정된 위키백과 문서를 검색한 결과가 여러 조건을 충족하면 수정 횟수가 표시된 열로 구성됩니다.
예시:
#legacySQL SELECT page_title, /* Populate these columns as True or False, */ /* depending on the condition */ IF (page_title CONTAINS 'search', INTEGER(total), 0) AS search, IF (page_title CONTAINS 'Earth' OR page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo, FROM /* Subselect to return top revised Wikipedia articles */ /* containing 'Google', followed by additional text. */ (SELECT TOP (title, 5) as page_title, COUNT (*) as total FROM [bigquery-public-data:samples.wikipedia] WHERE REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0 );반환:
+---------------+--------+------+ | page_title | search | geo | +---------------+--------+------+ | Google search | 4261 | 0 | | Google Earth | 0 | 3874 | | Google Chrome | 0 | 0 | | Google Maps | 0 | 2617 | | Google bomb | 0 | 0 | +---------------+--------+------+
-
HASH를 사용하여 데이터의 무작위 표본 선택
일부 쿼리는 결과 집합의 무작위 서브 샘플링을 사용해서 유용한 결과를 제공할 수 있습니다. 값의 무작위 표본을 가져오려면
HASH함수를 사용하여 해시의 나머지 'n'이 0인 결과를 반환합니다.예를 들어 다음 쿼리는 'title' 값의
HASH()를 찾은 후 값 나머지 '2'가 0인지 확인합니다. 그러면 값의 약 50%가 'sampled'로 표시됩니다. 더 적은 값을 표본 추출하려면 나머지 연산의 값을 '2'에서 더 큰 숫자로 늘립니다. 이 쿼리는HASH와 함께ABS함수를 사용합니다.HASH는 음수 값을 반환할 수 있으며, 음수 값에 대한 나머지 연산자는 음수 값을 생성합니다.예시:
#legacySQL SELECT title, HASH(title) AS hash_value, IF(ABS(HASH(title)) % 2 == 1, 'True', 'False') AS included_in_sample FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace = 0 LIMIT 5;