Google 시각화 API 쿼리 언어를 사용하면 데이터 소스에 대한 쿼리로 다양한 데이터 조작을 수행할 수 있습니다.

목차

소개

일반적으로 시각화에는 특정 형식의 데이터가 필요합니다. 예를 들어 원형 차트는 데이터를 텍스트 라벨과 숫자 값, 이렇게 두 개의 열로 예상할 수 있습니다. 데이터 소스 내의 데이터가 이 구조와 정확하게 일치하지 않을 수 있습니다. 예를 들어 데이터 소스에 열이 3개 이상 있거나 열의 순서가 원형 차트에서 예상하는 순서와 일치하지 않을 수 있습니다.

쿼리 언어는 데이터 조작 및 형식 지정 요청을 데이터 소스에 보내고 반환된 데이터 구조 및 콘텐츠가 예상 구조와 일치하는지 확인하는 기능을 제공합니다.

쿼리 언어의 구문은 SQL과 유사합니다. SQL에 익숙한 개발자는 이 쿼리 언어를 빠르게 배우고 사용할 수 있어야 합니다. 웹에서 다양한 SQL 가이드를 사용할 수 있습니다. 이 쿼리 언어와 SQL 간에는 syntax 섹션에 설명된 몇 가지 차이점이 있습니다.

쿼리 언어를 구현하는 데 데이터 소스가 필요한 것은 아니며, 구현해야 하는 경우에도 언어의 모든 기능을 구현할 수 있습니다. 그렇게 판단할 이유가 없다면 이 언어의 모든 기능을 데이터 소스에 의존하여 구현해서는 안 됩니다.

쿼리 언어 사용

JavaScript 코드 내에서 쿼리 문자열을 설정하거나 데이터 소스 URL에서 쿼리 문자열을 매개변수로 설정하는 두 가지 방법으로 쿼리 문자열을 데이터 소스 요청에 연결할 수 있습니다. 요청에 쿼리 문자열이 포함되지 않은 경우 데이터 소스의 기본 동작은 기본 행/열 순서와 형식을 사용하여 모든 행과 열을 반환하는 것입니다. 데이터 소스 요청에 쿼리 문자열을 포함하여 이를 변경할 수 있습니다.

JavaScript에서 쿼리 설정

JavaScript 코드 내에서 쿼리 문자열을 설정하려면 google.visualization.Query 클래스의 setQuery 메서드를 호출합니다.

var query = new google.visualization.Query(DATA_SOURCE_URL);
query.setQuery('select dept, sum(salary) group by dept');
query.send(handleQueryResponse);

데이터 소스 URL에서 쿼리 설정

tq 매개변수를 사용하여 쿼리 문자열을 데이터 소스 URL에 추가할 수 있습니다. JavaScript 대신 URL 매개변수에 쿼리를 설정하면 다른 개발자가 작성한 시각화를 쉽게 사용할 수 있으며 쿼리를 맞춤설정할 수도 있습니다.

쿼리 문자열이 URL 매개변수로 올바르게 인코딩되어야 합니다. JavaScript encodeURIComponent 함수를 사용하여 URL을 인코딩하거나 이 섹션의 마지막에 있는 인코딩 도구를 사용하여 직접 인코딩할 수 있습니다.

예:

Google 스프레드시트에 대해 다음 쿼리 문자열을 고려해보세요. 스프레드시트의 열 ID는 항상 문자이며 게시된 스프레드시트에 표시되는 열 제목 텍스트는 ID가 아니라 라벨입니다. 쿼리 문자열에서는 라벨이 아닌 ID를 사용해야 합니다.)

select A, sum(B) group by A

이 쿼리는 인코딩될 때 다음과 같이 됩니다.

select%20A%2C%20sum(B)%20group%20by%20A

이것이 스프레드시트의 URL이라고 가정해 보겠습니다.

https://docs.google.com/a/google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4

