https://teamsparta.notion.site/SQL-60c2a83eecdb473887ba73a75bd1c053
SQL 전체 강의자료
[내일배움단] 엑셀보다 쉬운 SQL
강의자료
teamsparta.notion.site
[수업 목표]
- Subquery(서브쿼리)의 사용 방법을 배워본다
- 실전에서 유용한 SQL 문법을 더 배워본다
- SQL을 사용하여 실전과 같은 데이터분석을 진행해본다
with절
with table1 as (
select course_id, count(distinct(user_id)) as cnt_checkins
from checkins group by course_id
), table2 as
( select course_id, count(*) as cnt_total from orders
group by course_id )
select c.title,
a.cnt_checkins,
b.cnt_total, (a.cnt_checkins/b.cnt_total) as ratio
from table1 a inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
문자열 쪼개기
[이메일에서 아이디만 가져와보기]
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
[이메일에서 도메인만 가져와보기]
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
[orders테이블에서 날짜까지 출력해보기]
select order_no, created_at, substring(created_at,1,10) as date from orders
[일별로 몇 개씩 주문이 일어났는지 살펴보기]
select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date
[포인트 보유액에 따라 다르게 표시해주기]
select pu.point_user_id, pu.point,
(case when pu.point > 10000 then '1만 이상!'
when pu.point > 5000 then '5천 이상!'
else '5천 미만' END) as lv
from point_users pu;
[심화버전]
select a.lv, count(*) as cnt from(
select pu.point_user_id, pu.point,
(case when pu.point > 10000 then '1만 이상!'
when pu.point > 5000 then '5천 이상!'
else '5천 미만' END) as lv
from point_users pu
) a
group by a.lv
또는
with table1 as (
select pu.point_user_id, pu.point,
(case when pu.point > 10000 then '1만 이상!'
when pu.point > 5000 then '5천 이상!'
else '5천 미만' END) as lv
from point_users pu
)
select a.lv, count(*) as cnt from table1 a
group by a.lv
