[SQL] 집계와 랭킹 _CASE WHEN, GROUP BY, RANK

select ~~~ from 테이블 ~~~~ group by / order by/ having/ limit etc

`` : 백틱 --> 필드명 보다 큰 값 ( 테이블/ DB명 )

"",'' : 문자열 --> 문자값의 조건 비교.

distinct → 여러개 종속적으로 걸기 위해서 group by 사용할 수 있다... ⇒ count(distinct ~~~ ) : 유니크한 값의 갯수...

use sqldb;

count 관련 부분 체크!!! ⇒ count(*) // count(1), count(PK) ++ 주의!! count(필드명) : null은 빼고 카운팅이 된다!!!!!!!

case when ==> 기존값을 나의 새로운 기준으로 변경!!!!

1. 기본 기능
   1. 기존의 값을 나의 원하는 기준대로 "변경" → select 집계처리 이외 사용됨!!!
   2. ex) 누적금액 바탕으로 : 고객 등급
   3. ex) 시대별
      1. 출생년도 → 60년대, 70년대, 30대, 40대,,,,, ⇒ 내가 생각한 대로 기준을 잡아서 나열!!! + 많으면,,내가 하나씩 다 나열!!!
      2. tip) 반올림, 올림, 음수 활용을 하면 편할 수 있다!!!!!
2. 합쳐져서 카운팅까지 가는 녀석도 생각!!
   1. 내가 원한느 기준에 의한 데이터의 수 체크!!/카운팅!!!

select * from usertbl;
select birthYear from usertbl;
select case when birthYear between 1950 and 1959 then "50년대" 
            when birthYear between 1960 and 1969 then "60년대" 
            when birthYear between 1970 and 1979 then "70년대" 
            when birthYear between 1980 and 1989 then "80년대" 
		else "기타세대"
        end `세대`
	from usertbl;

select userid, birthYear, case when birthYear between 1950 and 1959 then "50년대" 
            when birthYear between 1960 and 1969 then "60년대" 
            when birthYear between 1970 and 1979 then "70년대" 
            when birthYear between 1980 and 1989 then "80년대" 
		else "기타세대"
        end `세대`
	from usertbl;

# ==> 세대라는 기준으로 고객들을 파악하고자 함!!!
#     ex) 세대별로 고객들이 어떤 구성인지 : 카운팅!!!
select case when birthYear between 1950 and 1959 then "50년대" 
            when birthYear between 1960 and 1969 then "60년대" 
            when birthYear between 1970 and 1979 then "70년대" 
            when birthYear between 1980 and 1989 then "80년대" 
		else "기타세대"
        end `세대`,
        count(1) `고객수`
	from usertbl
    group by `세대`;
# ++ 위의 결과 중에서,,고객이 3명 이상인 나이대만 체크1!!! ==> 이벤트 초점!!
select case when birthYear between 1950 and 1959 then "50년대" 
            when birthYear between 1960 and 1969 then "60년대" 
            when birthYear between 1970 and 1979 then "70년대" 
            when birthYear between 1980 and 1989 then "80년대" 
		else "기타세대"
        end `세대`,
        count(1) `고객수`
	from usertbl
    group by `세대`
    having `고객수`>=3;

# ex) 지역에 대한 정보를 addr를 바탕으로 재구성!!!
# --> 서울, 경기 : 수도권
#     경북, 경남 : 경상도
#     그 외 지역 : 기타지역
select * from usertbl;
select addr, case when addr in ("서울","경기") then "수도권"
				  when addr in ("경북","경남") then "경상도"
                  else "기타지역"
			 end `권역별` 
             from usertbl;

# + 내가 새롭게 정의한 권역별로 고객들의 분포가 어떤지 체크(카운팅!!)
select case when addr in ("서울","경기") then "수도권"
				  when addr in ("경북","경남") then "경상도"
                  else "기타지역"
			 end `권역별`, count(1) 
             from usertbl
             group by `권역별`;

# ++ 집중을 수도권/ 그외 지역으로 나눈 상태에서 고객들의 분포를 보고자 합니다!!!
select case when addr in ("서울","경기") then "수도권"
                  else "기타지역"
			 end `권역별`, count(1) 
             from usertbl
             group by `권역별`;
# ==> 위와 같은 부분에 있어서 간단하게 하는 방법도 있음!!! Style!!!
#    ex) 수도권에 사는 고객들의 수가 얼마?
select addr, case when addr in ("서울","경기") then "수도권" 
			else "기타지역" end `권역별`
            from usertbl;
select addr, case when addr in ("서울","경기") then "수도권" 
			else "기타지역" end `권역별`,
            count(1)
            from usertbl
            group by `권역별`;
# ===> count : 조건에 맞는 거 하나씩 점수판 올리기!!!!(위의 쿼리 스타일)
#      sum+ case+ when : 조건에 맞으면1, 아니면 0 ==> sum( 모으면 됨!)
select sum( case when addr in ("서울","경기") then 1 else 0 end) `수도권`
            from usertbl;
# ==> 장점 : 내가 포커스하는 친구 중심으로만 카운팅!!! + 기준이 내가 원하는 기준(1과 0으로 변환!!)
#                                                    case when 
#                                                기존값을 1or0으로 변환
# ==> 자주 사용되는 스타일/스킬!!group by 안하고, 필터링 없이 
#     원하는 대상 중심으로 딱 카운팅!!!
# sum(case when 원하는 조건 1 else 0 )

