구글 빅쿼리 완벽 가이드 - 2장(2) - 집계

▣ 집계

• 앞서 살펴본 예제에서는 테이블의 모든 행으로부터 tripduration 컬럼의 값을 60으로 나눠 초를 분으로 변환했다. 하지만 집계 함수를 이용하면 모든 행의 값을 집계해 결과 집합에 하나의 행만 나타나도록 할 수 있다.

■ GROUP BY로 집계하기

• 다음 쿼리는 남성 사용자의 평균 대여 시간을 찾는다.

SELECT
  AVG(tripduration/60) AS avg_trip_duration
FROM
  `bigquery-public-data`.new_york_citibike.citibike_trips
WHERE
  gender = 'male'

• 이 결과를 보면 뉴욕에서 남성 사용자가 평균적으로 자전거를 대여하는 시간이 약 13.4분임을 알 수 있다. 그러나 데이터셋은 지속적으로 갱신되므로 이 쿼리를 실행하는 시점의 결과는 다를 수 있다.

---

• 여성 사용자의 경우, 위 쿼리로 2번 실행해도 되지만, 그러면 데이터셋에 2번 접근하면서 그만큼 낭비하게 된다. 이때는 WHERE절 대신 GROUP BY 절을 사용하면 된다.
• 결과와 같이, 집계 함수는 ORDER BY 절에 지정한 그룹별로 적용된다. 이때 SELECT 표현식에 그룹을 구분하는 컬럼 (성별)의 값과 집계(AVG) 값을 표함하는 것도 가능하다. 실제 데이터셋에는 성별을 나타내는 값이 male, female, unknown 3가지라는 점을 유의할 것.

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

---

■ COUNT로 레코드 수 세기

• 위 쿼리에서 계산한 평균값에 몇 건의 대여 기록이 포함되어 잇는지 궁금하다면 COUNT( ) 함수를 사용하면 된다.

SELECT
  gender,
  COUNT(*) AS rides,
  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으로 그룹화된 항목 필터링하기

• HAVING 절을 사용하면 그룹화 연산 이후에 필터링을 할 수 있다. 평균 14분 이상 대여한 성별을 알고 싶다면 다음 쿼리를 사용한다.
• WHERE 절에서 성별 또는 대여 기간을 필터링할 수 있지만, 평균 기간은 그룹화한 후 계산되므로 WHERE 절에서 평균 기간으로 필터링할 수 없다.

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

---

■ DISTINCT로 고윳값 찾기

• 데이터셋의 성별 컬럼에 어떤 값이 저장되어 있는지 호막인하고 싶으면 GROUP BY를 사용할 수도 있지만 컬럼의 고윳값을 추출하고 싶으면 간단하게 SELECT DISTINCT를 사용한다.

SELECT DISTINCT 
  gender
FROM
  `bigquery-public-data`.new_york_citibike.citibike_trips

---

SELECT
  bikeid,
  tripduration,
  gender
FROM
  `bigquery-public-data`.new_york_citibike.citibike_trips
WHERE
  gender = ""
LIMIT
  100

• 쿼리 결과를 확인하면 성별 값이 누락되거나 저품질 데이터가 존재하는 것같다.
• WHERE 절에서 NULL 값을 필터링할 때 NULL 값에 대한 비교 연산 (=, !=, <, >)은 NULL 값을 반환하므로 WHERE 조건으로 적절하지 않다. 대신 IS NULL 또는 IS NOT NULL을 사용하면 된다.

---

• 성별의 고윳값을 구하기 위해 DISTINCT를 사용했던 쿼리를 다시 확인해 보면, DISTINCT는 성별 컬럼뿐만 아니라 SELECT 절에서 지정한 컬럼 전체에 적용된다.

SELECT DISTINCT
  gender,
  usertype
FROM
  `bigquery-public-data`.new_york_citibike.citibike_trips
WHERE
  gender != ''

• 쿼리의 결과는 6행이 반환된다. 즉 데이터셋에 존재하는 고유한 성별과 고유한 사용자 유형을 토대로 가능한 모든 조합에 해당하는 결과를 얻게 된다.