2024-01-22

SQL 코드카타

 

1164. Product Price at a Given Date(SQL) (where in 서브쿼리와 exists 서브쿼리, MAX, GROUP, case when, with, left join, 가장 최근 날짜기준으로 데이터 가져오기)

https://leetcode.com/problems/product-price-at-a-given-date/description/

기본적으로 모든 상품의 기본값은 10으로 정해두고 바뀌면 product_id를 통해 어떤 상품이 change_date를 통해 언제 , new_price얼마로 바뀌었는지 확인 할 수 있는데 2019-08-16을 기준으로 모든 상품의 가격을 출력하는 문제이다.

SELECT p.product_id,
        p.new_price,
        p.change_date,
        MAX(p.change_date)
FROM Products p
WHERE change_date <= '2019-08-16'
GROUP BY p.product_id
HAVING p.change_date = MAX(p.change_date)

이렇게 하면

| product_id | new_price | change_date |
| ---------- | --------- | ----------- |
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  |
| 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  |
| 3          | 20        | 2019-08-18  |

#결과
| product_id | new_price | change_date | MAX(p.change_date) |
| ---------- | --------- | ----------- | ------------------ |
| 2          | 50        | 2019-08-14  | 2019-08-14         |

#HAVING을 지웠을 경우 결과
| product_id | new_price | change_date | MAX(p.change_date) |
| ---------- | --------- | ----------- | ------------------ |
| 1          | 20        | 2019-08-14  | 2019-08-16         |
| 2          | 50        | 2019-08-14  | 2019-08-14         |

#아마 group은 무조건 첫행에 대해 나오는데 이 때 max랑 같은 경우만 출력 된듯하다

HAVING p.change_date = MAX(p.change_date) 형태는 무조건 group내 첫 행과 비교하는듯

맨날 MAX가 문제인 것 같음..ㅠ case when으로 해줘도 MAX랑 비교하면 한번만 하는 듯…

SELECT CASE 
    WHEN p_s.change_date = MAX(p_s.change_date) THEN p_s.change_date
    END date
FROM Products p_s 
WHERE p_s.change_date <= '2019-08-16' 
AND p_s.product_id = 1

#out
| date |
| ---- |
| null |
#확인 해본 결과로 
#MAX(p_s.change_date)자체는 where로 필터링 된 데이터 내에서 max날짜인 것 확인했음
#group을 지어주지 않았어도 max와 비교하면 한번만하는듯..

우선 where절에서 in과 기준일을 기준으로 각 상품별 가장 최신 변경날짜와 가격을 출력하는 서브쿼리를이용하여 거기에 해당하는 행만을 출력한 latest_update테이블을 선언 후 기존 Products테이블과 join하면 어떻게 나오는지 우선 확인해보았다

WITH latest_update AS(
    SELECT p2.product_id,
        p2.new_price,
        p2.change_date
    FROM Products p2
    WHERE p2.change_date <= '2019-08-16'
        AND (p2.product_id, p2.change_date) IN (
                SELECT p_s.product_id, MAX(p_s.change_date) latest_date
                FROM Products p_s
                WHERE p_s.change_date <= '2019-08-16'
                GROUP BY p_s.product_id
                )
)
SELECT *
FROM Products p
LEFT JOIN latest_update lu ON p.product_id = lu.product_id

#out
| product_id | new_price | change_date | product_id | new_price | change_date |
| ---------- | --------- | ----------- | ---------- | --------- | ----------- |
| 1          | 20        | 2019-08-14  | 1          | 35        | 2019-08-16  |
| 2          | 50        | 2019-08-14  | 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  | 1          | 35        | 2019-08-16  |
| 1          | 35        | 2019-08-16  | 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  | 2          | 50        | 2019-08-14  |
| 3          | 20        | 2019-08-18  | null       | null      | null        |

내가 생각했던대로 잘 나왔다(각 상품 아이디에 대해 lu에 관한 데이터는 가장 기준일로 기준하여 최근변경데이터로, 그리고 기준일 보다 이전에 변경사항이 없다면 null로)

그리고 내가 자꾸 헷깔리는 것 같기도해 혹시 또 잊었을 때를 위해 적어두는데 IN안에 서브쿼리를 그냥 바로 쓰면 MAX에 매칭된 change_date데이터가 아닌 그냥 그룹내 제일 첫행의 데이터가 나오기 때문에 위와 같은 방법을 사용해줘야할 필요성이 있었다

