[ASAC 12일차] Case when, SQL의 Rank 방식

4월 5일의 기록

---

SQL 강의는 내가 좀 더 아는 부분이 많아 빠르게 익숙해진다.

그래서 이론 수업은 필요한 부분만 챙겨서 듣고 남는 시간에는 백준 문제를 풀었다.

낮은 단계의 문제들을 빠르게 해결하다보니 오늘만 해도 20개를 오전 시간에 풀 수 있었고 이제는 가벼운 문제의 흐름에 대해서 익숙해져 가는 것 같다. 역시 많은 문제를 풀어보면서 경험을 쌓아두는게 좋다.

매일 이렇게 순탄하게 풀리면 참 좋을 텐데.. 하하

목차

---

 

1. SQL 기본 문법 복습
2. SQL 고급
   1. Case when (조건문)
   2. 서브쿼리
   3. 비교: TopN
3. SQL EDA

1. SQL 기본 문법 복습

첫 시간에는 어제까지 간단하게 배웠던 select, sum, as, like 등등을 복습할 수 있는 약 30개의 문제를 실습해보았다.

### 먼저, 테스트용 테이블 생성 및 데이터 입력 부터 시작

CREATE TABLE tCity
(
	name CHAR(10) PRIMARY KEY,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL
);

INSERT INTO tCity VALUES ('서울',605,974,'y','경기');
INSERT INTO tCity VALUES ('부산',765,342,'y','경상');
INSERT INTO tCity VALUES ('오산',42,21,'n','경기');
INSERT INTO tCity VALUES ('청주',940,83,'n','충청');
INSERT INTO tCity VALUES ('전주',205,65,'n','전라');
INSERT INTO tCity VALUES ('순천',910,27,'n','전라');
INSERT INTO tCity VALUES ('춘천',1116,27,'n','강원');
INSERT INTO tCity VALUES ('홍천',1819,7,'n','강원');

SELECT * FROM tCity;

CREATE TABLE tStaff
(
	name CHAR (15) PRIMARY KEY,
	depart CHAR (10) NOT NULL,
	gender CHAR(3) NOT NULL,
	joindate DATE NOT NULL,
	grade CHAR(10) NOT NULL,
	salary INT NOT NULL,
	score DECIMAL(5,2) NULL
);

INSERT INTO tStaff VALUES ('김유신','총무부','남','2000-2-3','이사',420,88.8);
INSERT INTO tStaff VALUES ('유관순','영업부','여','2009-3-1','과장',380,NULL);
INSERT INTO tStaff VALUES ('안중근','인사과','남','2012-5-5','대리',256,76.5);
INSERT INTO tStaff VALUES ('윤봉길','영업부','남','2015-8-15','과장',350,71.25);
INSERT INTO tStaff VALUES ('강감찬','영업부','남','2018-10-9','사원',320,56.0);
INSERT INTO tStaff VALUES ('정몽주','총무부','남','2010-9-16','대리',370,89.5);
INSERT INTO tStaff VALUES ('허난설헌','인사과','여','2020-1-5','사원',285,44.5);
INSERT INTO tStaff VALUES ('신사임당','영업부','여','2013-6-19','부장',400,92.0);
INSERT INTO tStaff VALUES ('성삼문','영업부','남','2014-6-8','대리',285,87.75);
INSERT INTO tStaff VALUES ('논개','인사과','여','2010-9-16','대리',340,46.2);
INSERT INTO tStaff VALUES ('황진이','인사과','여','2012-5-5','사원',275,52.5);
INSERT INTO tStaff VALUES ('이율곡','총무부','남','2016-3-8','과장',385,65.4);
INSERT INTO tStaff VALUES ('이사부','총무부','남','2000-2-3','대리',375,50);
INSERT INTO tStaff VALUES ('안창호','영업부','남','2015-8-15','사원',370,74.2);
INSERT INTO tStaff VALUES ('을지문덕','영업부','남','2019-6-29','사원',330,NULL);
INSERT INTO tStaff VALUES ('정약용','총무부','남','2020-3-14','과장',380,69.8);
INSERT INTO tStaff VALUES ('홍길동','인사과','남','2019-8-8','차장',380,77.7);
INSERT INTO tStaff VALUES ('대조영','총무부','남','2020-7-7','차장',290,49.9);
INSERT INTO tStaff VALUES ('장보고','인사과','남','2005-4-1','부장',440,58.3);
INSERT INTO tStaff VALUES ('선덕여왕','인사과','여','2017-8-3','사원',315,45.1);

SELECT * FROM tStaff;

 
-- 1. tCity의 모든 값들을 확인하세요

