SQL TIL 3주차 : Join

SELECT * FROM users u

left join point_users p on u.user_id = p.user_id

Left Join : from 데이터를 기준으로 붙이기

 

SELECT * FROM users u

inner join point_users p on u.user_id = p.user_id

Inner Join : 교집합 데이터만 표시

 

SELECT pu.user_id, u.name, u.email , pu.`point` FROM point_users pu

INNER JOIN users u on pu.user_id = u.user_id

ORDER BY pu.point desc

공통된 컬럼을 선택하여 테이블을 붙일 수 있다.

그리고 원하는 정보만 선별해서 표시가능

 

SELECT 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 c2.week, c1.title

ORDER BY title ,c2.week

JOIN 을 한번 더 할 수 있다.

그리고 날짜를 부등호로 비교할 수 있다.

 

SELECT * FROM users u

LEFT JOIN point_users pu on u.user_id = pu.user_id

WHERE pu.point_user_id IS NULL

NULL 데이터는 IS를 붙여야 한다.

SELECT u.name, COUNT(*) FROM users u

LEFT JOIN point_users pu on u.user_id = pu.user_id

WHERE pu.point_user_id IS not NULL

GROUP BY u.name

NOT을 붙여서 값이 있는 데이터를 지정할 수도 있음

 

SELECT COUNT(pu.point_user_id) as pnt_user_cnt,

               COUNT(u.user_id) as tot_user_cnt,

               ROUND(COUNT(pu.point_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'

COUNT 값끼리 나눠서 비율을 구할 수 있다는 것도 알게되었다.

또한, COUNT는 NULL 값을 세지 않는다는 점을 활용하여 전체값과 특정값의 비교를 해보았다.

 

(

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

)

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

)

UNION 은 위 아래 쿼리를 들여쓰기하고 소괄호로 묶어서 가운데에 'UNION' 을 써주면 데이터가 합쳐진다.

 

 

WITH table1 as (

SELECT course_id ,COUNT(DISTINCT user_id) as cnt_checkins FROM checkins c

GROUP BY course_id

), table2 as (

SELECT course_id ,COUNT(*) as cnt_total FROM orders o

GROUP BY course_id

)

SELECT c2.title , table1.cnt_checkins, table2.cnt_total,ROUND((table1.cnt_checkins/table2.cnt_total),1) as ratio

FROM table1

INNER JOIN table2 on table1.course_id = table2.course_id

INNER JOIN courses c2 on table1.course_id = c2.course_id

WITH 절은 가상의 테이블 하나를 만들어준다고 생각하면 된다.

각 테이블을 만들어 놓고 아래에 원하는 데이터를 가공하기에 편리하다.

 

SELECT order_no , created_at , SUBSTRING(created_at,1,10) FROM orders o

SUBSTRING(컬럼, 시작위치, 길이) : 문자열 데이터 원하는 길이까지 자르기

 

SELECT user_id ,email, SUBSTRING_INDEX(email,'@', 1) FROM users u

SUBSTRING : 문자열 데이터 잘라서 리스트화? 시키기?

SELECT SUBSTRING(created_at,1,10) as date, COUNT(*) FROM orders o

GROUP BY date

해당 날짜에 생긴 데이터 개수 구하기 (ex. 판매량 등)

 

 

SELECT pu.user_id , pu.point,

CASE WHEN pu.point > 10000 THEN '잘 하고 있어요!'

ELSE '조금만 더 파이팅' END

FROM point_users pu

CASE : 경우에 따라 원하는 값을 출력해주는 것

 

SELECT a.lv, COUNT(*) FROM (

SELECT pu.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

이렇게 CASE 별로 나누어서 각 조건에 해당되는 값의 개수를 셀 수도 있고

WITH table1 as (

SELECT pu.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

이런식으로 WITH 구문을 활용해서 계산식과 데이터식을 분리해서 보기 편하게 작성할 수도 있다.