데이터베이스 질문과 답변
WITH T1 AS
(
SELECT 'cmpy1' AS CMPY_NO, '20230101' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230201' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230211' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230311' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230311' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230321' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy1' AS CMPY_NO, '20230321' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220101' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220201' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220211' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220311' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220311' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220321' AS STD_DT, 1000 AS ORD_PRC FROM dual UNION ALL
SELECT 'cmpy2' AS CMPY_NO, '20220321' AS STD_DT, 1000 AS ORD_PRC FROM dual
)
SELECT *
FROM ( SELECT CMPY_NO
, TO_CHAR(TO_DATE(STD_DT), 'FMMM')AS STS_DT
, SUM(ORD_PRC) AS TOTAL_PRC
FROM T1
WHERE CMPY_NO = 'cmpy1'
AND TO_CHAR(TO_DATE(STD_DT), 'YYYY') = 2023
GROUP BY CMPY_NO, TO_CHAR(TO_DATE(STD_DT), 'FMMM')
)
PIVOT (
SUM(TOTAL_PRC)
FOR STS_DT IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12')
);
안녕하세요~ 제가 통계 쿼리를 만들고 있는데 피벗형태로 출력을 하고 싶어서 검색하면서 한번 해 보았는데 아직 부족한 부분이 있어 질문드립니다!
고객사별 누적 매출금액과, 월별 매출금액을 뽑고 싶은데 이렇게 하는게 맞을까요? 결과값이 나오긴하는데 뭔가 부족한 부분이 있는거 같습니다(현재 해당하는 월별 매출금액의 누적금액을 못 뽑겠습니다)
결과값은 이렇게 출력하고 싶습니다
| 고객사 | 누적 매출 금액(1~12월 총) | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|cmpy1|7000|1000|2000|4000|0|0|0|0|0|0|0|0|0|
월별 금액이 없는것은 null로 나와도 됩니다!
1. 날짜 변환
- TO_DATE(std_dt) 는 수행 환경에 따라 오류발생 가능성이 높은 잘못된 사용법입니다.
- TO_DATE(std_dt, 'yyyymmdd') 와 같이 정확한 포멧을 지정하여 사용해야 합니다.
2. 컬럼을 가공하는 조건은 좋지 않습니다. 컬럼은 그대로 두고 조건 값을 가공하세요.
- 변경전 : TO_CHAR(TO_DATE(std_dt), 'YYYY') = 2023
- 변경후 : std_dt LIKE '2023' || '%'
-- 1. ROLLUP 을 이용하는 방법
SELECT *
FROM (SELECT cmpy_no
, NVL(TO_NUMBER(SUBSTR(std_dt, 5, 2)), 0) mm
, SUM(ord_prc) prc
FROM t1
WHERE cmpy_no = 'cmpy1'
AND std_dt LIKE '2023' || '%'
GROUP BY cmpy_no, ROLLUP(SUBSTR(std_dt, 5, 2))
)
PIVOT (SUM(prc) FOR mm IN (0 tot, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
;
-- 2. SUM() OVER() 분석함수를 이용하는 방법
SELECT *
FROM (SELECT cmpy_no
, TO_NUMBER(SUBSTR(std_dt, 5, 2)) mm
, SUM(SUM(ord_prc)) OVER() tot
, SUM(ord_prc) prc
FROM t1
WHERE cmpy_no = 'cmpy1'
AND std_dt LIKE '2023' || '%'
GROUP BY cmpy_no, SUBSTR(std_dt, 5, 2)
)
PIVOT (SUM(prc) FOR mm IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
;
감사합니다!!! 덕분에 SQL에 대해 많이 배워가고 통계도 잘 진행되고있습니다. 다름이 아니라 혹시 PIVOT 사용할 때 누적금액에 토탈금액도 뽑을 수 있을까요?
원하시는 결과표를 그려주세요.
결과는 이렇게 뽑고 싶습니다. 토탈 누적 금액을 앞단에서 뽑아쓰고 싶어서 전체 행에 들어가도 됩니다.
|cmpy_no|tot |1 |2 |3 |4 |5 |6 |7 |8 |9 |10 |11 |12 |토탈 누적금액|
|cmpy1 |1000 |1000| | | | | | | | | | | |3000 |
|cmpy2 |2000 |1000| |1000| | | | | | | | | |3000 |
WITH t1 AS
(
SELECT 'cmpy1' cmpy_no, '20230101' std_dt, 1000 ord_prc FROM dual
UNION ALL SELECT 'cmpy2', '20230101', 1000 FROM dual
UNION ALL SELECT 'cmpy2', '20230201', 1000 FROM dual
)
SELECT *
FROM (SELECT cmpy_no
, TO_NUMBER(SUBSTR(std_dt, 5, 2)) mm
, SUM(SUM(ord_prc)) OVER() tot_all
, SUM(SUM(ord_prc)) OVER(PARTITION BY cmpy_no) tot_sub
, SUM(ord_prc) prc
FROM t1
WHERE std_dt LIKE '2023' || '%'
GROUP BY cmpy_no, SUBSTR(std_dt, 5, 2)
)
PIVOT (SUM(prc) FOR mm IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))
ORDER BY cmpy_no
;
원하는 결과입니다!! 마농님 항상 감사합니다^^
) 버튼을 클릭하여 작성 하시면 됩니다.