sql, partition by 활용하기

각 회원 별 가장 많이 구매한 물건은?

• https://leetcode.com/problems/the-most-frequently-ordered-products-for-each-customer/
• 회원이 있다. 각 회원이 구매한 상품이 있다.
• 각 회원이 가장 많이 구매한 물품을 구한다.

Input: 
Customers table:
+-------------+-------+
| customer_id | name  |
+-------------+-------+
| 1           | Alice |
| 2           | Bob   |
| 3           | Tom   |
| 4           | Jerry |
| 5           | John  |
+-------------+-------+
Orders table:
+----------+------------+-------------+------------+
| order_id | order_date | customer_id | product_id |
+----------+------------+-------------+------------+
| 1        | 2020-07-31 | 1           | 1          |
| 2        | 2020-07-30 | 2           | 2          |
| 3        | 2020-08-29 | 3           | 3          |
| 4        | 2020-07-29 | 4           | 1          |
| 5        | 2020-06-10 | 1           | 2          |
| 6        | 2020-08-01 | 2           | 1          |
| 7        | 2020-08-01 | 3           | 3          |
| 8        | 2020-08-03 | 1           | 2          |
| 9        | 2020-08-07 | 2           | 3          |
| 10       | 2020-07-15 | 1           | 2          |
+----------+------------+-------------+------------+
Products table:
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
| 1          | keyboard     | 120   |
| 2          | mouse        | 80    |
| 3          | screen       | 600   |
| 4          | hard disk    | 450   |
+------------+--------------+-------+
Output: 
+-------------+------------+--------------+
| customer_id | product_id | product_name |
+-------------+------------+--------------+
| 1           | 2          | mouse        |
| 2           | 1          | keyboard     |
| 2           | 2          | mouse        |
| 2           | 3          | screen       |
| 3           | 3          | screen       |
| 4           | 1          | keyboard     |
+-------------+------------+--------------+

해결 방안

• 구매한 상품에 대하여, 각 회원별로 구매한 상품을 그룹핑하고 그것의 빈도를 구하고, 해당 빈도가 가장 많은 상품을 추출한다.
• 이를 해결할 적절한 문법을 찾기 어려웠다. select max(count(product_id)) 혹은 having max(count(product_id)) 등 요상한 문법을 작성해봤지만 동작하지 않았다.
• partition by를 사용하여 해결하였다. partition by를 통해 그룹핑을 하고 order by를 기준으로 랭크를 매긴다. 랭크가 1인 값 전체를 추출한다.

쿼리

select 
    tb.customer_id
    , tb.product_id
    , p.product_name
from (
    select 
        customer_id
        , product_id
        , rank() over(partition by customer_id  order by count(product_id) desc ) ranks
    from orders
    group by customer_id, product_id
)tb
join Products p on p.product_id = tb.product_id
where tb.ranks = 1 

각 통장 별 잔액 구하기 Account Balance

• 아래의 테이블은 거래내역이다.
• 각 account_id는 balance를 0원으로부터 시작한다.
• 거래는 입금과 출금만 존재하며 amount로 금액을 보여준다.
• 각 거래마다의 남은 잔액(balance)를 구해야 한다.

Input: 
Transactions table:
+------------+------------+----------+--------+
| account_id | day        | type     | amount |
+------------+------------+----------+--------+
| 1          | 2021-11-07 | Deposit  | 2000   |
| 1          | 2021-11-09 | Withdraw | 1000   |
| 1          | 2021-11-11 | Deposit  | 3000   |
| 2          | 2021-12-07 | Deposit  | 7000   |
| 2          | 2021-12-12 | Withdraw | 7000   |
+------------+------------+----------+--------+
Output: 
+------------+------------+---------+
| account_id | day        | balance |
+------------+------------+---------+
| 1          | 2021-11-07 | 2000    |
| 1          | 2021-11-09 | 1000    |
| 1          | 2021-11-11 | 4000    |
| 2          | 2021-12-07 | 7000    |
| 2          | 2021-12-12 | 0       |
+------------+------------+---------+
Explanation: 
Account 1:
- Initial balance is 0.
- 2021-11-07 --> deposit 2000. Balance is 0 + 2000 = 2000.
- 2021-11-09 --> withdraw 1000. Balance is 2000 - 1000 = 1000.
- 2021-11-11 --> deposit 3000. Balance is 1000 + 3000 = 4000.
Account 2:
- Initial balance is 0.
- 2021-12-07 --> deposit 7000. Balance is 0 + 7000 = 7000.
- 2021-12-12 --> withdraw 7000. Balance is 7000 - 7000 = 0.

해결 방안

• 입금은 금액을 양으로, 출금은 음으로 만든다.
• 해당 금액을 partition by를 사용하여 시간의 흐름에 따라 값을 누적한다. 이 때 sum over를 사용한다.

with tb as (
    select 
        account_id
        , day
        , case 
            when type = 'Deposit' then amount 
            else -amount
            end 'amt'    
    from transactions
)
select 
    account_id
    , day
    , sum(amt) over(partition by account_id order by day asc) balance
from tb

순서에 따른 누적값 구하기

• https://leetcode.com/problems/game-play-analysis-iii/
• 일자를 기준으로 각 회원의 접속 기록과 게임 횟수가 기록되어 있다. 일자를 기준으로 게임을 한 누적 횟수를 나타내는 새로운 칼럼을 만들어야 한다.

Input: 
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 1         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
Output: 
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1         | 2016-03-01 | 5                   |
| 1         | 2016-05-02 | 11                  |
| 1         | 2017-06-25 | 12                  |
| 3         | 2016-03-02 | 0                   |
| 3         | 2018-07-03 | 5                   |
+-----------+------------+---------------------+
Explanation: 
For the player with id 1, 5 + 6 = 11 games played by 2016-05-02, and 5 + 6 + 1 = 12 games played by 2017-06-25.
For the player with id 3, 0 + 5 = 5 games played by 2018-07-03.
Note that for each player we only care about the days when the player logged in.

해소

• sum() over 를 통하여 누적값을 구한다. order by를 기준으로 값을 누적한다.

select 
    player_id 
    , event_date 
    , sum(games_played) over(partition by player_id order by event_date asc) games_played_so_far
from activity

partition by

• leetcode의 sql 문제를 풀며 partition by를 자주 활용했다. 사실 partition by를 모르면 리트코드의 문제를 풀기 어렵다. 반절 이상의 문제를 partition by를 사용해야 한다.
• partition by는 group by와 같이 레코드를 그룹핑한다. order by로 그룹핑한 결과값에 대한 순서를 정한다. sum(), rank(), row_number(), count() 등 다양한 함수를 지원한다.