데이터베이스 질문과 답변
안녕하세요.
월별 집계쿼리를 만드던중 막히는 부분이 있어 이렇게 질문드립니다.
with a as (
select '사과' as 품종 ,'20200101' as 입고일 , '30' as 경과일, 50 as 수량 from dual
union all
select '사과' as 품종 ,'20200201' as 입고일 , '60' as 경과일, 60 as 수량 from dual
union all
select '사과' as 품종 ,'20200301' as 입고일 , '90' as 경과일, 100 as 수량 from dual
union all
select '배' as 품종 ,'20200101' as 입고일 , '30' as 경과일, 50 as 수량 from dual
union all
select '배' as 품종 ,'20200201' as 입고일 , '60' as 경과일, 100 as 수량 from dual
)
이렇게 데이터가 있을때
| 품종 | 총수량 | 1개월경과 | 2개월경과 | 3개월경과 | ……..조회조건에 따라 계속 증가 70이면 70개월까지 |
| 사과 | 210 | 50 | 60 | 100 | |
| 배 | 90 | 50 | 60 | 0 |
이런식으로 조회되게 하려고합니다.
제가 생각해낸 방법은 컬럼을 약 100개정도 만들어 넣고
각 컬럼별로 case 조건 걸고
(CASE
WHEN 1 <= 2 AND 2 <= 30 THEN '수량'
ELSE '0'
END as 1개월경과) // 이런식으로 100개;;
그걸 묵어서 마지막에 max치는 방법을 생각했는데
이렇게 만들면 쿼리가 너무 지저분하게되고 속도 문제도 있습니다.
이렇게 안하고 조회조건에 따라 컬럼이 동적으로 늘어나게끔 할수있을까요?
1. 조회조건에 따라 컬럼이 동적으로 늘어나는 것은
- 동적쿼리를 이용하셔야 하는데. 동적쿼리는 SQL 영역이 아닌 프로그램 영역입니다.
- 프로그래밍을 통해 SQL 문장을 만들어 내서 실행하는 것입니다.
2. 쿼리를 간결히 하려면
- 경과일수를 가지고 범위 검색 하는 것 보다는
- 경과일수를 이용해 경과 개월수를 우선 구한뒤 이퀄 비교하시면 간결해 질 듯 합니다.
- 11G 이상에선 피벗 구문을 이용할 수도 있구요.
3. 마지막에 MAX 친다는 것은?
- Max 가 아닌 Sum 이 맞을 듯 하네요.
WITH a AS
(
SELECT '사과' 품종, '20200101' 입고일, 30 경과일, 50 수량 FROM dual
UNION ALL SELECT '사과', '20200201', 60, 60 FROM dual
UNION ALL SELECT '사과', '20200301', 90, 100 FROM dual
UNION ALL SELECT '배' , '20200101', 30, 50 FROM dual
UNION ALL SELECT '배' , '20200201', 60, 100 FROM dual
)
SELECT *
FROM (SELECT 품종, 수량
, CEIL(경과일 / 30) 경과월
FROM a
)
PIVOT (SUM(수량) FOR 경과월 IN (1, 2, 3))
;
) 버튼을 클릭하여 작성 하시면 됩니다.