스프레드시트 URL에 /gviz/tq?tq=YOUR_QUERY_STRING을 추가하여 최종 쿼리 문자열을 가져옵니다.

https://docs.google.com/a/google.com/spreadsheets/d/1r8_mfnZAvTFmT02JHi1XgOwn_-sLCR9XgmR8wEQ4uW4/gviz/tq?tq=select%A%2C%20sum(B)%20group%20by%20A

아래 도구를 사용하여 쿼리 문자열을 인코딩 또는 디코딩합니다.

언어 구문

개요

Google 시각화 API 쿼리 언어 구문은 SQL 구문과 유사하게 설계되었습니다. 하지만 SQL의 하위 집합이며 자체적으로 몇 가지 기능을 배워야 합니다. SQL에 익숙하다면 학습하기는 어렵지 않을 것입니다

모든 예시에서 사용된 표:

이 섹션에 있는 모든 쿼리 예는 다음 표를 참조합니다. 열 헤더는 열 식별자입니다.

언어 조항

쿼리 언어의 구문은 다음 절로 구성됩니다. 각 절은 1~2개의 키워드로 시작합니다. 모든 절은 선택사항입니다. 절은 공백으로 구분됩니다. 절의 순서는 다음과 같아야 합니다.

선택

select 절은 반환할 열과 순서를 지정하는 데 사용됩니다. 이 절이 지정되지 않았거나 select *가 사용되면 데이터 소스 테이블의 모든 열이 원래 순서로 반환됩니다. 열은 라벨이 아닌 식별자로 참조됩니다. 예를 들어 Google 스프레드시트에서 열 식별자는 1~2자로 된 열 문자 (A, B, C, ...)입니다.

select 절의 항목은 열 식별자 또는 집계 함수, 스칼라 함수, 연산자의 출력일 수 있습니다.

예:

select *

select dept, salary

select max(salary)

다음 예에서 백따옴표는 공백 (이메일 주소)을 포함하거나 예약된 단어 (날짜)인 열 ID를 참조하는 데 사용됩니다.

select `email address`, name, `date`

예시 테이블에서 다음 쿼리 실행:

select lunchTime, name

다음 응답을 반환합니다.

위치

where 절은 지정된 조건과 일치하는 행만 반환하는 데 사용됩니다.

간단한 비교 연산자는 <=, <, >, >=, =, !=, <>입니다. 두 비교 연산자 != <> 모두 같지 않음을 의미합니다. 문자열은 사전식 값으로 비교됩니다. 같음은 대부분의 컴퓨터 언어에서와 같이 ==가 아닌 =로 표시됩니다. null와의 비교는 is null 또는 is not null를 사용하여 수행됩니다.

논리 연산자 and, or, not를 사용하여 여러 조건을 조인할 수 있습니다. 괄호는 명시적 우선순위를 정의하는 데 사용할 수 있습니다.

WHERE 절은 좀 더 복잡한 문자열 비교 연산자도 지원합니다. 이러한 연산자는 문자열 2개를 인수로 사용합니다. 문자열이 아닌 인수 (예: 날짜 또는 숫자)는 비교 전에 문자열로 변환됩니다. 문자열 일치는 대소문자를 구분합니다. 이 문제는 upper() 또는 lower() 스칼라 함수를 사용하여 해결할 수 있습니다.

