워밍업 겸 간단 복습 & 연습 문제 풀기
본격적으로 실전반 진도를 나가기 전에, 손도 좀 풀어볼 겸 실전반에 필요한 기본적인 내용들을 간단하게 복습하고 연습 문제를 함께 풀어봅시다.
집계하기 GROUP BY
Q. 요일별 매출액을 집계해주세요. 매출액은 반올림하여 소수점 둘째자리까지만 출력해주세요.
SELECT day
,ROUND(SUM(total_bill),2) as sales
FROM tips
GROUP BY day워밍업 연습문제
-- 첫 주문과 마지막 주문
SELECT DATE(MIN(order_purchase_timestamp)) AS first_order_date
,DATE(MAX(order_purchase_timestamp)) AS last_order_date
FROM olist_orders_dataset-- 배송 예정일 예측 성공과 실패
SELECT DATE(order_purchase_timestamp) AS purchase_date
,COUNT(CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN order_id END) AS success
,COUNT(CASE WHEN order_delivered_customer_date > order_estimated_delivery_date THEN order_id END) AS fail
FROM olist_orders_dataset o
WHERE o.order_purchase_timestamp BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59'
AND order_delivered_customer_date IS NOT NULL
AND order_estimated_delivery_date IS NOT NULL
GROUP BY purchase_date
ORDER BY purchase_date서브쿼리
간단하게 말하면 쿼리 안의 쿼리. Subquery 또는 Inner query 라고 부르며, 서브쿼리를 포함하는 쿼리를 Outer query 라고 부릅니다.
일단 간단한 예시를 통해 형태를 봅시다.
SELECT lastName, firstName
FROM employees
WHERE officeCode IN (SELECT officeCode FROM offices WHERE country = 'USA')WHERE 절 서브쿼리
단일행 서브쿼리 ✨ (중요)
비교연산자와 함께 (=, >, < 등)
Q. 평균 영수 금액보다 더 많이 지불한 경우를 모두 출력해주세요.
SELECT *
FROM tips
WHERE total_bill > (SELECT AVG(total_bill)FROM tips) -- 서브쿼리의 결과값은 1개 다중행 서브쿼리 ✨ (중요)
IN, NOT IN 과 함께
Q. 요일 별 판매 금액이 1500불 이상인 날의 결제 내역을 모두 출력해주세요.
SELECT *
FROM tips
WHERE day IN(
SELECT day
FROM tips
GROUP BY day
HAVING SUM(total_bill) >= 1500
) -- 서브쿼리의 결과값은 컬럼 1개, 로우 N개다중컬럼 서브쿼리
Q. 요일 별로 가장 높은 금액의 결제 내역들을 출력해주세요.
SELECT *
FROM tips
WHERE (day,total_bill) IN (
SELECT day
,MAX(total_bill)
FROM tips
GROUP BY day
) -- 서브쿼리 결과값은 컬럼 N개 ,로우 N개FROM 절 서브쿼리 ✨ (중요)
인라인 뷰(Inline View)라고도 한다.
Q. 이 레스토랑의 요일 별 총 매출액 평균을 구해주세요.
SELECT AVG(sales) AS 평균
FROM (
SELECT day
,SUM(total_bill) AS sales
FROM tips
GROUP BY day
) AS daily -- 인라인뷰는 반드시 별칭을줘야함!WITH문 ✨ (굉장히 많이씀)
WITH daily AS (
SELECT day
,SUM(total_bill) AS sales
FROM tips
GROUP BY day
)
SELECT *
FROM dailyQ. 각 영수 금액이, 요일 별 매출액에서 차지하는 비율을 계산해주세요. 비율은 반올림하여 소수점 둘째자리까지만 출력해주세요. 영수 금액이 높은 것부터 출력해주세요.
예시
WITH daily AS(
SELECT day,SUM(total_bill) as sales
FROM tips
GROUP BY day
)
SELECT tips.day
,tips.total_bill
,ROUND(tips.total_bill * 100 / daily.sales,2) AS pct
FROM tips
INNER JOIN daily ON tips.day = daily.day
ORDER BY total_bill DESCFAQ 1. WITH 문을 여러개 작성해도 문제 없나요?
답변: WITH 문도 여러개를 쓸 수 있습니다. 아래와 같은 형식으로 작성하시면 돼요.
FAQ 2. WITH 문을 사용해서 데이터를 추출하고 나면 WITH 문으로 뽑아 놓은 결과는 없어지나요?
답변: WITH 문은 테이블을 만들어서 저장하는 문법은 아니에요. 쿼리를 실행할 때에만 유효한 임시 결과물을 만들어서 별칭을 붙여준다고 이해하시면 됩니다.
새로운 테이블을 생성하는 문법은 CREATE TABLE 이라고 따로 있어요. 분석가들이 자주 사용할 문법은 아니기 때문에, SQL 데이터 분석 캠프에서는 다루지 않지만 혹시 궁금하신 분들은 아래 참고자료를 읽어보세요.
참고자료
- WITH 참고: https://dev.mysql.com/doc/refman/8.0/en/with.html
- 테이블 생성 문법 참고: https://dev.mysql.com/doc/refman/8.0/en/create-table.html
SELECT 절 서브쿼리
단일행 서브쿼리만 사용 가능하다.
Q. 각 영수 금액이, 요일 별 매출액에서 차지하는 비율을 계산해주세요. 비율은 반올림하여 소수점 둘째자리까지만 출력해주세요. 영수 금액이 높은 것부터 출력해주세요. (SELECT 절 서브쿼리 풀이)
예시
SELECT t1.day
,t1.total_bill
,(SELECT SUM(total_bill) FROM tips t2 WHERE t2.day=t1.day) AS sales
,ROUND(t1.total_bill * 100 /(SELECT SUM(total_bill) FROM tips t2 WHERE t2.day=t1.day),2) As pct
FROM tips t1
ORDER BY total_bill DESC;
-- SELECT 절 서브쿼리는 스칼라서브쿼리라고 하는데 한 행씩 계산하는 특징을 가지고 있다.Q. 각 영수 금액이, 이 레스토랑의 전체 매출액에서 차지하는 비율을 계산해주세요. 비율은 반올림하여 소수점 둘째자리까지만 출력해주세요. 영수 금액이 높은 것부터 출력해주세요.
SELECT t1.day
,t1.total_bill
,(SELECT SUM(total_bill) FROM tips t2) AS sales
,ROUND(t1.total_bill * 100 /(SELECT SUM(total_bill) FROM tips t2 ),2) As pct
FROM tips t1
ORDER BY total_bill DESC;연습문제
서울북부지방법원 따릉이 정류소
SELECT *
FROM station
WHERE lat > (
SELECT lat FROM station
WHERE name ='서울북부지방법원'
)고액 영수증 찾기
SELECT *
FROM tips
WHERE (size,total_bill) IN (
SELECT size
,MAX(total_bill)
FROM tips
GROUP BY size
)
ORDER BY size최종 요약
FROM 절 서브쿼리
- 테이블처럼 사용하며, Alias 를 반드시 써줘야 합니다.
WITH
WHERE 절 서브쿼리
- 단일행 (비교 연산자
=,>,<등과 함께 사용) - 다중행 (
IN,NOT IN과 사용)
