[교육&스터디]/[패스트 캠퍼스] 데이터 분석 부트캠프 9기

[데이터 분석 부트캠프] 7주차 진행 및 학습일지

icepack788 2023. 8. 10. 18:14

이미지 출처 : 인프런

[패스트 캠퍼스] 데이터 분석 부트캠프 7주차!! 

[7주차 : SQL 학습 및 정리 내용] 

1. SQL 고급 파트 

  • 서브쿼리 - EXISTS 연산자
    • EXISTS 연산을 이용해서 해당 조건이 존재하는 (매칭되는) 집합이 출력
    • NOT EXISTTS 연산을 이용해서 해당 조건이 존재하지 않는 집합만 출력
  • 인라인 뷰 서브쿼리
    • FROM 절 내에 존재하는 서브쿼리를 인라인 뷰 서브쿼리라고 함.
    • FROM절에 소괄호()로 되어있는 부분을 하나의 집합으로 봄. 인라뷰, 인라뷰 서브쿼리라고 부름.
    • 하나의 테이블에서 조건을 주어 조건을 만족하는 ‘결과 집합’을 만들고, 그 결과 집합을 불러오는 것. 따로 따로 불러오는 것보다, 한 쿼리안에서 모든 걸 해내면 좋기에 이렇게 도 많이 진행함.
  • 스칼라 서브쿼리 실습
    • SELECT 절 내에 존재하는 서브쿼리를 스칼라 서브쿼리라고 함
    • 출력할 행에 대한 다양한 조건들을 걸수가 있음
  • 윈도우 함수
    • 윈도우 함수를 사용해서 결과 집합 내에 행들간의 다양한 연산을 수행 가능, COUNT 함수로 행의 건수를 구할 수 있음
    • 윈도우 COUNT 함수 : 테이블의 내용 전체를 출력하면서 전체 건수도 구할 수 있음 → 원래는 같이 구할 수 없음, GROUP BY 오류 어쩌구 하면서 안구해짐(테스트 해봄)
    • 윈도우 COUNT + PARTITION BY절 : 테이블의 내용도 함께 출력하면서 ~~별 개수도 같이 구해줌 → ex) SELECT A.* , COUNT() OVER(partition by A.PRDT_GRP_NO) AS CNT → OVER 안에 partition by A.PRDT_GRP_NO 들어가고, 들어간 컬럼별로 묶어줌
    • 윈도우 AVG 함수 : AVG함수로 특정 컬럼의 특정 행 범위의 평균을 구할 수 있음 → 00 컬럼별 어떤(다른, 예를들어 가격) 컬럼 값의 평균을 구할 수 있음
    • 윈도우 RANK, DENSE_RANK, ROW_NUMBER 함수 : 특정값 기준 행간에서의 등수 구함 → 결과 집합안에서의 행들간에서 특정 컬럼을 기준으로 등수를 구할 수 있음!
    • 윈도우 함수 FIRST_VALUE, LAST_VALUE 함수 : 처음값과 마지막값을 구할 수 있음 → first_vaules(컬럼) over(order by 컬럼 rows between unbounded preceding and unbounded following) → 문법이기 때문에 그냥 받아들이는게 좋음, 어디서부터 어디까지의 값을 정할래? 이런 뜻임
    • 윈도우 함수 LEAD, LAG 함수 : LAG, LEAD를 이용하여 집합 내에서특정 컬럼의 이전행의 값과 다음행의 값을 구할 수 있음 → LAG 함수 : PRDT_NO 기준 정렬한 집합 내에서 이전행의 PRDT_PRC 컬럼 값을 출력 → 이전 행 값이 없으면 NULL 값이 나옴 → LEAD 함수 : PRDT_NO 기준 정렬한 집합 내에서 다음행의 PRDT_PRC 컬럼 값을 출력
    • 윈도우 함수 PEECENT_RANK, CUME_DISTE함수 : 비율값 계산 가능 → ‘PRDT_PRC’ 순위 기준 백분위를 구한다 / 소수점 첫째자리에서 반올림함 → CUME_DIST 함수 : ‘PRDO_PRC’ 순위 기준 누적 백분위를 구함 / 2등이 2명이면 3등을 기준으로 계산함(2등 2명 모두 3등을 기준으로 계산함)
    • 윈도우 함수 NTILE 함수 : 각각의 값을 전체 대비 등급으로 나눌 수 있다

