240623_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

오늘의 코드카타

문제 링크

예전에 풀었던 방법
이동평균을 구해야 하는 문제.
처음 접했을 때 어떻게 접근해야 할지 감을 못 잡아서
검색과 병행해서 해결했던 걸로 기억한다.

그 사이 SQL챌린지 세션을 통해
CTE와 Window함수의 사용법을 자세히 배웠기 때문에
오늘 풀이는 조금 다르게 해 봤다.

SELECT visited_on, amount, average_amount
from
(select visited_on,
       sum(amount) over(order by visited_on range between interval 6 day preceding and current row ) as 'amount',
       round(sum(amount) over(order by visited_on range between interval 6 day preceding and current row )/7,2) as 'average_amount'
from
(select visited_on,
       sum(amount) as amount
from Customer
group by 1) a) B
where visited_on >= (select min(visited_on) from Customer)+6
order by 1

오늘 풀었던 방법

WITH a
AS
  (
           SELECT   visited_on,
                    sum(amount) AS total_amount
           FROM     customer
           GROUP BY 1),
  b
AS
  (
           SELECT   visited_on,
                    sum(total_amount) over (ORDER BY visited_on range BETWEEN INTERVAL 6 day preceding AND      current row)            AS amount,
                    round(sum(total_amount) over (ORDER BY visited_on range BETWEEN INTERVAL 6 day preceding AND      current row)/7,2) AS average_amount
           FROM     a
           GROUP BY 1)
  SELECT visited_on,
         amount,
         average_amount
  FROM   b
  WHERE  visited_on >=
         (
                SELECT min(visited_on)
                FROM   customer) + 6;

시간복잡도 면에서는 저번이나 이번이나 큰 차이가 없는 것 같지만, 예전에 풀었던 방법에서 서브쿼리로 처리했던 부분들을 CTE로 빼서 처리한 덕분에 쿼리의 가독성이 조금 더 좋아진 것 같다.

이동평균을 구할 때 주의해야 할 문법

• 보통은 sum() over(partition by ~ order by ~)로 쓰는데, 여기서는 partition by 부분이 없이 order by가 앞으로 나오고, 그 뒤에 range between이 붙는다.
• 보통은 between 시점A and 시점B로 쓰는데, 여기서는 between 바로 다음에 interval이 붙고 그 다음에 and가 쓰인다.
• 6 days가 아니라 6 day preceding이고, 현재 행까지를 나타내고 싶을 때는 current row로 표기한다.

문제 링크

예전에 풀었던 방법
서브쿼리로 처리한 인라인 뷰에서 필요한 값들을 가져오는 방식.

SELECT id,
       Sum(num) AS num
FROM   (SELECT requester_id AS id,
               Count(*)     AS num
        FROM   requestaccepted
        GROUP  BY 1
        UNION ALL
        SELECT accepter_id AS id,
               Count(*)    AS num
        FROM   requestaccepted
        GROUP  BY 1) a
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  1

오늘 풀었던 방법

WITH friends
AS
  (
           SELECT   requester_id AS id,
                    count(*)     AS num
           FROM     requestaccepted
           GROUP BY 1
           UNION ALL
           SELECT   accepter_id,
                    count(*) AS num
           FROM     requestaccepted
           GROUP BY 1)
  SELECT   id,
           sum(num) AS num
  FROM     friends
  GROUP BY 1
  ORDER BY 2 DESC
  LIMIT    1

request 쪽에서 친구의 수를 한 번 세 주고
accept 쪽에서 친구의 수를 한 번 세 준 다음 그 결과를 union all로 합치고
friends라는 이름의 CTE로 지정해 주었다.

본 쿼리에서 id별로 num의 sum을 구해준 후 num을 기준으로 내림차순 정렬하고 limit 1을 주면 가장 많은 친구를 가진 id와 명수를 출력할 수 있다.
그리고 문제에서 추가적으로 주어진 아래의 조건을 해결하려면

Follow up: In the real world, multiple people could have the same most number of friends.
Could you find all these people in this case?

order by 2 desc limit 1 부분을 없애고 having 절을 써서 처리하면 된다.

WITH friends
AS
  (
           SELECT   requester_id AS id,
                    count(*)     AS num
           FROM     requestaccepted
           GROUP BY 1
           UNION ALL
           SELECT   accepter_id,
                    count(*) AS num
           FROM     requestaccepted
           GROUP BY 1)
  SELECT   id,
           sum(num) AS num
  FROM     friends
  GROUP BY 1
  HAVING   num =
           (
                    SELECT   sum(num)
                    FROM     friends
                    GROUP BY id
                    ORDER BY 1 DESC
                    LIMIT    1)

CTE로 미리 처리해 두니
복잡한 서브쿼리를 두 번 이상 써야 할 일이 없어서 많이 편해지긴 한다.
CTE를 쓰면 가독성, 유지보수의 편함, 최적화 엔진의 작동 측면에서 도움이 된다.

문제 링크

예전에 풀었던 방법
부서별로 salary의 랭킹을 먼저 구한 다음
그걸 인라인뷰 서브쿼리로 처리하고 department 테이블과 조인해서 해결한 문제.
이것도 CTE를 써서 간단히 정리만 해 보기로 했다.

SELECT d.NAME AS Department,
       a.NAME AS Employee,
       salary
FROM   (SELECT NAME,
               salary,
               departmentid,
               Dense_rank()
                 OVER (
                   partition BY departmentid
                   ORDER BY salary DESC) AS ranking
        FROM   employee) a
       LEFT JOIN department d
              ON a.departmentid = d.id
WHERE  ranking <= '3';

오늘 풀었던 방법

WITH temp
     AS (SELECT departmentid,
                id,
                NAME,
                salary,
                Dense_rank()
                  OVER(
                    partition BY departmentid
                    ORDER BY salary DESC) AS ranking
         FROM   employee),
     result
     AS (SELECT d.NAME      AS Department,
                temp.NAME   Employee,
                temp.salary Salary
         FROM   temp
                LEFT JOIN department d
                       ON temp.departmentid = d.id
         WHERE  temp.ranking <= 3)
SELECT department,
       employee,
       salary
FROM   result;

leetcode 난이도가 hard라고 되어있는 것 치고는 그렇게까지 막 어렵진 않은 문제.
CTE를 두 번 써서 아예 result까지 다 구해버리고,
본 쿼리에서는 result에 있는 결과만 출력했다.
이렇게 하면 ranking 조건이 3에서 4로 바뀌어도 result CTE만 수정해주면 될 듯.

주중에 밀렸던 문제들까지 전부 해결하고
leetcode SQL50 풀이를 완료했다! 나름 인증 배지도 주네🤔

내일부터 SQL은 Programmers에서 아직 못 풀었던 문제들을 찾아서 풀고
leetcode에서 판다스 관련된 문제들도 조금씩 풀면서 리뷰해 봐야겠다.