# ++ group by/ order by etc에서 숫자 사용!!
select addr, case when addr in ("서울","경기") then "수도권" 
			else "기타지역" end `권역별`,
            count(1)
            from usertbl
            group by `권역별`;
select addr, case when addr in ("서울","경기") then "수도권" 
			else "기타지역" end `권역별`,
            count(1)
            from usertbl
            group by 2;

서브쿼리문

• "조건"을 사용할 때 where = (select max(~~) ) ~~~ + any/all etc
• "from 출처"에 대한 것을 표현할 떄 (가공을 3차 이상,,,,,,)
• from (select ~~~) A vs where ~ (select ~~~ )

select * from usertbl;
select * from (select * from usertbl) A; -- mysql 종특상 별칭 부여 필수!
# ==> join을 여러번 해 나가면서,,,, 정보들을 2/3차 이상 처리를 할 때...

# ex) 서브쿼리를 활용해서 : 사용자 중에서 지역이 서울인 사람들의 아이디만 보자!!!
select * from usertbl;
select * from usertbl where addr = "서울";
select  userID from (select * from usertbl where addr = "서울") A;
# ==> 보통 질의에 대한 쿼리에서 중첩을 3단 이상 하는 것도 기본!!!
#     2~3단 정도는 가볍게 나올 수 있음!!!!
#     : 테이블에 정보가 흩어져 있고,,,가공하면서 처리들을 해야하기에,,,,2~3단 중첩은 기본!!

랭킹

• 명시적으로 기준을 잘 체크를 해서해야 함!!
• 랭킹자체를 db 값으로 기록을 잘 하지 않음 → 경기 결과, 기록 시간,,,, 이런 것들을 기록함
  • 1. 동일한 값에 대해서 다른 값 부여 : row_number()
  • 1. 동일한 값에 대해서 같은 값 부여 : rank계열
    • 2-1) 같은 값 이후에 등수 부여 이어지는 순위 : dense_rank()
    • 2-2) 같은 값 이후에 등수는 누적 인원 수 기준 : rank()
      • ++partition by : 칸막이 --> 랭킹산정에서,,
      • ex) 우리 회사 전체 데이터 기준 --> 랭킹 : 각 부서별로 1~n
      • ex) 지점별 매출 Top3 우수 영업 사원 선정!!!!
      • ++order by 컬럼정렬

# empoyees 동네 데이터 많으니 거기 가서,,
use employees;
select * from employees; -- 사원 정보만 존재.
select * from salaries; -- 연봉 정보만 존재
# 제안1) 그냥 jon 같은 것들을 사용해서 진행!!!! 
#      --> 매 번 join을 하면,,,,시간 지연이 엄청 발생을 하게 됨!!!
# 제안2-1) 임시 테이블 생성을 해서 사용하는 방법!!!!! with 사용해서 진행~~~~
# 제안2-2) 직접 만들고, ,,나중에 삭제... : 필요한 것들을 직접 생성해두고,,,
create table ~~ ( select ~~~ ) ;
select E.emp_no, E.first_name, E.last_name, E.gender, S.salary 
		from salaries S left join employees E
				on S.emp_no = E.emp_no;
create table temp ( select E.emp_no, E.first_name, E.last_name, E.gender, S.salary 
		from salaries S left join employees E
				on S.emp_no = E.emp_no);
# --> 양쪽이 모두 테이블이 커서 시간 지연 발생!@!
#     + 중복된 정보들이 생성이 됨!!!!!
select * from temp limit 5;

# ==> 연봉을 기준으로 랭킹을 부여해보자!!!
# 1) 고액 연봉 순으로 랭킹을 부여하겠다 정의!!!!
select emp_no, first_name, last_name, salary , 랭킹
		from (join한 결과 쿼리문: S-E) A;
select emp_no, first_name, last_name, salary , 
	   rank() over( order by salary desc) `Rank`
		from temp ; -- 65, 66, 66, 68

select emp_no, first_name, last_name, salary , 
	   dense_rank() over( order by salary desc) `Rank`
		from temp ; -- 65, 66, 66, 67

select emp_no, first_name, last_name, salary , 
	   row_number() over( order by salary desc) `Rank`
		from temp ; -- 65, 66, 67,68
# +++++++ 지금 위에 한 쿼리는 그냥 연봉 하나만 가지고 줄을 세운것임!!!!
#     우리회사 연봉1등 기록부터~~~~꼴지까지 기록을 줄 세움!!!!
# ==> 회사의 사원이 자기가 자기 연봉을 갱신하는 것들이 있을 수 있으니,,,
#     직원별로 본인 연봉의 Rank을 산정하자!!!
# ==> 1번 사번 직원 : 1,2,3,,,.,,
#     2번 사전 직원 : 1,2,2,2,3...
# +++ patition by 
select emp_no, first_name, last_name, salary , 
	   rank() over( partition by emp_no order by salary desc) `Rank`
		from temp ;
# ==>영업 지점별로 매출 상위 Top3 선정해주세요!!!!
#                   + 동일한 매출액의 영업사원이 있다면 모두 수여하겠다...(rank)