241215_TIL

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

SQL 문제풀이 복습

Leetcode

문제 링크
seat_id - row_id를
group by의 기준으로 사용하는 것이 포인트.
그리고 본 쿼리에서 consecutive_seats_len의 최대값을
구해야 하는 관계로 result라는 cte를 만들어서 써 주었다.

WITH result
     AS (SELECT Min(seat_id)                    AS "first_seat_id",
                Max(seat_id)                    AS "last_seat_id",
                Max(seat_id) - Min(seat_id) + 1 AS "consecutive_seats_len"
         FROM   (SELECT seat_id,
                        free,
                        ROW_NUMBER()
                          OVER(
                            ORDER BY seat_id) AS "row_no"
                 FROM   Cinema
                 WHERE  free = '1') a
         GROUP  BY ( seat_id - row_no ))
SELECT first_seat_id,
       last_seat_id,
       consecutive_seats_len
FROM   result
WHERE  consecutive_seats_len = (SELECT Max(consecutive_seats_len)
                                FROM   result)
ORDER  BY 1;

문제 링크
문자열 제어 문제.

특정 문자(@,#)의 갯수를 구하려면
전체 열의 길이(char_length)에서
찾고자 하는 특정 문자를 공백으로 변환한
결과(replace)를 뺀 길이를 구하면 된다.

SELECT tweet_id
FROM   Tweets
WHERE  CHAR_LENGTH(content) > 140
        OR CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, '@', '')) > 3
        OR CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE(content, '#', '')) > 3
ORDER  BY 1;

문제 링크
구해야 하는 값들이 크게 세 가지기 때문에
cte를 세 개 만들어서 푸는 게 가장 깔끔한 것 같다.

1. 전체 근로시간을 구하는 cte(task_hour)
   • 이건 별로 어렵지 않다
   • timestampdiff의 결과를 합산하면 되는데, 정확한 계산이 필요해서 단위는 minute으로 설정했다.
     
     
2. 오버랩된 시간의 양을 구하는 cte(overlapped)
   • 오버랩의 조건에 맞게 join을 할 때 주의해야 하는데, start_time은 명확하게 t1이 앞서야 하고 end_time은 명확하게 t2가 앞서야 한다.
   • 마찬가지로 timestampdiff의 결과를 minute 단위로 sum하면 된다.
     
     
3. 오버랩된 시간의 '최대 갯수'를 구하는 cte(concurrent)
   • 이 문제에서 가장 어려운 부분이자 포인트.
   • 오버랩된 시간의 양을 구하는 위 cte에서 바로 구하려고 시도하면 해결이 어렵다. 왜냐하면 오버랩된 시간이 세 구간, 네 구간이 있을 수도 있으므로.
   • 해서, 여기서는 한 방에 구하려고 하기보다는 join의 조건 중 start_time을 조금 느슨하게 설정해서 오버랩 된 시간과 안 된 시간의 결과를 모두 뽑아내되
   • employee_id를 기준으로 group by만 하면 된다.
   • 나머지 max()를 구하는 계산은 본 쿼리에서 하면 되므로.

cte 3개만 이상없이 만들었다면
본 쿼리에서의 계산은 어렵지 않다.
floor를 사용하는 부분만 약간 주의하면 된다 정도?

WITH task_hour
     AS (SELECT employee_id,
                Sum(TIMESTAMPDIFF(minute, start_time, end_time)) AS
                "total_task_hours"
         FROM   Tasks
         GROUP  BY 1),
     overlapped
     AS (SELECT t1.employee_id,
                Sum(TIMESTAMPDIFF(minute, t2.start_time, t1.end_time)) AS
                "overlap"
         FROM   Tasks t1
                JOIN Tasks t2
                  ON t1.employee_id = t2.employee_id
                     AND t1.start_time < t2.start_time
                     AND t1.end_time > t2.start_time
         GROUP  BY 1),
     concurrent
     AS (SELECT t1.employee_id,
                Count(t2.task_id) AS "concurrent"
         FROM   Tasks t1
                JOIN Tasks t2
                  ON t1.employee_id = t2.employee_id
                     AND t1.start_time <= t2.start_time
                     AND t1.end_time > t2.start_time
         GROUP  BY t1.employee_id,
                   t1.task_id)
SELECT th.employee_id,
       Floor(( total_task_hours - IFNULL(overlap, 0) ) / 60) AS
       "total_task_hours",
       Max(concurrent)                                       AS
       "max_concurrent_tasks"
FROM   task_hour th
       LEFT JOIN overlapped o
              ON th.employee_id = o.employee_id
       LEFT JOIN concurrent c
              ON th.employee_id = c.employee_id
GROUP  BY 1
ORDER  BY 1;