Ch06 SQL Aggregates

2020-04-26

구글 클라우드, Google Cloud, 빅쿼리, BigQuery, Python

Page content

1. 구글 클라우드 설정

본격적인 빅쿼리 실습에 앞서서, Python과 연동하는 예제를 준비하였다. 빅쿼리 시작에 앞서서 선행적으로 클라우드 사용을 해야 한다.

만약 GCP 프로젝트가 없다면, 계정을 연동한다. Go to Cloud Resource Manager
그리고, 비용결제를 위한 카드를 등록한다. Enable billing
마지막으로 BigQuery API를 사용해야 하기 때문에 빅쿼리 API 사용허가를 내준다.Enable BigQuery

위 API를 이용하지 않으면 Python 또는 R과 연동해서 사용할 수는 없다. 자주 쓰는것이 아니라면 비용은 거의 발생하지 않으니 염려하지 않아도 된다. 비용관리에 대한 자세한 내용은 BigQuery 권장사항: 비용 관리에서 확인하기를 바란다.

2. 사용자 계정 인증

구글 코랩을 사용해서 인증 절차를 밟도록 한다. 아래 소스코드는 변경시키지 않는다. 아래 절차대로 진행하면 된다. Gmail 인증 절차와 비슷하다.

from google.colab import auth
auth.authenticate_user()
print('Authenticated')

Authenticated

3. 쿼리 기본

빅쿼리는 기본적으로 데이터 웨어하우스의 기능을 가지고 있다. Structured(데이터프레임) & Semi-Structured(JSON)과 같은 DB를 지원하고 있다. Create, Read, Update, Delete 기능을 지원한다.

빅쿼리는 데이터 분석을 위한 기본적인 도구이기 때문에, 웹/앱을 활용한 마케팅 분석이 필요한 업종에서는 어쩌면 반드시 가져가야할 일종의 언어이기도 하다.

기본적으로 SQL문법을 지원하며, BigQuery 나름의 문법을 또한 구성하고 있다. 이제 본격적으로 코드를 작성해보자.

현재 작성하는 모든 코드는 Python-BigQuery로 연동하여 작성하려고 한다. 이제 간단하게 쿼리를 작성해보자.

Ch05-Query_Essentials(1)에서는 SELECT와 AS에 대해 배우는 시간을 가졌다.
Ch05-Query_Essentials(2)에서는 WHERE와 EXCEPT, 그리고 REPLACE에 대해 배우는 시간을 가졌다.
Ch05-Query_Essentials(3)에서는 SubQuery에 대해서 집중적으로 배우는 시간을 가졌다.

오늘 포스트에서는 Aggregates, 일반적인 집계함수 처리법에 대해 학습하도록 한다.

(1) Group By

SQL에서 평균을 구해보자. SQL이 일반적으로 조회기능만 가지고 있다고 생각하면 안된다. 기본적으로 SQL에서 처리할 수 있는 단순한 계산들은 Database에서 해주는 것이 많은 도움이 된다.

평균을 구하려는 건, 다름이 아니라 남성 이용자들의 평균 tripduration 시간을 알아보자는 데 있다. 어떻게 해야할까?

from google.cloud import bigquery
from tabulate import tabulate

project_id = 'your_project_id'
client = bigquery.Client(project=project_id)

temp = client.query('''
  SELECT 
    AVG(tripduration / 60) as avg_trip_duration
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE gender = 'male'
  ''').to_dataframe()

print(tabulate(temp, tablefmt="pipe", headers="keys"))

WARNING:google.auth._default:No project ID could be determined. Consider running `gcloud config set project` or setting the GOOGLE_CLOUD_PROJECT environment variable


|    |   avg_trip_duration |
|---:|--------------------:|
|  0 |             13.4156 |

다른 구문은 지난시간에 계속 배운 것이기 때문에 익숙하지만, AVG() 함수가 사용되었다.

이번에는 조금 더 업그레이드를 해본다. 실무에서 전체 평균은 그렇게 중요하지 않다. 각 속성별로 gender를 그룹화 해서 비교하는 것을 기본 원칙으로 삼는다.

temp = client.query('''
  SELECT
    gender, AVG(tripduration / 60) AS avg_trip_duration
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE tripduration is not NULL
  GROUP BY
    gender
  ORDER BY
    avg_trip_duration
  ''')

print(tabulate(temp, tablefmt='pipe', headers='keys'))