2. SQL 문제풀이 연습 

  • sakila 데이터로 연습문제 진행.
#1
select concat(first_name, ' ' , last_name) as 'Staff Member', sum(amount) as 'Total Amount' from staff S
join payment P on S.staff_id = P.staff_id 
where P.payment_date >='2005-08-01 00:00:00' and P.payment_date <='2005-08-31 23:59:59' 
group by 1 ;
# 2
select [C.name](<http://c.name/>), avg(F.length) from film F
join film_category FC on FC.film_id = F.film_id
join category C on C.category_id = FC.category_id
group by [C.name](<http://c.name/>)
having avg(F.length) > (select avg(length) from film)  ;
# 3
select [C.name](<http://c.name/>),
avg(timestampdiff(hour, R.rental_date,R.return_date))
from rental R
join inventory I on I.inventory_id = R.inventory_id
join film F on F.film_id = I.film_id
join film_category FC on F.film_id = FC.film_id
join category C on FC.category_id = C.category_id
group by [C.name](<http://c.name/>);
# 4
select sum(P.amount) as 'Total Sales', [C.name](<http://c.name/>) as 'Genre' from payment P
	join rental R on P.rental_id = R.rental_id
	join inventory I on R.inventory_id = I.inventory_id
	join film_category FC On I.film_id = FC.film_id
	join category C on FC.category_id = C.category_id
	group by 2
	order by Total Sales desc
	limit 5 ;
select * from top5_genres ;
#5 
select F.title, count(*) as '가장 많이 대여된 영화' from rental R
join inventory I on R.inventory_id = I.inventory_id
join film_category FC On I.film_id = FC.film_id
join film F on FC.film_id = F.film_id
join category C on FC.category_id = C.category_id
where R.rental_date >='2005-05-01 00:00:00' and R.rental_date <='2005-05-31 23:59:59'
group by F.title
order by 2 desc
limit 1 ;
# 프로그래머스 문제 : 그룹별 조건에 맞는 식당 목록 출력하기
SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE,'%Y-%m-%d') AS 'REVIEW_DATE'
FROM MEMBER_PROFILE M
JOIN REST_REVIEW R ON M.MEMBER_ID = R.MEMBER_ID
WHERE M.MEMBER_ID =
(SELECT MEMBER_ID FROM REST_REVIEW GROUP BY MEMBER_ID
ORDER BY COUNT(*) DESC LIMIT 1)
ORDER BY 3 ASC, 2 ASC ;
# 6
select concat(C.first_name, ' ', C.last_name) as Name, sum(P.amount) as total_price from payment P
join customer C on P.customer_id = C.customer_id
group by C.customer_id
having total_price > (
select avg(amount_customer) from
(select sum(amount) as amount_customer from payment group by customer_id) as sum_customer ) ;
# 7
create view Total_amount as 
	select S.store_id, round(sum(P.amount),0) as '총 매출' from store S
	join staff ST on S.store_id = ST.store_id
	join payment P on ST.staff_id = p.staff_id
	group by 1 ;
select * from Total_amount ;
drop view Total_amount ;
# 8
select CY.country as '국가', count(*) as '고객 수' from customer C
join address A on C.address_id = A.address_id
join city CT on A.city_id = CT.city_id
join country CY on CT.country_id = CY.country_id
group by 1
order by 2 desc
limit 5 ;

할꺼도 많고 ㅠㅠ 정리할꺼도 많고 ㅠㅠ 학습일지에는 간단하게 올리고, 부트캠프 관련 후기를 따로 올리고 있으니 요기서는 이제 음슴체로 진행 및 간단하게  진행하도록 하겠습니다 ㅎㅎ

자격증 공부도 해야하고 연습문제 풀이 및 프로젝트도 이제 슬슬 해야하니 머리가 아파오는군요,,ㅎㅎ 암튼 다음주에 뵙겠읍니다... 그럼 이만... 

반응형