[Big Query] BigQuery개념 정리, SQL 기초 체력 키우기(실습 5문제)

학습 내용

---

SQL 심화 콘텐츠는 총 4개의 노드로 구성되어 있다.

1. ✅  빅데이터 톺아보기
   • 빅데이터를 지탱하는 툴과 기술
   • 빅쿼리(BigQuery) 실습 환경 설정하기

https://jpocket.tistory.com/29

[Big Query] 빅쿼리 시작하기

 

2. ✅ SQL 기초 체력 키우기

• 주문 내역에서 VIP 고객 찾기
• 레스토랑 손님 중 대식가 찾기
• 우리 사이트 베스트 상품 추천!
• 베스트 리뷰어에게 경품을 주자
• 다음 환자분, 진료실로 들어오세요

3. 실무에서 많이 쓰이는 고급 SQL 기술

• 윈도우 함수(Window Function) (1): 함수 구조와 순위 함수
• 윈도우 함수(Window Function) (2): 집계 함수
• 그룹함수
• 복잡한 데이터 처리(JSON Formatting)

4. SQL로 데이터 분석하기 [프로젝트]

• 회사의 일별 매출액 구하기
• PV, UV 계산하기
• 쇼핑몰 일별 매출과 ARPPU 구하기
• 퍼널 분석: 사람들은 어디에서 이탈하고 있을까?
• 리텐션 분석: 내가 VIP가 될 상인가?
• RFM 분석

 

 

🛠 BigQuery

BigQuery에 대해 간단히 개념 정리하자면,

• 빅쿼리는 빅데이터를 분석하고 관리하는 클라우드 기반 데이터 웨어하우스이다.
• 구글 클라우드 플랫폼(GCP)에서 사용할 수 있다.
• 클라우드 서비스이므로 인터넷만 연결된다면 어디서든 사용 가능하다.
• 데이터 센터에 복제본이 분산되어 저장되기 때문에 유실 위험이 적고,
• 방대한 양의 데이터 처리 속도가 빠르다는 장점이 있다.

 

🛠 스몰 데이터란?

• 스몰 데이터는 대용량 데이터 또는 빅데이터와 대조되는 개념으로, 상대적으로 작은 규모의 데이터 셋을 가리킨다.
• 대량으로 들어오는 데이터가 아닌 고객에 대한 사소한 정보까지 담을 수 있는 데이터로, 획기적인 아이디어가 탄생하고 브랜드를 변화시킬 수 있는 데이터라고도 표현한다.

스몰 데이터는 빅데이터와 대비되는 개념이며 잘 들여다 보았을 때 의외의 인사이트를 얻을 수 있는 데이터라는 점도 참고하면 좋을 것 같다.😊

 

 

🛠  Scale out, Scale up

서비스를 운영하면서 데이터는 점차 쌓여져가는데, 이에 따라 데이터베이스도 확장해야 한다.

확장하는 2가지 방법으로는,

 

1. 수평(적) 확장(성) (수평 스케일링, scale out)

2. 수직(적) 확장(성) (수직 스케일링, scale up)

 

Scale out : 새로운 서버 추가

업그데이드가 쉽고, 비용이 적게 든다.

서버에 부하가 되더라도 분산해서 처리하기 때문에 장애에 치명적이지 않다.

 

하지만, 버그를 찾거나 해결하기 어렵고, 데이터 센터 비용이 크게 증가할 수 있다는 단점이 있다.

보통은 웹 서비스에서 수평적 확장을 사용한다.

사용자가 급증하면 서버를 확장하고, 그렇지 않을 땐 서버를 줄이면 되기 때문이다. 

 

 

Scale in : 서버의 리소스, 용량, 스펙을 늘려 성능 향상

전통적으로 데이터베이스를 확장하는 방법이다.

확장 과정이 간단하고, 데이터 센터 비용이 크게 증가하지 않는다.

 

하지만, 확장할 때마다 더 많은 비용 지불, 서버 업그레이드 동안 시스템 사용이 어렵고,

서버에 부하가 집중될 경우 장애가 생길 수 있다는 단점이 있다.

따라서 수직적 확장은 데이터 일관성과 무결성이 중요한 데이터베이스 서버에 적합하다.

 

 

 

 

 

 

---

SQL 기초 체력 키우기

 

 

주문 내역에서 VIP 고객 찾기

---

 

총 주문 금액이 높은 상위 3명의 손님 리스트를 출력하라.