| 0       |       1 |
|:--------|--------:|
| male    | 13.4156 |
| female  | 15.9775 |
| unknown | 31.4395 |

위 구문을 이해하는데 있어서 GROUP BY를 주의해서 봐야한다. 그룹화 하려는 Column명을 SELECT다음에 명시를 해줘야 한 뒤, 작성해야줘야 한다. 만약 SELECT문에 gender가 없으면 그룹별 값은 구해지지만, gender가 없기 때문에 불완전한 쿼리 결과물이 나온다. 빠르게 확인해보자.

temp = client.query('''
  SELECT
    AVG(tripduration / 60) AS avg_trip_duration
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE tripduration is not NULL
  GROUP BY
    gender
  ORDER BY
    avg_trip_duration
  ''')

print(tabulate(temp, tablefmt='pipe', headers='keys'))

|       0 |
|--------:|
| 13.4156 |
| 15.9775 |
| 31.4395 |

두 구문을 비교해서, 어떻게 GROUP BY를 써야 할지 확인해본다.

(2) HAVING

Having은 언제 쓸까? 영어 동사를 알고 있다면 이해하는데 큰 어려움이 없을 것이다. 간단하게 설명하면, Having은 WHERE절과 비슷한 기능을 갖는 일종의 필터링을 한다. male, female, unknown 값이 나온 것에서 평균 15이하인 것을 제거한다고 해보자.

temp = client.query('''
  SELECT
    gender, AVG(tripduration / 60) AS avg_trip_duration
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE tripduration is not NULL
  GROUP BY
    gender
  HAVING avg_trip_duration > 14
  ORDER BY
    avg_trip_duration
  ''')

print(tabulate(temp, tablefmt='pipe', headers='keys'))

| 0       |       1 |
|:--------|--------:|
| female  | 15.9775 |
| unknown | 31.4395 |

15이하였던 male이 사라진 것을 확인할 수 있다. 그런데, 이 때, HAVING절의 위치가 중요하다. HAVING절이 ORDER BY앞에 명시가 되고 있는 것을 확인할 수 있다.

만약 HAVING절이 ORDER BY뒤에 오면 어떻게 될까?

temp = client.query('''
  SELECT
    gender, AVG(tripduration / 60) AS avg_trip_duration
  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE tripduration is not NULL
  GROUP BY
    gender
  ORDER BY
    avg_trip_duration
  HAVING avg_trip_duration > 14
  ''')

print(tabulate(temp, tablefmt='pipe', headers='keys'))

---------------------------------------------------------------------------

BadRequest                                Traceback (most recent call last)

<ipython-input-13-3a9913fbbf59> in <module>()
     11   ''')
     12 
---> 13 print(tabulate(temp, tablefmt='pipe', headers='keys'))


/usr/local/lib/python3.6/dist-packages/tabulate.py in tabulate(tabular_data, headers, tablefmt, floatfmt, numalign, stralign, missingval, showindex, disable_numparse, colalign)
   1425         tabular_data = []
   1426     list_of_lists, headers = _normalize_tabular_data(
-> 1427         tabular_data, headers, showindex=showindex
   1428     )
   1429 


/usr/local/lib/python3.6/dist-packages/tabulate.py in _normalize_tabular_data(tabular_data, headers, showindex)
   1023 
   1024     else:  # it's a usual an iterable of iterables, or a NumPy array
-> 1025         rows = list(tabular_data)
   1026 
   1027         if headers == "keys" and not rows:


/usr/local/lib/python3.6/dist-packages/google/cloud/bigquery/job.py in __iter__(self)
   3110 
   3111     def __iter__(self):
-> 3112         return iter(self.result())
   3113 
   3114 


/usr/local/lib/python3.6/dist-packages/google/cloud/bigquery/job.py in result(self, timeout, page_size, retry, max_results)
   2972         """
   2973         try:
-> 2974             super(QueryJob, self).result(timeout=timeout)
   2975 
   2976             # Return an iterator instead of returning the job.


/usr/local/lib/python3.6/dist-packages/google/cloud/bigquery/job.py in result(self, timeout, retry)
    766             self._begin(retry=retry)
    767         # TODO: modify PollingFuture so it can pass a retry argument to done().
--> 768         return super(_AsyncJob, self).result(timeout=timeout)
    769 
    770     def cancelled(self):


/usr/local/lib/python3.6/dist-packages/google/api_core/future/polling.py in result(self, timeout)
    125             # pylint: disable=raising-bad-type
    126             # Pylint doesn't recognize that this is valid in this case.
--> 127             raise self._exception
    128 
    129         return self._result


BadRequest: 400 Syntax error: Unexpected keyword HAVING at [10:3]

(job ID: 96ceb51a-9673-479f-be00-22c50a047e24)

                  -----Query Job SQL Follows-----                  

    |    .    |    .    |    .    |    .    |    .    |    .    |
   1:
   2:  SELECT
   3:    gender, AVG(tripduration / 60) AS avg_trip_duration
   4:  FROM `bigquery-public-data.new_york_citibike.citibike_trips`
   5:  WHERE tripduration is not NULL
   6:  GROUP BY
   7:    gender
   8:  ORDER BY
   9:    avg_trip_duration
  10:  HAVING avg_trip_duration > 14
  11:  
    |    .    |    .    |    .    |    .    |    .    |    .    |

보다시피, 에러가 나타나는데, 에러 메시지는 400 Syntax error: Unexpected keyword HAVING at [10:3] 즉, 문법에 맞지 않다는 뜻이다. 이런 SQL구문을 처음 접하는 사람에게는 어려울 수 있지만, 기본 문법은 문법이기 때문에 명시적으로 암기해서 작성하는 것이 좋다.

(3) DISTINCT

DISTINCT는 특정 그룹에서 구별되다, 따로 분리되다 등 해석되고는 합니다. 각 문자열의 각 Column들은 특정 값들이 반복해서 나타나게 되는데, 이 때, DISTINCT를 사용하면 반복되는 값들을 빠르게 확인할 수 있습니다. 코드를 통해서 결과를 확인해봅니다.

temp = client.query('''
  SELECT DISTINCT
     usertype
  FROM
    `bigquery-public-data.new_york_citibike.citibike_trips`
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers='keys'))

