240703_TIL

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

SQL 코드카타
여기저기 휩쓸리지 말고 묵묵히 할 일을 하자.

문제 링크

WITH outtime
     AS (SELECT event_day,
                emp_id,
                Sum(out_time) AS outtime
         FROM   employees
         GROUP  BY 1,
                   2),
     intime
     AS (SELECT event_day,
                emp_id,
                Sum(in_time) AS intime
         FROM   employees
         GROUP  BY 1,
                   2)
SELECT o.event_day      AS "day",
       o.emp_id,
       outtime - intime AS "total_time"
FROM   outtime o
       INNER JOIN intime i
               ON o.event_day = i.event_day
                  AND o.emp_id = i.emp_id
GROUP  BY 1,
          2;

문제 링크

WITH result
     AS (SELECT product_id,
                "store1" AS "store",
                store1   AS "price"
         FROM   products
         GROUP  BY 1,
                   2
         UNION ALL
         SELECT product_id,
                "store2" AS "store",
                store2   AS "price"
         FROM   products
         GROUP  BY 1,
                   2
         UNION ALL
         SELECT product_id,
                "store3" AS "store",
                store3   AS "price"
         FROM   products
         GROUP  BY 1,
                   2)
SELECT product_id,
       store,
       price
FROM   result
WHERE  price IS NOT NULL
ORDER  BY 1,
          2;

테이블이 여러 개일 때를 대비해
recursive CTE를 써서도 풀어보자.

문제 링크

SELECT employee_id,
       CASE
         WHEN employee_id%2 = 1
              AND name NOT LIKE 'M%' THEN salary
         ELSE 0
       end AS "bonus"
FROM   employees
ORDER  BY 1;

문제 링크

SELECT user_id,
       Max(time_stamp) AS "last_stamp"
FROM   logins
WHERE  Year(time_stamp) = '2020'
GROUP  BY 1;

문제 링크

WITH result
     AS (SELECT e.employee_id
         FROM   employees e
                LEFT JOIN salaries s
                       ON e.employee_id = s.employee_id
         WHERE  s.salary IS NULL
         UNION ALL
         SELECT s.employee_id
         FROM   salaries s
                LEFT JOIN employees e
                       ON s.employee_id = e.employee_id
         WHERE  e.NAME IS NULL)
SELECT employee_id
FROM   result
ORDER  BY 1;

문제 링크

WITH a
     AS (SELECT id,
                visit_date,
                people,
                Row_number()
                  OVER(
                    ORDER BY id) AS rownum
         FROM   stadium
         WHERE  people >= 100)
SELECT id,
       visit_date,
       people
FROM   a
WHERE  id - rownum IN (SELECT id - rownum
                       FROM   a
                       GROUP  BY 1
                       HAVING Count(*) >= 3);

모처럼 풀어본 난이도 hard 문제.

사람(people)이 100명 이상 방문한 날들의 id가 3번 이상 연속될 경우,
그 날짜에 해당하는 데이터들만 출력해야 하는 문제.
위의 예에서는 id 5~8까지가 이 조건에 해당한다.

주의해야 할 것은 날짜가 사흘 이상 연속되는 경우를 찾는 게 아니라는 점.
id 5~8번의 경우도 2017년 1월 8일의 데이터는 비어있지만,
100명 이상의 방문자가 찍힌 id가 3번 이상 연속되므로 문제가 없다.

먼저 people 값이 100 이상인 id만 찾고,
window 함수 중 row_number를 써서 'rownum'이라는 별칭을 붙여주었다.
이 함수를 써 둔 이유는 본 쿼리에서 컬럼처럼 활용하기 위해서다.
결과물을 a라는 이름의 CTE로 지정해 둔다.

WITH a
     AS (SELECT id,
                visit_date,
                people,
                Row_number()
                  OVER(
                    ORDER BY id) AS rownum
         FROM   stadium
         WHERE  people >= 100)

다음으로 본 쿼리에서는 id, visit_date, people을 불러오되,
위의 CTE에서 지정해 둔 id와 rownum의 차이를 따로 구해주었다.
100명 넘게 방문한 id가 '연속'된다면 rownum과 똑같이 1씩 증가할 것이므로,
id에서 rownum을 뺀 값도 일정하게 연속될 것이기 때문.

예를 들어, 첨부한 위의 이미지에서
100명 넘게 방문한 id가 5에서 8까지 연속된다면
rownum은 3,4,5,6으로 연속될 것이므로 (100명 넘는 경우만 출력하기로 한 CTE의 where절 때문에)
id에서 rownum을 뺀 값은 모두 2로 동일해질 것이다.

WITH a
     AS (SELECT id,
                visit_date,
                people,
                Row_number()
                  OVER(
                    ORDER BY id) AS rownum
         FROM   stadium
         WHERE  people >= 100)
SELECT id,
       visit_date,
       people
FROM   a
WHERE  id - rownum

결론적으로,
3회 이상 연속되는 경우의 수를 구하려면
id-rownum이 3 이상인 경우를 구하면 될 것이다. 따라서 where 절까지 완성하면

WITH a
     AS (SELECT id,
                visit_date,
                people,
                Row_number()
                  OVER(
                    ORDER BY id) AS rownum
         FROM   stadium
         WHERE  people >= 100)
SELECT id,
       visit_date,
       people
FROM   a
WHERE  id - rownum IN (SELECT id - rownum
                       FROM   a
                       GROUP  BY 1
                       HAVING Count(*) >= 3);

이렇게 완성된다.
본 쿼리의 where 절 아래에 서브쿼리를 넣는 게 싫다면
아예 그 부분까지 별도의 CTE로 만들어 놓고
본 쿼리에서는 b의 값들만 불러오는 방법도 있을 것 같다.
이러나 저러나 결과값은 동일하게 나올 것이다.

---

오늘부터 또다시 새로운 팀프로젝트에 들어가기 때문에
클러스터링에 따로 시간을 내기는 어려울 것 같다.
우선 눈앞의 팀플에 집중하고 주말이나 틈나는 때에 부지런히 해서 얼른 완성시켜야지.