select * from tcity;

-- 2.  tStaff의 모든 값들을 확인하세요
select * from tstaff;

-- 3. tCity의 도시 이름과 인구에 대한 정보를 확인하세요
select name, popu from tcity;

-- 4. tCity의 도시이름, 지역, 면적에 대한 정보를 확인하세요.
select name, region, area from tcity;

-- 5.  tStaff의 이름과 월급에 대한 정보를 확인하세요.
select name, salary from tstaff;

-- 6. 직원테이블에서 이름, 부서, 직급만 출력하세요.
select name, depart, grade from tstaff;

-- 7. 도시테이블에서 도시명, 면접(제곱km) 인구(만명)으로 이름이 보이도록 출력하세요.
select name as '도시명', area as '면접(제곱km)', popu as  '인구(만명)' from tcity;

-- 8. 도시테이블에서 name, popu 값에 10000을 곱해서 인구(명)으로 이름이 보이도록 출력하세요.
select name as '도시명', area as '면접(제곱km)', (popu * 10000) as  '인구(명)' from tcity;

-- 9. 도시테이블에서 이름, 면적, 인구와 인구밀도라는 이름으로 (기존의 popu * 10000 / area 로 계산이 되는)것을 보고 나타내도록 하세요.
select name as '도시명', area as '면접(제곱km)', (popu * 10000) as  '인구(명)', ((popu * 10000)/ area) as 인구밀도  from tcity;

-- 10. 도시테이블에서 면적이 1000제곱키로미터 이상인 도시만 출력하세요
select * from tcity
where area >= 1000;

-- 11. 도시테이블에서 면적이 1000재곱키로미터 이상인 도시의 이름과 면적을 출력하세요.
select name, area from tcity
where area >= 1000;

-- 12. 인구가 10만명 미만의 도시의 이름을 출력하세요
select * from tcity
where popu < 10;

-- 13. 전라도에 있는 도시의 정보를 출력하세요
select * from tcity
where region like "%전라%";

-- 14. 월급이 400만원 이상인 직원의 이름을 출력하세요
select * from tstaff
where salary >= 400;

-- 15. 스탭의 테이블에서 SCORE의 값이 NULL인 정보를 출력하세요
select * from tstaff
where score is null;

-- 16. 스탭의 테이블에서 SCORE의 값이 있는 사람들의 정보를 출력하세요.
select * from tstaff
where score is not null;

-- 17. 도시테이블에서 인구가 100만이상이면서, 면적이 700제곱키로 이상인 도시를 찾아보세요
select * from tcity
where area >= 700 and popu >= 100;

-- 18. 도시테이블에서 경기권 도시 중에서 인구가 50만명 이상이거나 또는 경기원이 아니고 인구가 50만보다 적더라도 면적이 500이상인 도시를 찾아보세요.
select * from tcity
where (region like "%경기%" and popu >= 50) or (region not like "%경기%" and (popu < 50 or area >= 500));

-- 19.  직원 목록에서 월급이 300미만이면서 성취도는 60 이상인 직원이 누구인지 찾아보세요
select * from tstaff
where salary < 300 and score >= 60;

-- 20. 영업무의 여직원 분들의 이름을 찾아보세요
select name from tstaff
where depart like "%영업부%"
and gender like "여";
-- 21.  도시 이름에 ‘천’이 들어가는 도시들을 찾아보세요.
select * from tcity
where name like "%천%";

-- 22. 직원 목록에서 성이 “정”씨인 사람들을 찾아보세요
select * from tstaff
where name like "정%";

-- 23.  이름에 “신”자가 포함된 직원을 찾아보세요.
select * from tstaff
where name like "%신%";

-- 24.  인구가 50~100만 사이인 도시를 찾아보세요.
select * from tcity
where popu between 50 and 100;

-- 25. 직원들 중에서 입사일이 2015년부터 2018년 사이의 분들을 찾아보세요
select * from tstaff
where joindate between '2015-01-01' and '2018-12-31';

-- 26. 면적인 50~1000사이의 도시의 목록을 조사하세요
select * from tcity
where area between 50 and 1000;

-- 27. 월급이 200만원대의 직원들을 조사하세요.
select * from tstaff
where salary between 200 and 300;

-- 28. 지역이 경상/전라인 모든 도시를 찾아보세요.
select * from tcity
where region like "%경상%"  or region like "%전라%";

-- 29. 인구가 적은 도시부터 출력하세요.
select * from tcity
order by popu;

-- 30. 지역, 도시이름, 면적, 인구에 대한 것을 지역과 도시 이름에 대해서 정렬해보세요.
select region, name, area, popu from tcity
order by region, name;