• contains - 하위 문자열 일치입니다. whole contains part가 whole 내 어디에든 있으면 true입니다. 예: where name contains 'John'는 'John', 'John Adams', 'Long John Silver'와 일치하지만 'john adams'와는 일치하지 않습니다.
• starts with - 프리픽스 일치입니다. prefix가 value의 시작 부분에 있으면 value starts with prefix가 true입니다. 예: where dept starts with 'engineering'은 '엔지니어링' 및 '엔지니어링 관리자'와 일치합니다. where dept starts with 'e'는 'engineering', 'eng', 'e'과 일치합니다.
• ends with - 서픽스 일치입니다. value ends with suffix는 suffix가 value의 끝에 있으면 true입니다. 예: where role ends with 'y'는 'cowboy', 'boy', 'y'와 일치합니다.
• matches - (preg) 정규 표현식 일치입니다. haystack matches needle은 needle의 정규 표현식이 haystack과 일치하면 true입니다. 예: where country matches '.*ia'는 인도 및 나이지리아와 일치하지만 인디애나는 일치하지 않습니다. 글로벌 검색이 아니므로 where country matches 'an'은 'Canada'와 일치하지 않습니다.
• like - 와일드 카드 2개를 지원하는 텍스트 검색입니다. 0개 이상의 모든 문자와 일치하는 % 및 1개 문자와 일치하는 _ (밑줄)가 지원됩니다. 이는 SQL LIKE 연산자와 유사합니다. 예: where name like fre%는 'fre', 'fred', 'freddy'와 일치합니다.

예:

where salary >= 600
where dept != 'Eng' and date '2005-01-21' < hireDate
where (dept<>'Eng' and isSenior=true) or (dept='Sales') or seniorityStartTime is null

예시 테이블에서 다음 쿼리 실행:

select name where salary > 700

다음 응답을 반환합니다.

그룹화 기준

group by 절은 여러 행에서 값을 집계하는 데 사용됩니다. 그룹화 기준 절의 고유한 값 조합마다 단일 행이 생성됩니다. order by 절에서 달리 지정되지 않는 한 데이터는 그룹화 열을 기준으로 자동 정렬됩니다.

참고: group by 절을 사용하는 경우 select 절에 나열된 모든 열은 group by 절에 나열되거나 집계 함수로 래핑되어야 합니다.

예:

select dept, max(salary) group by dept

예시 테이블에서 다음 쿼리 실행:

select lunchTime, avg(salary), count(age) group by isSenior,lunchTime

다음 응답을 반환합니다.

피벗

pivot 절은 열의 고유한 값을 새 열로 변환하는 데 사용됩니다. 예를 들어 '연도' 열을 기준으로 피벗하면 원본 테이블에 표시되는 고유한 각 연도의 열이 포함된 테이블이 생성됩니다. 예를 들어 선 차트 시각화에서 각 열을 별도의 선으로 그리는 경우에 유용할 수 있습니다. 각 연도에 대해 별도의 선을 그리고 '연도'가 원본 테이블의 열 중 하나인 경우 피벗 작업을 사용하여 필요한 데이터를 변환하는 것이 좋습니다.

참고: pivot 절을 사용하는 경우 select 절에 나열된 모든 열은 group by 절에 나열되거나 집계 함수로 래핑되어야 합니다.

여러 행에 피벗 열에 동일한 값이 포함될 수 있으므로 피벗은 집계를 의미합니다. group by를 사용하지 않고 pivot를 사용하면 결과 테이블에 정확히 1개의 행이 포함됩니다. 예를 들어 테이블 예시에서 다음 쿼리를 실행합니다.

select sum(salary) pivot dept

다음 응답을 반환합니다.

2, 100은 엔지니어링 부서의 급여를 합한 것이며, 마케팅 부서의 급여를 800으로 합한 금액이기 때문입니다.

pivot를 group by와 함께 사용하면 각 셀에 관련 행과 열의 집계 결과가 포함된 테이블이 생성되므로 훨씬 더 유용할 수 있습니다. 예를 들어 테이블 예시에서 다음 쿼리를 실행합니다.

select dept, sum(salary)
  group by dept
  pivot lunchTime

다음 응답을 반환합니다.

pivot 열과 group by 열 간에 전환하여 열과 행을 전환하여 이 표를 '반전'할 수도 있습니다. 예시 테이블에서 다음 쿼리 실행:

select lunchTime, sum(salary)
  group by lunchTime
  pivot dept

다음 응답을 반환합니다.

