[SQL/MySQL] SQL로 하는 데이터 분석 기초

1. 기본 조회 쿼리들

--  
SELECT name,
       price,
       price/cost,
       (CASE 
            WHEN price/cost >= 1 AND price/cost < 1.5 THEN 'C. 저효율 메뉴'
            WHEN price/cost >= 1.5 AND price/cost < 1.7 THEN 'B. 중효율 메뉴'
            WHEN price/cost >= 1.7 THEN 'A. 고효율 메뉴'
        END) AS efficiency
FROM pizza_price_cost
ORDER BY efficiency DESC, price ASC
LIMIT 6;

-- 각 지역에서 주문된, 각 메뉴들의 수
SELECT order_area,
       name,
       COUNT(*) 
FROM order
GROUP BY order_area, name;

-- 각 지역에서 주문된, 각 메뉴들의 수 안에 속한 row 수가 3개 이상인 그룹들
SELECT order_area,
       name, 
       COUNT(*) 
FROM order 
GROUP BY order_area, name 
HAVING COUNT(*) >=3 
ORDER BY COUNT(*) DESC;

-- SELECT 문에서 쓰이는 각 절의 정확한 실행 순서
-- FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT

-- 영화 장르별, 주로 5월에 상영하는 영화 안에 속한 row 중 총 관객 수가 3 백만 이상인 그룹들
SELECT category, 
       main_month, 
       COUNT(*) AS '영화 수', 
       SUM(view_count) AS '총 관객 수' 
FROM 2020_movie_report
GROUP BY category, main_month
HAVING main_month = 5 AND SUM(view_count) >= 3000000;

-- 지역별, 성별 안에 속한 row

2. 다른 종류 테이블들끼리 / 같은 종류 테이블들 합치기

   • 결합 연산 : 두 테이블을 가로 방향으로 합치기
     - LEFT OUTER JOIN
     - RIGHT OUTER JOIN
     - INNER JOIN

   • 집합 연산 : 두 테이블을 세로 방향으로 합치기
     - INTERSECT
     - MINUS
     - UNION
     - UNION ALL

-- 두 테이블을 조인해서, 각 피자별 판매량 조회
SELECT c.name, coalesce(s.sales_volume,'판매량 정보 없음') as '판매량'
FROM pizza_price_cost as c
 LEFT OUTER JOIN sales as s
 ON c.id = s.menu_id;

-- A ∩ B (INTERSECT 연산자 사용)
SELECT * FROM member_A
INTERSECT 
SELECT * FROM member_B

-- A - B (MINUS 연산자 또는 EXCEPT 연산자 사용)
SELECT * FROM member_A 
MINUS
SELECT * FROM member_B

-- B - A (MINUS 연산자 또는 EXCEPT 연산자 사용)
SELECT * FROM member_B
MINUS
SELECT * FROM member_A

-- A U B (UNION 연산자 사용)
SELECT * FROM member_A
UNION
SELECT * FROM member_B

3. 세 개의 테이블 JOIN과 1:1 관계 , 1:n 관계

SELECT *
	FROM
		item AS i LEFT OUTER JOIN review AS r
			ON r.item_id = i.id
		LEFT OUTER JOIN member AS m
			ON r.mem_id = m.id;
            
-- 각 상품별 여성 회원이 남긴 리뷰의 갯수가 2개 이상인 row와 별점이 높은 상품
SELECT i.id, i.name, AVG(star), COUNT(*)
	FROM
		item AS i LEFT OUTER JOIN review AS r
			ON r.item_id = i.id
		LEFT OUTER JOIN member AS m
			ON r.mem_id = m.id
	WHERE m.gender = 'f'
GROUP BY i.id, i.name
HAVING count(*) > 1
ORDER BY 
	AVG(star) DESC,
    count(*) DESC;

 -- 남녀 공용 상품의 등록 연도별 평균 별점
SELECT YEAR(registration_date) '등록 연도', count(*) '리뷰 개수', AVG(star) AS '별점 평균값'
	FROM
		item AS i INNER JOIN review AS r
			ON r.item_id = i.id
		INNER JOIN member AS m
			ON r.mem_id = m.id
	WHERE i.gender = 'u'
    group by YEAR(registration_date)
		HAVING COUNT(*) >= 10
	ORDER BY AVG(star) DESC;
    

4. 서브쿼리

   • 상관 서브쿼리 (EXISTS, NOT EXISTS) : 서브쿼리가 outer query에 적힌 테이블 이름 등과 상관 관계를 갖고 있어서 그 단독으로는 실행되지 못하는 서브쿼리
     1. 일단 item 테이블의 첫 번째 row를 생각한다.
     2. 그 row의 id 컬럼의 값과 같은 값을 item_id 컬럼에 가진 stock 테이블의 row를 찾는다
     3. 찾은 stock 테이블의 row의 inventory_count 컬럼의 값을 리턴한다.

   --  FROM 절 : derived table alies를 반드시 붙어야 함 
   SELECT MAX(copang_report.price) AS max_price, 
          AVG(copang_report.star) AS avg_star,
          COUNT(DISTINCT(copang_report.email)) AS distinct_email_count 
   FROM (
          SELECT price, 
                 star, 
                 email 
          FROM item AS i INNER JOIN review AS r ON r.item_id = i.id
          INNER JOIN member AS m ON r.mem_id = m.id
          ) AS copang_report;
   -- SELECT 절
   
   -- WHERE 절
   SELECT * 
   FROM member 
   WHERE SUBSTRING(address, 1, 2) =
       (
           SELECT SUBSTRING(address, 1, 2) 
           FROM member 
           GROUP BY SUBSTRING(address, 1, 2) 
           ORDER BY COUNT(*) DESC 
           LIMIT 1
       );
   -- HAVING 절
   
   -- IN 절 : 여러 ROW 리턴
   
   -- ANY( ... ) :  하나라도 조건을 만족하는 경우가 있으면 TRUE를 리턴
   
   -- SOME( ... ) :  row의 값들 중 하나라도 조건을 만족하면 TRUE를 리턴
   
   -- ALL( ... ) :  row의 값들 중 하나라도 조건을 만족하면 TRUE를 리턴

5. 뷰 : 자주 쓰는 테이블

   CREATE VIEW three_tables_joined AS
     SELECT i.id, i.name, avg(star) as avg_start, count(*) as count_star
     FROM item as i left outer join review as r on r.item_id = i.id
         left outer join member as m on r.mem_id = m.id
     WHERE m.gender='f'
     group by i.id, i.name
     having count(*) >=2
     order by avg(star) desc, count(*) desc;
   
   /* 실습 예제 */
   DESC employee;
   SELECT id, name, age, department, phone_num, hire_date
     FROM employee;
     
   CREATE VIEW v_emp AS
   			SELECT id, name, age, department, phone_num, hire_date
     FROM employee;
   
   SELECT * from v_emp;

Reference

• MySQL 이스케이핑
• MySQL 대소문자 구분하여 비교 및 검색 (BINARY)
• LIMIT Select 명령문에서 반환되는 row의 개수
• MySQL 8.0 Mathematical Functions
• SQL COALESCE 함수로 NULL 값 치환
• MYSQL CAST 함수- 값의 형변환
• MYSQL 📚 WITH ROLLUP & Grouping 함수
• MYSQL JOIN에서 ON과 USING을 쓰는 경우
• JOIN, EQUI JOIN, NON-EUQI JOIN, OUTER JOIN, SELF JOIN 알아보기