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 구문을 활용해서 계산식과 데이터식을 분리해서 보기 편하게 작성할 수도 있다.
'개발일지' 카테고리의 다른 글
2023.10.11. TIL 그라데이션 (0) | 2023.10.11 |
---|---|
SQL TIL 4주차 : Subquery, 그 외 (1) | 2023.10.11 |
SQL TIL 2주차 : Group by, Order by (0) | 2023.10.11 |
SQL TIL 1주차 : Select, Where (0) | 2023.10.11 |
HTML, CSS 기초 (1) | 2023.10.11 |