[TIL]2025-04-04(QCC 3회차)

QCC 2번

dense_rank() over(order by salary desc)

✏️partition by 생략 가능!!

• 앞에 partition by 때문에 헷갈렸는데, 이럴 경우에는 생략 가능하다는 걸 배웠다.

QCC 3번

문제: 회사 경영진은 부서 간 협업 활성화 현황을 파악하고자 합니다.
이를 위해 전체 메시지 중에서, 서로 다른 부서 간 주고 받은 메시지의 비율을 계산하려고 합니다.

• messages 테이블을 기준으로, sender_id와 receiver_id를 각각 employees 테이블에 조인하여 각 메시지의 송신자와 수신자의 부서를 확인해야 합니다.
• sender와 receiver의 부서가 다를 경우만 부서간 메시지로 간주합니다.
• 전체 메시지 중 부서 간 메시지가 차지하는 비율(%)을 계산하고, 소수점 1자리까지 반올림해야 합니다.
• 보낸 사람(sender_id)과 받는 사람(receiver_id) 모두 employees 테이블에서 부서 정보가 존재하는 경우에만 메시지를 유효하게 판단합니다.
  즉, 부서가 없는 직원이 포함된 메시지는 분자, 분모값 등 분석 대상에서 제외합니다.

#내가 푼 것
SELECT ROUND(100* SUM(CASE WHEN a.department != b.dept THEN 1 ELSE 0 END)/count(*),1) as inter_department_msg_pct
FROM 
    (
      select m.sender_id SENDER_ID, 
             e.employee_id EMPLOYEE_ID, 
             m.receiver_id RECEIVER_ID,
             e.department DEPARTMENT,
             m.sent_time SENT
      from messages m
      join employees e
      on m.sender_id=e.employee_id
    ) a
JOIN 
    (
      select m.sender_id SENDER,  
             m.receiver_id RECEIVER,
             e.employee_id EMPLOYEE,
             e.department DEPT,
             m.sent_time TIME
      from messages m
      join employees e
      on m.receiver_id=e.employee_id
    ) b
ON a.receiver_id=b.receiver and a.sender_id=b.sender
where a.department is not null
order by a.department

#튜터님 풀이
SELECT
  ROUND(100.0 * SUM(CASE WHEN e1.department != e2.department THEN 1 ELSE 0 END) / COUNT(*), 1) AS inter_department_msg_pct
FROM qcc.messages m
JOIN qcc.employees e1 ON m.sender_id = e1.employee_id
JOIN qcc.employees e2 ON m.receiver_id = e2.employee_id

풀긴했는데, 튜터님 답이랑 비교해보고 맥이 탁 풀렸다😢
쓸 데 없이 왜 서브쿼리 썼지..?
아니야!!! 푼 게 어디야. 푼 거에 의의를 두자ㅎㅎ 잘했다^^

✏️ 각각 JOIN하는 것도 가능!!
이번에 messages 테이블이랑 employees테이블 2개가 있었다.
messages 테이블에 employees(sender,receiver)를 두 번 각각 JOIN.
아래와 같이 ⭐️e1, e2로 구분⭐️을 해주면 된다.

select *
from messages m
join employees e1 on m.sender_id=e1.employee_id
join employees e2 on m.receiver_id=e2.employee_id

+여기서 또 이런 의문이 생겼다. 여기서는 RECEIVER 부서만 보이는데..?
뭔가 잘못 한 거 아닌가??

하지만 사실은 e1.department와 e2.department 둘 다 SELECT에 포함되어 있어. 그런데 만약 같은 컬럼명이 있어서 보이지 않는 것처럼 보였다면, 그건 SQL이 같은 컬럼명을 자동으로 하나만 표시했을 가능성이 높아.

이럴 때는 ⭐️정확하게 지정⭐️해주는 게 중요하다. (내가 이걸 못해서 서브쿼리 남발)

select sender_id,
	   e1.department sender_dept, 
       receiver_id,
       e2.department receiver_dept,
       e1.employee_id
from messages m
join employees e1 on m.sender_id=e1.employee_id
join employees e2 on m.receiver_id=e2.employee_id