데이터베이스 질문과 답변
by 신월동연산군 [2022.07.13 09:50:34]
단순하게 매입매출테이블을 예로 들겠습니다.
컬럼1 PK(auto_increase)
컬럼1 구분(1:매입,2:매출)
컬럼2 일자
컬럼3 금액
컬럼4 거래처명
위와같이 테이블 설계를 하고 검색은 시작일자 종료일자 두가지 조건으로 검색합니다.
이때 첫번째행에 매입은 - 매출은 +로 계산하여 전월의 합계를 넣으려고합니다.
SELECT '이월금액' AS '구분', '' AS 일자,SUM(IF(구분 = '1', -금액,금액)), '' 거래처명
FROM 매입매출
WHERE 일자 < 검색시작일자
UNION
SELECT 구분, 일자, 금액, 거래처명
FROM 매입매출
WHERE 일자 BETWEEN 검색시작일자 AND 검색종료일자;
현재 생각나는 방법은 UNION을 사용하여 위와 같이 짜는것 밖에 생각이 나지않아 질문드립니다.
다르게 조회가능한 방법이 있을까요??
1. UNION 이 아닌 UNION ALL 이 맞습니다.
- 만약 같은 거래처에 같은날 같은 금액으로 거래가 발생하는 경우 중복이 제거되어 결과가 누락되게 됩니다.
- 의미상, 성능상 UNION ALL 이 맞습니다.
2. 금액
- 상단 쿼리에서 구분에 따라 +/- 로 나누었다면?
- 하단 쿼리에서도 똑같이 적용해 줘야 하지 않나요?
3. 개선
- 간단하게는 UNION ALL 을 이용하시면 됩니다.(단, 위 1,2 번 사항 고려)
- 아래와 같이 테이블을 한번만 읽도록 개선 가능합니다.
SELECT 구분
, 일자
, 거래처명
, SUM(금액) 금액
FROM (SELECT CASE WHEN 일자 >= :검색시작일자 THEN 구분 ELSE '이월금액' END 구분
, CASE WHEN 일자 >= :검색시작일자 THEN pk END pk
, CASE WHEN 일자 >= :검색시작일자 THEN 일자 END 일자
, CASE WHEN 일자 >= :검색시작일자 THEN 거래처명 END 거래처명
, CASE WHEN 구분 = '1' THEN -금액 ELSE 금액 END 금액
FROM 매입매출
WHERE 일자 <= :검색종료일자
) a
GROUP BY 일자, pk, 구분, 거래처명
;
답변 감사합니다.
말씀해주신 대로 쿼리를 짜도 제가 원하는대로 적용이 되네요.
금액에 대해서는 제 설명이 부족했던 것 같습니다.
실제 보여주고싶은 내용은 금액과 잔액을 같이 표시하려고 합니다.
그래서 +/-는 잔액표시컬럼에 적용하려고 하였습니다.
잔액계산은 아래와 같이 하려는데 개선해야될 부분이 있나요?
SELECT 구분
, 일자
, 거래처명
,ABS(SUM(금액)) 금액
, SUM(금액) OVER (ORDER BY `pk`) AS 잔액
FROM (SELECT CASE WHEN 일자 >= :검색시작일자 THEN 구분 ELSE '이월금액' END 구분
, CASE WHEN 일자 >= :검색시작일자 THEN pk END pk
, CASE WHEN 일자 >= :검색시작일자 THEN 일자 END 일자
, CASE WHEN 일자 >= :검색시작일자 THEN 거래처명 END 거래처명
, CASE WHEN 구분 = '1' THEN -금액 ELSE 금액 END 금액
FROM 매입매출
WHERE 일자 <= :검색종료일자
) a
GROUP BY 일자, pk, 구분, 거래처명
;
1. "ABS(SUM(금액)) 금액" 부분의 의미가 모호합니다.
- 이월금액이 마이너스가 나는 경우가 없다면? 가능한 방법입니다.
- 이월금액이 마이너스가 나는 경우가 있다면? 의미가 모호해 집니다.
2. 분석함수 누적합계 사용 부분은
- 오류 : SUM(금액) OVER (ORDER BY `pk`) AS 잔액
- 수정 : SUM(SUM(금액)) OVER(ORDER BY 일자, pk) 잔액
) 버튼을 클릭하여 작성 하시면 됩니다.