728x90
◆ 목차
- 문제풀기를 위한 정리
- 실습 문제 풀기
- 여러 문법
1. MySQL - 문제풀기를 위한 정리
◆ 날짜, 시간별 정리
SELECT NOW();
SELECT CURRENT_DATE();
SELECT EXTRACT(MONTH FROM "2021-01-01"); -- 날짜에서 month(월)만 뽑기
SELECT DAY("2021-01-01"); -- 날짜에서 day(일)만 뽑기
SELECT DATE_ADD("2021-01-01", INTERVAL 7 DAY); -- 날짜 더해주기
SELECT DATE_SUB("2017-06-15", INTERVAL 7 DAY); -- 날짜 빼기
SELECT DATEDIFF("2017-06-25", "2017-06-15"); -- 두개의 날짜 간격 뽑기
SELECT TIMEDIFF("2021-01-25 12:10:00", "2021-01-25 10:10:00"); -- 두개의 시간 간격 뽑기
SELECT DATE_FORMAT(NOW(), "%Y-%m-%d"); -- 현재 날짜, 시간에서 지정된 부분까지 조회
◆ LIMIT와 OFFSET
■ LIMIT
- 쿼리 결과에서 반환할 행(row)의 최대 개수를 지정
■ OFFSET
- 쿼리 결과에서 시작할 위치를 지정
- 단독 사용이 불가능, LIMIT와 같이 사용
■ WITH 문
- 서브 쿼리를 사용해서 임시 테이블처럼 사용할 수 있는 구문
WITH 가상테이블명 AS
(
SELECT * FROM 테이블명
WHERE 조건식
)
SELECT * FROM 가상테이블명;
■ LAG
- 이전 행을 가져옴
■ LEAD
- 특정 위치의 행을 가져옴
- default는 1 : 다음행을 가져
2. MySQL - 실습 문제 풀기
연습문제 1)
2020년 7월의 총 Revenue(수익)를 구하기
SELECT SUM(price) AS Revenue FROM tbl_purchase
WHERE YEAR(purchased_at) = "2020"
AND MONTH(purchased_at) = "7";
연습문제 2)
2020년 7월의 MAU(월별 활동한 이용자)를 구하기
SELECT DISTINCT COUNT(customer_id) FROM tbl_visit
WHERE YEAR(visited_at) = "2020"
AND MONTH(visited_at) = "7";
연습문제 3)
7월에 우리 Active 유저의 구매율(Paying Rate)은 어떻게 되나요?
-- 구매유저 수 / 전체 활성유저
-- 전체 활성 유저 : 16414
SELECT COUNT(DISTINCT customer_id) FROM tbl_visit
WHERE visited_at LIKE "2020-07%";
-- 구매유저 수 : 11174
SELECT COUNT(DISTINCT customer_id) FROM tbl_purchase
WHERE purchased_at LIKE "2020-07%";
SELECT ROUND(11174 / 16414 * 100);
연습문제 4)
7월에 구매 유저의 월 평균 구매금액은 어떻게 되나요?
SELECT AVG(Revenue) FROM (SELECT customer_id, sum(price) AS Revenue FROM tbl_purchase
WHERE purchased_at LIKE "2020-07%"
GROUP BY customer_id) AS coustomer_avg;
연습문제 5)
7월에 가장 많이 구매한 고객 Top3와 Top10 ~ 15 고객을 뽑기
-- Top3
SELECT DISTINCT customer_id, SUM(price) AS Revenue FROM tbl_purchase
WHERE purchased_at LIKE "2020-07%"
GROUP BY customer_id
ORDER BY Revenue DESC
LIMIT 3;
-- Top10 ~ 15
SELECT DISTINCT customer_id, SUM(price) AS Revenue FROM tbl_purchase
WHERE purchased_at LIKE "2020-07%"
GROUP BY customer_id
ORDER BY Revenue DESC
LIMIT 9, 5;
-- OFFSET 활용
SELECT DISTINCT customer_id, SUM(price) AS Revenue FROM tbl_purchase
WHERE purchased_at LIKE "2020-07%"
GROUP BY customer_id
ORDER BY Revenue DESC
LIMIT 6 OFFSET 10;
연습문제 6)
2020년 7월의 평균 DAU를 구하기, Active user 수가 증가하는 추세인가?
-- DAU = 일별 활동 유저
-- 구해보니 8월1일이 같이 조회 -> %T를 사용해서 시간이 바뀌는걸 확인하기
SELECT *, DATE_FORMAT(visited_at, "%Y-%m-%d %T") AS date
FROM tbl_visit
WHERE visited_at LIKE "2020-07%";
-- 적용하기
SELECT AVG(customer)
FROM(SELECT DATE_FORMAT(visited_at - INTERVAL 9 HOUR, "%Y-%m-%d") AS date,
COUNT(DISTINCT customer_id) AS customer
FROM tbl_visit
WHERE visited_at LIKE "2020-07%"
GROUP BY 1
ORDER BY 1) AS AD;
연습문제 7)
2020년 7월의 평균 WAU를 구하기
-- %U = 몇번째 주를 나타냄
SELECT AVG(customer) FROM (SELECT DATE_FORMAT(visited_at - INTERVAL 9 HOUR, "%Y-%m-%U") AS date,
COUNT(DISTINCT customer_id) AS customer
FROM tbl_visit
WHERE visited_at >= "2020-07-05"
AND visited_at < "2020-07-26"
GROUP BY 1
ORDER BY 1) AS AD;
연습문제 8)
2020년 7월의 Daily Devenue는 증가하는 추세인가요? 평균 Daily Revenue도 구하기
-- 2020년 7월의 Daily Devenue 추세
SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%Y-%m-%d") AS date_at,
SUM(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= "2020-07-01"
AND purchased_at < "2020-08-01"
GROUP BY 1
ORDER BY 1;
-- 2020년 7월의 평균 Daily Revenue도 구하기
SELECT AVG(revenue) FROM
(SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%Y-%m-%d") AS date_at,
SUM(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= "2020-07-01"
AND purchased_at < "2020-08-01"
GROUP BY 1
ORDER BY 1) DD;
연습문제 9)
2020년 7월의 평균 Weekly Devenue 구하기
SELECT AVG(revenue) FROM (
SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%Y-%m-%U") AS date_at, SUM(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= "2020-07-05"
AND purchased_at < "2020-07-26"
GROUP BY 1
ORDER BY 1) WD;
연습문제 10)
2020년 7월의 요일별 Devenue를 구하기/어느 요일이 Devenue 가장 높고 어느 요일이 Devenue 가장 낮은가요?
SELECT DATE_FORMAT(date_at, "%W") AS day_order,
DATE_FORMAT(date_at, "%W") AS day_name,
AVG(revenue)
FROM(
SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%Y-%m-%d") AS date_at,
SUM(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= "2020-07-01"
AND purchased_at < "2020-08-01"
GROUP BY 1) WD
GROUP BY 1, 2
ORDER BY 1;
연습문제 11)
2020년 07월 시간대별 Revenue를 구하기 / 어느 시간대 Revenue가 가장 높고 어느 시간대 Revenue가 가장 낮은지
SELECT hour_at, AVG(revenue)
FROM(
SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%Y-%m-%d") AS date_at,
DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%H") AS hour_at,
SUM(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= "2020-07-01"
AND purchased_at < "2020-08-01"
GROUP BY 1, 2) AD
GROUP BY 1
ORDER BY 2 DESC;
연습문제 12)
2020년 07월 요일 및 시간대별 Revenue를 구하기 / 어느 요일 및 시간대가 Revenue이 가장 높고 어느 시간대가 Revenue이 가장 낮은지
SELECT day_week_at, hour_at, AVG(revenue)
FROM (
SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%Y-%m-%d") AS date_at,
DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%W") AS day_week_at,
DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%H") AS hour_at,
SUM(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= "2020-07-01"
AND purchased_at < "2020-08-01"
GROUP BY 1, 2, 3) AD
GROUP BY 1, 2
ORDER BY 3 DESC;
연습문제 13)
전체 유저의 Demograhic을 구하기 / 성, 연령별로 유저 숫자 구하기.
어느 세그먼트가 가장 숫자가 많은지? 성별은 하나로, 연련은 5세 단위로 적당히 묶고 숫자가 높은 순서대로 보이기
SELECT CASE WHEN length(gender) < 1 THEN "Others"
ELSE gender END AS gender,
CASE WHEN age <= 15 THEN "0_15세 이하"
WHEN age <= 20 THEN "1_15 ~ 20세"
WHEN age <= 25 THEN "2_21 ~ 25세"
WHEN age <= 30 THEN "3_26 ~ 30세"
WHEN age <= 35 THEN "4_31 ~ 35세"
WHEN age <= 40 THEN "5_36 ~ 40세"
WHEN age <= 45 THEN "6_41 ~ 45세"
WHEN age <= 50 THEN "7_46세 이상"
END AS age,
COUNT(*)
FROM tbl_customer
GROUP BY 1, 2
ORDER BY 3 DESC;
연습문제 14)
연습문제 13 결과의 성, 연령을 성별(연령) EX)남성(25세-29세 이하)으로 통합하기
각 성, 연령이 전체 고객에서 얼마나 차지하는지 분포(%)를 알려주세요. 분포가 높은 순서대로
SELECT CONCAT(CASE WHEN length(gender) < 1 THEN "기타"
WHEN gender = "Others" THEN "기타"
WHEN gender = "M" THEN "남성"
WHEN gender = "F" THEN "여성"
END, "(",
CASE WHEN age <= 15 THEN "0_15세 이하"
WHEN age <= 20 THEN "1_15 ~ 20세"
WHEN age <= 25 THEN "2_21 ~ 25세"
WHEN age <= 30 THEN "3_26 ~ 30세"
WHEN age <= 35 THEN "4_31 ~ 35세"
WHEN age <= 40 THEN "5_36 ~ 40세"
WHEN age <= 45 THEN "6_41 ~ 45세"
WHEN age <= 50 THEN "7_46세 이상"
END, ")" ) AS segement,
ROUND(COUNT(*) / (SELECT COUNT(*) FROM tbl_customer) * 100, 2) AS per
FROM tbl_customer
GROUP BY 1
ORDER BY 2 DESC;
연습문제 15)
2020년 7월의 성별/연령대에 따라 구매 건수와, 총 Revenue를 구하기
남, 녀 이외의 성별은 하나로 묶어주기
SELECT CASE WHEN B.gender = "M" THEN "남성"
WHEN B.gender = "F" THEN "여성"
WHEN B.gender = "Others" THEN "기타"
WHEN LENGTH(B.gender) < 1 THEN "기타"
END AS gender,
COUNT(*) AS cnt,
SUM(price) AS revenue
FROM tbl_purchase A
LEFT JOIN tbl_customer B
on A.customer_id = B.customer_id
WHERE A.purchased_at >= "2020-07-01"
AND A.purchased_at < "2020-08-01"
GROUP BY 1;
연습문제 16)
2020년 7월의 성별 / 연령대에 따라 구매 건수와 총 Revenue를 구하기
남, 녀 이외의 성별은 하나로 묶기
SELECT CASE WHEN B.gender = "M" THEN "남성"
WHEN B.gender = "F" THEN "여성"
WHEN B.gender = "Others" THEN "기타"
WHEN LENGTH(B.gender) < 1 THEN "기타"
END AS gender,
CASE WHEN age <= 15 THEN "15세 이하"
WHEN age <= 20 THEN "15 ~ 20세"
WHEN age <= 25 THEN "21 ~ 25세"
WHEN age <= 30 THEN "26 ~ 30세"
WHEN age <= 35 THEN "31 ~ 35세"
WHEN age <= 40 THEN "36 ~ 40세"
WHEN age <= 45 THEN "41 ~ 45세"
WHEN age >= 46 THEN "46세 이상"
END AS age_group,
COUNT(*) AS cnt,
SUM(price) AS revenue
FROM tbl_purchase A
LEFT JOIN tbl_customer B
on A.customer_id = B.customer_id
WHERE A.purchased_at >= "2020-07-01"
AND A.purchased_at < "2020-08-01"
GROUP BY 1, 2
ORDER BY 4 DESC;
연습문제 17)
2020년 7월 일별 매출과 증감폭, 증감률을 구하기
-- 7월에 대한 일별 매출
SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%Y-%m-%d") AS D_DATE,
SUM(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= "2020-07-01"
AND purchased_at < "2020-08-01"
GROUP BY 1;
-- 임시 테이블 생성 WITH AS
WITH tbl_revenue AS(
SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%Y-%m-%d") AS D_DATE,
SUM(price) AS revenue
FROM tbl_purchase
WHERE purchased_at >= "2020-07-01"
AND purchased_at < "2020-08-01"
GROUP BY 1)
-- 전일 매출 가져오기
SELECT *, LAG(revenue) OVER(ORDER BY D_DATE ASC)
FROM tbl_revenue;
-- 전일 대비 증감폭
SELECT *, revenue - LAG(revenue) OVER(ORDER BY D_DATE ASC)
FROM tbl_revenue;
-- 증감률
SELECT *, revenue - LAG(revenue) OVER(ORDER BY D_DATE ASC) AS diff_revenue,
ROUND((revenue - LAG(revenue) OVER(ORDER BY D_DATE ASC)) / LAG(revenue) OVER(ORDER BY D_DATE ASC) * 100, 2) AS chg_revenue
FROM tbl_revenue;
연습문제 18)
2020년 7월 일별로 많이 구매한 고객들한테 소정의 선물을 줄려고함
7월에 일별로 구매 금액 기준으로 가장 많이 지출한 고객 Top3는?
SELECT * FROM(
SELECT DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%Y-%m-%d") D_DATE,
ustomer_id,
SUM(price),
DENSE_RANK() OVER (PARTITION BY DATE_FORMAT(purchased_at - INTERVAL 9 HOUR, "%Y-%m-%d") ORDER BY SUM(price) DESC) AS rank_rev
FROM tbl_purchase
WHERE purchased_at >= "2020-07-01"
AND purchased_at < "2020-08-01"
GROUP BY 1, 2) sub
WHERE rank_rev < 4;
연습문제 19)
2020년 7월 우리 신규유저가 하루 안에 결제로 넘어가는 비율이 어떻게 되는지 구하시오
그 비율이 어떤지 알고 싶고 결제까지 보통 몇 분 정도가 소요되는지 확인 /
→ 신규유저의 가입일, 최초 구매일을 알아보기
-- 신규유저 가입일은 customer 테이블에서 확인
SELECT * FROM tbl_customer;
-- 최초 구매일
SELECT customer_id, MIN(purchased_at) AS purchased_at
FROM tbl_purchase
GROUP BY customer_id;
-- 결제하지 않은 고객 정보 알아내기
SELECT A.customer_id,
A.created_at,
B.customer_id AS paying_user,
B.purchased_at
FROM tbl_customer A
LEFT JOIN (SELECT customer_id,
MIN(purchased_at) AS purchased_at
FROM tbl_purchase
GROUP BY 1) B
ON A.customer_id = B.customer_id
AND B.purchased_at < A.created_at + INTERVAL 1 DAY
WHERE A.created_at >= "2020-07-01"
AND A.created_at < "2020-08-01";
-- 결제로 넘어가는 비율
WITH tbl_rt AS (
SELECT A.customer_id,
B.customer_id AS paying_user,
TIME_TO_SEC(TIMEDIFF(B.purchased_at, A.created_at)) / 3600 AS diff_hour
FROM tbl_customer A
LEFT JOIN (SELECT customer_id,
MIN(purchased_at) AS purchased_at
FROM tbl_purchase
GROUP BY 1) B
ON A.customer_id = B.customer_id
AND B.purchased_at < A.created_at + INTERVAL 1 DAY
WHERE A.created_at >= "2020-07-01"
AND A.created_at < "2020-08-01")
SELECT ROUND(COUNT(paying_user) / COUNT(customer_id) * 100, 2)
FROM tbl_rt;
-- 결제까지 보통 몇 분 정도 소요인지 구하기
WITH tbl_rt AS (
SELECT A.customer_id,
B.customer_id AS paying_user,
TIME_TO_SEC(TIMEDIFF(B.purchased_at, A.created_at)) / 3600 AS diff_hour
FROM tbl_customer A
LEFT JOIN (SELECT customer_id,
MIN(purchased_at) AS purchased_at
FROM tbl_purchase
GROUP BY 1) B
ON A.customer_id = B.customer_id
AND B.purchased_at < A.created_at + INTERVAL 1 DAY
WHERE A.created_at >= "2020-07-01"
AND A.created_at < "2020-08-01")
SELECT AVG(diff_hour)
FROM tbl_rt;
연습문제 20)
2020년 7월 우리 서비스는 유저의 재방문율이 높은 서비스인지?
이를 파악하기 위해 7월 기준 Day1 Retention이 어떤지 구해주고 추세를 보기 위해 Daily로 추출하기
SELECT DATE_FORMAT(A.visited_at - INTERVAL 9 HOUR, "%Y-%m-%d") AS D_DATE,
COUNT(DISTINCT A.customer_id) AS active_user,
COUNT(DISTINCT B.customer_id) AS ratained_user,
COUNT(DISTINCT B.customer_id) / COUNT(DISTINCT A.customer_id) AS retention
FROM tbl_visit A
LEFT JOIN tbl_visit B
ON A.customer_id = B.customer_id
AND DATE_FORMAT(A.visited_at - INTERVAL 9 HOUR, "%Y-%m-%d") = DATE_FORMAT(B.visited_at - INTERVAL 9 HOUR - INTERVAL 1 DAY, "%Y-%m-%d")
WHERE A.visited_at >= "2020-07-01"
AND A.visited_at < "2020-08-01"
GROUP BY 1;
연습문제 22)
우리 서비스는 신규유저가 많은지? 기존 유저가 많은지?
가입기간별로 고객 분포가 어떤지 DAU 기준으로 알려주기
WITH tbl_visit_by_joined AS (SELECT DATE_FORMAT(A.visited_at - INTERVAL 9 HOUR, "%Y-%m-%d") AS D_DATE,
A.customer_id,
B.created_at AS d_joined,
MAX(A.visited_at) AS last_visit,
DATEDIFF(MAX(A.visited_at), B.created_at) AS DATE_diff
FROM tbl_visit A
LEFT JOIN tbl_customer B
ON A.customer_id = B.customer_id
WHERE A.visited_at >= "2020-07-01"
AND A.visited_at < "2020-08-01"
GROUP BY 1, 2, 3)
SELECT A.D_DATE,
CASE WHEN A.date_diff >= 730 THEN "2년 이상"
WHEN A.date_diff >= 365 THEN "1년 이상"
WHEN A.date_diff >= 183 THEN "6개월 이상"
WHEN A.date_diff >= 91 THEN "3개월 이상"
WHEN A.date_diff >= 30 THEN "1개월 이상"
ELSE "1개월 미만"
END AS segement,
B.all_users,
COUNT(A.customer_id) AS users,
ROUND(COUNT(A.customer_id) / B.all_users * 100, 2) AS per
FROM tbl_visit_by_joined A
LEFT JOIN (SELECT D_DATE,
COUNT(customer_id) AS all_users
FROM tbl_visit_by_joined
GROUP BY 1) B
ON A.D_DATE = B.D_DATE
GROUP BY 1, 2, 3
ORDER BY 1,2;
3. 함수 정리
◆ PARTITION BY
- 데이터를 특정 기준에 따라 파티셔닝(분할)하여 각 파티션 내에서 계산을 할 수 있도록 해줌
<윈도우 함수> OVER (PARTITION BY <파티션 기준 열> ORDER BY <정렬 기준 열>)
◆ 윈도우 프레임을 정의할 때 사용하는 절
- ROWS
- RANGE
■ ROWS
- 물리적인 행 기준으로 윈도우 프레임을 정의 / 현재 행을 기준으로 몇 개의 행을 포함할지를 지정
■ RANGE
- 논리적인 값 범위 기준으로 윈도우 프레임을 정의 / 현재 행의 값과 동일한 값 또는 범위에 속하는 행들을 포함
■ 윈도우 함수 종류
- ROW_NUMBER() : 각 행에 순서를 매기는 함, 결과 집합에서 각 행에 순차적으로 번호를 할당
SELECT ROW_NUMBER() OVER (ORDER BY [컬럼명] DESC) AS rank, emp_name, salary
FROM [테이블명];
- RANK() : 동점자가 있을 경우 같은 순위를 부여하고 다음 순위는 건너뛰는 함수
SELECT RANK() OVER (ORDER BY [컬럼명] DESC) AS rank, student_name, score
FROM [테이블명];
- DENSE_RANK() : 동점자가 있을 경우 같은 순위를 부여하지만 다음 순위를 건너뛰지 않고 증가시키는 함수
SELECT DENSE_RANK() OVER (ORDER BY [컬럼명] DESC) AS rank, salesperson, sales
FROM [테이블명];
- SUM()
- AVG()
■ PERCENT_RANK()
- 행의 백분위 순위를 계산
■ CUME_DIST()
- 행의 누적 분포를 계산
■ NTILE(n)
- 행을 n개의 그룹으로 분할
■ WITH ROLLUP
- 집계 함수를 사용할 때 추가적인 총계 행을 생성하는 기능
SELECT [컬럼 이름1], [컬럼 이름2], SUM(sales) AS total_sales
FROM [테이블 이름]
GROUP BY [컬럼 이름1], [컬럼 이름2] WITH ROLLUP;
예제)
-- 동물이 몇마리인지 구하기
SELECT animal, COUNT(*) FROM animal_info GROUP BY animal;
- 위에 각 동물에 대한 수를 구했다면 WITH ROLLUP을 사용하여 모든 동물의 총 합계를 구하기
-- 동물들의 총합계를 구하기
SELECT animal, COUNT(*) FROM animal_info GROUP BY animal WITH ROLLUP;
- 동물별 이름과 타입으로 GROUP BY
-- GROUP BY를 함께 사용하여 동물들의 타입별 총합계를 구하기
SELECT animal, type, COUNT(*) FROM animal_info GROUP BY animal, type WITH ROLLUP;
728x90
'데이터분석 부트캠프' 카테고리의 다른 글
| SQL 프로젝트_Olist 분석 (0) | 2024.07.07 |
|---|---|
| vsCode에서 MySQL 연동하기 (0) | 2024.06.25 |
| 데이터 관련 직무 (0) | 2024.06.12 |
| 데이터분석 부프캠프 14기 - 8주차_MySQL (0) | 2024.06.12 |
| 데이터분석 부프캠프 14기 - 7주차_MySQL (1) | 2024.06.08 |
