[MySQL] 2주차

https://leetcode.com/problems/reformat-department-table/

Reformat Department Table - LeetCode

[if(조건, 참, 거짓), CASE WHEN 사용]

SELECT id
       ,SUM(CASE WHEN month = 'Jan' THEN revenue ELSE null END) as 'Jan_Revenue' 
       ,SUM(IF(month='Feb', revenue, null))as 'Feb_Revenue'
       ,SUM(IF(month='Mar', revenue, null))as 'Mar_Revenue'
       ,SUM(IF(month='Apr', revenue, null))as 'Apr_Revenue'
       ,SUM(IF(month='May', revenue, null))as 'May_Revenue'
       ,SUM(IF(month='Jun', revenue, null))as 'Jun_Revenue'
       ,SUM(IF(month='Jul', revenue, null))as 'Jul_Revenue'
       ,SUM(IF(month='Aug', revenue, null))as 'Aug_Revenue'
       ,SUM(IF(month='Sep', revenue, null))as 'Sep_Revenue'
       ,SUM(IF(month='Oct', revenue, null))as 'Oct_Revenue'
       ,SUM(IF(month='Nov', revenue, null))as 'Nov_Revenue'
       ,SUM(IF(month='Dec', revenue, null))as 'Dec_Revenue'
FROM Department
GROUP BY id

 

 

 

 

https://leetcode.com/problems/exchange-seats/

Exchange Seats - LeetCode

[cte 사용]

WITH tmp AS(
    SELECT id, student
           ,LEAD(student, 1) OVER(ORDER BY id) AS 'next_student'
         ,LAG(student, 1) OVER(ORDER BY id) AS 'pre_student'
    FROM Seat
)
SELECT id
       ,CASE 
             WHEN MOD(id, 2) = 1 AND next_student IS NOT NULL THEN next_student 
             WHEN MOD(id, 2) = 0 THEN pre_student
             ELSE student
        END AS 'student'
FROM tmp

[다중 case when, from절 서브쿼리 사용]

SELECT id
       ,CASE 
             WHEN MOD(id, 2) = 1 AND next_student IS NOT NULL THEN next_student 
             WHEN MOD(id, 2) = 0 THEN pre_student
             ELSE student
        END AS 'student'
FROM(
    SELECT id, student
           ,LEAD(student, 1) OVER(ORDER BY id) AS 'next_student'
         ,LAG(student, 1) OVER(ORDER BY id) AS 'pre_student'
    FROM Seat
    )tmp

* 알게된 사실: 컬럼명을 예약어로 사용할 경우, ''(작은 따옴표)로 묶어준다면 에러없이 사용 가능

ex)     SELECT id, student
                  ,LEAD(student, 1) OVER(ORDER BY id) AS 'lead'
                  ,LAG(student, 1) OVER(ORDER BY id) AS 'lag'
         FROM Seat

 

하지만 다음과 같이 상위 SELECT 절을 FROM 절의 서브쿼리로 만들어 사용할 경우, CASE WHEN 조건절에서 [예약어를 사용한 컬럼명]을 처리할 때 문제 발생

SELECT id
       ,CASE 
             WHEN MOD(id, 2) = 1 AND 'lead' IS NOT NULL THEN 'lead' 
             WHEN MOD(id, 2) = 0 THEN 'lag'
             ELSE student
        END AS 'student'
FROM(
    SELECT id, student
           ,LEAD(student, 1) OVER(ORDER BY id) AS 'lead'
         ,LAG(student, 1) OVER(ORDER BY id) AS 'lag'
    FROM Seat
    )tmp

위와 같은 쿼리로 접근할 경우, [CASE 문으로 생성된 컬럼명 student]의 컬럼값으로 [lead, lag 컬럼의 컬럼값]이 대입되는 것이 아닌 [문자열 'lead', 문자열 'lag']가 저장되는 것을 알 수 있다.

-> 교훈 : 컬럼명이나 변수명, 데이터베이스 테이블명으로 예약어를 사용하는건 되도록이면 피하자! 

 

 

 

https://leetcode.com/problems/human-traffic-of-stadium/

Human Traffic of Stadium - LeetCode

[cte 사용]

WITH tmp AS(SELECT s.id, s.visit_date, s.people
                  ,s.id-ROW_NUMBER() OVER(ORDER BY s.id) as grp
                  ,ROW_NUMBER() OVER(ORDER BY s.id) as row_num
            FROM Stadium s
            WHERE s.people >= 100
           )
SELECT tmp.id, tmp.visit_date, tmp.people
FROM tmp
WHERE grp in(
              SELECT grp
              FROM tmp
              GROUP BY grp
              HAVING COUNT(*) >= 3
             )

row_number 함수를 통해 id 순으로 순위를 부여해준 컬럼을 row_num이라고 하자.

row_num 값을 각 id값과 빼면 다음과 같이 연이은 id 번호에 대해 일정한 패턴이 나타난다. 즉, 연속된 id 번호는 grp에서 같은 값을 가지게 된다. 따라서 grp로 그룹화하여 그룹별 행의 개수가 3이상인 row들을 출력해주면 정답이 된다.    

[윈도우 함수 사용]

SELECT id, visit_date, people
FROM(
        SELECT *
               ,LEAD(people, 1) OVER(ORDER BY id) as lead1
               ,LEAD(people, 2) OVER(ORDER BY id) as lead2
               ,LAG(people, 1) OVER(ORDER BY id) as lag1
               ,LAG(people, 2) OVER(ORDER BY id) as lag2
        FROM Stadium
    )t
WHERE (people >= 100 AND lead1 >= 100 AND lead2 >=100)
      OR (people >= 100 AND lag1 >= 100 AND lag2 >=100)