[퀴즈] 사과 나눠주기 퀴즈

사과 나눠주기 퀴즈입니다. (사실 이미 알고 계신분이 있을 겁니다.)

 

아래와 같은 테이블 A가 주어집니다.

 

with A as (
select 9 ID, 2 avail_qty from dual union all
select 1,  3 from dual union all
select 12, 4 from dual union all
select 4,  5 from dual union all
select 2,  8 from dual union all
select 3, 13 from dual union all
select 8,  7 from dual)

 

A테이블은 ID(특정사람의 식별자), avail_qty(해당 ID가 최대한 먹을 수 있는 사과의 양) 컬럼으로 구성되어 있습니다.

 

위의 테이블과 나누어 주는 사과의 갯수(변수이며 :NUM 으로 표시)가 주어졌을 때, 한 사람에게 돌아가는 사과의 양을 계산하는 문제입니다.

 

로직은 아래와 같습니다.

 

1) ID가 작은 순서부터 차례대로 1개씩 사과를 주고, 한 순번이 돌면 다시 제일 작은 ID부터 1개씩 차례대로 사과를 준다.

 

2) 1)번을 계속 반복하면서 나누어 주다가, 해당 ID가 최대한 먹을 수 있는 사과의 양(avail_qty)보다 더 많게 되는 경우에는 해당 ID에게 사과를 나누어 주지 않고 다음 ID로 건너뛴다. (즉, avail_qty가 3일 경우 최대 3개까지만 사과를 받을 수 있다.)

 

3) 변수로 주어지는 사과의 갯수가 0일 경우, 모두 0을 반환하고, 주어지는 사과의 갯수가 모든 ID의 avail_qty를 합한 값보다 클 경우에는 그냥 avail_qty와 동일한 값을 반환한다.

 

위의 로직으로, 한 사람에게 돌아가는 사과의 양을 계산하는 쿼리를 만들어 보세요.

 

아래는 사과가 10개 주어졌을때의 결과 입니다.

 

        ID   AVAIL_QTY   RCV_QTY(해당ID에게 돌아가는 사과의 양)
---------- -------------- ----------
         1              3          2
         2              8          2
         3             13          2
         4              5          1
         8              7          1
         9              2          1
        12              4          1

 

아래는 사과가 31개 주어졌을때의 결과 입니다.

 

        ID   AVAIL_QTY   RCV_QTY(해당ID에게 돌아가는 사과의 양)
---------- -------------- ----------
         1              3          3
         2              8          6
         3             13          6
         4              5          5
         8              7          5
         9              2          2
        12              4          4

 

자! 재미있겠죠?

 

[정답] <=== 트리플클릭
SLECT id, avail_qty
, COUNT(CASE WHEN cnt <= :num THEN 1 END) rcv_qty
FROM
(
SELECT lv, id, avail_qty
, COUNT(*) OVER(ORDER BY lv, id) cnt
FROM a
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= :num)
WHERE avail_qty >= lv
)
GROUP BY id, avail_qty
ORDER BY id
;