pivot 절에서 열을 두 개 이상 사용할 수도 있습니다. 이 경우 응답 테이블의 열은 원본 테이블에 있는 열의 모든 고유한 값 조합으로 구성됩니다. 예를 들어 테이블 예시에서 다음 쿼리를 실행합니다.

select sum(salary)
  pivot dept, lunchTime

다음 응답을 반환합니다.

원본 테이블에 표시되는 조합만 응답 테이블에 열이 제공됩니다. 이 때문에 마케팅(12:00:00) 또는 영업(13:00:00) 열이 없습니다.

둘 이상의 집계를 사용할 수도 있습니다. 예를 들어 테이블 예에서 다음 쿼리를 실행합니다.

select sum(salary), max(lunchTime)
  pivot dept

다음 응답을 반환합니다.

select 절에서 여러 집계, group by 절에서 여러 열, pivot 절에서 여러 열을 결합할 수 있습니다. 내부적으로 집계는 그룹화 기준 및 피벗 절의 열을 연결하여 수행됩니다.

pivot 절에 지정된 열은 select, group by 또는 order by 절에 나타날 수 없습니다. pivot가 사용되는 경우 order by 절은 집계 열을 포함할 수 없습니다. 그 이유는 select 절에 지정된 각 집계에 대해 결과 테이블에 많은 열이 생성되기 때문입니다. 그러나 pivot를 사용할 때 집계 열의 형식을 지정할 수 있습니다. 이러한 형식을 사용하면 피벗 연산으로 생성된 특정 집계와 관련된 모든 새 열이 지정된 패턴으로 형식이 지정됩니다. 위 예시에서 format sum(salary) "some_format_string"을 추가하면 영어 합계, 마케팅 합계, 급여, 영업 합계 열이 영향을 받습니다.

집계 열에 라벨을 지정할 수 있습니다. label 절에 라벨이 지정되지 않은 경우, 피봇팅의 결과로 생성된 열의 라벨은 피봇 열의 값 목록, 집계 유형 (최소, 최대, 합계, ...), 집계된 열의 라벨로 구성됩니다. 예: 'Eng,12:00:00 sum 급여' select 절에 하나의 집계만 지정된 경우, 집계 부분은 라벨에서 삭제되고 피벗 열의 값 목록만 유지됩니다. 예: 'Eng,12:00:00' label 절이 집계 열의 라벨을 지정하면 요청된 라벨이 select 절에 집계가 하나만 있는 경우와 집계가 두 개 이상인 경우 모두 값 목록에 추가됩니다. 예를 들어 label sum(salary) "sumsal"는 'Eng,12:00:00 sumsal', 'Eng,13:00:00 sumsal' 등의 열 라벨을 생성합니다.

정렬 기준

order by 절은 지정된 열의 값을 기준으로 행을 정렬하는 데 사용됩니다.

order by 절의 항목은 열 식별자 또는 집계 함수, 스칼라 함수, 연산자의 출력일 수 있습니다.

예:

order by dept, salary desc

select dept, max(salary) group by dept order by max(salary)

한도

limit 절은 반환되는 행 수를 제한하는 데 사용됩니다.

예:

limit 100

오프셋

offset 절은 지정된 수의 첫 행을 건너뛰는 데 사용됩니다. limit 절이 사용되는 경우 offset가 먼저 적용됩니다. 예를 들어 limit 15 offset 30는 31~45행을 반환합니다.

예:

offset 10
limit 30 offset 210

라벨

label 절은 하나 이상의 열에 라벨을 설정하는 데 사용됩니다. 쿼리에서 ID 대신 라벨 값을 사용할 수 없습니다.

label 절의 항목은 열 식별자 또는 집계 함수, 스칼라 함수, 연산자의 출력일 수 있습니다.

구문:

label column_id label_string [,column_id label_string]

column_id

: 라벨이 할당되는 열의 식별자입니다.

label_string

해당 열에 할당할 라벨입니다. 원형 차트의 범례 라벨과 같이 많은 시각화에서 열 라벨을 텍스트로 사용하여 최종 사용자에게 표시합니다. 라벨은 문자열 리터럴이며 이러한 구문 규칙을 따릅니다.