-- 31. 면적에 의해서 도시들의 정보들을 정렬해보세요.
select * from tcity
order by area desc;

-- 32.  도시이름을 인구수에 따라서 도시의 이름만 출력해보세요.
select name from tcity
order by popu desc;

-- 33. 경기도에 있는 도시만 골라서 면적별로 그 도시의 정보들을 출력해보세요.
select * from tcity

where region like '%경기%'
order by area desc;

-- 34. 직원 목록을 월급이 적은 사람부터 순서대로 출력하되, 월급이 같다면 성취도가 높은 사람을 먼저 출력하세요.]
select * from tstaff
order by salary , score desc;

-- 35. 영업부 직원을 먼저 입사한 순서대로 정렬하세요.
select * from tstaff
order by joindate

2. SQL 고급

2-1) CASE WHEN

• MySQL의 내장 함수 중 제어 흐름 함수이다.
• Case는 내장 함수가 아니며 연산자로 분류 된다.
• case 문은 CASE ~~~ WHEN ~~ ELSE ~~~ END 형태를 띈다.

CASE문은 이런 형태로 생겼다.

간단 실습

-- 참고 : case when에 대해서 진행..
-- 조건이 다양하고, 할 일이 좀 더 복잡할 때 같이 사용
-- 여러 집계 함수도 같이 사용이 될 수 있다.
-- >> 이제는 선택들을 case by case로 좀 다양하게 하자!!!!

-- 예) buytbl의 내용들 중에서,,,,구매액(price * amount)을 기준으로
--     1500원 이상은 최우수고객, 
--     1000원 이상~1500미만 : 우수고객
--     1원 이상~   1000미만 : 일반고객
--     없으면,,,           : 유령고객..
use sqldb;
select userID, price, amount from buytbl;
select userID, price * amount from buytbl;
-- 고객 단위로 묶어서 봐야하는 것이고,,,,누적 구매금액 sum 집계처리
select userID, sum(price * amount) from buytbl group by userID;
-- 누적이 금액이 큰 고객을 먼저 보자..
select userID, sum(price * amount) as `TotalPrice` from buytbl group by userID
	order by sum(price * amount) desc;
select userID, sum(price * amount) as `TotalPrice` from buytbl group by userID
	order by `TotalPrice` desc;
-- 어느 고객인지 이름을 같이 보자!!!!!!
select U.userID, U.name, sum(price * amount) as `TotalPrice`
		from buytbl B left join usertbl U
				on B.userID = U.userID
			group by B.userID, U.name
			order by `TotalPrice` desc;
-- 문제가....구매한 이력이 없는 유령고객은 나타나지 않음!!!!!!
-- ++ 구매이력이 있다면,,,총구매금액, 혹 구매이력이 없다면,,,없는대로 고객정보는보자!!!!
select U.userID, U.name, sum(price * amount) as `TotalPrice`
		from buytbl B right join usertbl U
				on B.userID = U.userID
			group by B.userID, U.name
			order by `TotalPrice` desc;
            
