코테준비 | Second Highest Salary (의도적으로 null 값 출력하기)

☑️  176. Second Highest Salary

[문제]

• Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).
• 두번째로 높은 연봉자 조회하기, 없다면 null 로 반환

---

[문제 풀이]

내 코드 - window & with 문

WITH dnkt AS (
SELECT *
      ,DENSE_RANK() OVER (ORDER BY salary DESC) as dnk
      ,count(id) OVER () AS total_cnts
FROM employee
), nullt AS (
SELECT IF(total_cnts>=2 and dnk=2,salary,null) AS check_null
FROM dnkt
)
SELECT MAX(check_null) as SecondHighestSalary
FROM nullt

 

개선 코드 1 - 서브쿼리 

select max(salary) as SecondHighestSalary 
from Employee  
Where salary < (select max(salary) from Employee)

 

개선코드 2 - limit offset 활용 & sql 기본 동작 활용

SELECT (
    SELECT salary
    FROM (SELECT salary FROM employee GROUP BY salary) AS grouped -- 중복 연봉 제외 
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1 -- 두번째로 큰 값 출력
)as SecondHighestSalary

 

개선코드 3 - limit offset 활용 & sql 기본 동작 활용 (+개선코드2에서 distinct 로 중복 제거)

SELECT ( -- DISTINCT 로 간단하게 중복제거
    SELECT DISTINCT salary -- 중복 연봉 제외
    FROM employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1 -- 두번째로 큰 값 출력
)as SecondHighestSalary

 

이슈 및 해결 과정

• 아이디어1(WINDOW & CTE) : 연봉 순 넘버링후 출력 
  • salary 순으로 넘버링, 동일 연봉자 제외하기위해 dense_rank 사용 후 출력
  • 문제) 문제 조건에서 두번째 고액 연봉자가 없는 경우 null 로 반환 but 내 쿼리에서는 값이 없으므로 아무것도 반환되지 않음(공백) 
  • 해결 ) 값이 없을때 null 값 반환하기 위해
    • dnkt CTE 에 전체 행 수 추가 : `count(id) OVER () AS total_cnts`
    •  nullt CTE에 순번이 2일때 연봉, 아닐때 NULL 출력시킴
    • 아우터 쿼리에서 최대값 최종 출력 => 두번째 연봉자가 있으면 연봉,아니면 NULL 값 최종 출력됨
•  아이디어2(서브쿼리) : 연봉이 가장 큰 값보다 작은값만 필터링 한뒤 그중에 가장 큰 값 출력
  • WHERE 절 서브쿼리로 전체 데이터에서 최고 연봉 제외 ( 연봉 <최고 연봉)
  • 최고 연봉이 제외한 상황에서 최고 연봉 출력 (MAX~) 
  • 값이 없을때 null 값 반환  : WHERE 에서 값이 없을경우 자동 NULL 로 반환됨 
• 아이디어3(LIMIT OFFSET옵션) : 연봉 높은순으로 내림차순 한뒤 두번째 순번만 출력
  • 인라인 서브쿼리로 중복 연봉 제거 한뒤, 연봉 내림차순 정렬
  • LIMIT 1 OFFSET 1 옵션 활용하여, 두번째 고액 연봉 출력
  • `LIMIT n1 OFFSET n2`: n2번째 건너뛰고 n1개 만큼 출력
  • 값이 없을때 null 값 반환  : 위에까지만 하면 값이 없을때 공백으로 출력됨.
    • sql 기본 동장 : 서브쿼리 결과값이 없으면 null로 반환 
    • 위 쿼리를 스칼라 서브쿼리로 넣어 값이 없을때 NULL 로 반환하게 해서 해결
  • 추가 개선점 : 인라인 서브쿼리로 중복 제거 해줬는데(개선코드2 참고), DISTINCT 로 간단하게 중복 제거 할수도 있음 

한줄 포인트

• 위에 아이디어 중 가장 성능이 좋은 쿼리는 마지막 LIMIT -OFFSET & 서브쿼리를 사용한 쿼리문임
• 공백이 아닌 NULL 값 반환시, 서브쿼리에 조건을 넣고 출력값이 없을경우 NULL 값 반환 한다고 생각하면 쉬움
• LIMIT -OFFSET 은 건너뛰고 몇개만 추출할때 은근 유용하다  
• 조건이 순서가 있을경우 (EX. 작은것들중 더 작은거) WHERE 절 서브쿼리 활용
• 서브쿼리의 결과값이 없으면 NULL 로 반환함