[TIL] camp_day28_240524

프로젝트 발표까지 무사히 끝났다. 프로젝트 결과는 나중에 따로 정리할 생각이다.

학습 내용

---

1. SQL에서도 Python의 for문 처럼 반복하여 결과를 출력하게 할 수 있을까?!

   • 계속 못 풀고 있던 코딩테스트 문제가 있었다.

   • 문제 : 프로그래머스_입양 시각 구하기(2)

   • 오답 이유 : 0시부터 23시까지 모든 시간대에 대해 count 결과가 0이어도 행이 출력되어야 함.
     하지만 단순히 GROUP BY를 한 결과로는 데이터셋에 존재하는 시간대의 결과만 도출할 수 있어 오답 처리됨.

   • 정답 탐색 과정(1) : 다른 코딩테스트에서 풀었던 문제가 유사한 형태의 결과를 요구해서, 이 때 사용한 방법(UNION 활용)을 적용할 수 있었다.

       SQL
       -- 진짜 바보같이 풀어서 조금 부끄러움...
         SELECT
             t_h.HOUR
             ,IFNULL(`COUNT`,0) AS 'COUNT'
         FROM
         (
             SELECT '0' AS HOUR UNION
             SELECT '1' AS HOUR UNION
             SELECT '2' AS HOUR UNION
             SELECT '3' AS HOUR UNION
             SELECT '4' AS HOUR UNION
             SELECT '5' AS HOUR UNION
             SELECT '6' AS HOUR UNION
             SELECT '7' AS HOUR UNION
             SELECT '8' AS HOUR UNION
             SELECT '9' AS HOUR UNION
             SELECT '10' AS HOUR UNION
             SELECT '11' AS HOUR UNION
             SELECT '12' AS HOUR UNION
             SELECT '13' AS HOUR UNION
             SELECT '14' AS HOUR UNION
             SELECT '15' AS HOUR UNION
             SELECT '16' AS HOUR UNION
             SELECT '17' AS HOUR UNION
             SELECT '18' AS HOUR UNION
             SELECT '19' AS HOUR UNION
             SELECT '20' AS HOUR UNION
             SELECT '21' AS HOUR UNION
             SELECT '22' AS HOUR UNION
             SELECT '23' AS HOUR) AS t_h
         LEFT JOIN
         (
          SELECT 
             DATE_FORMAT(DATETIME, '%k') AS HOUR
             ,COUNT(ANIMAL_ID) AS 'COUNT'
          FROM 
             ANIMAL_OUTS
          GROUP BY 1
          ORDER BY 1 ASC
         ) AS c
             ON t_h.HOUR = c.HOUR

   • 정답 탐색 과정(2) : 정답은 구했는데 쿼리 행 개수도 그렇고, readable 한 쿼리도 아니고,
     모든 면에서 너무 불만족스러운 쿼리를 쓰게 되어서 더 발전시키고 싶었다.

   • Python 으로 알고리즘 문제를 풀 때 사용했던 for문과 while문 처럼,
     MySQL에서도 재귀함수와 비슷한 형태로 작동하는 구문이 있을 것 같아 찾아봤다.

   • 그러다 찾게 된 재귀적 CTE_Recursive CTE

   • Recursive CTE

     1. 구조

        1. intial query : 최초의 쿼리. anchor member 라고도 불림. 이 쿼리문을 바탕으로 재귀문이 작동
        2. recursive query : 재귀 쿼리. UNION ALL 이나 UNION DISTINCT 를 사용해서 조인한다.
        3. 종결 조건 : 반복문이 종료되는 시점부터 재귀 쿼리는 더 이상 행을 출력하지 않는다.

     2. 쿼리문 예시

         SQL
         WITH RECURSIVE cte_count (n)   -- 여기서 n은 컬럼 이름이다. RECURSIVE 라고 테이블명 앞에 적어야 작동한다.
         AS (
               SELECT 1
               UNION ALL     -- UNION, UNION ALL, UNION DISTINCT 사용 가능
               SELECT n + 1  -- sum() 같은 집계함수, GROUP BY, ORDER BY, LIMIT 그리고 DISTINCT 는 사용 불가능하다.
               FROM cte_count 
               WHERE n < 3   -- 이럴 경우 n은 1에서 3까지 출력되고 종료된다.
                                           -- 3 + 1 (=4)는 조건절에 해당하지 않는 범위라서 3까지 출력
             )
         SELECT n 
         FROM cte_count;

     3. 내 쿼리에 적용 : 앞선 쿼리문과 비교하면 정말 많이 발전해서 만족스럽다.

        SQL
        WITH RECURSIVE CTE (HOUR)
         AS (
             SELECT 0
             UNION ALL
             SELECT HOUR + 1
             FROM CTE
             WHERE HOUR < 23
         )
        
         SELECT
             CTE.HOUR
             ,IFNULL(`COUNT`,0) AS 'COUNT'  -- LEFT JOIN으로 생기는 NULL 값 처리하기 위한 함수
         FROM
             CTE
         LEFT JOIN
         (
          SELECT 
             DATE_FORMAT(DATETIME, '%k') AS HOUR
             ,COUNT(ANIMAL_ID) AS 'COUNT'
          FROM 
             ANIMAL_OUTS
          GROUP BY 1
          ORDER BY 1 ASC
         ) AS c
             ON CTE.HOUR = c.HOUR

