MySQL - SELECT(4주차)

◎수업내용

• Subquery
• with 절
• SQL 문법 - 문자열
• SQL 문법 - CASE

 

Subquery

1. 특정값을 활용하기
2. 특정 범위를 활용하기
3. 새로운 테이블 처럼 활용하기

 

◎ 특정값 활용하기

SELECT avg(likes)
FROM checkins
WHERE user_id = '4b8a10e6';

이 값을 활용해서 sql 문을 활용해보자.

SELECT
    checkin_id ,
    user_id ,
    likes,
    (SELECT avg(likes) FROM checkins WHERE user_id = '4b8a10e6') AS avg_likes_user
FROM checkins;

지금처럼 특정값을 하나의 컬럼으로 활용해서 사용할 수도 있다.

다음은 WHERE 절에서의 특정값 활용을 해보자

SELECT avg(`point`)
FROM point_users;

SELECT *
FROM point_users pu
WHERE point > (SELECT avg(`point`) FROM point_users);  >> 5380.8339 와 같은 효과

 

◎ 특정 범위를 활용하기

SELECT user_id
FROM orders
WHERE payment_method = 'kakaopay'

>> 결과 : 카카오페이를 사용한 주문정보를 모아온 것









SELECT user_id, name, email
FROM users
WHERE user_id IN ( SELECT user_id FROM orders WHERE payment_method = 'kakaopay');

* 필드명 in (값1, 값2, 값3, ... ) : 지정한 값과 일치하는 것들을 필터링하는 것

 

◎ 새로운 테이블 처럼 활용하기

SELECT course_id, count(DISTINCT(user_id)) AS cnt_checkins
FROM checkins
GROUP BY course_id;

>> 결과 : 과목별로 유저의 체크인 갯수를
                확인하는 것



SELECT course_id, count(*) AS cnt_total
FROM orders
GROUP BY course_id;

>> 결과 : 과목별로 유저 전체 수를 구한 것.



위 두개의 테이블은 실제로 존재하는 테이블은 아니다
하지만 우리가 course_id라는 값을 가지고 두개의 결과를 조회하고 나서, 
두 결과를 join 하는 것이 가능하다.
SELECT a.course_id, a.cnt_checkins, b.cnt_total, round((a.cnt_checkins/b.cnt_total), 2) AS ratio
FROM (
             SELECT course_id, count(DISTINCT(user_id)) AS cnt_checkins
             FROM checkins
             GROUP BY course_id) a
INNER JOIN (
                     SELECT course_id, count(*) AS cnt_total
                     FROM orders
                     GROUP BY course_id ) b
ON a.course_id = b.course_id;

 

 

with 절

with 구문은 내가 subquery로 작성한 새로운 테이블을 미리 지정하는 작업을 도와주는 것

위에서 진행한 예제를 가지고 해보자

SELECT a.course_id, a.cnt_checkins, b.cnt_total, round((a.cnt_checkins/b.cnt_total), 2) AS ratio
FROM (
SELECT course_id, count(DISTINCT(user_id)) AS cnt_checkins
FROM checkins
GROUP BY course_id) a
INNER JOIN (           
SELECT course_id, count(*) AS cnt_total
FROM orders
GROUP BY course_id) b
ON a.course_id = b.course_id
INNER JOIN courses c
ON a.course_id = c.course_id;

---

with 구문 사용

WITH table1 as(
                   SELECT course_id, count(distinct(user_id)) as cnt_checkins
                   FROM checkins
                   GROUP BY course_id ),
table2 as(
                  SELECT course_id, count(*) as cnt_total
                  FROM orders
                  GROUP BY course_id )
select c.title, 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 c ON a.course_id = c.course_id;


이렇게 WITH 구문을 사용하면 이전보다 query를 쉽게 볼 수 있다.