02-03) SQL심화-01

01. 빅쿼리(BigQuery) 소개

• 구글 클라우드 플랫폼(Google Cloud Platform, GCP)에서 사용 가능
• 빅쿼리는 클라우드 서비스이므로 서버 유지, 보수 등에 대한 염려 없이
  인터넷만 연결되어 있으면 어디서나 사용 가능(온프레미스와 다른 점)
• 관계형 데이터베이스에서 사용하는 SQL 언어를 그대로 사용
• 빅쿼리 vs SQL(MySQL/Oracle/PostgreSQL)

항목	BigQuery	MySQL / Oracle / PostgreSQL
종류	🌐 클라우드 기반 데이터 웨어하우스	💻 로컬 또는 서버 기반 관계형 DBMS
운영 환경	Google Cloud Platform (GCP)	로컬서버, 클라우드 (AWS, Azure 등) 다양
사용 목적	초대용량 데이터 분석 (TB~PB 단위)	OLTP (온라인 트랜잭션), 실시간 CRUD용
확장성	완전 관리형, 무제한 확장	서버 성능/디스크 한계 존재
속도/최적화	분산처리로 빠름, 하지만 내부 최적화 어려움	인덱스/조인 최적화로 빠름
과금 방식	쿼리당 데이터 스캔량(GB 기준)으로 과금	서버 호스팅비/라이선스비 (또는 무료 오픈소스)
SQL 문법	ANSI SQL 기반 + 약간 GCP 특화 기능 추가	ANSI SQL + 각 DB마다 약간 다른 확장 문법 사용
기능 차이	ML 모델 훈련, 외부 데이터 연결 (Cloud Storage 등)	정규화, 트랜잭션, 저장 프로시저 등 강력한 RDB 기능

• 기업 속성별 bigquery 적용 여부
  • 대기업의 경우 온프레미스 환경에서 운영(과금 문제)

구분	선택 예시
대기업 온프레미스	Oracle, Tibero, MS SQL
클라우드 분석	BigQuery (분석용), Snowflake, Redshift
스타트업/중소	BigQuery or Firebase DB (GCP 연계)

02. 수평 확장 vs 수직 확장

• 수평 확장
  • 기존의 데이터베이스에 새로운 서버를 추가
  • 수평적 확장은 업그레이드가 쉽고 구현이 간단
• 수직 확장
  • 기존에 존재하던 서버의 리소스, 용량, 스펙을 늘려 성능을 향상
  • 수직적 확장은 확장 과정이 간단하고 데이터 센터 비용이 크게 증가하지 않음
  • 확장할 때마다 더 많은 비용을 지불해야 하고, 업그레이드할 수 있는 양에 제한이 있음

03. SQL복습(BigQuery 내)

• BigQuery에서 연습한 결과
• (Q1) 총 주문 금액 높은 상위 3명 추출
• 문제 데이터

-- 테이블 생성   
CREATE OR REPLACE TABLE `project_name.dataset_name.ex1`(
   order_id STRING,
   user_id STRING,
   item_id STRING,
   price FLOAT64
);

-- 데이터 삽입
INSERT INTO `project_name.dataset_name.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);
--BigQuery 환경

• 풀이과정

SELECT user-id, sum(price) as total_spent
FROM oceanic-antler-456102-i6.modulabs.ex1
GROUP BY user-id
ORDER BY total_spent DESC
LIMIT 3

• (Q2) 식사 금액이 전체 평균 식사 금액보다 많은 경우 찾기
• 문제 데이터

-- 테이블 생성   
CREATE OR REPLACE TABLE `project_name.dataset_name.ex2` (
   table_id STRING,
   total_bill FLOAT64,
   tip FLOAT64,
   gender STRING,
   party_size INT64,
   day STRING,
   time STRING
);
-- 데이터 삽입
INSERT INTO project_name.dataset_name.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');

• 풀이과정(강사님 모범 답안과 다르지만 그냥 내 풀이를 올림)
• 재밌는 점은 나도 현업 때는 강사님처럼 with로 먼저 임시 테이블
  정의하고 풀었던 적이 많았다는 것

select *
from oceanic-antler-456102-i6.modulabs.ex2
where total_bill >= (select avg(total_bill) from oceanic-antler-456102-i6.modulabs.ex2)

• (Q3) 판매량 합계가 가장 많은 제품
• 문제 데이터

-- 테이블 생성   
CREATE OR REPLACE TABLE `project_name.dataset_name.ex3` (
   PRODUCT_ID INT64 NOT NULL,
   PRODUCT_LINE STRING NOT NULL,
   TOTAL_ORDER INT64 NOT NULL
);
-- 데이터 삽입
INSERT INTO `project_name.dataset_name.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);

