240831_TIL

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

SQL 코드카타

문제 링크
어려워서 한동안 묵혀놨었던 문제.
머리가 차분할 때 풀어보니 생각보다 어렵지 않게 풀렸다.

1. 먼저 recursive CTE를 만들어서 1부터 12까지의 month를 모두 구한다.
2. 그 뒤에 cte 테이블에 rides를 조인하는데, 조인의 조건은 month가 똑같을 것, 그리고 rides 테이블의 requested_at이 2020년 1월부터 12월까지일 것 이렇게 두 개다.그리고 수치를 계산해야 하므로 acceptedrides 테이블도 ride_id가 같은 조건으로 함께 조인해 준다.
   
   그리고 여기에서 month별로 총 distance와 duration의 합을 구해서 a라는 CTE에 담는다.
3. CTE b에서는 3달간의 이동평균을 구한다. 이 때 preceding의 반대에 해당하는 문법이 뭔지를 몰라서 result CTE를 만들고 lead 함수를 써서 2칸 위로 땡겨왔다.
4. 마지막으로 result CTE로부터 본 쿼리를 작성하되, month만 10 이하로 조정해 주면 끝.

아래는 완성된 정답 쿼리.

WITH recursive cte
AS
  (
         SELECT 1 AS "month"
         UNION ALL
         SELECT month+1
         FROM   cte
         WHERE  month < 12),
  a
AS
  (
            SELECT    c.month,
                      ifnull(sum(ride_distance),0) AS "total_distance",
                      ifnull(sum(ride_duration),0) AS "total_duration"
            FROM      cte c
            LEFT JOIN rides r
            ON        c.month = month(r.requested_at)
            AND       date_format(r.requested_at,'%Y-%m') BETWEEN '2020-01' AND '2020-12'
            LEFT JOIN acceptedrides a
            ON        r.ride_id = a.ride_id
            GROUP BY  1),
  b
AS
  (
           SELECT   month,
                    total_distance,
                    total_duration,
                    avg(total_distance) over(ORDER BY month rows BETWEEN 2 preceding AND current row) AS "avg_distance_temp",
                    avg(total_duration) over(ORDER BY month rows BETWEEN 2 preceding AND current row) AS "avg_duration_temp"
           FROM     a),
  result
AS
  (
           SELECT   month,
                    ifnull(round(lead(avg_distance_temp,2) over(ORDER BY month),2),0) AS "average_ride_distance",
                    ifnull(round(lead(avg_duration_temp,2) over(ORDER BY month),2),0) AS "average_ride_duration"
           FROM     b)
  SELECT   month,
           average_ride_distance,
           average_ride_duration
  FROM     result
  WHERE    month <= 10
  ORDER BY 1;

위의 3번~4번 단계를 하나로 압축하려면
이동평균을 구하는 단계에서 쿼리를 다르게 짜면 된다.
처음 풀 때는 몰라서 preceding 2와 current row 사이의 이동평균을 구하고
lead를 써서 2칸을 당겨오는 불필요한 연산을 추가했지만,
current row와 2 following으로 쿼리를 바꾸면
굳이 result CTE를 만들 필요가 없게 된다.

아래는 조금 더 축약된 버전의 정답 쿼리.

WITH recursive cte
AS
  (
         SELECT 1 AS "month"
         UNION ALL
         SELECT month+1
         FROM   cte
         WHERE  month < 12),
  a
AS
  (
            SELECT    c.month,
                      ifnull(sum(ride_distance),0) AS "total_distance",
                      ifnull(sum(ride_duration),0) AS "total_duration"
            FROM      cte c
            LEFT JOIN rides r
            ON        c.month = month(r.requested_at)
            AND       date_format(r.requested_at,'%Y-%m') BETWEEN '2020-01' AND '2020-12'
            LEFT JOIN acceptedrides a
            ON        r.ride_id = a.ride_id
            GROUP BY  1),
  b
AS
  (
           SELECT   month,
                    round(avg(total_distance) over(ORDER BY month rows BETWEEN current row AND 2 following),2) AS "average_ride_distance",
                    round(avg(total_duration) over(ORDER BY month rows BETWEEN current row AND 2 following),2) AS "average_ride_duration"
           FROM     a)
  SELECT   month,
           average_ride_distance,
           average_ride_duration
  FROM     b
  WHERE    month <= 10
  ORDER BY 1;

오늘의 문제풀이는 여기까지.
내일은 묵혀놨던 문제 중 또 하나를 해결해 봐야겠다.
프로그래밍을 해야 하는 게 아니라면 Leetcode 문제는 완벽하게 다 풀어서 끝을 내야지.