오라클 11g 환경에서
프로시져를 사용하지않고 엑셀없이 SQL 구문만으로 딱 나오게 해보라고 하여
이리저리해보고있는데 어렵네요.
아래 구문의 목적은
2020년 01월 ~12월 사이의 MMS 발송건과 SMS발송건을 체크하여 매월 통신비가 얼마 나갔는지 체크하기 위함입니다.
현재 제가 못하고 있는 부분은 매월 MMS 비용과 SMS비용의 합을 3열에 표기하고 싶은데 못하고있습니다.
현재 맨아래 구문을 돌릴 경우
MMS SMS
1 156217.05 1681.9
2 73233.6 2698.3
3 44548.35 6824.4
4 74801.1 4513.3
.
.
12 17838.15 5856.4
로 나옵니다.
제가 원하는 결과 값은
MMS SMS 비용
1 156217.05 1681.9 157898.95
2 73233.6 2698.3 75.931.9
3 44548.35 6824.4 51,372.75
4 74801.1 4513.3 79.314.4
.
.
12 17838.15 5856.4 23694.55
로 나오게 만들고싶습니다.
되게 간단한거일거같은데 키워드를 떠올리지 못하는건지
연관되는 문장들을 구글링해도 도저히 못찾겠어서 문의드리고자 합니다.
긴 글 읽어주셔서 감사합니다.
아래는 제가 짠 코드입니다.
WITH SMS_PRICE(MMSS, SMSS) AS(
SELECT
(SELECT COUNT(msg_type) from MSG_RESULT_202001 WHERE result = 100 AND msg_type = 3) * 31.35 AS MMS,
(SELECT COUNT(msg_type) from MSG_RESULT_202001 WHERE result = 100 AND msg_type = 1) * 12.1 AS SMS
FROM dual
UNION ALL
SELECT
(SELECT COUNT(msg_type) from MSG_RESULT_202002 WHERE result = 100 AND msg_type = 3) * 31.35 AS MMS,
(SELECT COUNT(msg_type) from MSG_RESULT_202002 WHERE result = 100 AND msg_type = 1) * 12.1 AS SMS
FROM dual
UNION ALL
.
. -- 3월부터 12월까지 테이블명만 바꿔서 계속 진행
.
SELECT MMSS, SMSS FROM SMS_PRICE;
-- 그대로 고쳐보면 -- SELECT mmss , smss , mmss + smss tot -- 그냥 더하시면 됩니다. FROM sms_price ; -- 쿼리를 간결하게 바꿔보면 -- WITH tmp AS ( SELECT 1 mm, result, msg_type FROM msg_result_202001 UNION ALL SELECT 2, result, msg_type FROM msg_result_202002 UNION ALL SELECT 3, result, msg_type FROM msg_result_202003 UNION ALL SELECT 4, result, msg_type FROM msg_result_202004 UNION ALL SELECT 5, result, msg_type FROM msg_result_202005 UNION ALL SELECT 6, result, msg_type FROM msg_result_202006 UNION ALL SELECT 7, result, msg_type FROM msg_result_202007 UNION ALL SELECT 8, result, msg_type FROM msg_result_202008 UNION ALL SELECT 9, result, msg_type FROM msg_result_202009 UNION ALL SELECT 10, result, msg_type FROM msg_result_202010 UNION ALL SELECT 11, result, msg_type FROM msg_result_202011 UNION ALL SELECT 12, result, msg_type FROM msg_result_202012 ) SELECT mm , COUNT(DECODE(msg_type, 3, 1)) * 31.35 mmss , COUNT(DECODE(msg_type, 1, 1)) * 12.10 smss , COUNT(DECODE(msg_type, 3, 1)) * 31.35 + COUNT(DECODE(msg_type, 1, 1)) * 12.10 tot FROM tmp WHERE result = 100 AND msg_type IN (3, 1) GROUP BY mm ORDER BY mm ;
감사합니다. 생각이 매우 짧았군요..
DECODE 는 구글링을 통해 배울때 무조건 2가지 경우에 대해 써야하는줄알았는데 이렇게 쓰는방법도 있군요
배워갑니다.
혹시 프로시져를 사용하지않고 SQL 구문만 사용하면서도
변수를 사용이 가능할까요??
WITH tmp를 만들기전에
varchar 변수명 = "msg_result_2020"으로
변수명에 msg_result_2020을 할당한다음
UNION ALL SELECT 2, result, msg_type FROM 변수명 || 01
UNION ALL SELECT 2, result, msg_type FROM 변수명 || 02
UNION ALL SELECT 2, result, msg_type FROM 변수명 || 03
UNION ALL SELECT 2, result, msg_type FROM 변수명 || 04
와 같은 방식으로 활용이 가능한지 궁금합니다.
정적 쿼리에서는 테이블명 컬럼명 등을 변수로 처리할 수는 없습니다.
그러려면 동적쿼리를 사용하시면 됩니다.
정적쿼리 만으로 처리하려면. 모든 연도를 다 union 한 후에 사용하세요.
매번 WITH 구문 길게 쓰지 마시고 VIEW 를 미리 만들어 두고 사용하세요.
WITH tmp AS ( SELECT '2019' yyyy, 1 mm, result, msg_type FROM msg_result_201901 UNION ALL SELECT '2019', 2, result, msg_type FROM msg_result_201902 UNION ALL SELECT '2019', 3, result, msg_type FROM msg_result_201903 UNION ALL SELECT '2019', 4, result, msg_type FROM msg_result_201904 UNION ALL SELECT '2019', 5, result, msg_type FROM msg_result_201905 UNION ALL SELECT '2019', 6, result, msg_type FROM msg_result_201906 UNION ALL SELECT '2019', 7, result, msg_type FROM msg_result_201907 UNION ALL SELECT '2019', 8, result, msg_type FROM msg_result_201908 UNION ALL SELECT '2019', 9, result, msg_type FROM msg_result_201909 UNION ALL SELECT '2019', 10, result, msg_type FROM msg_result_201910 UNION ALL SELECT '2019', 11, result, msg_type FROM msg_result_201911 UNION ALL SELECT '2019', 12, result, msg_type FROM msg_result_201912 UNION ALL SELECT '2020', 1, result, msg_type FROM msg_result_202001 UNION ALL SELECT '2020', 2, result, msg_type FROM msg_result_202002 UNION ALL SELECT '2020', 3, result, msg_type FROM msg_result_202003 UNION ALL SELECT '2020', 4, result, msg_type FROM msg_result_202004 UNION ALL SELECT '2020', 5, result, msg_type FROM msg_result_202005 UNION ALL SELECT '2020', 6, result, msg_type FROM msg_result_202006 UNION ALL SELECT '2020', 7, result, msg_type FROM msg_result_202007 UNION ALL SELECT '2020', 8, result, msg_type FROM msg_result_202008 UNION ALL SELECT '2020', 9, result, msg_type FROM msg_result_202009 UNION ALL SELECT '2020', 10, result, msg_type FROM msg_result_202010 UNION ALL SELECT '2020', 11, result, msg_type FROM msg_result_202011 UNION ALL SELECT '2020', 12, result, msg_type FROM msg_result_202012 ) SELECT yyyy , mm , COUNT(DECODE(msg_type, 3, 1)) * 31.35 mmss , COUNT(DECODE(msg_type, 1, 1)) * 12.10 smss , COUNT(DECODE(msg_type, 3, 1)) * 31.35 + COUNT(DECODE(msg_type, 1, 1)) * 12.10 tot FROM tmp WHERE result = 100 AND msg_type IN (3, 1) AND yyyy = '2020' -- 연도 조건 GROUP BY yyyy, mm ORDER BY yyyy, mm ;