2. 같은 문제 다른 쿼리로 풀어보기(SQL)

   • 문제 : 리트코드_급여 카테고리 별 수 구하기
   • 어제 SQL 챌린지반에서 알게된 쿼리 활용법을 적용하고 싶었고, 쿼리문이 작동할 때 런타임 차이가 있을까 궁금했다.
   • 물론 코딩테스트 사이트에서 푼 결과이므로... submit 할 때마다 달라지고 실제와 차이가 있을 수 있지만 그래도 혹시 모르니까!
   • 어제 배웠던 것 : 함수 내부에 CASE WHEN 구문을 넣어서 특정 데이터만 선택한 결과를 출력할 수 있다.

   1. CASE WHEN 활용

      • 런타임은 쿼리를 이렇게 작성할 때 더 길었다.
        

        

        SQL
        
          SELECT
              'High Salary' AS category
              ,COUNT(CASE WHEN income > 50000 THEN account_id ELSE NULL END) AS accounts_count
          FROM 
              Accounts
        UNION
          SELECT
              'Average Salary' AS category
              ,COUNT(CASE WHEN income BETWEEN 20000 AND 50000 THEN account_id ELSE NULL END) AS accounts_count
          FROM
              Accounts
        UNION
          SELECT
              'Low Salary' AS category
              ,COUNT(CASE WHEN income < 20000 THEN account_id ELSE NULL END) AS accounts_count
          FROM
              Accounts
        

   2. CASW WHEN 미사용

      • 이게 더 빨랐다. 왜 그럴까...? 하지만 쿼리는 위쪽이 더 깔끔한 것 같다.
        

        

        SQL
            SELECT
                'High Salary' AS category
                ,COUNT(account_id) AS accounts_count
            FROM 
                Accounts
            WHERE income > 50000
        UNION
            SELECT
                'Average Salary' AS category
                ,COUNT(account_id) AS accounts_count
            FROM
                Accounts
            WHERE income <= 50000
                AND income >= 20000
        UNION
            SELECT
                'Low Salary' AS category
                ,COUNT(account_id) AS accounts_count
            FROM
                Accounts
            WHERE income < 20000
        

고찰 & 기타

---

• SQL 쿼리를 발전시켜보려고 새로 배운 내용을 적용하고, 검색해서 알게 된 부분을 활용해보았다.
• SQL은 재밌는데 Python은 왜 정이 가지 않을까... 다음 주에는 알고리즘 풀이에 시간과 관심과 애정을 좀 더 기울여야겠다.