240821_TIL

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

SQL 코드카타

문제 링크
recursive CTE를 써서
11월의 모든 금요일 테이블을 먼저 만든 후
inner join을 쓰면 간단히 해결할 수 있는 문제.

WITH recursive friday
AS
  (
         SELECT row_number() over() AS "row_num",
                '2023-11-03'        AS "pdate"
         UNION ALL
         SELECT row_num+1,
                date_add(pdate, INTERVAL 7 day)
         FROM   friday
         WHERE  pdate <= '2023-11-17')
  SELECT     f.row_num         AS "week_of_month",
             f.pdate           AS "purchase_date",
             sum(amount_spend) AS "total_amount"
  FROM       friday f
  INNER JOIN purchases p
  ON         f.pdate = p.purchase_date
  GROUP BY   1,
             2
  ORDER BY   1;

문제 링크
위 문제의 심화 버전...이긴 한데
이미 위에서 recursive CTE를 만들어서 풀었다면
이 문제에서는 조건만 left join으로 바꾸고 ifnull을 써서
비어있는 금요일만 0으로 처리해 주면 된다.

이걸 hard로 해 놓은 걸 보니
위 문제(medium)는 recursive CTE를 안 쓰고도 푸는 방법이 있었나 보다.

WITH recursive friday
AS
  (
         SELECT row_number() over() AS "row_num",
                '2023-11-03'        AS "pdate"
         UNION ALL
         SELECT row_num+1,
                date_add(pdate, INTERVAL 7 day)
         FROM   friday
         WHERE  pdate <= '2023-11-17')
  SELECT    f.row_num                   AS "week_of_month",
            f.pdate                     AS "purchase_date",
            ifnull(sum(amount_spend),0) AS "total_amount"
  FROM      friday f
  LEFT JOIN purchases p
  ON        f.pdate = p.purchase_date
  GROUP BY  1,
            2
  ORDER BY  1;

문제 링크

WITH a
     AS (SELECT user_id,
                session_type,
                Rank()
                  OVER(
                    partition BY user_id
                    ORDER BY session_start) AS "ranking"
         FROM   sessions),
     first_viewer
     AS (SELECT user_id
         FROM   a
         WHERE  ranking = 1
                AND session_type = 'Viewer')
SELECT f.user_id,
       Count(*) AS "sessions_count"
FROM   first_viewer f
       INNER JOIN sessions s
               ON f.user_id = s.user_id
WHERE  session_type = 'Streamer'
GROUP  BY 1
ORDER  BY 2 DESC,
          1 DESC;

문제 링크
지금까지는 concat을 쓸 때

t1.topping_name,',',t2.topping_name

이런 식으로 쓰는 게 일반적이었지만,
결합해야 할 문자열이 많아지면 일일이 타이핑하기 귀찮기도 하거니와
오타가 날 가능성도 높아진다.

이럴 때는 그냥 concat 대신 concat_ws를 넣어서
(※ ws는 With Separator의 약자임)
제일 앞에 기준이 될 구분자를 넣어주고 컬럼들을 쭉 붙이면
동일한 결과를 얻을 수 있음.

Concat_ws(',', t1.topping_name, t2.topping_name, t3.topping_name)

이런 식으로.
아래는 정답 코드.

SELECT Concat_ws(',', t1.topping_name, t2.topping_name, t3.topping_name) AS
       "pizza",
       t1.cost + t2.cost + t3.cost                                       AS
       "total_cost"
FROM   toppings t1
       INNER JOIN toppings t2
               ON t1.topping_name < t2.topping_name
       INNER JOIN toppings t3
               ON t2.topping_name < t3.topping_name
ORDER  BY 2 DESC,
          1;

문제 링크
가장 무식하게 푼 버전.