-- 총 주문 금액이 높은 상위 3명의 손님 리스트를 출력
-- 테이블 생성   
CREATE OR REPLACE TABLE `white-might-456101-t9.modulabs.ex1`(
   order_id STRING,
   user_id STRING,
   item_id STRING,
   price FLOAT64
);


-- 데이터 삽입
INSERT INTO `white-might-456101-t9.modulabs.ex1` (order_id, user_id, item_id, price)
VALUES
   ('order_001', 'customer_01', 'product_001', 100.0),
   ('order_001', 'customer_01', 'product_002', 150.0),
   ('order_002', 'customer_02', 'product_003', 200.0),
   ('order_003', 'customer_03', 'product_004', 80.0),
   ('order_004', 'customer_04', 'product_005', 220.0),
   ('order_004', 'customer_04', 'product_006', 90.0),
   ('order_005', 'customer_05', 'product_007', 140.0),
   ('order_006', 'customer_01', 'product_008', 110.0),
   ('order_007', 'customer_06', 'product_009', 300.0),
   ('order_008', 'customer_07', 'product_010', 130.0),
   ('order_009', 'customer_03', 'product_011', 250.0),
   ('order_010', 'customer_08', 'product_012', 90.0);


--  총 주문 금액이 높은 상위 3명의 손님 리스트를 출력
select user_id, sum(price) as total_spent from white-might-456101-t9.modulabs.ex1
group by user_id
order by total_spent desc
limit 3

 

How

주문 금액의 합계에서 내림차순하여 3개만 출력하면 상위 3명의 손님 리스트가 출력된다.

user_id로 그룹화하여, total_spent를 내림차순으로 정렬하여 3개만 출력한다.

컬럼명은 user_id, price의 합계에 대해서 total_spent 라고 지정한다.

 

 

 

 

 

 

레스토랑 손님 중 대식가 찾기

---

식사 금액이 전체 평균 식사 금액보다 많은 경우를 찾아내어, 특히 많은 양의 음식을 주문한 큰손 손님들을 식별하라.

 

 

-- 식사 금액이 전체 평균 식사 금액보다 많은 경우를 찾아내어, 특히 많은 양의 음식을 주문한 큰손 손님들을 식별
-- 테이블 생성   
CREATE OR REPLACE TABLE `white-might-456101-t9.modulabs.ex2` (
   table_id STRING,
   total_bill FLOAT64,
   tip FLOAT64,
   gender STRING,
   party_size INT64,
   day STRING,
   time STRING
);

-- 데이터 삽입
INSERT INTO white-might-456101-t9.modulabs.ex2 (table_id, total_bill, tip, gender, party_size, day, time)
VALUES
   ('T01', 24.59, 3.61, 'Female', 2, 'Sun', 'Dinner'),
   ('T02', 21.01, 3.50, 'Male', 3, 'Sun', 'Dinner'),
   ('T03', 23.68, 3.31, 'Male', 2, 'Sun', 'Dinner'),
   ('T04', 24.59, 3.61, 'Female', 4, 'Sun', 'Dinner'),
   ('T05', 25.29, 4.71, 'Male', 4, 'Sun', 'Dinner'),
   ('T06', 8.77, 2.00, 'Male', 2, 'Sun', 'Dinner'),
   ('T07', 26.88, 3.12, 'Male', 2, 'Sun', 'Dinner'),
   ('T08', 15.04, 1.96, 'Male', 2, 'Sun', 'Dinner'),
   ('T09', 14.78, 3.23, 'Male', 2, 'Sun', 'Dinner'),
   ('T10', 10.27, 1.71, 'Male', 2, 'Sun', 'Dinner'),
   ('T11', 35.26, 5.00, 'Female', 4, 'Sun', 'Dinner'),
   ('T12', 15.42, 1.57, 'Male', 2, 'Sun', 'Dinner');

-- 식사 금액이 전체 평균 식사 금액보다 많은 경우를 찾아내어, 특히 많은 양의 음식을 주문한 큰손 손님들을 식별
-- 서브쿼리
select *
from white-might-456101-t9.modulabs.ex2
where total_bill > ( select avg(total_bill) from `white-might-456101-t9.modulabs.ex2`)


-- WITH절
WITH AverageBill AS (
  SELECT AVG(total_bill) AS avg_bill
  FROM white-might-456101-t9.modulabs.ex2
)
SELECT *
FROM white-might-456101-t9.modulabs.ex2 ex2, AverageBill
WHERE ex2.total_bill > AverageBill.avg_bill;

 