예:

다음 예시에서는 dept 열의 라벨을 '부서'로, 이름 열의 라벨을 '직원 이름'으로, 위치 열의 라벨을 '직원 위치'로 설정합니다.

label dept 'Department', name "Employee Name", location 'Employee Location'

형식

format 절은 하나 이상의 열에 있는 셀의 형식이 지정된 값을 지정하는 데 사용됩니다. 반환된 데이터에는 형식이 지정된 열의 각 셀에 대한 실제 값과 형식이 지정된 값이 모두 포함되어야 합니다. 많은 시각화에서 계산에는 형식이 지정되지 않은 값을 사용하지만 표시에는 형식이 지정된 값을 사용합니다. 이 절에서 지정하는 패턴은 일반적으로 해당 열의 pattern 속성에 반환됩니다.

패턴 구문:

number, date, timeofday, datetime

ICU에서 정의한 날짜 및 숫자 패턴입니다.

boolean

패턴은 'value-if-true:value-if-false' 형식의 string입니다.

예:

format salary '#,##0.00', hireDate 'dd-MMM-yyyy', isSenior 'Yes!:Not yet'

옵션

options 절은 쿼리 실행을 위한 추가 옵션을 제어하는 데 사용됩니다. options 절 다음에 올 수 있는 키워드는 다음과 같습니다.

• no_format 결과에서 서식이 지정된 값을 삭제하고 기본 값만 남깁니다. 특정 시각화에서 응답 크기를 줄이기 위해 형식이 지정된 값을 사용하지 않는 경우에 사용할 수 있습니다.
• no_values 결과에서 기본 값을 삭제하고 형식이 지정된 값만 남깁니다. 특정 시각화에서 형식이 지정된 값만 사용하여 응답 크기를 줄이는 경우에 사용할 수 있습니다.

데이터 조작 함수

단일 열에서 데이터를 조작 또는 집계하거나 여러 열에서 데이터를 비교 또는 결합할 수 있는 여러 종류의 연산자와 함수가 있습니다. sum() (열의 모든 값 더하기), max (열에서 가장 큰 값 찾기), + (같은 행에 두 열의 값 더하기) 등을 예로 들 수 있습니다.

일부 함수는 모든 절에 나타날 수 있으며, 일부는 절의 하위 집합으로 표시될 수 있습니다. 이에 대해서는 아래에 설명되어 있습니다.

예:

select upper(name), year(startDate)

다음 데이터 조작 함수는 Google 시각화 API 쿼리 언어로 정의됩니다.

• 집계 함수
• 스칼라 함수
• 산술 연산자

집계 함수

집계 함수에는 단일 열 식별자가 전달되고 각 그룹의 모든 값에 대해 작업을 수행합니다. 그룹은 group by 또는 pivot 절로 지정되거나 이러한 절이 사용되지 않는 경우에는 모든 행으로 지정됩니다.

예:

select max(salary)               // Returns a table with one column and one row.
select max(salary) group by dept // Returns a table with the max salary for each dept column value.
select max(salary) pivot dept    // Returns a one-row table with a column for each dept,
                                 //   and the max salary for that department.

집계 함수는 select, order by, label, format 절에서 사용할 수 있습니다. where, group by, pivot, limit, offset 또는 options 절에 나타날 수 없습니다 .

다음은 지원되는 집계 함수입니다.

참고: 집계 함수는 열 식별자만 인수로 사용할 수 있습니다.

max(startDate)                      // OK
min(firstScore) + min(secondScore)  // OK
max(year(startDate))                // INVALID. max requires column ID
sum(salary + perks)                 // INVALID. sum requires column ID.

스칼라 함수

