mssql 곱셈 쿼리 문의 1 6 250

by 오라클funfun [SQL Query] [2019.08.10 22:13:22]


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 함수를 사용하면 되는데, 

쿼리에서는 어떻게 구현 가능한지 궁금합니다.

by 생각 [2019.08.12 09:50:58]
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에서는 사용할 수 없을 것 같네요.

 


by 마농 [2019.08.12 11:11:10]

누적곱셈은 EXP(SUM(LOG(v))) 방식을 이용하면 됩니다.
다만, 질문이 좀 이상한데요?
2*2*2*2*2*2*2*1 = 14
왜 결과가 14 죠? 128 인 듯 한데요?
곱셈 질문 맞나요?


by 오라클funfun [2019.08.12 12:43:03]

 

네. 128이 맞는데 14로 잘못 적었네요. 오타였습니다


by 마농 [2019.08.12 13:19:23]

EXP(SUM(LOG(v))) 방식을 이용해 보세요.


by 마농 [2019.08.12 15:24:06]
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)
;

 


by 오라클funfun [2019.08.12 20:21:35]

감사합니다!!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입