[TIL] 10일차 TIL(20230217) - RANK, ROLL UP, 공공데이터를 이용한 SQL 실습

 

[TIL] 10일차 TIL(20230217) - VIEW, RANK, ROLL UP, 공공데이터를 이용한 SQL 실습

 

 

1. VIEW

- 자주 사용하는 쿼리문을 수행한 결과를 가상의 테이블로 저장해두고 사용할 수 있음
- CREATE OR REPLACE VIEW 뷰이름(뷰 칼럼1, 뷰 칼럼2, ...)
  AS SELECT ~ FROM ~

- SELECT 구문 그대로 사용, SELECT할 칼럼의 이름들을 위에 명시하는 것(선택사항)

-- 세 테이블을 합한 새로운 VIEW 생성
CREATE OR REPLACE VIEW V_ORDERS
AS SELECT ORDERID, O.CUSTID, USERNAME, O.BOOKID, SALEPRICE, ORDERDATE
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE C.CUSTID = O.CUSTID AND B.BOOKID = O.BOOKID;

SELECT * FROM V_ORDERS;

 

 

2. 순위함수

- RANK: 공동 순위를 매기고, 공동 순위만큼 건너뛰는 방식으로 순위를 매김
- DENSE_RANK: 공동 순위를 매기고, 공동 순위를 건너뛰지 않는 방식으로 순위를 매김
- ROW_NUMBER: 공동 순위를 무시하는 방식으로 순위를 매김

RANK()/ROWNUMBER()/DENSE_RANK()
OVER (
PARTITION BY COLUMN
ORDER BY COLUMN)

-- 고객별로 주문한 도서의 가격 랭킹
SELECT O.CUSTID, B.BOOKNAME,
	RANK() OVER(PARTITION BY O.CUSTID ORDER BY O.SALEPRICE) RANKING
FROM ORDERS O, BOOK B
WHERE O.BOOKID = B.BOOKID;

3. 소계함수

- GROUP BY COLUMN WITH ROLLUP
- 그룹화 한 값의 소계값을 확인하고 싶을 때 사용
- 결측치 칸을 생성해서 소계를 반환, IFNULL과 함께 사용

-- 지역별로 판매된 도서의 수량 조회
SELECT ADDRESS, IFNULL(BOOKNAME, '===소계==='), COUNT(*)
FROM CUSTOMER C, ORDERS O, BOOK B
WHERE C.CUSTID = O.CUSTID AND B.BOOKID = O.BOOKID
GROUP BY ADDRESS ,BOOKNAME WITH ROLLUP
HAVING ADDRESS IS NOT NULL
ORDER BY ADDRESS, BOOKNAME DESC;

 

3-1. SQL 실습 예제

##################################################
# 고객별 총 주문횟수, 총구매액, 평균구매액, 최소/최대구매액 구하기
# 구매하지 않은 고객 포함하기
# 구매하지 않은 고객은 집계 결과 0으로 표현하기
# 총 구매액 순으로 순위 매기기(RANK)
##################################################


SELECT O.CUSTID, C.USERNAME, 
	IFNULL(COUNT(O.CUSTID), 0) 주문횟수,
    IFNULL(SUM(SALEPRICE), 0) 총구매액,
    IFNULL(AVG(SALEPRICE),0) 평균구매액,
    IFNULL(MIN(SALEPRICE),0) 최소구매액,
    IFNULL(MAX(SALEPRICE), 0) 최대구매액,
    RANK() OVER (ORDER BY SUM(SALEPRICE) DESC) AS RNK
FROM ORDERS O
RIGHT JOIN CUSTOMER C
ON C.CUSTID = O.CUSTID
GROUP BY C.CUSTID;

 

 

4. 사분위수(QUANTILE)

- MySQL에서는 4분위수를 반환하는 함수가 따로 존재하지 않기 때문에 직접 계산해서 구해야 함
- GROUP_CONCAT으로 모든 값을 일렬로 나열한 뒤, 각 분위에 해당하는 위치 값을 SUBSTRING_INDEX로 추출하는 방식으로 코드를 작성

SELECT 	MIN(saleprice) AS 'MIN',
        SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(saleprice ORDER BY saleprice SEPARATOR ','),',', 25/100 * COUNT(*) + 1), ',', -1) AS `25%`,
        SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(saleprice ORDER BY saleprice SEPARATOR ','),',', 50/100 * COUNT(*) + 1), ',', -1) AS `50%`,
        AVG(saleprice) AS 'MEAN', 
        SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(saleprice ORDER BY saleprice SEPARATOR ','),',', 75/100 * COUNT(*) + 1), ',', -1) AS `75%`, 
        MAX(saleprice) AS 'MAX'
