SQL Multi-table join (Using Lyft Trip Data)

이번 글에서는 Lyft의  trips, riders, cars 등의 테이블을 활용해 SQL의 JOIN, UNION, 집계 함수(AVG, COUNT), 및 정렬(ORDER BY) 기능을 실습해보겠다. 이를 통해 실무에서 데이터 분석을 할 때 어떻게 SQL을 활용할 수 있는지 살펴보자.

SELECT * FROM trips;
id	date	pickup	dropoff	rider_id	car_id	type	cost
1001	2017-12-05	06:45	07:10	102	1	X	28.66
1002	2017-12-05	08:00	08:15	101	3	POOL	9.11
1003	2017-12-05	09:30	09:50	104	4	X	24.98
1004	2017-12-05	13:40	14:05	105	1	X	31.27
1005	2017-12-05	15:15	16:00	103	2	POOL	18.95
1006	2017-12-05	18:20	18:55	101	3	XL	78.52

SELECT * FROM riders;
id	first	last	username	rating	total_trips	referred
101	Sonny	Li	@sonnynomnom	4.66	352	
102	Laura	Breiman	@lauracle	4.99	687	101
103	Kassa	Korley	@kassablanca	4.63	42	
104	Yakov	Kagan	@yakovkagan	4.52	1910	103

SELECT * FROM cars;
id	model	OS	status	trips_completed
1	Ada	Ryzac	active	82
2	Ada	Ryzac	active	30
3	Turing XL	Ryzac	active	164
4	Akira	Finux	maintenance	22

 

 

riders와 cars 테이블 간 CROSS JOIN 수행

SELECT riders.first,
   riders.last,
   cars.model
FROM riders, cars;

 

설명

• CROSS JOIN(교차 조인)은 두 테이블의 모든 가능한 조합을 반환한다.
• riders 테이블의 모든 행과 cars 테이블의 모든 행이 조합되므로, 결과 행의 개수는 riders의 행 수 × cars의 행 수가 된다.
• 만약 riders에 5개의 행, cars에 3개의 행이 있다면 결과는 5 × 3 = 15개의 행이 된다.
• 일반적으로 CROSS JOIN은 모든 가능한 조합을 생성할 때 사용되며, 필터링 조건이 없으면 결과가 매우 많아질 수 있다.

trips와 riders 테이블을 LEFT JOIN으로 연결

SELECT *
FROM trips
LEFT JOIN riders
  ON trips.rider_id = riders.id;

 

설명

• trips 테이블을 왼쪽(기준) 테이블로 설정하고, riders 테이블과 rider_id를 기준으로 조인한다.
• LEFT JOIN은 trips 테이블의 모든 데이터를 유지하면서, riders 테이블에 해당하는 정보가 있으면 결합한다.
• riders에 일치하는 rider_id가 없는 경우 NULL 값이 들어간다.

 

각 여행(trip)에서 사용된 차량 정보 추가

SELECT *
FROM trips
JOIN cars
  ON trips.car_id = cars.id;

 

설명

• INNER JOIN을 사용하여 trips.car_id와 cars.id가 일치하는 경우에만 데이터를 가져온다.
• INNER JOIN은 두 테이블에서 일치하는 데이터만 반환하며, 매칭되지 않는 데이터는 제외된다.
• 예를 들어, trips 테이블에 car_id가 있지만 cars 테이블에 해당 id가 없으면 해당 trip은 결과에 포함되지 않는다.

 

기존 riders 테이블과 새로운 riders2 테이블 병합

SELECT *
FROM riders
UNION
SELECT *
FROM riders2;

설명

• UNION을 사용하면 두 개의 테이블을 세로로 병합할 수 있다.
• 중복되는 데이터는 자동으로 제거된다.
• 중요: riders와 riders2의 컬럼 개수와 데이터 타입이 동일해야 한다.

 

모든 여행의 평균 비용 구하기

SELECT AVG(cost) AS 'Average Cost'
FROM trips;

설명

• AVG(cost)는 모든 trips.cost의 평균값을 계산한다.
• AS 'Average Cost'는 결과 컬럼명을 Average Cost로 지정한다.

 

라이드를 500번 미만으로 이용한 사용자를 찾기

SELECT *
FROM riders
WHERE total_trips < 500;

설명

• WHERE total_trips < 500을 사용하여 total_trips 값이 500 미만인 사용자를 필터링한다.
• 만약 riders2도 포함하려면 UNION을 사용할 수 있다.

 

활성 상태(active)인 차량 수 계산 (COUNT)

SELECT COUNT(*)
FROM cars
WHERE status = 'active';

설명

• COUNT(*)는 cars 테이블에서 총 개수를 계산한다.
• WHERE status = 'active'를 추가하여 활성화된(active) 차량만 카운트한다.

 

가장 많은 여행을 완료한 차량 2개 조회

SELECT *
FROM cars
ORDER BY trips_completed DESC
LIMIT 2;

설명

• ORDER BY trips_completed DESC를 사용해 trips_completed 기준으로 내림차순 정렬한다.
• LIMIT 2를 사용하여 상위 2개의 결과만 가져온다.

 

핵심 정리

1. CROSS JOIN을 사용하여 모든 조합을 조회
2. LEFT JOIN을 사용해 trips와 riders 데이터를 연결
3. INNER JOIN을 사용해 trips와 cars 데이터를 결합
4. UNION을 사용해 riders와 riders2 데이터를 합치기
5. AVG()를 사용해 평균 여행 비용 계산
6. WHERE를 사용해 특정 조건을 만족하는 사용자 찾기
7. COUNT()를 사용해 활성 차량 수 계산
8. ORDER BY + LIMIT을 사용해 가장 많이 이용된 차량 찾기