• 풀이 과정

SELECT product_line, sum(total_order) as line_sum
from oceanic-antler-456102-i6.modulabs.ex3
group by product_line
order by product_line desc
limit 1

• (Q4) ex4_member 테이블과 ex4_review 테이블을 사용하여 리뷰를
  가장 많이 남긴 회원의 리뷰를 조회
• 문제 데이터

-- 테이블 생성   
CREATE OR REPLACE TABLE `project_name.dataset_name.ex4_member` (
   MEMBER_ID STRING NOT NULL,
   MEMBER_NAME STRING NOT NULL,
   TLNO STRING,
   GENDER STRING,
   DATE_OF_BIRTH DATE
);
CREATE OR REPLACE TABLE `project_name.dataset_name.ex4_review` (
   REVIEW_ID STRING NOT NULL,
   REST_ID STRING,
   MEMBER_ID STRING,
   REVIEW_SCORE INT64,
   REVIEW_TEXT STRING,
   REVIEW_DATE DATE
);
-- 데이터 삽입
INSERT INTO `project_name.dataset_name.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 `project_name.dataset_name.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');

• 문제 풀이

WITH ReviewCount as(
SELECT 
member_id,
count(*) as NumberOfReviews
FROM oceanic-antler-456102-i6.modulabs.ex4_member
GROUP BY member_id
Order BY NumberOfReviews DESC
LIMIT 1
) -- 일단 가장 review count 많은 경우 출력
SELECT  
T0.member_name,
T1.review_text,
T1.review_date
from oceanic-antler-456102-i6.modulabs.ex4_member T0
join oceanic-antler-456102-i6.modulabs.ex4_review T1
on T0.member_id = T1.member_id
join ReviewCount
on ReviewCount.member_id = T1.member_id
ORDER BY 
T1.review_date ASC,
T1.review_text ASC

• (Q5) "ex5_patient" 라는 이름의 테이블에는 환자들에 대한 정보가,
  "ex5_apnt" 테이블에는 진료 예약 정보가 있음. 아직 진료 완료(TREATMENT_STATUS = 'Completed')가 되지 않은 예약 건들에 대하여
  '취소 되지 않은 다음 진료 예약' 의 예약자명을 찾아보기
• 문제 데이터

-- 테이블 생성  
CREATE OR REPLACE TABLE `project_name.dataset_name.ex5_patient` (
   PATIENT_NO STRING,
   PATIENT_NAME STRING,
   GENDER STRING,
   AGE INT64
);
CREATE OR REPLACE TABLE `project_name.dataset_name.ex5_apnt` (
   APNT_YMD TIMESTAMP,
   APNT_NO INT64,
   PATIENT_NO STRING,
   APNT_CANCEL_YN STRING,
   TREATMENT_STATUS STRING
);
-- 데이터 삽입
INSERT INTO `project_name.dataset_name.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 `project_name.dataset_name.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', '');

• 문제 풀이

SELECT p.patient_name
from oceanic-antler-456102-i6.modulabs.ex5_patient as p
left join oceanic-antler-456102-i6.modulabs.ex5_apnt as a
on p.patient_no = a.patient_no
where 1=1
and a.treatment_status <> 'Completed'
and a.apnt_cancel_yn = 'N'
LIMIT 1

여기까진 정말 할 만했다. 그리고 이후의 window함수도 대충은 잘 쓰지는
못해도 어느 정도 알고는 있었다. 사실 window함수까지 다 배우고,
e-commerce 분야에서 흔히 쓰이는 RFM 분석, cohort 분석 때부터가
상당히 힘들었다.