WITH latest_update AS(
    SELECT p2.product_id,
        p2.new_price,
        p2.change_date
    FROM Products p2
    WHERE p2.change_date <= '2019-08-16'
        AND (p2.product_id, p2.change_date) IN (
                SELECT p_s.product_id, MAX(p_s.change_date) latest_date
                FROM Products p_s
                WHERE p_s.change_date <= '2019-08-16'
                GROUP BY p_s.product_id
                )
)
SELECT p.product_id , 
        CASE WHEN lu.product_id IS NULL THEN 10
        ELSE lu.new_price END price
FROM Products p
LEFT JOIN latest_update lu ON p.product_id = lu.product_id
GROUP BY p.product_id

그렇게 해준 다음 문제에서 요구하는 대로 출력을 해주는데 기준일 이전에 변경사항이 없는 경우는 lu.chagne_date가 null로 되어있으므로 case when을 사용하여 기본값으로 10으로 출력해주었다

아래는 EXISTS로 시도한 방식이다

WITH latest_update AS(
    SELECT p2.product_id,
        p2.new_price,
        p2.change_date
    FROM Products p2
    WHERE p2.change_date <= '2019-08-16'
        AND EXISTS (
                SELECT 1
                FROM Products p_s
                WHERE p_s.change_date <= '2019-08-16'
                 AND p2.product_id = p_s.product_id
                 AND p2.change_date = p_s.change_date
                HAVING MAX(p_s.change_date) = p2.change_date
                )
)
SELECT *
FROM Products p
LEFT JOIN latest_update lu ON p.product_id = lu.product_id

#out
| product_id | new_price | change_date | product_id | new_price | change_date |
| ---------- | --------- | ----------- | ---------- | --------- | ----------- |
| 1          | 20        | 2019-08-14  | 1          | 35        | 2019-08-16  |
| 2          | 50        | 2019-08-14  | 2          | 50        | 2019-08-14  |
| 3          | 20        | 2019-08-18  | null       | null      | null        |

각 행에 대해 중복없이 유니크하게 확인하기 위해서 연결하는 조건을 p2.product_id = p_s.product_id AND p2.change_date = p_s.change_date으로 두 개를 써주었다 그리고 in에서는 select에 바로 Max를 사용하여 MAX날짜가 출력되는 방식으로 해주었지만 EXISTS는 각 메인쿼리에서 하고있는 행(여기서는 with문내에서만 볼 때의 메인쿼리)에 대해 해당하는 행이 원하는 행에 포함된다 안된다를 판별해야하기 때문에 Having을 통해 필터링해주었다.

WITH latest_update AS(
    SELECT p2.product_id,
        p2.new_price,
        p2.change_date
    FROM Products p2
    WHERE p2.change_date <= '2019-08-16'
        AND EXISTS (
                SELECT 1
                FROM Products p_s
                WHERE p_s.change_date <= '2019-08-16'
                 AND p2.product_id = p_s.product_id
                 AND p2.change_date = p_s.change_date
                HAVING MAX(p_s.change_date) = p2.change_date
                )
)
SELECT p.product_id , 
        CASE WHEN lu.product_id IS NULL THEN 10
        ELSE lu.new_price END price
FROM Products p
LEFT JOIN latest_update lu ON p.product_id = lu.product_id
GROUP BY p.product_id

그냥 바로 통과될줄 알았는데 제출하니 tc통과 안된 케이스가 꽤 많아서 좀 확인을 해줘야 할듯하다

아래는 중간 시행착오 과정이다

SELECT *
FROM Products p
WHERE p.change_date <= '2019-08-16'
    AND p.product_id = 3

#out
| product_id | new_price | change_date |
| ---------- | --------- | ----------- |
| 3          | 64        | 2019-08-08  |
| 3          | 72        | 2019-07-22  |
| 3          | 21        | 2019-06-19  |
| 3          | 23        | 2019-08-07  |
#로 제일 첫행이 64로 나와야한다
#일단 아래 join 어떻게 나오는지만 확인했을 때(데이터가 길어서 3에대해서만 나오도록했음)
WITH latest_update AS(
    SELECT p2.product_id,
        p2.new_price,
        p2.change_date
    FROM Products p2
    WHERE p2.change_date <= '2019-08-16'
        AND EXISTS (
                SELECT 1
                FROM Products p_s
                WHERE p_s.change_date <= '2019-08-16'
                 AND p2.product_id = p_s.product_id
                 AND p2.change_date = p_s.change_date
                HAVING MAX(p_s.change_date) = p2.change_date
                )
)
SELECT *
FROM Products p
LEFT JOIN latest_update lu ON p.product_id = lu.product_id
GROUP BY p.product_id
HAVING p.product_id = 3

