데이터베이스 질문과 답변
MSSQL에서 엑셀의 PRODUCT 함수를 쿼리로 구현하고 싶습니다.
base_date 기준일자로 group by 를 하여
SUM, AVG 와 같이 곱셈 결과를 얻고 싶은데요.
따로 함수가 없는지, 없다면 어떻게 구현 가능할지 질문드립니다.
다음과 같은 테이블 정보에서 캘린더 화면에 데이터를 출력하고 싶습니다.
어떤 회사에서 보고서를 작성 중인데,
Calander 테이블에서 base date 년월 기준으로 count (물품 판매량)이 존재합니다.
<테이블 Calander>
base_date count
2019-01 2
2019-02 2
2019-03 2
- - -
2019-08 1
2018-01 2
2018-02 3
2018-03 1
- - -
2018-12 2
그런데 화면 (보고서)에 출력할 때,
년도별로 count의 곱셈 결과를 보고 싶습니다.
예를 들어 화면 (보고서) 에는,
년도별로 1월부터 12월까지의 count (건수) 컬럼끼리 곱셈한 결과를 출력하고 싶습니다.
*** 화면(보고서)에서 원하는 출력 결과
-> Year (년도)를 Group by 해서 월별 count 끼리 곱한결과를 출력
Year count 곱셈결과
2019 128 (2018년 1월부터 현재 8월까지의 count 건수끼리 곱셈한 결과)
2018 60 (2018년 1월부터 12월까지의 count 건수끼리 곱셈한 결과)
* 2019년의 경우, count 곱셈결과 근거
2019년 1월의 count 2
2019년 2월의 count 2
2019년 3월의 count 2
2019년 4월의 count 2
2019년 5월의 count 2
2019년 6월의 count 2
2019년 7월의 count 2
2019년 8월의 count 1
-> 2019년은 1월부터 8월까지의 count ( 판매량 ) 곱셈결과가 2*2*2*2*2*2*2*1 = 128
엑셀로 계산할 경우,
PRODUCT 함수를 사용하면 되는데,
쿼리에서는 어떻게 구현 가능한지 궁금합니다.
WITH T1(BASE_DATE,CNT) AS(
SELECT '2019-01','2' FROM DUAL UNION ALL
SELECT '2019-02','2' FROM DUAL UNION ALL
SELECT '2019-03','2' FROM DUAL UNION ALL
SELECT '2019-08','1' FROM DUAL UNION ALL
SELECT '2018-01','1' FROM DUAL UNION ALL
SELECT '2018-02','1' FROM DUAL UNION ALL
SELECT '2018-03','1' FROM DUAL UNION ALL
SELECT '2018-12','2' FROM DUAL
)
SELECT Max(CNT) OVER(PARTITION BY YEAR ORDER BY CNT) TOT_CNT
,YEAR
FROM
(
SELECT ROUND(EXP(SUM(LN(CNT)) OVER( PARTITION BY YEAR)), 1) CNT
--,ROUND(EXP(SUM(LN(CNT)) OVER()), 1) CNT_2 (현재까지 누적곱)
,YEAR
,RN
FROM
(
SELECT Row_Number() OVER(PARTITION BY SubStr(BASE_DATE,1,4) ORDER BY BASE_DATE) RN
,SubStr(BASE_DATE,1,4) YEAR
,CNT
FROM T1
)
)
WHERE RN = '1';
-- 한, 두단계는 더 줄일수 있을거 같아요. 고민좀 해봐야겠네요.
-- MSSQL에서는 사용할 수 없을 것 같네요.
누적곱셈은 EXP(SUM(LOG(v))) 방식을 이용하면 됩니다.
다만, 질문이 좀 이상한데요?
2*2*2*2*2*2*2*1 = 14
왜 결과가 14 죠? 128 인 듯 한데요?
곱셈 질문 맞나요?
네. 128이 맞는데 14로 잘못 적었네요. 오타였습니다
EXP(SUM(LOG(v))) 방식을 이용해 보세요.
WITH t AS
(
SELECT '2019-01' ym, 2 cnt
UNION ALL SELECT '2019-02', 2
UNION ALL SELECT '2019-03', 2
UNION ALL SELECT '2019-04', 2
UNION ALL SELECT '2019-05', 2
UNION ALL SELECT '2019-06', 2
UNION ALL SELECT '2019-07', 2
UNION ALL SELECT '2019-08', 1
UNION ALL SELECT '2018-03', 3
UNION ALL SELECT '2018-04', 4
UNION ALL SELECT '2018-05', 5
)
SELECT SUBSTRING(ym, 1, 4) y
, ROUND(EXP(SUM(LOG(cnt))), 0) x
FROM t
GROUP BY SUBSTRING(ym, 1, 4)
;
감사합니다!!!
) 버튼을 클릭하여 작성 하시면 됩니다.