WITH p
     AS (SELECT candidate_id
         FROM   candidates
         WHERE  skill = 'Python'),
     t
     AS (SELECT candidate_id
         FROM   candidates
         WHERE  skill = 'Tableau'),
     ps
     AS (SELECT candidate_id
         FROM   candidates
         WHERE  skill = 'PostgreSQL')
SELECT p.candidate_id
FROM   p
       INNER JOIN t
               ON p.candidate_id = t.candidate_id
       INNER JOIN ps
               ON t.candidate_id = ps.candidate_id
ORDER  BY 1;

깔끔 버전.

SELECT candidate_id
FROM   candidates
WHERE  skill IN ( 'Python', 'Tableau', 'PostgreSQL' )
GROUP  BY 1
HAVING Count(*) = 3
ORDER  BY 1; 

왜 이 두 가지 버전을 굳이 다 기재했냐면 (물론 정답은 빠르게 냈다)
둘 중에 어떤 쿼리가 더 우수한지 알아볼 수 있는
MySQL 명령어를 공부해보기 위함이다.

쿼리 앞에 explain을 치면 되는데, 예를 들어

EXPLAIN WITH p
     AS (SELECT candidate_id
         FROM   candidates
         WHERE  skill = 'Python'),
     t
     AS (SELECT candidate_id
         FROM   candidates
         WHERE  skill = 'Tableau'),
     ps
     AS (SELECT candidate_id
         FROM   candidates
         WHERE  skill = 'PostgreSQL')
SELECT p.candidate_id
FROM   p
       INNER JOIN t
               ON p.candidate_id = t.candidate_id
       INNER JOIN ps
               ON t.candidate_id = ps.candidate_id
ORDER  BY 1;

EXPLAIN SELECT candidate_id
FROM   candidates
WHERE  skill IN ( 'Python', 'Tableau', 'PostgreSQL' )
GROUP  BY 1
HAVING Count(*) = 3
ORDER  BY 1; 

이렇게 생긴 쿼리를 각각 실행하면

이런 결과를 얻을 수 있다. GPT에게 물어보니

• id : 1은 이 쿼리가 단일 SELECT 문으로 이루어져 있음을 나타냅니다.
• select_type: SIMPLE은 서브쿼리나 복잡한 조인이 없는 단순 SELECT 문임을 나타냅니다.
• table: candidates 테이블에서 데이터를 가져오는 것을 나타냅니다.
• type: ALL은 테이블의 모든 행을 스캔하는 것을 의미합니다. 이는 쿼리가 테이블을 풀 스캔(Full Table Scan)하고 있음을 나타내며, 성능에 좋지 않은 영향을 줍니다.
• possible_keys와 key: null로 표시된 것은 쿼리에서 사용 가능한 인덱스가 없고, 실제로 인덱스가 사용되지 않았음을 의미합니다.
• rows: MySQL이 예상하는 처리 행 수는 12로, 테이블 전체를 스캔하여 필요한 행을 찾을 것이라는 것을 의미합니다.
• filtered: 30은 WHERE 절에서 약 30%의 행이 필터링된다는 것을 의미합니다.
• Extra:
  - Using where: WHERE 절이 사용되고 있음을 나타냅니다.
  - Using temporary: 쿼리가 임시 테이블을 사용하고 있음을 나타냅니다. 이는 조인이나 그룹화, 정렬을 처리하기 위해 임시 데이터를 저장하고 있음을 의미하며, 성능에 부정적인 영향을 미칠 수 있습니다.
  - Using filesort: MySQL이 ORDER BY를 처리하기 위해 파일 정렬을 사용했음을 나타냅니다. 이는 성능에 부정적인 영향을 줄 수 있습니다.
  

이런 식으로 해석하면 된다고 한다.
전체적인 쿼리의 짜임새가 어떻고 이게 성능에 어떤 영향을 주는지
일목요연하게 정리해주는 기능인데, 당장 쓸 일은 없지만 기록해 두면
나중에 한 번 꺼내 쓸 일이 있을 것 같다.

오늘 코드카타는 여기까지.