-- 위에서 구한 TotalPrice를 기준으로 고객등급을 구별하고자 함!!!!
-- 값에 따라서 이럴 떄 저럴때`~~  : case when~~~~~
select U.userID, U.name, sum(price * amount) as `TotalPrice`,
	 case -- 순차적으로 걸리는 부분이다 보니,,,조건에 따라서 순서에 유의!!!
		when ( sum(price * amount) >= 1500) then "VVIP"
        when ( sum(price * amount) >= 1000) then "VIP"
        when ( sum(price * amount) >= 1 )   then "Basic"
        else "Ghost"
     end as `CustomerClass` 
		from buytbl B right join usertbl U
				on B.userID = U.userID
			group by B.userID, U.name
			order by `TotalPrice` desc;
            

 

참고) CASE WHEN에서 계산식을 왜 변수로 하면 못알아 먹을까?

예를 들어 select sum(price * amount) as “totalprice” from 테이블 이라고 하였을 때

이 변수 명은 기존 테이블에 있는 원천 소스가 아니다.

따라서, 변수를 가지고 재연산을 하려고 할 때 에러가 발생하는 것이다.

물론 해결 방법은 있다.

-- 해결방법 1) 그냥 원천 컬럼이 있는 것들을 귀찮아도 연산식을 계속 when에 넣어서 처리 하는 것
# 원본 컬럼 명으로 sql이 직접적으로 추적을 해서 값을 가지고 올 수 있다.

select u.userid, u.name, sum(price * amount) as 'totalprice'

	case
			when (sum(price * amount) >= 1500) then "VVIP"
			when (sum(price * amount) >= 1000) then "VIP"
			when (sum(price * amount) >= 1) then "Basic"
			else "Ghost"
	end as CustomerClass

from buytbl b right join usertbl u on b.userid = u.userid

group by b.userid, u.name
order by 'totalprice' desc) A;

-- 해결방법 2) 자기 자신을 돌려서 처리하는 방식이 있다. join,
# 기존 컬럼들을 대상으로 1차 가공 후, 다시 그 대상으로 2차 가공을 할 때..
#     순서적으로 진행 할 때 자기 참조...
#     ==> mysql에서는 꼭 자기 참조에 대한 별칭이 필요하다. (안써도..모양상 세팅해야 함.)

-- case by case를 나누기 위한 테이블
select u.userid, u.name, sum(price * amount) as 'totalprice'
from buytbl b right join usertbl u on b.userid = u.userid

group by b.userid, u.name
order by 'totalprice' desc;

-- 이제는 userid, name, totalprice라는 컬럼명이 생성되기 때문에 

-- 이를 가지고 변수처럼 활용이 가능하다.
select *,

	case
			when (totalprice >= 1500) then "VVIP"
			when (totalprice >= 1000) then "VIP"
			when (totalprice >= 1) then "Basic"
			else "Ghost"
	end as CustomerClass

from 
(select u.userid, u.name, sum(price * amount) as 'totalprice'
from buytbl b right join usertbl u on b.userid = u.userid

group by b.userid, u.name
order by 'totalprice' desc) A; #--> my sql에서는 별칭으로 만들어야 함

++ SQL을 퀴즈, 코테의 입장에서 본다면

• 데이터 테이블에 있는 필드명을 그대로 사용해서 계산식을 작성하고 만든다면 전체 구조가 눈에 들어오기 힘들다.
• 따라서, 접근 자체를 스텝별로 진행하는게 좋다.
  1. 필요한 정보들 세팅하고 소스를 만든다. 이때, 서브쿼리를 써서 변수를 지정해야 한다면 그렇게 하는게 생각을 정리하기 좋다.
  2. 필요한 정보들에 대한 출력을 해본다.
  3. 부가적인 정보들이 있으면 가져다 붙이면서, 조건을 붙인다.
  4. 위의 내용을 기반으로 다시 작업을 진행한다.

2-2) 서브 쿼리문

• 조건을 할 때 where ~~ =, in( )
• 찾으려는 대상 from : mysql은 꼭 뒤에 별칭
• select ~~ from (select ~~~) A;
  • ⇒ 원하는 기능에 대한 쿼리문을 검색하면 주로 oracle sql
  • 나의 dbms의 종류 mysql에서 체크
  • 혹시 나온 쿼리에 from 안에 select~~~ 참조가 없다면 체크!

•  

2-3) 비교 : topN

• (그냥 보여주는 갯수, 등수를 부여하지는 않는다..)
• 랭킹을 정하기 위해서는 "기준 값"이 필요하다!

동일한 값에 대해 다른 등수로 랭킹을 하겠다 : row_number()

• 예시) 100, 150,200,200,300 : 1등, 2등, 3등, 4등, 5등 이렇게 넘김

row number()는 동일한 값에도 다른 등수를 매긴다!

 

동일한 값에 대해 같은 등수를 부여하고 싶을 때 : dense_rank(), rank()

같은 값을 다음 값의 랭킹으로 연속되어 사용 : dense_rank()

• 예시) 100, 150, 200, 200, 300 : 1등, 2등, 3등, 3등, 4등

 

같은 값을 다음 값의 랭킹으로 점프해서 할 때 : rank()

• 예시) 100, 150, 200, 200, 300 : 1등, 2등, 3등, 3등, 5등
• 문제 요구 사항을 확인해보고 그에 맞는 것을 잘보고 선택해야 한다!

 

 

데이터를 분위로 나눠서 랭크를 매겨준다면: n-tile()

• 데이터를 덩어리로 구별한다.
• 즉, 분위 별로 나눠서 확인할 때 사용한다.

 

++) partition : 동일한 특정 요소에 따라 랭킹을 매길 때

• 예시) 동일한 사번에 대해 묶어서  그에 대한 랭킹을 따로 매기는 경우

 

3. SQL EDA (주말에 완료하고 함께 포스팅)

오후가 끝나가는 시간에 SQL EDA의 실습이 이어졌는데, 수업 시간에 모든 내용을 다루지는 못해 주말 동안에 독학을 통해서 내용을 다 정리해서 함께 포스팅 하려고 한다!