데이터베이스 질문과 답변
by 오후8시 [DB 기타] POSTGRESQL [2021.03.24 11:11:25]
(현재쿼리)
select CUSTOMER_NAME,
TRADE_DATE,
TOTAL_COST,
BALANCE
from (
--기간이월부분
(SELECT DM.CUSTOMER_NAME,
'-[기간전이월]'AS TRADE_DATE,
0 AS TOTAL_COST,
ABS(P_PD.ZERO_COST - DM.DCOD) AS BALANCE
FROM
( SELECT
A.MEMOFFICE_CD,
A.CUS_CD,
COALESCE(SUM(CASE
WHEN a.trade_date < '2020-10-01' THEN B.TOTAL_COST END), 0) ZERO_COST
FROM PURSALES A
INNER JOIN PURSALES_DETAIL B ON A.PURSALES_CD = B.PURSALES_CD
AND A.MEMOFFICE_CD = B.MEMOFFICE_CD
WHERE 1=1
AND A.MEMOFFICE_CD = 'MN202100002'
AND A.PURSALES_TYPE = '2'
AND A.CUS_CD IN ('CM202100003','CM202100004')
AND A.TRADE_DATE < '2020-12-01'-- 시작일
GROUP BY A.MEMOFFICE_CD,A.CUS_CD
)P_PD
INNER JOIN
(
SELECT MEMOFFICE_CD, CUSTOMER_NAME, CUS_CD, COALESCE(SUM(COST),0) DCOD
FROM DEPOSIT_MANAGE
WHERE 1=1
AND DM_TYPE = '1'
AND MEMOFFICE_CD = 'MN202100002'
AND CUS_CD IN ('CM202100003')
AND DM_DATE::date < '2020-10-01'-- 시작일
GROUP BY MEMOFFICE_CD,CUSTOMER_NAME,CUS_CD
)DM ON P_PD.MEMOFFICE_CD = DM.MEMOFFICE_CD AND P_PD.CUS_CD = DM.CUS_CD
GROUP BY DM.CUSTOMER_NAME, P_PD.ZERO_COST, DM.DCOD
)
UNION ALL
-- 상품부분
(SELECT
C.CUSTOMER_NAME,
P_PD.TRADE_DATE ,
P_PD.TOTAL_COST,
SUM(P_PD.TOTAL_COST)
OVER(PARTITION BY C.CUSTOMER_NAME,P_PD.TRADE_DATE ORDER BY C.CUSTOMER_NAME,P_PD.TRADE_DATE,P_PD.BIGO
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BALANCE
FROM CUSTOMER as C
INNER JOIN(
SELECT P.CUSTOMER_NAME,
P.CUS_CD,
P.PURSALES_CD,
P.PURSALES_TYPE,
P.MEMOFFICE_CD,
P.TRADE_DATE,
PD.PRODUCT_NAME,
PD.STANDARD,
PD.COUNT,
PD.PRODUCT_COST,
PD.SUPPLY_COST,
PD.TAX_COST,
(PD.SUPPLY_COST + pd.TAX_COST - PD.TOTAL_COST) AS DISCOUNT_COST,
PD.TOTAL_COST,
P.BIGO
FROM PURSALES P
INNER JOIN PURSALES_DETAIL PD on P.MEMOFFICE_CD = PD.MEMOFFICE_CD AND P.PURSALES_CD = PD.PURSALES_CD
WHERE 1=1
AND P.PURSALES_TYPE='2' AND P.MEMOFFICE_CD= 'MN202100002' AND P.CUS_CD IN('CM202100003','CM202100004')
AND P.TRADE_DATE >= '2020-10-01' -- 시작일
AND P.TRADE_DATE <= '2020-11-11' -- 종료일
ORDER BY P.CUSTOMER_NAME,P.TRADE_DATE
) P_PD ON C.MEMOFFICE_CD = P_PD.MEMOFFICE_CD AND C.CUS_CD = P_PD.CUS_CD AND C.CUSTOMER_NAME = P_PD.CUSTOMER_NAME
WHERE 1=1
AND C.USE_YN = 'Y'
AND C.CUSTOMER_TYPE IN ('2','3')
AND C.CUS_CD IN ('CM202100003')
AND C.MEMOFFICE_CD = 'MN202100002'
ORDER BY C.CUSTOMER_NAME,P_PD.TRADE_DATE,P_PD.BIGO,BALANCE)
) RESULT_DATA
(위 쿼리의 결과 첫번째사진)
제가 고심중인 부분은 두번째 사진처럼 조회하고싶어서입니다.
조회를 했을때 두번째사진마지막조회결과 처럼 한번에 조회되게 하고싶습니다.
lag,lead함수와 재귀쿼리 쪽을 시도해보고있지만 ...막혀서 질문드립니다.
total_cost + lag(balance)over(order by trade_date, total_cosr) 해주시면 되지 않나요?
저도 해보니 1900000데이터까지만 나오고기간이월부분 null로 나오고1900000아래부분 데이터는 더해주지않아서 말씀드렸어요
SUM(CASE WHEN trade_date = '-[기간전이월]' THEN balance ELSE total_cost END) OVER(PARTITION BY customer_name ORDER BY trade_date, total_cost) AS balance
랑에님 답변감사드립니다. 제가 아는바론 포스트그레에서는 디코드함수가없는걸로 알고있는데요 검색을 해보니
CASE WHEN TRADE_DATE '-[기간전이월]' THEN BALANCE ELSE TOTAL_COST END OVER(PARTITION BY customer_name ORDER BY trade_date, total_cost) AS balance
CASE문으로 변환해서 하라고합니다. 하지만 위의 쿼리로도 해보고 다각도로 시도해보니 잘안되네요 ㅠ
case 문으로 수정했습니다 ㅎㅎ
SELECT memoffice_cd
, cus_cd
, customer_name
, trade_date
, SUM(total_cost) total_cost -- + 금액
, SUM(deposit_cost) deposit_cost -- - 금액
, SUM(total_cost - deposit_cost) cost -- 차액
, SUM(SUM(total_cost - deposit_cost)) OVER(
PARTITION BY memoffice_cd, cus_cd ORDER BY trade_date) balance -- 누계
FROM (SELECT a.memoffice_cd
, a.cus_cd
, a.customer_name
, b.total_cost
, 0 deposit_cost
, CASE WHEN a.trade_date < '2020-10-01' THEN '-[기간전이월]' ELSE a.trade_date END trade_date
FROM pursales a
INNER JOIN pursales_detail b
ON a.pursales_cd = b.pursales_cd
AND a.memoffice_cd = b.memoffice_cd
WHERE 1=1
AND a.pursales_type = '2'
AND a.memoffice_cd = 'MN202100002'
AND a.cus_cd IN ('CM202100003', 'CM202100004')
AND a.trade_date < '2020-11-11'
UNION ALL
SELECT memoffice_cd
, cus_cd
, customer_name
, 0 total_cost
, cost deposit_cost
, CASE WHEN dm_date < '2020-10-01' THEN '-[기간전이월]' ELSE dm_date END dm_date
FROM deposit_manage
WHERE 1=1
AND dm_type = '1'
AND memoffice_cd = 'MN202100002'
AND a.cus_cd IN ('CM202100003', 'CM202100004')
AND dm_date < '2020-11-11'
) a
GROUP BY memoffice_cd, cus_cd, customer_name, trade_date
;
마농님이 주신 쿼리가 조금 단어 몇개가 안맞아서 제가 살짝수정해서 이렇게 해보았습니다.
SELECT memoffice_cd
, cus_cd
, customer_name
, trade_date
, SUM(total_cost) AS TOT_COST -- + 금액
, SUM(deposit_cost) AS DEP_COST -- - 금액
, SUM(total_cost - deposit_cost) MINUS_COST -- 차액
, SUM(SUM(total_cost - deposit_cost)) OVER(
PARTITION BY memoffice_cd, cus_cd ORDER BY trade_date) AS BALANCE -- 누계
FROM (SELECT P.memoffice_cd
, P.cus_cd
, P.customer_name
, PD.total_cost
, 0 AS deposit_cost
, CASE WHEN P.trade_date < '2020-10-01' THEN '-[기간전이월]' ELSE P.trade_date END trade_date
FROM pursales P
INNER JOIN pursales_detail PD
ON P.pursales_cd = PD.pursales_cd
AND P.memoffice_cd = PD.memoffice_cd
WHERE 1=1
AND P.pursales_type = '2'
AND P.memoffice_cd = 'MN202100002'
AND P.cus_cd IN ('CM202100003')
AND P.trade_date <= '2020-11-11'
UNION ALL
SELECT memoffice_cd
, cus_cd
, customer_name
, 0 total_cost
, cost deposit_cost
, CASE WHEN dm_date < '2020-10-01' THEN '-[기간전이월]' ELSE dm_date END dm_date
FROM deposit_manage
WHERE 1=1
AND dm_type = '1'
AND memoffice_cd = 'MN202100002'
AND cus_cd IN ('CM202100003')
AND dm_date <= '2020-11-11'
) RESULT_DATE
GROUP BY memoffice_cd, cus_cd, customer_name, trade_date
| MEMOFFICE_CD | CUS_CD | CUSTOMER_NAME | TRADE_DATE | TOT_COST | DEP_COST | MINUS_COST | BALANCE |
| MN202100002 | CM202100003 | 이안인테리어 | -[기간전이월] | 0 | 100000 | -100000 | -100000 |
| MN202100002 | CM202100003 | 이안인테리어 | 2020-11-11 | 4050000 | 0 | 4050000 | 3950000 |
이렇게 조회가 되네용 ㅠ
제가 올린사진같이는 포스트그레에서는 안되는건가요?>
원본 쿼리가 불필요하게 복잡하고, 또 이상한 부분도 많네요.
그래서 나름 테이블의 특성을 짐작하여 쿼리를 간결화 해 봤습니다.
결과가 다르게 나온 이유는 원본쿼리에는 ABS 부분이 있었네요.
ABS 가 왜 들어가야 하는지 이해가 안가네요.
--기간이월 부분에 보시면 아래부분이나오는데
ABS(P_PD.ZERO_COST - DM.DCOD) AS BALANCE
P_PD.ZERO_COST - DM.DCOD 이렇게 차액을 내게 되는데 ZERO_COST가 0인때는 값을 음수로 뱉어냅니다.
필요한 값이 양수로 나와야해서 ABS를 썻습니다.
원본쿼리가 불필요하고 이상한이유는 제가아는 지식한도내에서 나온 쿼리라 그럴듯합니다. 저는 이게 보기편하고 쿼리를 짜는 스타일이 이렇게 밖에 못짜나봅니다. 소중한 답변감사합니다 마농님^^
WITH COST_TAB AS ( SELECT 'TEST' NM, 1 SEQ, 0 TT_COST FROM DUAL UNION ALL SELECT 'TEST' NM, 2 SEQ, 1800000 TT_COST FROM DUAL UNION ALL SELECT 'TEST' NM, 3 SEQ, 2250000 TT_COST FROM DUAL ), BAL_TAB AS ( SELECT 'TEST' NM, 1 SEQ, 100000 BALANCE FROM DUAL ) SELECT CT.NM, CT.SEQ, CT.TT_COST, BT.BALANCE + CT.SUM_TT BALANCE FROM BAL_TAB BT ,( SELECT NM, SEQ, TT_COST, SUM(TT_COST)OVER(ORDER BY SEQ) SUM_TT FROM COST_TAB) CT WHERE CT.NM = BT.NM
도움감사드립니다 알렌님. 덕분에 참고해서 원하는결과 만들었습니다. 쿼리로우가 97줄로 늘어났지만요...ㅎㅎ
ABS(P_PD.ZERO_COST - DM.DCOD) AS BALANCE 에서
0 일때는 음수가 나와서 ABS 를 해줘야 한다?
0 이 아닐때는 어떻게 될까요?
데이터가 일관성이 없어지는 것 같습니다.
ABS 를 사용하는 것이 너무 이상하네요.
혹시 음수를 양수로 바꿔 버릴게 아니라 0으로 처리해야 하는 건 아닐런지?
ZERO_COST가 0이아닐때는 빼기 DM_DCOD를 해주면 상관없는데
ZERO_COST가 0일때는 DM_DCOD(예:50000)를 빼면 -50000 나옵니다. 당연한말이지만.
하지만 0 - 50000을 했을때 양수50000이 나오게 하기위해 ABS를 썻습니다. 데이터의 일관성을 위해서라도 한번더 검수해보겠습니다!^^ 혹시라도 이부분에서 양수 50000이 나올수있는 다른방법이 있으시다면 첨언 부탁드려도되겠습니까?
방법상의 문제를 말하는게 아닙니다.
그게 원하는 거라면 절대값 쓰면 됩니다.
다만 왜 그래야 하는지 납득하기 어렵네요.
+100 에 -110 이면 -10 이되어 10 이 되는데
+10 에 -110 이면 -100 이되어 100 이 된다면?
+ 금액이 더 적은데도 결과는 더 크게 나오는 역전현상이 발생되어
형평성이 어긋나는 것 같네요.
지인들에게도 물어보니 제가 생각을 잘못하고있었던거였습니다마농님;;; 음수를 양수로 바꿔주면 안되는거였어요
+100 - -100 = -10이 나와야하는게 맞았습니다. 모르고 넘어갔으면 나중에 문제가 생겼겟네요 ㅠ
끝까지 이해시켜주셔서 감사합니다!^^
) 버튼을 클릭하여 작성 하시면 됩니다.