241018_TIL

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

SQL 문제풀이 복습

---

Leetcode

문제 링크
recursive cte도 쓰고
이동평균도 구해야 하는 hard 문제.

1트(8/31) 때와는 다르게 이번에는 recursive cte를 제외하면
다른 cte는 만들지 않고 서브쿼리로만 한번에 해결했다.
대신 window 함수의 생긴 모양이 조금 복잡해졌는데,

1. ride_distance나 ride_duration의 평균을 구할 때, null인 경우를 0으로 처리해야 이동평균 계산이 제대로 되므로 ifnull 처리를 먼저 하고
2. 같은 달에 여러 건의 ride가 있었을 수도 있으니 월별로 sum과 group by를 추가하고
3. window함수를 써서 이것들을 이동평균한 다음 (avg over)
   • 이 때, rows between current row and 2 following이라는 구문을 눈여겨 봐 두자.
   • 예전에 풀었던 이동평균 문제는 "이전" 2개월을 포함한 3개월치의 이동평균을 구하는 것이었는데, 그 때는 rows between 2 preceding and current row 라는 형태로 썼던 기억이 있다.
   • RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
     이런 형태로도 쓸 수 있지만, rows between은 행 단위로 작동하는데 비해 interval은 시간 단위로 작동하는 기능
4. 마지막으로 문제에서 요구한 조건에 따라 round(,2) 처리해 주면 된다.

그리고 where 조건을 써서 month를 10월 이내로 제한하면
12월까지의 이동평균을 구할 수 있는데,
저걸 쿼리에 바로 적용하면 11월, 12월 데이터가 아예 빠져버리면서
10월에 구한 이동평균의 값이 틀어지게 되므로,
연산 결과를 한 번 a라는 인라인뷰로 감싼 다음에
where 조건을 써 주어야 한다.
(이동평균을 구하는 등의 문제에서 매번 나오는 패턴)

WITH recursive cte
AS
  (
         SELECT 1 AS "month"
         UNION ALL
         SELECT month+1
         FROM   cte
         WHERE  month < 12)
  SELECT   month,
           average_ride_distance,
           average_ride_duration
  FROM     (
                     SELECT    cte.month,
                               round(avg(sum(ifnull(ride_distance,0))) over(ORDER BY month rows BETWEEN current row AND 2 following),2) AS "average_ride_distance",
                               round(avg(sum(ifnull(ride_duration,0))) over(ORDER BY month rows BETWEEN current row AND 2 following),2) AS "average_ride_duration"
                     FROM      cte
                     LEFT JOIN Rides r
                     ON        cte.month = month(r.requested_at)
                     AND       year(requested_at) = '2020'
                     LEFT JOIN AcceptedRides a
                     ON        r.ride_id = a.ride_id
                     GROUP BY  1) a
  WHERE    month <= 10
  ORDER BY 1;

문제 링크
1트(4/22), 2트(6/16) 때의 풀이와 비교하면
확실히 쿼리가 깔끔해진 것 같다는 생각이 든다.

굳이 cte를 만들거나 어렵게 join할 필요 없이
case when과 집계함수를 쓰면 간단할 문제.

SELECT machine_id,
       round(avg(CASE
                   WHEN activity_type = 'end' THEN timestamp
                 end) - avg(CASE
                              WHEN activity_type = 'start' THEN timestamp
                            end), 3) AS "processing_time"
FROM   Activity
GROUP  BY 1;

문제 링크
문자열을 다루는 문제.
upper와 lower, 그리고 substr과 concat을 쓸 수 있으면 간단하다.
아래는 중간 풀이 결과.

select
    user_id,
    upper(left(name,1)),
    lower(substr(name,2)),
    concat(upper(left(name,1)),lower(substr(name,2))) as "name"
from Users;

아래는 정답 쿼리.

SELECT user_id,
       concat(upper(LEFT(name, 1)), lower(substr(name, 2))) AS "name"
FROM   Users
ORDER  BY 1;

---

프로그래머스

오늘 프로그래머스 문제는 너무 간단해서
따로 리뷰라고 할 만한 건 없었음.
문제 링크

SELECT count(*) AS "FISH_COUNT"
FROM   FISH_INFO f
       JOIN FISH_NAME_INFO fi
         ON f.FISH_TYPE = fi.FISH_TYPE
WHERE  FISH_NAME IN ( 'BASS', 'SNAPPER' );

문제 링크

SELECT id,
       length
FROM   FISH_INFO
ORDER  BY 2 DESC,
          1 ASC
LIMIT  10;

문제 링크

SELECT count(*) AS "FISH_COUNT"
FROM   FISH_INFO
WHERE  year(TIME) = '2021';

문제 링크

SELECT concat(max(LENGTH), 'cm') AS "MAX_LENGTH"
FROM   FISH_INFO;

문제 링크
join과 서브쿼리 활용 문제.

SELECT id,
       fn.FISH_NAME,
       f.LENGTH
FROM   FISH_INFO f
       JOIN (SELECT fish_type,
                    max(length) AS "length"
             FROM   FISH_INFO
             GROUP  BY 1) a
         ON f.FISH_TYPE = a.FISH_TYPE
            AND f.LENGTH = a.LENGTH
       LEFT JOIN FISH_NAME_INFO fn
              ON fn.FISH_TYPE = a.FISH_TYPE
ORDER  BY 1;