스칼라 함수는 0개 이상의 매개변수에 대해 작동하여 다른 값을 생성합니다. 스칼라 함수는 적절한 유형의 매개변수로 평가되는 모든 표현식에 전달될 수 있습니다. 이러한 유형은 이 문서의 리터럴 섹션에 정의된 유형이며, 유사한 이름의 자바스크립트 객체와 약간 다를 수 있습니다.

열 이름은 스칼라 함수로 래핑하여 변경됩니다.

스칼라 함수는 단일 값으로 평가되는 모든 것을 매개변수로 취할 수 있습니다.

year(max(startDate))
datediff(now(), todate(1234567890000))

스칼라 함수는 select, where, group by, pivot, order by, label,, format 절에서 사용할 수 있습니다.

산술 연산자

산술 연산자를 사용하여 단일 숫자로 평가되는 모든 항목 (즉, 적절한 집계 함수, 연산자, 상수의 출력)에 대해 수학 연산을 수행할 수 있습니다.

예:

select empSalary - empTax
select 2 * (max(empSalary) / max(empTax))

다음 연산자가 정의됩니다.

언어 요소

리터럴

리터럴은 비교 또는 할당에 사용되는 값입니다. 리터럴은 string, 숫자, 불리언 값 또는 다양한 날짜/시간 유형일 수 있습니다. 다음은 쿼리 구문에 사용되는 리터럴의 몇 가지 예시입니다.

where startDate < date "2008-03-18"  // date "2008-03-18" is a date literal
limit 30                             // 30 is a numeric literal
format salary '#,##0.00', isSenior 'not yet:of course!'  // '#,##0.00' and 'not yet:of course!' are both string literals

다음은 각 리터럴 유형의 형식입니다.

string

string 리터럴은 작은따옴표나 큰따옴표로 묶어야 합니다. 예: "fourteen" 'hello world' "It's raining".

number

숫자 리터럴은 십진수 표기법으로 지정됩니다. 예: 3  3.0  3.14  -71  -7.2  .6

boolean

부울 리터럴은 true 또는 false입니다.

date

키워드 date 다음에 yyyy-MM-dd 형식으로 string 리터럴을 사용합니다. 예: date "2008-03-18".

timeofday

키워드 timeofday 다음에 HH:mm:ss[.SSS] 형식으로 string 리터럴을 사용합니다. 예: timeofday "12:30:45".

datetime

날짜 및 시간으로, 키워드 datetime 또는 키워드 timestamp 뒤에 yyyy-MM-dd HH:mm:ss[.sss] 형식의 string 리터럴이 사용됩니다. 예: datetime '2008-03-18 12:30:34.123'

식별자

식별자 (또는 ID)는 열을 식별하는 텍스트 string입니다.

중요: 식별자가

• 공백 있음,
• 예약어,
• 영숫자 문자 또는 밑줄을 제외한 모든 문자 ([a-zA-Z0-9_]) 또는
• 숫자로 시작

작은따옴표가 아닌 백따옴표로 묶어야 합니다.

그렇지 않은 경우 식별자를 따옴표로 묶지 않아도 됩니다. 구문에 의해 정의된 모든 키워드가 예약 단어인 것은 아닙니다. 따라서 예를 들어 'max'를 작은따옴표로 묶지 않고 식별자로 사용할 수 있습니다.

예: col1   employee_table   `start date`   `7 days traffic`   `select`

역따옴표를 사용하는 것을 잊어버리거나 실수로 '역따옴표' 대신 '작은따옴표'를 사용하기 쉬우므로 백따옴표를 요구하는 식별자는 선택하지 않는 것이 좋습니다. 이는 흔히 발생하는 실수이며 디버그하기 어려운 경우가 많습니다.

대소문자 구분

식별자와 문자열 리터럴은 대소문자를 구분합니다. 다른 모든 언어 요소는 대소문자를 구분하지 않습니다.

예약어

다음 예약어를 식별자로 사용할 경우 역따옴표로 묶어야 합니다.

and
asc
by
date
datetime
desc
false
format
group
label
limit
not
offset
options
or
order
pivot
select
timeofday
timestamp
true
where