#out
| product_id | new_price | change_date | product_id | new_price | change_date |
| ---------- | --------- | ----------- | ---------- | --------- | ----------- |
| 3          | 64        | 2019-08-08  | 3          | 23        | 2019-08-07  |
#저렇게 테이블이 나온 것을 확인할 수 있었는데
#아래와 같이 lu테이블의 결과를 확인해봤는데 (마찬가지로 3에 대해서만)
WITH latest_update AS(
    SELECT p2.product_id,
        p2.new_price,
        p2.change_date
    FROM Products p2
    WHERE p2.change_date <= '2019-08-16'
        AND EXISTS (
                SELECT 1
                FROM Products p_s
                WHERE p_s.change_date <= '2019-08-16'
                 AND p2.product_id = p_s.product_id
                 AND p2.change_date = p_s.change_date
                HAVING MAX(p_s.change_date) = p2.change_date
                )
)
SELECT *
FROM latest_update lu
WHERE lu.product_id = 3

#out
| product_id | new_price | change_date |
| ---------- | --------- | ----------- |
| 3          | 64        | 2019-08-08  |
| 3          | 72        | 2019-07-22  |
| 3          | 21        | 2019-06-19  |
| 3          | 23        | 2019-08-07  |
#하나가 아닌 여러개의 행이 나왔다 생각해보니 위의 조건에서 
#p2.product_id = p_s.product_id AND p2.change_date = p_s.change_date를 해줬는데
#이러면 각 한 행에 대해서만 max를 구하게 되어 
#모든 값이 max가 되어 다 출력되는게 맞는 것 같기도하다

#다만 한가지 의문은 왜 위에 join한 결과테이블에서 
#제일 첫행이 아니라 마지막행에 해당하는 행과 연결이 된 것인지 의문이다

#해결 방법 자체는 그냥 p2.change_date = p_s.change_date대신 
#product_id에 대하여 group을 해주면 될듯하다

WITH latest_update AS(
    SELECT p2.product_id,
        p2.new_price,
        p2.change_date
    FROM Products p2
    WHERE p2.change_date <= '2019-08-16'
        AND EXISTS (
                SELECT 1
                FROM Products p_s
                WHERE p_s.change_date <= '2019-08-16'
                 AND p2.product_id = p_s.product_id
                GROUP BY p_s.product_id
                HAVING MAX(p_s.change_date) = p2.change_date
                )
)
SELECT p.product_id , 
        CASE WHEN lu.product_id IS NULL THEN 10
        ELSE lu.new_price END price
FROM Products p
LEFT JOIN latest_update lu ON p.product_id = lu.product_id
GROUP BY p.product_id

그런데 살짝 생각외로 in방식에 비해 훨씬 성능이 안 좋은듯하다

추가로 그냥 쌩 join을 이용해서 하는 방법도 있을 듯 한데 성능이 별로일 것이라고 예상되고, 현재는 이것보다는 다른 할 일이 중요성이 더 높다 판단되어 현재는 이정도만 더 시도해보고 마무리 짓도록 하겠다

 

1204. Last Person to Fit in the Bus(SQL) (where 서브쿼리문, with, limit, 각 행을 합하여 구하는데 가장 높은 값하나만 출력하는 방식)

https://leetcode.com/problems/last-person-to-fit-in-the-bus/description/

순서대로 승객을 태우는데 전체 무게가 1000이하가 되도록 승객을 태울 때 마지막 승객의 이름을 출력하는 문제이다.

우선 문제의 설명방식대로 그대로 해보겠다

WITH total_by_turn AS (
    SELECT q.turn, q.person_id, q.person_name, q.weight,
    (SELECT SUM(q_s.weight) FROM Queue q_s 
                            WHERE q_s.turn<=q.turn) sum_weight
    FROM Queue q
    ORDER BY q.turn DESC
)
SELECT tbt.person_name
FROM total_by_turn tbt
WHERE tbt.sum_weight <=1000
LIMIT 1

그 뒤 다른사람들이 주로 했던 방식 중에 먼저 떠오르는 각 순서마다 그 순서까지의 무게를 다 더한 값을 구하여 무게조건을 만족하는 순서까지 가져온다음 순서로 내림차순 정렬해준 뒤 가장 위의 데이터 하나만을 출력하는 방식으로 해보겠다

SELECT q.person_name
FROM Queue q
WHERE 1000 >= (
    SELECT SUM(q_s.weight) 
    FROM Queue q_s 
    WHERE q_s.turn<=q.turn
)
ORDER BY q.turn DESC
LIMIT 1;

막상 작성하고 나니 내가 처음한 것과 크게 다르지 않다는 것을 깨닫게 되었다 단지 with를 사용해 임시테이블을 선언해주고 하냐 안하고 하냐 정도 인듯한데 데이터가 그리 크지 않은 것인지 생각외로 그리 성능차이는 나지 않는 듯 하였다. (그리고 서브쿼리에서는 테이블명 안붙이는 것이 더빠른가? 라는 의문도 조금 생겼다)

 

