데이터 웨어하우스 스키마는 종종 이벤트를 포함한 기본 팩트 테이블과 느리게 변경되는 정보를 포함하는 위성 테이블(여러 곳에 나누어 저장된 테이블)인 차원 테이블에 의존한다. 예를 들어 소매 스키마는 '판매' 테이블을 팩트 테이블로, '제품', '고객' 테이블을 차원 테이블로 구성할 수 있다. 이 유형의 스키마를 사용하는 대부분의 쿼리는 특정 고객이 구입한 모든 제품의 이름을 반환하는 등 조인 작업이 필요하다.
빅쿼리는 이너 조인, 아우터 조인, 크로스 조인, 안티 조인, 세미 조인, 안티 세미 조인 등 관계 대수의 모든 공통 조인을 지원한다.
빅쿼리는 거의 모든 크기의 테이블을 효율적으로 조인할 수 있다.
■ 조인의 작동 원리
WITH bicycle_rentals AS(
SELECT
COUNT (starttime) AS num_trips,
EXTRACT (DATE FROM starttime) AS trip_date
FROM `bigquery-public-data`.new_york_citibike.citibike_trips
GROUP BY trip_date
),
rainy_days AS
(
SELECT
date,
(MAX(prcp) > 5) AS rainy
FROM (
SELECT
wx.date AS date,
IF(wx.element = 'PRCP', wx.value/10, NULL) AS prcp
FROM
`bigquery-public-data`.ghcn_d.ghcnd_2016 AS wx
WHERE
wx.id = 'USW00094728'
)
GROUP BY
date
)
SELECT
ROUND(AVG(bk.num_trips)) AS num_trips,
wx.rainy
FROM bicycle_rentals AS bk
JOIN rainy_days AS wx
ON wx.date = bk.trip_date
GROUP BY wx.rainy
첫 번째 WITH 절은 citibike_trips 테이블에서 일별 대여 건수를 가져와 bicyle_rentals라는 이름의 from_item을 생성한다. 이 from_item은 테이블은 아니지만 테이블처럼 사용할 수 있다. 그래서 from_item이라 부른다. 두 번째 from_item인 raiany_days는 지구 기후 네트워크에서 매일 수집한 데이터를 쿼리한다. 이 from_item은 뉴욕의 기상청 USW00094728가 제공한 데이터로부터 적어도 5mm의 강수량이 관측된 날은 비가 온 날로 표시한다.
이제 2개의 from_item이 준비되었다. 개별적으로 코드를 보면 다음과 같다.
bicycle_rentals from_item의 데이터
WITH bicycle_rentals AS(
SELECT
COUNT (starttime) AS num_trips,
EXTRACT (DATE FROM starttime) AS trip_date
FROM `bigquery-public-data`.new_york_citibike.citibike_trips
GROUP BY trip_date
)
SELECT * FROM bicycle_rentals LIMIT 5
rainy_days from_item의 데이터
SELECT
date,
(MAX(prcp) > 5) AS rainy
FROM (
SELECT
wx.date AS date,
IF(wx.element = 'PRCP', wx.value/10, NULL) AS prcp
FROM
`bigquery-public-data`.ghcn_d.ghcnd_2016 AS wx
WHERE
wx.id = 'USW00094728'
)
GROUP BY
date
이제 두 from_item의 trip_date 컬럼 값이 일치하는 조인 조건을 사용해 다음과 같이 두 결과 셋을 조인할 수 있다.
WITH bicycle_rentals AS(
SELECT
COUNT (starttime) AS num_trips,
EXTRACT (DATE FROM starttime) AS trip_date
FROM `bigquery-public-data`.new_york_citibike.citibike_trips
GROUP BY trip_date
),
rainy_days AS(
SELECT
date,
(MAX(prcp) > 5) AS rainy
FROM (
SELECT
wx.date AS date,
IF(wx.element = 'PRCP', wx.value/10, NULL) AS prcp
FROM
`bigquery-public-data`.ghcn_d.ghcnd_2016 AS wx
WHERE
wx.id = 'USW00094728'
)
GROUP BY
date
)
SELECT
bk.trip_date,
bk.num_trips,
wx.rainy
FROM bicycle_rentals AS bk
JOIN rainy_days AS wx
ON wx.date = bk.trip_date
LIMIT 5
이 테이블로부터 비가 오는 날과 비가 오는 날의 평균 대여 횟수를 찾는 것은 간단하다. 앞에서 설명한 것을 이너 조인이라고 하며, 조인 유형을 지정하지 않으면 기본적으로 사용되는 유형이다.
조인의 작동 방식은 다음과 같다.
from_item을 2개 만든다. 2개의 테이블, 서브 쿼리, 배열 또는 SELECT 할 수 있는 WITH 문 등 어떤 것이든 from_item이 될 수 있다.
조인 조건을 결정한다. 조인 조건은 반드시 동등 조건일 필요는 없다. 2개의 from_item을 사용하는 불리언 조건이라면 조인 조건으로 사용할 수 있다.
원하는 컬럼을 선택한다. from_item에 같은 이름의 컬럼이 있으면 별칭을 사용해 어떤 from_item의 컬럼을 읽을 것인지 명확하게 지정한다.
이너 조인을 사용하지 않으면 원하는 조인 유형을 지정한다.
조인을 하려면 from_item을 작성할 때 사용하는 모든 데이터셋이 동일한 빅쿼리 리전region에 있어야 한다. (빅쿼리의 모든 공개 데이터셋은 미국 region에 있다.)
■ 이너 조인
테이블을 조인하는 방법은 여러 가지가 있다.
그중 앞의 예제에서 기본값으로 사용했던 조인은 이너 조인으로, 값을 선택할 수 있는 공통 행의 집합을 생성한다.
WITH from_item_a AS(
SELECT 'Dalles' AS city, 'OR' AS state
UNION ALL SELECT 'Tokyo', 'Tokyo'
UNION ALL SELECT 'Mumbai', 'MAharashtra'
),
from_item_b AS(
SELECT 'OR' AS state,'USA' AS country
UNION ALL SELECT 'Tokyo', 'Japan'
UNION ALL SELECT 'MAharashtra', 'India'
)
SELECT from_item_a.*, country
FROM from_item_a
JOIN from_item_b
ON from_item_a.state = from_item_b.state
첫 번째 from_item에는 도시 목록이 있고, 두 번째 from_item에는 각 주가 속한 국가가 있다. 이 두 from_item을 조인하면 3개의 컬럼이 있는 데이터셋이 생성된다.
조인 조건에 동등성 검사(equality check)를 할 필요는 없다. 빅쿼리는 조인을 효율적으로 실행할 수 없을 때 오류를 반환하므로 가능한 한 동등 조건을 사용하는 것이 좋지만, 기본적으로는 어떤 불리언 조건도 사용할 수 있다.
예를 들어 한 국가에서 다른 국가로 배송할 때 추가 요금이 부과되는 비즈니스 규칙이 있다고 가정해 보자. 지정된 위치에서 추가 요금이 부과되는 국가 목록을 얻으려면 다음과 같이 지정하면 된다.
WITH from_item_a AS(
SELECT 'Dalles' AS city, 'OR' AS state
UNION ALL SELECT 'Tokyo', 'Tokyo'
UNION ALL SELECT 'Mumbai', 'MAharashtra'
),
from_item_b AS(
SELECT 'OR' AS state,'USA' AS country
UNION ALL SELECT 'Tokyo', 'Japan'
UNION ALL SELECT 'MAharashtra', 'India'
)
SELECT from_item_a.*, country AS surcharge
FROM from_item_a
JOIN from_item_b
ON from_item_a.state != from_item_b.state
조인 조건이 충족될 때마다 행이 나타난다. 조인 조건인 주state가 서로 다른 행이 2개 있으므로 from_item_a에 있는 행마다 2개의 행을 얻게 된다. 조인 조건과 일치하지 않는 행의 데이터는 출력되지 않는다.
■ 크로스 조인
크로스 조인 또는 곱집합은 조인 조건이 없다. 즉 2개의 from_item의 모든 행이 결합된다. 이 조인은 INNER JOIN의 조인 조건이 항상 참으로 평가되면 얻을 수 있는 조인이다.
예를 들어 토너먼트에서 각 경기의 우승자를 저장하는 테이블과 각 경기의 선물에 대한 테이블을 가지고 있다고 가정해 보자. 각 이벤트의 우승자에게 줄 선물을 쿼리하려면 다음과 같이 INNER JOIN을 사용해야 한다.
WITH winners AS(
SELECT 'John' AS person, '100m' AS event
UNION ALL SELECT 'Hiroshi', '200m'
UNION ALL SELECT 'Sita', '400m'
),
gifts AS(
SELECT 'Google Home' AS gift, '100m' AS event
UNION ALL SELECT 'Google Hub', '200m'
UNION ALL SELECT 'Pixel3', '400m'
)
SELECT winners.*,gift
FROM winners
JOIN gifts
USING (event)
반면에 수상자 모두에게 모든 선물을 주려면 (세 개의 선물을 모두 주는 경우) 다음과 같이 CROSS JOIN을 사용하면 된다.
WITH winners AS(
SELECT 'John' AS person, '100m' AS event
UNION ALL SELECT 'Hiroshi', '200m'
UNION ALL SELECT 'Sita', '400m'
),
gifts AS(
SELECT 'Google Home' AS gift, '100m' AS event
UNION ALL SELECT 'Google Hub', '200m'
UNION ALL SELECT 'Pixel3', '400m'
)
SELECT winners.*,gift
FROM winners
CROSS JOIN gifts
살펴본 쿼리는 CROSS JOIN 문을 명시적으로 사용했다.
SELECT from_item_a.*, from_item_b.*
FROM from_item_a
CROSS JOIN from_item_b
하지만 다음과 같이 쉼표를 이용해 더 간단하게 작성할 수 있다.
SELECT from_item.a*, from_item_b.*
FROM from_item_a, from_item_b
이런 이유로 크로스 조인은 쉼표 크로스 조인이라고도 한다.
■ 아우터 조인
선물이 없는 이벤트와 토너먼트에서 일어나지 않은 이벤트에 대한 선물이 있다고 가정해 보자.
WITH winners AS(
SELECT 'John' AS person, '100m' AS event
UNION ALL SELECT 'Hiroshi', '200m'
UNION ALL SELECT 'Sita', '400m'
UNION ALL SELECT 'Kwame', '50m'
),
gifts AS(
SELECT 'Google Home' AS gift, '100m' AS event
UNION ALL SELECT 'Google Hub', '200m'
UNION ALL SELECT 'Pixel3', '400m'
UNION ALL SELECT 'Google Mini', '5000m'
)
SELECT winners.*,gift
FROM winners
INNER JOIN gifts ON
winners.event = gifts.event
이너 조인을 실행하면 50m 경주 우승자는 선물을 받지 못한다.
5000n 경주의 선물은 우숭즈가 없으므로 선물이 지급되지 않는다.
CROSS JOIN을 실행하면 모든 우승자가 모든 선물을 받지만, OUTER JOIN은 조인 조건이 충족되지 않을 때 발생하는 상황을 제어한다.