How

WITH절로 풀면 avg_bill 컬럼명이 추가되는데 데이터 결과에 영향을 주는 건 아니지만 굳이 출력되어야 하는 건 아니라서

서브쿼리를 선택했다.

 

WITH절 방법에서, 메인쿼리의 from절에 AverageBill을 쓰지 않으면 전체 평균 식사 금액을 구한 것에 대한 값을 이용할 수 없게 되기에 컬럼 추가가 불가피하다. 🔥AverageBill.avg_bill 사용이 안 되기 때문에 from절에 AverageBill가 있어야 한다는 뜻이다. 

 

👉 실행 흐름은 다음과 같다.

테이블에서 total_bill 컬럼명의 평균을 구하고, 

total_bill의 평균을 구하는 서브쿼리에서, 전체 평균 식사 금액을 구한다.

메인쿼리에서는 total_bill이 전체 평균 식사 금액보다 더 많은 것에 대해 모든 컬럼명을 출력한다. 🚀

 

 

 

 

 

 

 

 

 

 

우리 사이트 베스트 상품 추천!

---

ex3 테이블을 사용하여 판매량 합계가 가장 많은 ‘제품 라인(Product Line) 을 찾아라.

-- 판매량 합계가 가장 많은 ‘제품 라인(Product Line)
-- 테이블 생성   
CREATE OR REPLACE TABLE `white-might-456101-t9.modulabs.ex3` (
   PRODUCT_ID INT64 NOT NULL,
   PRODUCT_LINE STRING NOT NULL,
   TOTAL_ORDER INT64 NOT NULL
);


-- 데이터 삽입
INSERT INTO `white-might-456101-t9.modulabs.ex3` (PRODUCT_ID, PRODUCT_LINE, TOTAL_ORDER)
VALUES
(101, 'Sneakers', 3200),
(102, 'Boots', 2500),
(103, 'Sandals', 1800),
(104, 'Running Shoes', 2100),
(105, 'Sneakers', 3000),
(106, 'Boots', 2700),
(107, 'Sandals', 1600),
(108, 'Running Shoes', 2200),
(109, 'Sneakers', 3100),
(110, 'Boots', 2600),
(111, 'Sandals', 1500),
(112, 'Running Shoes', 2000),
(113, 'Sneakers', 3300),
(114, 'Boots', 2400),
(115, 'Sandals', 1700),
(116, 'Running Shoes', 2300),
(117, 'Sneakers', 3400),
(118, 'Boots', 2800),
(119, 'Sandals', 1900),
(120, 'Running Shoes', 2500);


-- 판매량 합계가 가장 많은 ‘제품 라인(Product Line)
select PRODUCT_LINE, sum(TOTAL_ORDER) as total_orders from white-might-456101-t9.modulabs.ex3
group by product_line
order by total_orders desc
limit 1

 

how

1번 문제와 크게 다를 게 없는 문제였다.

가장 많은 = 상위 1개 이므로 limit으로 1을 주었다.

total_order의 합계를 total_orders 컬럼으로 지정하고, 이것을 product_line으로 그룹화하여 내림차순으로 정렬 후 1개만 출력한다. 

 

 

 

 

 

 

베스트 리뷰어에게 경품을 주자

---

가장 많은 리뷰를 작성한 member_id의 리뷰를 조회하라.

출력 결과

-- 가장 많은 리뷰를 작성한 member_id의 리뷰 조회
-- 테이블 생성   
CREATE OR REPLACE TABLE `white-might-456101-t9.modulabs.ex4_member` (
   MEMBER_ID STRING NOT NULL,
   MEMBER_NAME STRING NOT NULL,
   TLNO STRING,
   GENDER STRING,
   DATE_OF_BIRTH DATE
);
CREATE OR REPLACE TABLE `white-might-456101-t9.modulabs.ex4_review` (
   REVIEW_ID STRING NOT NULL,
   REST_ID STRING,
   MEMBER_ID STRING,
   REVIEW_SCORE INT64,
   REVIEW_TEXT STRING,
   REVIEW_DATE DATE
);
-- 데이터 삽입
INSERT INTO `white-might-456101-t9.modulabs.ex4_member` (MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH)
VALUES
('kevin@gmail.com', 'Kevin', '01076432111', 'M', '1992-02-12'),
('james@gmail.com', 'James', '01032324117', 'M', '1992-02-22'),
('alice@gmail.com', 'Alice', '01023258688', 'W', '1993-02-23'),
('maria@gmail.com', 'Maria', '01076482209', 'W', '1993-03-16'),
('duke@gmail.com', 'Duke', '01017626711', 'M', '1990-11-30');