1907. Count Salary Categories(SQL) (union, 집계함수 sum등 괄호 안에 조건넣어서 바로 사용가능한듯?, ifnull이라고 null처리방법 내용 살짝 포함?,다른방식 못 찾겠음)

https://leetcode.com/problems/count-salary-categories/description/

카테고리를 low, average, high로 나눠서 각 계좌의 수입을 각 카테고리에 맞는 갯수를 출력하는 문제이다.

WITH segment AS(
    SELECT a.account_id,
        CASE 
            WHEN a.income > 50000 THEN "High Salary"
            WHEN a.income BETWEEN 20000 and 50000 THEN "Average Salary"
            ELSE "Low Salary" 
        END category
    FROM Accounts a
)
SELECT s.category , COUNT(s.account_id) accounts_count
FROM segment s
GROUP BY s.category;

처음에는 이렇게 해주었으나 average에 해당하는 데이터가 하나도 없어 average에 대해서는 표시 되지 않아 틀리고 말았다. 없는 부분을 그냥 추가해줄려면 번거로울 것 같아서 역시 union을 쓰는 것이 좋을 듯하여 시도해보았다.

SELECT "High Salary" category,
    COUNT(*) accounts_count
FROM Accounts a_h 
WHERE a_h.income > 50000
UNION
SELECT "Average Salary" category,
    COUNT(*) accounts_count
FROM Accounts a_a 
WHERE a_a.income BETWEEN 20000 and 50000
UNION
SELECT "Low Salary" category,
    COUNT(*) accounts_count
FROM Accounts a_l 
WHERE a_l.income < 20000

추가로 union을 쓰지않고 해볼려고 했는데 매우 복잡해지는 듯하고 딱히 되는 방법이 생각나지 않아서(직접 데이터베이스에 데이터 추가해주는 식으로 안해주는 한) 여기서 마무리 짓도록하겠다

 다른분이 적은 쿼리인데 집계함수 sum안에 바로 조건을 적어서 사용하는 방식도 되는 건가 해서 가져와봤다

SELECT 
    'Low Salary' AS category,
    SUM(income < 20000) AS accounts_count
FROM 
    Accounts

UNION 

    SELECT 
        'Average Salary' AS category,
        SUM(income BETWEEN 20000 AND 50000 ) AS accounts_count
    FROM 
        Accounts

UNION

    SELECT 
        'High Salary' AS category,
        SUM(income > 50000) AS accounts_count
    FROM 
        Accounts;

그리고 추가로 NULL이라는 함수?있음 IFNULL(확인할 컬럼, 변환값)

오늘은 SQL코드카타 3문제와 통계학 기초 강의를 마저 들었다 주말내 일찍 잠들지 못해서 그런지 오늘 다소 많이 피곤한 상태여서 그랬는지 안그래도 어렵다고 느끼던 통계학 내용이 더더욱 머리에 잘 들어오지 않고 이해가 되지 않았다. 그리고 초반에는 평소 내 스타일대로(어려운 부분일수록) 세세하게 필기를 해가면서 듣고 있었는데 너무 진도가 안나가고 원래 계획한 오늘내로 다 못들을 듯하여 결국 일차적으로 타협하고 데이터 전처리 & 시각화 강의 까지 다듣고 틈틈히 다시 복습하면서 듣는 방식으로 우회하였다(원래도 어렵다고 생각했기에 복습할 생각은 많이 있었지만 들으면서 적어도 살짝 긴가 민가한 부분도 있고 조급하게 쫓기면서 들으면 더더욱 효율이 안좋을듯 할 것 같아 과감히 틀었다 그렇게 거의 필기를 하지 않고 남은 부분 다들어도 정규시간이 지난 10시조금 넘어 강의를 1회차 완강하였다) 여러차례 통계학 강의 내에서 알려준 부분이 어렵기는 하지만 그래도 내가 흥미를 끄는 쪽을 가려면 해두는게 좋을 듯하여 앞으로도 공부해야할 내용들이 많이 남은 것을 생각하면 계속 헤이해지지말고 열심히 더 공부해야겠다는 생각을 다시 한번 가졌다 그리고 차후 어느정도 데이터분석가와 데이터사이언티스트 등 관련하여 각 직업별로 하는 업무 등과 필요한 내용 등이 머리속에 정리가 되면 이번 통계학 기초를 강의해주신 안창배튜터님을 비롯하여 관심가는 분야나 방식의 업무를 하시는 튜터님들에게 찾아가서 상담을 계획해봐야겠다