|    | usertype   |
|---:|:-----------|
|  0 | Subscriber |
|  1 | Customer   |
|  2 |            |

결과를 조회해보니, 4개가 출력되었다. 왜 4개일까? 결과적으로 말하면 공백이 있다는 뜻이다. 실무에서 이런경우는 매우 많이 벌어진다. 또한, 처음 업무에 투입하면 들어오는 데이터를 모르는 경우가 많기 때문에 SQL에서 여러 조회를 하다보면, 이런 결과값이 나오는 것이 부지기수다. 이럴 때 당황할 필요 없이 다음 SQL문을 실행해보면 된다.

temp = client.query('''
  SELECT
    bikeid,
    tripduration,
    usertype
  FROM
    `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE gender= ""
  LIMIT 5
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers='keys'))

|    | bikeid   | tripduration   | usertype   |
|---:|:---------|:---------------|:-----------|
|  0 |          |                |            |
|  1 |          |                |            |
|  2 |          |                |            |
|  3 |          |                |            |
|  4 |          |                |            |

공백으로 나타내는 것을 확인할 수 있다. python jupyter notebook에서는 나타나고 있지 않지만, 이를 BigQuery GUI에서 위 쿼리문을 다시 실행하면 NULL 값을 확인할 수 있다.

문제는 이 다음이다. 어떻게 처리해야 할까? 당연히 조회값으로는 적정하지 않으니, 이를 연산자를 통해 제거한다. (WHERE절에서 작성)

temp = client.query('''
  SELECT DISTINCT
    gender, usertype
  FROM
    `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE gender != ''
  ''').to_dataframe()

print(tabulate(temp, tablefmt='pipe', headers='keys'))

|    | gender   | usertype   |
|---:|:---------|:-----------|
|  0 | male     | Subscriber |
|  1 | female   | Subscriber |
|  2 | male     | Customer   |
|  3 | unknown  | Subscriber |
|  4 | female   | Customer   |
|  5 | unknown  | Customer   |

이제 원하는 대로 공백값이 지워지고 각각 매칭되도록 결과값이 나온 것을 확인할 수 있다.

이번 포스트에서는 GROUP를 활용한 HAVING 그리고 DISTINCT 대해서 배웠는데, 도움이 되었기를 바란다.

다음 포스트에서는 Arrays & Structs에 대해 학습하도록 하겠다. 이 부분이 사실 기초 파트에서는 매우 중요한 포인트가 될 것 같다!

4. Reference

Lakshmanan, V., & Tigani, J. (2020). Google BigQuery: the definitive guide: data warehousing, analytics, and machine learning at scale. Beijing: OReilly.