INSERT INTO `white-might-456101-t9.modulabs.ex4_review` (REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT, REVIEW_DATE)
VALUES
('R000000065', '00028', 'alice@gmail.com', 5, 'The broth for the shabu-shabu was clean and tasty', '2022-04-12'),
('R000000066', '00039', 'duke@gmail.com', 5, 'The kimchi stew was the best', '2022-02-12'),
('R000000067', '00028', 'duke@gmail.com', 5, 'Loved the generous amount of ham', '2022-02-22'),
('R000000068', '00035', 'kevin@gmail.com', 5, 'The aged sashimi was fantastic', '2022-02-15'),
('R000000069', '00035', 'maria@gmail.com', 4, 'No fishy smell at all', '2022-04-16'),
('R000000070', '00040', 'kevin@gmail.com', 4, 'Cozy atmosphere and great experience', '2022-05-10'),
('R000000071', '00041', 'kevin@gmail.com', 5, 'Top-notch service and taste', '2022-05-12'),
('R000000072', '00042', 'kevin@gmail.com', 3, 'Average taste but friendly staff', '2022-05-14'),
('R000000073', '00043', 'james@gmail.com', 5, 'Both the taste and service were satisfying', '2022-05-15'),
('R000000074', '00044', 'alice@gmail.com', 4, 'The ingredients were fresh', '2022-05-16');


-- member_id별로 리뷰 개수를 조회
with review_count as (
   select member_id, count(*) as review_cnt
   from white-might-456101-t9.modulabs.ex4_review
   group by member_id
), 

-- 가장 많은 리뷰를 작성한 member_id를 조회
top_reviewer as (
   select member_id
   from review_count
   order by review_cnt desc
   limit 1
)

select m.member_name, r.review_text, r.review_date
from white-might-456101-t9.modulabs.ex4_review as r
join white-might-456101-t9.modulabs.ex4_member as m
on m.member_id = r.member_id
join top_reviewer on r.member_id = top_reviewer.member_id
order by r.review_date asc, r.review_text asc

 

how

member_id별로 리뷰 개수를 조회하는  review_count 테이블을 생성한다.

review_count에서 가장 많은 리뷰를 작성한 member_id를 뽑아 ex4.member 테이블과 조인하여 member_name을 얻는다.

ex4.review 테이블과 조인하여 review_text, review_date를 가져온다.

 

실행 흐름 과정은 다음과 같다.

member_id별로 리뷰 개수를 조회하는 테이블이 review_count이다.

새로 만들어진 review_count테이블에서 가장 많은 리뷰를 작성한 member_id를 뽑는다.

member_name은 ex4.member 테이블에서 가져올 수 있기 때문에 join해야한다.

top_reviewer에 있는 member_id는 실제 리뷰 내용이 있는 ex4.review 테이블과 조인하여 review_text, review_date를 가져온다.

 

 

 

 

 

 

 

 

다음 환자분, 진료실로 들어오세요

---

아직 진료 완료(TREATMENT_STATUS = 'Completed')가 되지 않은 예약 건들에 대하여 '취소 되지 않은 다음 진료 예약' 의 예약자명을 조회하라.

-- 아직 진료 완료(TREATMENT_STATUS = 'Completed')가 되지 않은 예약 건들에 대하여 '취소 되지 않은 다음 진료 예약' 의 예약자명

-- 테이블 생성  
CREATE OR REPLACE TABLE `white-might-456101-t9.modulabs.ex5_patient` (
   PATIENT_NO STRING,
   PATIENT_NAME STRING,
   GENDER STRING,
   AGE INT64
);
CREATE OR REPLACE TABLE `white-might-456101-t9.modulabs.ex5_apnt` (
   APNT_YMD TIMESTAMP,
   APNT_NO INT64,
   PATIENT_NO STRING,
   APNT_CANCEL_YN STRING,
   TREATMENT_STATUS STRING
);
-- 데이터 삽입
INSERT INTO `white-might-456101-t9.modulabs.ex5_patient` (PATIENT_NO, PATIENT_NAME, GENDER, AGE) VALUES
('PT22000024', '영희', 'W', 30),
('PT22000035', '철수', 'M', 45),
('PT22000046', '은지', 'W', 20),
('PT22000057', '준호', 'M', 35),
('PT22000068', '수민', 'W', 28),
('PT22000079', '현준', 'M', 52),
('PT22000080', '서연', 'W', 22),
('PT22000091', '지후', 'M', 40),
('PT22000102', '민서', 'W', 33),
('PT22000113', '예준', 'M', 47);


