데이터베이스 질문과 답변
제가 오랜만에 쿼리를 만들다보니 너무 복잡하게 나열되어서 고수님들의 손길을 부탁드립니다.
0
2
564
by trust [SQL Query] [2017.12.06 10:33:05]
WITH BASE_A AS ( SELECT '20171201' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 1516.8 AS QTY, '10' AS SPICE_CD FROM DUAL UNION ALL SELECT '20171202' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 1516.8 AS QTY, '10' AS SPICE_CD FROM DUAL UNION ALL SELECT '20171203' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 1516.8 AS QTY, '10' AS SPICE_CD FROM DUAL UNION ALL SELECT '20171201' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 1529.6 AS QTY, '20' AS SPICE_CD FROM DUAL UNION ALL SELECT '20171202' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 1529.6 AS QTY, '20' AS SPICE_CD FROM DUAL UNION ALL SELECT '20171203' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 1529.6 AS QTY, '20' AS SPICE_CD FROM DUAL UNION ALL SELECT '20171201' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 0.255 AS QTY, NULL AS SPICE_CD FROM DUAL UNION ALL SELECT '20171202' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 0.255 AS QTY, NULL AS SPICE_CD FROM DUAL UNION ALL SELECT '20171203' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 0.255 AS QTY, NULL AS SPICE_CD FROM DUAL UNION ALL SELECT '20171201' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 0.269 AS QTY, '40' AS SPICE_CD FROM DUAL UNION ALL SELECT '20171202' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 0.269 AS QTY, '40' AS SPICE_CD FROM DUAL UNION ALL SELECT '20171203' AS PLAN_DATE, 'SM001' AS LIST_CODE, 6400 AS WORK_QTY, 0.269 AS QTY, '40' AS SPICE_CD FROM DUAL) SELECT A.PLAN_DATE , A.LIST_CODE , MAX(A.WORK_QTY) AS WORK_QTY , SUM(DECODE(A.SPICE_CD, '10', A.QTY)) YELLOW_QTY, SUM(DECODE(A.SPICE_CD, '20', A.QTY)) LAMINA_QTY, ROUND((MAX(A.WORK_QTY) - (SUM(DECODE(A.SPICE_CD, '10', A.QTY)) + SUM(DECODE(A.SPICE_CD, '20', A.QTY)))) * SUM(DECODE(A.SPICE_CD, NULL, A.QTY)), 2) ADD_BACK_CSC, ROUND((MAX(A.WORK_QTY) - (SUM(DECODE(A.SPICE_CD, '10', A.QTY)) + SUM(DECODE(A.SPICE_CD, '20', A.QTY)))) * SUM(DECODE(A.SPICE_CD, '40', A.QTY)), 2) ADD_BACK_ETC, SUM(DECODE(A.SPICE_CD, '10', A.QTY)) + SUM(DECODE(A.SPICE_CD, '20', A.QTY)) + ROUND((MAX(A.WORK_QTY) - (SUM(DECODE(A.SPICE_CD, '10', A.QTY)) + SUM(DECODE(A.SPICE_CD, '20', A.QTY)))) * SUM(DECODE(A.SPICE_CD, NULL, A.QTY)), 2) + ROUND((MAX(A.WORK_QTY) - (SUM(DECODE(A.SPICE_CD, '10', A.QTY)) + SUM(DECODE(A.SPICE_CD, '20', A.QTY)))) * SUM(DECODE(A.SPICE_CD, '40', A.QTY)), 2) AS TOTAL_QTY FROM BASE_A A GROUP BY A.PLAN_DATE, A.LIST_CODE
제가 쿼리를 보기에도 너무 복잡하고 간결하게 정리해서 볼 수 있는지요?
by 마농
[2017.12.06 11:06:06]
1. 테이블이 하나라면 a. 을 빼는게 간결하구요.
2. 동일식이 반복된다면 인라인뷰를 사용해 보세요.
3. 대소문자 구별하는게 가독성이 좋습니다.(명령문은 대문자, 테이블/컬럼은 소문자)
4. 컴마를 앞쪽에 주면
- 컬럼 하나가 여러줄에 걸친 복잡한 계산식일때에도 컬럼을 구별하기 좋습니다.
WITH base_a AS
(
SELECT '20171201' plan_date, 'SM001' list_code, 6400 work_qty, 1516.8 qty, '10' spice_cd FROM dual
UNION ALL SELECT '20171202', 'SM001', 6400, 1516.8, '10' FROM dual
UNION ALL SELECT '20171203', 'SM001', 6400, 1516.8, '10' FROM dual
UNION ALL SELECT '20171201', 'SM001', 6400, 1529.6, '20' FROM dual
UNION ALL SELECT '20171202', 'SM001', 6400, 1529.6, '20' FROM dual
UNION ALL SELECT '20171203', 'SM001', 6400, 1529.6, '20' FROM dual
UNION ALL SELECT '20171201', 'SM001', 6400, 0.255, null FROM dual
UNION ALL SELECT '20171202', 'SM001', 6400, 0.255, null FROM dual
UNION ALL SELECT '20171203', 'SM001', 6400, 0.255, null FROM dual
UNION ALL SELECT '20171201', 'SM001', 6400, 0.269, '40' FROM dual
UNION ALL SELECT '20171202', 'SM001', 6400, 0.269, '40' FROM dual
UNION ALL SELECT '20171203', 'SM001', 6400, 0.269, '40' FROM dual
)
SELECT plan_date
, list_code
, work_qty
, yellow_qty
, lamina_qty
, ROUND((work_qty - total_qty) * csc, 2) add_back_csc
, ROUND((work_qty - total_qty) * etc, 2) add_back_etc
, total_qty
+ ROUND((work_qty - total_qty) * csc, 2)
+ ROUND((work_qty - total_qty) * etc, 2) total_qty
FROM (SELECT plan_date
, list_code
, work_qty
, SUM(DECODE(spice_cd, '10', qty)) yellow_qty
, SUM(DECODE(spice_cd, '20', qty)) lamina_qty
, SUM(DECODE(spice_cd, '10', qty, '20', qty)) total_qty
, SUM(DECODE(spice_cd, null, qty)) csc
, SUM(DECODE(spice_cd, '40', qty)) etc
FROM base_a
GROUP BY plan_date, list_code, work_qty
)
;
by trust
[2017.12.06 11:10:04]
마농님 감사합니다.
테이블은 여러개를 활용해서 계산된 값을 with절에 넣었습니다.
여기서는 무조건 대문자로만 활용해야해서.. ^^;; 조언은 감사합니다 ^^
) 버튼을 클릭하여 작성 하시면 됩니다.