[필수 트랙] SQL 2일차

엑셀보다 쉬운 SQL 3주차

테이블 조인

INNER JOIN - 교집합

A LEFT JOIN B - A를 기준으로 B를 조인 (A에 연결되는 B가 없는 경우 B 데이터는 NULL값)

SELECT c.title, c2.week ,COUNT(*) 
  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 1
;

SELECT COUNT(p.point) pnt_user_cnt, 
       COUNT(p.point IS NOT NULL) tot_user_cnt, 
       ROUND(COUNT(p.point)/COUNT(p.point IS NOT NULL),2) ratio
  FROM users u LEFT JOIN point_users p ON u.user_id = p.user_id
 WHERE u.created_at BETWEEN '2020-07-10' AND '2020-07-20'
;

UNION

2개 이상의 쿼리문을 합쳐 하나에 데이터로 출력할 때 사용

출력되는 칼럼수가 같아야 된다.

UNION - 중복된 데이터 제거 후 합침

UNION ALL - 모든 데이터 합침

(
SELECT '7월' as month, c.title, c2.week, count(*) as cnt 
 FROM checkins c2 INNER JOIN courses c ON c2.course_id = c.course_id
		  INNER JOIN orders o ON o.user_id = c2.user_id
WHERE o.created_at < '2020-08-01'
GROUP BY c2.course_id, c2.week
ORDER BY c2.course_id, c2.week
)
UNION ALL
(
SELECT '8월' as month, c.title, c2.week, count(*) as cnt
 FROM checkins c2 INNER JOIN courses c ON c2.course_id = c.course_id
		  INNER JOIN orders o ON o.user_id = c2.user_id
WHERE o.created_at > '2020-08-01'
GROUP BY c2.course_id, c2.week
ORDER BY c2.course_id, c2.week
)

3주차 숙제

enrolled_id별 수강완료(done=1)한 강의 개수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.

SELECT e.enrolled_id , e.user_id , COUNT(*)
  FROM enrolleds_detail ed LEFT JOIN enrolleds e ON ed.enrolled_id = e.enrolled_id 
 WHERE ed.done = 1
GROUP BY e.enrolled_id
ORDER BY 3 desc

 

엑셀보다 쉬운 SQL 4주차

Subquery 활용

쿼리 안에 쿼리를 사용하는 방법으로 주로 select, from, where 절에 자주 사용한다.

SELECT c.checkin_id, 
       c.course_id, 
       c.user_id, 
       c.likes,
       (SELECT ROUND(AVG(likes),1)
          FROM checkins
         WHERE course_id = c.course_id) course_avg
  FROM checkins c

SELECT ed.enrolled_id, a.done_cnt, COUNT(done) as total_cnt
  FROM enrolleds_detail ed INNER JOIN (SELECT enrolled_id, COUNT(done) as done_cnt
					 FROM enrolleds_detail
					WHERE done = 1
					GROUP BY enrolled_id) a ON ed.enrolled_id  = a.enrolled_id
 GROUP BY ed.enrolled_id

SELECT *
  FROM point_users pu  
 WHERE point > (SELECT AVG(point)
	 	  FROM point_users pu2 INNER JOIN users u ON pu2.user_id = u.user_id 
	 	 WHERE u.name = '이**')

 

With절 활용

임시 테이블을 선언하여 가져와서 사용하여 코드를 보기 쉽게 정리가 가능하다.

WITH table1 as (
  SELECT enrolled_id, COUNT(done) as done_cnt
    FROM enrolleds_detail
   WHERE done = 1
   GROUP BY enrolled_id
)
SELECT ed.enrolled_id, 
       a.done_cnt, 
       COUNT(ed.done) as total_cnt, 
       ROUND(a.done_cnt/COUNT(ed.done),2) as ratio
  FROM enrolleds_detail ed INNER JOIN table1 a ON ed.enrolled_id  = a.enrolled_id
 GROUP BY ed.enrolled_id

유용한 SQL 문법

문자열 문법

-- SUBSTRING_INDEX(문자열 컬럼, 문자열 가를 기준문자, 갈려진 문자열 위치 1부터 시작 마지막 -1)
SELECT SUBSTRING_INDEX(email, '@', -1), COUNT(*) 
  FROM users
 GROUP BY SUBSTRING_INDEX(email, '@', -1)

--SUBSTRING(문자열 컬럼, 첫 글자위치, 출력까지의 글자 위치)
SELECT order_no, SUBSTRING(created_at, 1, 10) as date 
  FROM orders

case문

SELECT point_user_id , 
       point, 
       CASE WHEN point >= (SELECT AVG(point) FROM point_users) THEN '잘 하고 있어요!' 
            ELSE '열심히 합시다!' END as msg
  FROM point_users

 

 

---

마치며.

join, union, subquery 등의 문법들은 대부분 사용해본 적이 있어서 다시 보는데 어렵지 않았는데, With절의 경우에는 사용해 본적이 없어서 강의를 보면서 사용하던 중 중간에 잘못 이해해서 subquery부분을 임시로 선언하여 사용하는 것으로 이해했었다.. 

여러 가지 방법으로 테스트를 해보는데 계속 오류가 나서 다시 확인해 보니 with절은 subquery를 임시로 선언하는 게 아니라 테이블을 임시로 선언하여 from절에서만 사용되는 subquery로 대체가 가능했다. 일찍 확인해서 다행이다.