INSERT INTO `white-might-456101-t9.modulabs.ex5_apnt` (APNT_YMD, APNT_NO, PATIENT_NO, APNT_CANCEL_YN, TREATMENT_STATUS) VALUES
(TIMESTAMP '2024-01-01 09:00:00', 49, 'PT22000068', 'Y', 'Completed'),
(TIMESTAMP '2024-01-01 09:30:00', 44, 'PT22000024', 'N', 'Completed'),
(TIMESTAMP '2024-01-01 10:00:00', 50, 'PT22000079', 'N', 'Completed'),
(TIMESTAMP '2024-01-01 10:30:00', 45, 'PT22000035', 'N', ''),
(TIMESTAMP '2024-01-01 11:00:00', 51, 'PT22000080', 'N', ''),
(TIMESTAMP '2024-01-01 11:30:00', 47, 'PT22000046', 'N', ''),
(TIMESTAMP '2024-01-01 13:00:00', 52, 'PT22000091', 'N', ''),
(TIMESTAMP '2024-01-01 14:30:00', 48, 'PT22000057', 'N', ''),
(TIMESTAMP '2024-01-01 15:00:00', 53, 'PT22000102', 'N', ''),
(TIMESTAMP '2024-01-01 16:00:00', 54, 'PT22000113', 'Y', '');


-- 1번 코드
-- 완료되지 않은 예약건들
with not_completed as (
select *
from `white-might-456101-t9.modulabs.ex5_apnt`
where TREATMENT_STATUS != 'Completed'
),
-- 취소되지 않은 건들
not_cancelled as (
select *
from `white-might-456101-t9.modulabs.ex5_apnt`
where APNT_CANCEL_YN = 'N'
)
select `white-might-456101-t9.modulabs.ex5_patient`.PATIENT_NAME
from not_completed as nc
join not_cancelled as nc2 on nc.patient_no = nc2.patient_no
join `white-might-456101-t9.modulabs.ex5_patient` on nc.patient_no = `white-might-456101-t9.modulabs.ex5_patient`.patient_no
order by nc.apnt_ymd asc
limit 1





-- 2번 코드
SELECT patient.patient_name
FROM `white-might-456101-t9.modulabs.ex5_apnt` AS apnt
JOIN `white-might-456101-t9.modulabs.ex5_patient` AS patient 
  ON apnt.patient_no = patient.patient_no
WHERE apnt.TREATMENT_STATUS != 'Completed' 
  AND apnt.APNT_CANCEL_YN = 'N'
ORDER BY apnt.apnt_ymd ASC
LIMIT 1;

 

how

1번 코드)

WITH문을 사용하였다.

아직 진료 완료(TREATMENT_STATUS = 'Completed')가 되지 않은 예약 건들을 먼저 구하고,  '취소 되지 않은 다음 진료 예약'을 구한다.

예약자명은 patient테이블에 있으므로 join해야 한다.

나온 결과에 대해서 날짜를 오름차순으로 정렬했을 때 가장 처음에 나오는 데이터가 원하는 결과다.

 

🔎 2번 코드)

예시 답안을 보고 생각해 낸 코드다. 💡

treatment_status와 apnt_cancel_yn 컬럼이 모두 같은 테이블에 있기 때문에 굳이 join을 2번 할 필요가 없다. ❌

(나 왜 WITH문으로 풀었지)

그리고 where 절에 준 조건들을 where 1=1 을 이용하면 다음과 같이 코드를 줄일 수 있다.

 

 

답안코드)

SELECT p.PATIENT_NAME
FROM project_name.dataset_name.ex5_apnt a
JOIN project_name.dataset_name.ex5_patient p ON a.PATIENT_NO = p.PATIENT_NO
WHERE 1=1
  AND a.APNT_CANCEL_YN = 'N'
  AND a.TREATMENT_STATUS != 'Completed'
ORDER BY a.APNT_YMD
LIMIT 1;

 

🔥where 1=1 은 항상 참인 조건이다. 즉 조건 자체의 필터링에는 영향을 주지 않는다.

조건들을 and 로 일관되게 이어붙일 수 있다.

쿼리의 조건들을 깔끔하게 추가하거나 수정하기 쉽게 하기 위함이다.