FROM Orders;

 

 

5. 공공자전거 이용 현황 데이터(202012, 202101)를 이용한 SQL 실습

 

5.1 월별 평균, 최대 대여시간, 평균 이용거리 집계

SELECT MONTH(대여일시) 연월, AVG(이용시간) 평균대여시간, MAX(이용시간) 최대이용시간,
		AVG(이용거리) 평균이용거리, MAX(이용거리) 최대이용거리
FROM BICYCLE_RENTAL
GROUP BY 연월;

 

5.2 총 이용시간별 대여소 등급 매기기

SELECT 대여소명, COUNT(자전거번호) 대여횟수, SUM(이용시간) 총시간,
	CASE
		WHEN (SUM(이용시간) >= 80000) THEN '최우수'
        WHEN (SUM(이용시간) >= 15000) THEN '우수'
        WHEN (SUM(이용시간) >= 8000) THEN '일반'
        ELSE '기타'
	END AS '등급'
FROM BICYCLE_RENTAL
GROUP BY 대여소명
ORDER BY 총시간 DESC;

 

5.3 202012, 202101 데이터를 합쳐 VIEW로 저장

CREATE OR REPLACE VIEW BICYCLE_RENTAL
AS SELECT * FROM BICYCLE_202012
UNION ALL
SELECT * FROM BICYCLE_202101;

DESC BICYCLE_RENTAL;

 

5.4 요일별 평균 이용시간

SELECT
	CASE DAYOFWEEK(대여일시)
		WHEN 1 THEN 'SUN'
        WHEN 2 THEN 'MON'
        WHEN 3 THEN 'THU'
        WHEN 4 THEN 'WED'
        WHEN 5 THEN 'THU'
        WHEN 6 THEN 'FRI'
        WHEN 7 THEN 'SAT'
	END AS 요일,
	AVG(이용시간) AS 평균이용시간
FROM BICYCLE_RENTAL
GROUP BY 요일
ORDER BY 평균이용시간 DESC;

 

5.5 대여소별 이용시간이 평균보다 높은 곳들 구하기

SELECT 대여소명, AVG(이용시간) 평균이용시간
FROM BICYCLE_RENTAL
GROUP BY 대여소명
HAVING AVG(이용시간) >= (SELECT AVG(이용시간) FROM BICYCLE_RENTAL)
ORDER BY 평균이용시간 DESC;

 

5.6 월별, 오전/오후별 평균 이용시간

SELECT DATE_FORMAT(대여일시,'%Y-%m') MONTHLY,
	CASE DATE_FORMAT( 대여일시, '%p')
		WHEN 'AM' THEN "오전"
		WHEN 'PM' THEN "오후"
	  END AMPM,
    AVG(이용시간) AS 시간
FROM BICYCLE_RENTAL
GROUP BY MONTHLY, AMPM;

 

5.7 대여소와 자전거별 이용시간 합계

SELECT IFNULL(대여소명,'소계'), IFNULL(자전거번호,'소계'), SUM(이용시간)
FROM BICYCLE_RENTAL
WHERE DATE_FORMAT(대여일시,'%Y-%m-%d') BETWEEN "2020-12-01" AND  "2020-12-05"
GROUP BY 대여소명, 자전거번호 WITH ROLLUP;

 

5.8 대여소별 이용량이 많은 곳 TOP10(대여소 이용량 순위)

SELECT 대여소명, COUNT(*) 총이용량,
	RANK() OVER(ORDER BY COUNT(*) DESC) RNK
FROM BICYCLE_202012
GROUP BY 대여소명
LIMIT 10;

 

5.9 월별 대여소 이용량이 많은 곳 TOP20

SELECT MONTH(대여일시) 년월, 대여소명, COUNT(*) 총이용량,
	RANK() OVER(ORDER BY COUNT(*) DESC) RNK
FROM BICYCLE_RENTAL
GROUP BY 년월, 대여소명
HAVING 총이용량 <= 10
ORDER BY 년월, RNK DESC;

 

 

---

 

소감 및 정리

초반에 SQL관련 기초 문법들을 쉴새없이 배웠는데 마지막 날에는 배웠던 내용을 활용해보는 실습 내용이 많아서 조금 수월하게 할 수 있었다. 실습을 진행하면서 그동안 배웠던 여러 함수들을 합쳐서 하나의 결과를 완성해가는 재미를 느낄 수 있었다. 특히 VIEW 테이블의 경우 이번에 처음 알게 되었는데, 반복해서 사용할 일이 있는 테이블의 경우 한번 만들어두면 관련 구문을 여러번 사용하지 않아도 된다는 점에 있어서 실무에서 자주 사용할 것 같다는 생각이 들었다.