winterakoon

Case when 문 

with table1 as (
   SELECT user_id, `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

Quiz
1. point_users 테이블에서 평균이상 포인트를 가지고 있으면 잘하고 있어요. 낮으면 열심히 합시다 표시

SELECT point_user_id, `point`, (
       case when `point` > (select AVG(`point`) from point_users) then '잘 하고 있어요!'
            else '열심히 합시다!' end
      ) as msg
 from point_users pu

2. users 테이블에서 이메일 도메인별 유저의 수 세어보기

SELECT SUBSTRING_INDEX(email,'@', -1) as domain, COUNT(*) from users u
group by domain

-- 위와 아래 동일

SELECT domain, COUNT(*) as cnt from (
   SELECT SUBSTRING_INDEX(email,'@', -1) as domain from users u
) a 
group by domain

3. enrolled_detail 테이블의 enrolled_id별 전체 강의 수와 들은 강의수(done) 출력하기

with table1 as (
   SELECT enrolled_id, COUNT(*) as done_cnt from enrolleds_detail
   WHERE done = 1
   group by enrolled_id
), table2 as (
   SELECT enrolled_id, COUNT(*) as total_cnt
   from enrolleds_detail
   group by enrolled_id 
)
select a.enrolled_id, 
       a.done_cnt, 
       b.total_cnt,
       ROUND(a.done_cnt/b.total_cnt, 2) as ratio
 from table1 a
 inner join table2 b on a.enrolled_id = b.enrolled_id

Subquery☆
큰 쿼리 안에 들어가있는 쿼리문

SELECT u.user_id, u.name, u.email from users u 
inner join orders o on u.user_id = o.user_id 
where o.payment_method = 'kakaopay'

/*위와 아래는 동일함*/

select u.user_id, u.name, u.email from users u 
WHERE user_id in (
   select user_id from orders o 
   where payment_method = 'kakaopay'
)

1.  Where (중첩 서브쿼리)

형태 : Where 필드명 in (subquery)

가장 일반적인 서브쿼리로, A 테이블의 X 데이터와 B 테이블의 X 데이터를 조회

--ex) point_users와 users를 이용하여 이씨 성을 가진 유저의 평균 보인트보다 큰 유저들의 데이터 추출
SELECT * from point_users pu 
WHERE point > (
   SELECT AVG(point)  from point_users pu 
   inner join users u on pu.user_id = u.user_id
   where u.name = '이**'
)
--다른 풀이
SELECT * from point_users pu 
WHERE point > (
   SELECT * from point_users pu
   WHERE user_id in (
      select user_id from users u where u.name = '이**'
   )
)

2. Select

형태 : select 필드명, 필드명, (subquery) from

select절에 사용되는 서브쿼리로, 하나의 상수 또는 Join을 대체하기 위한 용도. 하나의 열처럼 사용.

/*ex) checkins에 course를 Join하고 checkin_id, user_id, title, likes, course_avg를 출력하세요. 
course_avg는 course_id별 좋아요의 평균값임*/

SELECT ch.checkin_id,
       ch.user_id,
       c.title,
       ch.likes,
       (
          select ROUND(AVG(likes),1) from checkins ch2
      WHERE ch2.course_id = ch.course_id
       ) as course_avg
 from checkins ch 
 inner join courses c on ch.course_id = c.course_id

3. From

형태 : select * from ( select * from ) 테이블명

하나의 테이블처럼 사용. 열 이름을 반드시 명시해야 함.

/*ex) checkins와 orders의 course_id별 중복 없이 유저의 체크인 개수를 구하고, 
course의 title과 course_id, cnt_checkins, cnt_total, ratio(비율)*/

SELECT c2.title,
       a.course_id, 
       a.cnt_checkins, 
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
 FROM 
(
   SELECT course_id, COUNT(DISTINCT(user_id)) as cnt_checkins from checkins c
   group by c.course_id
) a
inner join (
   SELECT course_id, count(DISTINCT(user_id)) as cnt_total from orders o
   group by o.course_id 
) b on a.course_id = b.course_id
inner join courses c2 on a.course_id = c2.course_id

With 절

제일 윗단에 작성. 임시 테이블 같은 역할.

with table1 as (
   SELECT course_id, COUNT(DISTINCT(user_id)) as cnt_checkins from checkins c
   group by c.course_id
), table2 as (
   SELECT course_id, count(DISTINCT(user_id)) as cnt_total from orders o
   group by o.course_id 
)

SELECT c2.title,
       a.course_id, 
       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 c2 on a.course_id = c2.course_id 

/*위의 from문과 같음. with문이 더 명시적이라는 장점*/

실전 SQL 문자열

1. substring_index (문자 쪼개기)

SELECT user_id, email, SUBSTRING_INDEX(email,'@',-1) from users u 
-- 1은 첫번째, -1은 마지막 것을 보여줌

2. substring (문자열 일부만 출력)

--ex) 날짜의 일부(시간 생략)만 출력해 주문 개수 세기
SELECT SUBSTRING(created_at, 1, 10) as date, COUNT(*) as total from orders o
group by date

Join(여러테이블 연결)
key 값(공통된 정보, 기준)으로 테이블을 연결

1. Left Join

SELECT * from users u
left join point_users pu
on u.user_id = pu.user_id
/*left에 point_users 붙이기 없으면 null로 보여줌*/

2. Inner Join

SELECT * from users u
inner join point_users pu
on u.user_id = pu.user_id
/*교집합만 출력*/

Quiz
1. orders 테이블에 users 테이블을 연결해서 네이버 이메일을 사용하는 유저의 성씨별 주문건수가 많은 순으로 배열

SELECT u.name, COUNT(*) as cnt, o.email from orders o
inner join users u on o.user_id = u.user_id 
where o.email like '%naver%'
group by u.name 
order by cnt desc

/*쿼리 실행 순서: from → join → where → group by → select*/

2. 결제하고 시작하지 않은 유저들(is_registered가 0) 성씨를 세어 내림차순으로 정렬

SELECT u.name, COUNT(*) as cnt_name from enrolleds e 
inner join users u on e.user_id = u.user_id
where is_registered = 0
GROUP by u.name 
order by cnt_name desc

3.  course에 checkins+orders를 붙이고 8월 1일 이후 구매한 고객들만 선별하여 반별, 주차별로 세어 정렬하기

SELECT c.title, c2.week, COUNT(*) as cnt  from courses c 
inner join checkins c2 on c.course_id = c2.course_id 
inner join orders o on c2.user_id = o.user_id 
WHERE o.created_at >= '2020-08-01'
group by c.title, c2.week
order by c.title, c2.week

4. users에 poin_users를 left join 하여 7/10 ~7/19 가입한 고객중 포인트를 가징 고객의 수, 전체 수, 비율

SELECT COUNT(pu.user_id) as pnt_user_cnt, 
       COUNT(u.user_id) as tot_user_cnt, 
       ROUND(COUNT(pu.user_id)/COUNT(u.user_id), 2) as ratio
 from users u 
 left join point_users pu on u.user_id = pu.user_id
 where u.created_at BETWEEN '2020-07-10' and '2020-07-20'

UNION
형태 :  ( ) union all ( )
Union과 Join의 차이
Union은 수직결합, Join은 수평결합
ex) Union은 7, 8, 9월등의 자료를 결합할 때
     Join은 추가적인 데이터를 결합할 때

(select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week)
union all
(select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week)

팁! 
Count는 Null을 제외하고 셈
Union의 괄호 안에서 order by를 해도 union으로 결합하면 적용X

Group by 기능

    1. 범주의 개수
          ex_주차별 오늘의 다짐 개수 구하기

SELECT week, count(*) from checkins
group by week

    2. 범주의 최소값 구하기
            ex_주차별 좋아요 최소값 구하기

SELECT week, min(likes) FROM checkins
group by week

    3. 범주의 최대값 구하기
            ex_주차별 좋아요 최대값 구하기

SELECT week, max(likes) FROM checkins
group by week

    4. 범주의 평균 구하기
            ex_주차별 좋아요 평균값 구하기

SELECT week, round(avg(likes),2) from checkins
group by week
--평균값은 소수점 round와 함께 쓰임

    5. 범주의 합계 구하기
            ex_주차별 좋아요 합계 구하기

SELECT week, sum(likes) from checkins
group by week

정렬 (order by)

SELECT payment_method, count(*) FROM orders
WHERE course_title = "웹개발 종합반"
group by payment_method
order by count(*) desc  /*내림차순

=> 기본은 오름차순. 꼭 group by 와 함께 사용할 필요 X
       쿼리 실행 순서: from → group by → select → order by*/

 

Quiz
    1.gmail을 사용하는 성씨별 회원수 세기

SELECT name, COUNT(*) from users
WHERE email like '%gmail%'
GROUP by name

    2. course_id별 like 갯수의 평균(소수점 두자리까지)을 구하기

SELECT course_id, ROUND(AVG(likes),2) from checkins
group by course_id

이외의 문법
    별칭 기능 (Alias)

SELECT payment_method, COUNT(*) as cnt from orders o
WHERE o.course_title = '웹개발 종합반'
group by payment_method

/* 1) orders 뒤의 o 별칭. 왜? course_title이 어디있는지 명시적으로 보여주기 위해
   2) as 로 필드명 별칭*/

    Quiz. 네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세기

SELECT payment_method as payment, COUNT(*) from orders o
where o.course_title = '앱개발 종합반' and o.email like '%naver%'
group by payment_method

Where 절 문법

    1. 같지 않음 !=
    2. 범위 between

SELECT * FROM orders
WHERE created_at BETWEEN '2020-07-13' and '2020-07-15'

--13일과 14일 데이터만 가져오기

    3. 포함

SELECT * FROM checkins
WHERE week in (1,3)

--괄호안에 숫자와 일치하는 데이터만 보고 싶다

    4. 패턴

SELECT * FROM users
WHERE email like '%daum.net'

--대부분 like와 %를 함께 씀. %는 앞에 무엇이든 간에 daum.net으로 끝나는 것

SELECT * FROM users
WHERE email like 's%com'and name = '이**'

--s로 시작해서 com으로 끝나며 성이 이씨인 유저만

이외의 문법

    1. 일부 데이터 가져오기 (limit)
        ex) 큰 테이블의 데이터를 조회할 때

SELECT * FROM users
WHERE email like '%daum.net'
limit 5

    2. 중복 제거하기 (distinct)

SELECT distinct(payment_method) FROM orders

    3. 갯수 세기 (count)

SELECT count(*) FROM orders
WHERE payment_method = 'kakaopay'

--name의 중복을 제거한 갯수 세기

SELECT COUNT(DISTINCT(name)) from users

Quiz
    1. 성이 남씨인 유저의 이메일만 추출하기

SELECT email FROM users
WHERE name = '남**'

    2. Gmail을 사용하는 2020/07/12 ~13에 가입한 유저 추출하기

SELECT * FROM users
WHERE email like '%gmail.com' and created_at BETWEEN '2020-07-12' and '2020-07-14'

    3. Gmail을 사용하는 2020/07/12 ~13에 가입한 유저 수 세기

SELECT COUNT(*) FROM users
WHERE email like '%gmail.com' and created_at BETWEEN '2020-07-12' and '2020-07-14'

    4. naver 이메일을 사용하며 웹개발 종합반이고 결제는 kakaopay인 데이터 추출

SELECT * FROM orders
WHERE email like '%naver%'
and course_title = '웹개발 종합반'
and payment_method = 'kakaopay'

범주의 통계
    where 절을 여러개 작성하는 것이 비효율적

    성씨별 회원수를 Group by로 구해보기

SELECT name, COUNT(*) from users
group by name

-- users를 group by name으로 묶기, group by와 함께 count를 쓰면 name으로 묶이는 필드의 개수를 셈

팁!
    문자열은 ' '로 열거하지만 숫자는 사용하지 않음

테이블이란? 홈페이지 새창 이름
필드란? 테이블을 구성하는 칸

    show tables 
    select * from orders          - orders 전부다
    select created_at, email from orders           -일부 필드만 가져오기

Where절  : 조건문

select * from orders
where payment_method = 'kakaopay'

--orders= 테이블,  payment_method = 필드명,  kakaopay = 문자열

SELECT * from point_users
where point > 5000

SELECT * from orders
where course_title = '앱개발 종합반' and payment_method = 'CARD'

--교집합 and, 합집합 or

단축키
ctrl + enter = SQL 실행