SQL 기초 5일차
#스파르타코딩클럽, #내일배움단
학습일자: 2022/03/11
재수강일: 2022/05/01
강의: 엑셀보다 쉬운 SQL
진도: 4-5 ~ 4-7
=====================
실전에서 유용한 SQL 문법 (문자열)
select user_id, email from users
email에서 메일주소를 뺀 아이디만 보기
select user_id, email, substring_index(email, '@', 1) from users
메일주소만 보고싶으면 1 대신 -1
파이썬이나 자바스크립트의 인덱싱이랑은 조금 다르네용, 굳이 따지자면 엑셀의 left 함수 같은 느낌?
왜냐면 1 대신 2를 넣는다고 해서 2번째 위치의 문자열만 불러오는게 아님
=====================
문자열 일부만 출력하기
select order_no, created_at, substring(created_at, 1, 10) as date from orders
일별로 몇개씩 주문했는지 알려면?
select substring(created_at, 1, 10) as date, count(*) from orders
group by date
엑셀의 mid 함수와 같은 조건값
=====================
case문 = 경우에 따라 원하는 값을 출력해주는 문법
select user_id, point,
(case when point > 10000 then '잘하고 있어요!'
else '좀만 화이팅!' end) as msg
from point_users pu
배운걸 토대로 0~5천, 5천~1만, 1만 이상 통계 내기
select a.lv, count(*) from (
select user_id, point,
(case when point > 10000 then '1만 이상'
when point > 5000 then '5천 이상'
else '5천 미만' end) as lv
from point_users pu
) a
group by a.lv
with로 깔끔하게 정리
with table1 as (
select user_id, point,
(case when point > 10000 then '1만 이상'
when 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
=====================
4-6 퀴즈풀기
점점 어려워지는 SQL... 철저한 복습이 필요할 때! 오늘 나 잠 안자!
퀴즈1. 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!
select avg(point) as avg_point from point_users pu2
select pu1.user_id, pu1.point,
(case when pu1.point > (select avg(point) from point_users) then '잘 하고 있어요'
else '열심히 합시다!' end) as msg from point_users pu1
case when A then a else b end, 그리고 서브쿼리를 활용해서 해결
평균포인트 값 = 5380.8339
퀴즈2. 이메일 도메인별 유저의 수 세어보기
select substring_index(email, "@", -1)as use_mail, count(*) from users u
group by use_mail
아까 배운 substring_index를 활용해서 처리했다.
퀴즈3. '화이팅'이 포함된 오늘의 다짐만 출력해보기
select comment from checkins c
where comment like '%화이팅%'
서브스트링이니 뭐니보다 like가 문제를 해결해줄 때가 많다
퀴즈4. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
퀴즈4의 1단계
테이블을 두개 만든다. 전체강의와 수강한강의 각각 하나씩
1) 전체강의수 파악
select enrolled_id, count(*)as 전체강의수 from enrolleds_detail
group by enrolled_id
2) 수강강의수 파악
select enrolled_id, count(*) as 수강한강의수 from enrolleds_detail
where done = 1
group by enrolled_id
퀴즈4의 2단계
inner join할 틀을 만든다
select * from (
)
inner join (
)
on
1과 2에 아까 만든 테이블 하나씩 넣고 3에 연결키값 넣어줌
select * from (
select enrolled_id, count(*)as 전체강의수 from enrolleds_detail
group by enrolled_id
) a
inner join (
select enrolled_id, count(*) as 수강한강의수 from enrolleds_detail
where done = 1
group by enrolled_id
) b
on a.enrolled_id = b.enrolled_id
퀴즈4의 3단계
select * -> 원하는 추출값으로 변경
select a.enrolled_id, 전체강의수, 수강한강의수, round(수강한강의수/전체강의수, 2) as ratio
from (
select enrolled_id, count(*)as 전체강의수 from enrolleds_detail
group by enrolled_id
) a
inner join (
select enrolled_id, count(*) as 수강한강의수 from enrolleds_detail
where done = 1
group by enrolled_id
) b
on a.enrolled_id = b.enrolled_id
퀴즈4의 4단계
with문으로 쿼리를 깔끔하게 정리하기
with table1 as(
select enrolled_id, count(*)as 전체강의수 from enrolleds_detail
group by enrolled_id
), table2 as (
select enrolled_id, count(*) as 수강한강의수 from enrolleds_detail
where done = 1
group by enrolled_id
)
select a.enrolled_id, 전체강의수, 수강한강의수, round(수강한강의수/전체강의수, 2) as ratio
from table1 a
inner join table2 b
on a.enrolled_id = b.enrolled_id
퀴즈4의 다른 풀이
select enrolled_id,
count(enrolled_id) as 총강의,
sum(done) as 수강한강의,
round(sum(done)/count(*),2) as 진도율
from enrolleds_detail
group by enrolled_id
=====================
4-7 끝, 숙제 설명
드디어 마지막 강!
개발일지에 쿼리문법을 정리해서 올리기
끝!