패스트캠퍼스 챌린지 41일차

SQL 순위 집계 예제

 

1. RANK 예제

tProduction 테이블은 직원들이 제품을 생산한 정보가 들어있는 테이블이다

생산량이 많은 직원 기준 상위 10명에게 상여금을 준다

해당하는 직원 번호와 총 생산량을 상위 10명까지 순위를 매겨 출력하라

만약 공돈 순위가 있다면 다음 순위는 중복 된 순위의 수만큼 떨어진다 // rank 함수 사용

정답

 select  tpr.enumber as 직원번호, sum(tpr.pcount) as 총_생산량,

 rank() over(order by sum(tpr.pcount) desc) as 총_생산량_순위 // 순위를 같이 출력

 from tProduction as tpr

 group by tpr.enumber

 limit 10

 

---

2. Row_num 예제

tProduction 테이블은 직원들이 제품을 생산한 정보가 들어있는 테이블이다

장농 생산 이력을 전부 출력하되, 생산량이 높은 순서대로 생산한 직원의 번호와 제품번호, 생산량을 순위를 매겨 출력

공동 순위가 나오지 않게 출력 해야 하며 동률일 경우 직원코드가 작은 번호가 우선순위를 가지도록 한다

(장농의 제품 코드는 I2003 이다)

정답

select tpr.enumber as 직원번호, tar.inumber as 제품번호, tpr.pcount as 생산량

row_number() over (partition by tpr.inumber order by tpr.pcount desc, tpr.enumber asc) as 생산량_순위

from tproduction as tpr

where tpr.inumber ='I2003'

---

3. DENSE-RANK 예제

tEmployee : 직원 정보 테이블

부서별 연령 파악을 위해 각 부서마다 속해있는 직원들의 나이로 출생연도를 알아본다

이를 위해 부서명, 직원명, 출생연도를 출력하되 각 부서의 직원들은 출생연도가 빠른 순으로 순위를 매겨라

공동순위는 동일하게 부여하고, 그 다음 순위는 공동 순위 다음 번호로 순위가 출력

출생 연도는 tEmployee의 ERRN의 앞 2자리로 비교하여 출력

정답

select

   case

     when tem.DNumber = 'D1001' then '문구 생산부'

     when tem.DNumber = 'D1002' then '가구 생산부'

     when tem.DNumber = 'D1003' then '악세사리 생산부'

     when tem.DNumber = 'D1004' then '전자기기 생산부'

     when tem.DNumber = 'D1005' then '음료 생산부'

  else '부서없음'

  end as 부서명

  , tem.EName as 직원명

  , substring(tem.ERRN,1,2) as 출생연도

  , DENSE_RANK() over ( partition by tem.DNumber order by substring(tem.ERRN,1,2)) as 출생연도_순위

from tEmployee as tem

---

4. JOIN 예제 - 1 

tEmployee : 직원 정보 테이블, tDepartment : 직원 부서 정보 테이블, tRank : 회사 내 직급 테이블

현재까지 입사했던 모든 직원들의 직원번호, 부서명(tDepartment), 직원명, 직급명(tRank), 입사일, 퇴사일 출력

부서와 직급의 경우 코드가 아닌 부서명으로 출력하고, 입사일과 퇴사일은 연,월,일만 출력 되도록 한다.

정답

select tem.ENumber as 직원번호, tde.DName as 부서명, tem.EName as 직원명, tra.RName as 직급명

            , to_char(tem.StartDate,'YYYY-MM-DD') as 입사일

            , to_char(tem.Resignationdate, 'YYYY-MM-DD') as 퇴사일

from tEmployee as tem

join tDepartment as tde on tem.DNumber = tde.DNumber // 부서 테이블과 직원 테이블 join

join tRank as tra on tem.RNumber = tra.RNumber // 직급 테이블과 직원 테이블 join

 

---

5. JOIN 예제 - 2

tProduction : 생산된 제품 정보 테이블 , tOrder : 고객 주문 정보 테이블, tItem : 생산하기 위한 제품 정보 테이블

2021년 1월 전자기기류 판매금 정산을 위해 전자기기류 제품들의 제품명과 해당 제품의 총 판매량을 출력

전자기기류 제품 코드는 INumber 번호가 I4로 시작한다

정답

select tit.IName as 제품명, sum(tpr.PCount) as 총_판매량

from tOder as tor

join tProduction as tpr on tor.pNumber = tpr.pNumber

join tItem as tit on tpr.INumber = tit.INumber

where to_char(tor.ODate,'YYYY-MM') = '2021-01' and substring(tit.Number,1,2) = 'I4'

group by tit.IName

 

 

41일차 인증

 

 

 

 

본 포스팅은 패스트캠퍼스 환급 챌린지 참여를 위해 작성되었습니다.

https://bit.ly/37BpXiC

패스트캠퍼스 [직장인 실무교육]