구름 아카이브

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

습득한 점: 

연속되는 숫자를 찾는 새로운 아이디어.

GROUP BY, PARTITION BY 차이 재확인.

hard 난이도는 아니었다.

문제:

사람 수 (people)가 100이상일 때, 3개 이상의 연속되는 "id"값에 대한 행을 모두 조회하는 쿼리 작성 

결과값은 "visit_date" 기준으로 오름차순 정렬

문제파악:

일단 visit_date는 정렬 빼고는 신경 쓸 필요는 없을 것 같았고,

우선 사람 수가 100명 이상인 테이블을 걸러낸 결과값을

WITH문으로 테이블을 새롭게 구성한 뒤 쿼리 작성을 시작하면 될 것 같다.

대략적으로 2가지 풀이법이 보인다. 셀프 조인 혹은 윈도우 함수 사용하기

풀이:

1. 'people'이 100 이상인 행들을 걸러냈을 때, id 값의 모양을 예상해본다.

데이터 사이사이에 100보다 낮은 값들이 포함되어 있었을 것이니,
아래 표와 같이 row_number()를 활용해서 강제적으로 맨 위부터 순위를 매긴 후
id 값에서 row_number값을 뺀 차이값 diff를 구하면,
연속되는 id값에서 벗어나는 순간에 diff값은 숫자가 바뀌게 된다.

ex) 아래 표를 예시로 든다면, id_1이 4에서 6으로 변할때, 차이값은 0에서 1로 변한다.

ex), id_2에서 5로 변할때, 차이값은 0에서 2로 변한다.

일종의 파티션을 나눈다고 생각하면 된다.

이런식의 계산을 이용하려면, 반드시 id 값을 기준으로 오름차순 정렬이 먼저 이루어져 있어야 한다.

WITH Sorted AS (
SELECT id
     , visit_date
     , people
     , id - ROW_NUMBER() OVER (ORDER BY id) AS diff
FROM Stadium
WHERE people >=100
ORDER BY id     
)​

2.  diff를 기준으로 GROUP BY 이후,  행의 갯수 조회

기존 만들어진 WITH문을 그대로 이용해서 SELECT문에는 count(*)을 추가하고,  diff 기준으로 groupby,
HAVING count(*) >= 3을 조건으로 쿼리를 실행하면 diff의 그룹별 행의 갯수가 표현된다.

WITH Sorted AS (
SELECT id
     , visit_date
     , people
     , id - ROW_NUMBER() OVER (ORDER BY id) AS diff
FROM Stadium
WHERE people >=100
ORDER BY id     
)

, Ranked AS (
    SELECT id
         , visit_date
         , people
         , diff
         , COUNT(*) AS consecutive_num
    FROM Sorted
    GROUP BY diff
    HAVING COUNT(*) >=3
)

3. 찾아낸 diff를 활용하여 정답 찾기

"consecutive_num" 은 3이상이면 나타나는 값이니 신경 쓸 필요는 없다.

2번의 테스트 케이스에 따르면, diff 가 2인 것이 연속된 id 4개를 가지고 있다는 것이니, 
연속되는 id값의 행들만 찾고 싶다면, diff를 기준으로 Sorted 테이블과 inner 조인 시켜주면 된다.

문제에 날짜기준 오름차순 정렬조건도 있지만, id가 커짐에따라 방문 일자도 하루씩 늘어나기 때문. 

WITH Sorted AS (
SELECT id
     , visit_date
     , people
     , id - ROW_NUMBER() OVER (ORDER BY id) AS diff
FROM Stadium
WHERE people >=100
ORDER BY id     
)

, Ranked AS (
    SELECT id
         , visit_date
         , people
         , diff
         , COUNT(*) AS consecutive_num
    FROM Sorted
    GROUP BY diff
    HAVING COUNT(*) >=3
)

SELECT Sorted.id
     , Sorted.visit_date
     , Sorted.people
       
FROM Sorted
   INNER JOIN Ranked ON Sorted.diff = Ranked.diff

PARTITION BY 이용:

WITH Sorted AS (
    SELECT id
         , visit_date
         , people
         , id - ROW_NUMBER() OVER (ORDER BY id) AS diff
    FROM Stadium
    WHERE people >= 100
),
    consecutive_count AS (
        SELECT id
            , visit_date
            , people
            , diff
            , COUNT(*) OVER (PARTITION BY diff) AS consecutive_num
        FROM Sorted
    )

SELECT *
FROM consecutive_count
ORDER BY id

큰 차이는 없지만, PARTITION BY를 활용하면 더욱 직관적으로 쿼리를 작성할 수 있다.

무엇보다 GROUP BY는 조건에 따른 그룹의 첫번째 행만을 남기고, 이외 행은 다 날리게 되지만
(아래 output케이스는 Ranked 테이블에서 HAVING count(*) >=3 조건만 없다면 count(*) id가 2, 5인 행만 남을 것이다)

PARTITION BY는 조건에 해당하는 모든 id 행을 보여준다.

이후 consecutive_num >=3  인 값들만 찾으면 된다.

또한 Sorted테이블 이후에는, 정렬순서가 유지되지 않을 수도 있기에

+ ORDER BY id 추가해주면 된다.

 (*** GROUP BY를 활용하는 경우, 이미 id 기준 오름차순 정렬된 테이블에 inner 조인하는 것이 때문에 따로 추가할 필요는 없다)

https://leetcode.com/problems/department-top-three-salaries/

습득한 점:

SELECT val
     , ROW_NUMBER() OVER (ORDER BY val) AS row_number
     , RANK() OVER (ORDER BY val) AS rank
     , DENSE_RANK() OVER (ORDER BY val) AS dense_rank
FROM Sample;

- row_number는 어떻게 해서든 123456 으로 순위 매김 (중복포함)

- rank는 동일 값에 같은 등수를 부여, 공동 등수의 갯수를 고려한, 다음 순위를 매김.
  아래 예시의 경우에는 2등과 5등은 스킵한 뒤 순위를 매김

- dense_rank는 공동 등수를 하나로 보고 다음 순위를 매김

문제:

A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write a solution to find the employees who are high earners in each of the departments.

Return the result table in any order.

요약: # 각 부서에서 직원들의 급여 순으로, 부서별 1위 2위 3위 찾기 (급여가 같은 사람이 2명이상이라면 모두 포함시키기)

문제파악:

풀이:

난이도가 hard 이지만 어렵진 않았다.

전체적인 틀은 같지만,

FROM 절에 넣어도 되고 WITH 절에 넣어도 괜찮을 것 같다.

1) WITH절 활용

WITH COUNTER AS (
      SELECT Dep.name DepName
          , Emp.name EmpName
          , salary
          , DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS denserank
      FROM Employee AS Emp
          INNER JOIN Department AS Dep ON Dep.id = Emp.departmentId
                 )

SELECT DepName AS Department
     , EmpName AS Employee
     , salary
FROM COUNTER
WHERE denserank <=3

2) FROM절 서브쿼리 활용

SELECT t.department
     , t.employee
     , t.salary
     
FROM(
    SELECT department.name AS dapartment
        , employee.name AS employee
        , employee.salary
        , DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS dr

    FROM Employee
        INNER JOIN dapartment ON employee.departmentId = dapartment.id
    ) t
WHERE t.dr <=3