-
006. 그룹 함수, 분석 함수(ROLLUP / CUBE / GROUPING SETS), HAVING절, 서브상관쿼리, ROW_NUMBER() / SEQUENCE()SsY/Class 2023. 3. 29. 17:52728x90
2023.3.29 (수)
SCOTT 계정 실습
- 어제 이어 문제 풀이
-- 위에서 조회한 내용을 아래와 같이 조회될 수 있도록 쿼리문을 구성한다. /* 부서번호 급여합 ----------- --------- 10 8750 20 10875 30 9400 인턴 8000 모든부서 37025 */
더보기SELECT CASE DEPTNO WHEN NULL THEN '인턴' ELSE TO_CHAR(DEPTNO) END "부서번호" FROM TBL_EMP; --==>> /* 20 30 30 20 30 30 10 20 10 30 20 30 20 10 (null) (null) (null) (null) (null) */ SELECT CASE WHEN DEPTNO IS NULL THEN '인턴' ELSE TO_CHAR(DEPTNO) END "부서번호" FROM TBL_EMP; --==>> /* 20 30 30 20 30 30 10 20 10 30 20 30 20 10 인턴 인턴 인턴 인턴 인턴 */ SELECT CASE WHEN DEPTNO IS NULL THEN '인턴' ELSE TO_CHAR(DEPTNO) END "부서번호" , SAL "급여" FROM TBL_EMP; --==>>> /* 20 800 30 1600 30 1250 20 2975 30 1250 30 2850 10 2450 20 3000 10 5000 30 1500 20 1100 30 950 20 3000 10 1300 인턴 1500 인턴 1000 인턴 2000 인턴 2500 인턴 1000 */ SELECT CASE WHEN DEPTNO IS NULL THEN '인턴' ELSE TO_CHAR(DEPTNO) END "부서번호" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY DEPTNO; --==>> /* 30 9400 인턴 8000 20 10875 10 8750 */ SELECT CASE WHEN DEPTNO IS NULL THEN '인턴' ELSE TO_CHAR(DEPTNO) END "부서번호" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> /* SELECT CASE WHEN DEPTNO IS NULL THEN '인턴' ELSE TO_CHAR(DEPTNO) END "부서번호" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); */ SELECT NVL(DEPTNO,'인턴') "부서번호" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> 에러 발생 // DEPTNO 숫자컬럼 /* ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number. */ SELECT NVL2(DEPTNO, TO_CHAR(DEPTNO), '인턴') "부서번호" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> /* 10 8750 20 10875 30 9400 인턴 8000 인턴 37025 */
- GROUPING()
- 그루핑
--※ GROUPING() SELECT DEPTNO "부서번호", SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> /* 10 8750 20 10875 30 9400 8000 37025 */ SELECT DEPTNO "부서번호", SUM(SAL) "급여합", GROUPING(DEPTNO) "그루핑결과" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> /* 10 8750 0 20 10875 0 30 9400 0 8000 0 37025 1 */ SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN DEPTNO ELSE '모든부서' END "부서번호" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> 에러 발생 /* ORA-00932: inconsistent datatypes: expected NUMBER got CHAR 00932. 00000 - "inconsistent datatypes: expected %s got %s" *Cause: *Action: 203행, 18열에서 오류 발생 */ SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN TO_CHAR(DEPTNO) ELSE '모든부서' END "부서번호" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> /* 10 8750 20 10875 30 9400 8000 모든부서 37025 */ SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴') ELSE '모든부서' END "부서번호" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> /* 10 8750 20 10875 30 9400 인턴 8000 모든부서 37025 */
더보기-- 그루핑 개념 후 내 풀이 SELECT CASE WHEN GROUPING(DEPTNO) = 0 AND DEPTNO IS NULL THEN '인턴' WHEN GROUPING(DEPTNO) = 1 AND DEPTNO IS NULL THEN '모든부서' ELSE TO_CHAR(DEPTNO) END "부서번호" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO); --==>> /* 10 8750 20 10875 30 9400 인턴 8000 모든부서 37025 */
--○ TBL_SAWON 테이블을 다음과 같이 조회될 수 있도록 쿼리문을 구성한다. /* -------------------------------- 성별 급여합 -------------------------------- 남 XXXXXX 여 XXXXXX 모든사원 XXXXXX -------------------------------- */
더보기--내 풀이 -- 방법1 SELECT CASE WHEN GROUPING(T.성별) = 1 THEN '모든사원' ELSE T.성별 END "성별" , SUM(T.급여)"급여합" FROM ( SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남' WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여' ELSE '판별불가' END "성별" , SAL "급여" FROM TBL_SAWON ) T GROUP BY ROLLUP(T.성별); --방법2 SELECT NVL(T.성별,'모든사원')"성별" , SUM(T.급여)"급여합" FROM ( SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남' WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여' ELSE '판별불가' END "성별" , SAL "급여" FROM TBL_SAWON ) T GROUP BY ROLLUP(T.성별);
더보기-- 강사님 풀이 SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남' WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여' ELSE '확인불가' END "성별" , SAL "급여" FROM TBL_SAWON; --==>> /* 여 100 여 2000 여 5000 여 4000 여 2000 여 2000 남 2000 남 1000 여 1000 남 1500 남 3000 여 2000 남 4000 남 3000 여 2000 남 4000 */ SELECT T.성별 "성별" , SUM(T.급여) "급여합" FROM ( SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남' WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여' ELSE '확인불가' END "성별" , SAL "급여" FROM TBL_SAWON ) T GROUP BY ROLLUP(T.성별); --==>> /* 남 18500 여 20100 38600 */ SELECT CASE GROUPING(T.성별) WHEN 0 THEN T.성별 ELSE '모든사원' END "성별" , SUM(T.급여) "급여합" FROM ( SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','3') THEN '남' WHEN SUBSTR(JUBUN,7,1) IN('2','4') THEN '여' ELSE '확인불가' END "성별" , SAL "급여" FROM TBL_SAWON ) T GROUP BY ROLLUP(T.성별); --==>> /* 성별 급여합 ----------- ---------- 남 18500 여 20100 모든사원 38600 */
- VIEW 목록 검색
- 현재 가지고 있는 VIEW 목록들을 확인하고 싶어서 추가로 확인
더보기SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'VIEW'
- 데이터 수정 , 확인 , 커밋
더보기SELECT * FROM VIEW_SAWON; --○ 데이터 수정 UPDATE TBL_SAWON SET JUBUN = '9010102234567' WHERE SANO = 1001; --==>> 1 행 이(가) 업데이트되었습니다. --○ 수정된 내용 확인 SELECT * FROM VIEW_SAWON; --> 데이터 수정 확인 --○ 커밋 COMMIT; --==>> 커밋 완료.
--○ TBL_SAWON 테이블을 다음과 같이 연령대별 인원수 형태로 -- 조회할 수 있도록 쿼리문을 구성한다. /* -------------------- 연령대 인원수 -------------------- 10 X 20 X 30 X 40 X 50 X 전체 XX -------------------- */
더보기-- 내 풀이 SELECT EXTRACT(YEAR FROM SYSDATE) - CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899 WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999 ELSE -1 END "현재 나이" FROM TBL_SAWON; --==>> /* 34 25 30 25 25 28 26 28 22 19 49 53 18 19 23 51 */ SELECT CASE WHEN T.현재나이 >=50 AND T.현재나이 <60 THEN '50' WHEN T.현재나이 >=40 THEN '40' WHEN T.현재나이 >=30 THEN '30' WHEN T.현재나이 >=20 THEN '20' WHEN T.현재나이 >=10 THEN '10' ELSE '판별불가' END "연령대" FROM ( SELECT EXTRACT(YEAR FROM SYSDATE) - CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899 WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999 ELSE -1 END "현재나이" FROM TBL_SAWON )T; --==>> /* 30 20 30 20 20 20 20 20 20 10 40 50 10 10 20 50 */ SELECT CASE GROUPING(S.연령대) WHEN 1 THEN '전체' ELSE S.연령대 END "연령대" , COUNT(*) "인원수" FROM ( SELECT CASE WHEN T.현재나이 >=50 AND T.현재나이 <60 THEN '50' WHEN T.현재나이 >=40 THEN '40' WHEN T.현재나이 >=30 THEN '30' WHEN T.현재나이 >=20 THEN '20' WHEN T.현재나이 >=10 THEN '10' ELSE '판별불가' END "연령대" FROM ( SELECT EXTRACT(YEAR FROM SYSDATE) - CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899 WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999 ELSE -1 END "현재나이" FROM TBL_SAWON )T )S GROUP BY ROLLUP(S.연령대); SELECT CASE GROUPING(S.연령대) WHEN 1 THEN '전체' ELSE S.연령대 END "연령대" , COUNT(S.연령대) "인원수" FROM ( SELECT CASE WHEN T.현재나이 >=50 AND T.현재나이 <60 THEN '50' WHEN T.현재나이 >=40 THEN '40' WHEN T.현재나이 >=30 THEN '30' WHEN T.현재나이 >=20 THEN '20' WHEN T.현재나이 >=10 THEN '10' ELSE '판별불가' END "연령대" FROM ( SELECT EXTRACT(YEAR FROM SYSDATE) - CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899 WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999 ELSE -1 END "현재나이" FROM TBL_SAWON )T )S GROUP BY ROLLUP(S.연령대); --==>> /* 연령대 인원수 ---------- ---------- 10 3 20 8 30 2 40 1 50 2 전체 16 */
-- 방법 1. (INLINEVIEW 를 두 번 중첩) SELECT CASE GROUPING(Q2.연령대) WHEN 0 THEN TO_CHAR(Q2.연령대) ELSE '전체' END "연령대" , COUNT(Q2.연령대) "인원수" FROM ( -- 연령대 SELECT Q1.나이 "나이" -- 연령대 제대로 나오는지 확인 , CASE WHEN Q1.나이 >=50 AND Q1.나이 < 60 THEN 50 WHEN Q1.나이 >=40 THEN 40 WHEN Q1.나이 >=30 THEN 30 WHEN Q1.나이 >=20 THEN 20 WHEN Q1.나이 >=10 THEN 10 ELSE 0 END "연령대" FROM ( --나이 SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN('1','2') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2) + 1899)) WHEN SUBSTR(JUBUN,7,1) IN('3','4') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2) + 1999)) ELSE 0 END "나이" FROM TBL_SAWON ) Q1 )Q2 GROUP BY ROLLUP(Q2.연령대); --==>> /* 연령대 인원수 ---------------------------------------- ---------- 10 3 20 8 30 2 40 1 50 2 전체 16 */
-- 방법 2. (INLINEVIEW 를 한 번만 사용) SELECT CASE GROUPING(Q.연령대) WHEN 0 THEN TO_CHAR(Q.연령대) ELSE '전체' END "연령대" , COUNT(Q.연령대) "인원수" FROM ( SELECT TRUNC(CASE WHEN SUBSTR(JUBUN,7,1) IN('1','2') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2) + 1899)) WHEN SUBSTR(JUBUN,7,1) IN('3','4') THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2) + 1999)) ELSE 0 END,-1) "연령대" FROM TBL_SAWON )Q GROUP BY ROLLUP(Q.연령대); --==>> /* 연령대 인원수 ---------------------------------------- ---------- 10 3 20 8 30 2 40 1 50 2 전체 16 */
- ROLLUP 활용 및 CUBE + GROUPING SETS
--※ ROLLUP() 과 CUBE() 는 -- 그룹을 묶어주는 방식이 다르다. (차이) -- ROLLUP(A,B,C) -- → (A,B,C) / (A,B) / (A) / (A) -- CUBE(A,B,C) -- → (A,B,C) / (A,B) / (A,C) / (B,C) / (A) / (B) / (C) / ()
- ROLLUP()
SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY DEPTNO, JOB ORDER BY 1,2; --==>> /* DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 */ SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB) ORDER BY 1,2; --==>> /* DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 -- 10번 부서 모든 직종의 급여 합 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 -- 20번 부서 모든 직종의 급여 합 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400 -- 30번 부서 모든 직종의 급여 합 2902 -- 모든 부서 모든 직종의 급여 합 */
- CUBE()
- ROLLUP() 보다 더 자세한 결과를 반환
- ++ 된 부분이 ROLLUP 보다 추가 된 결과
--○ CUBE() → ROLLUP() 보다 더 자세한 결과를 반환 받을 수 있다. SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO, JOB) ORDER BY 1,2; --==>> /* DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 -- 10번 부서 모든 직종의 급여 합 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 -- 20번 부서 모든 직종의 급여 합 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400 -- 30번 부서 모든 직종의 급여 합 ANALYST 6000 -- 모든 부서 ANALYST 직종의 급여합 ++ CLERK 4150 -- 모든 부서 CLERK 직종의 급여합 ++ MANAGER 8275 -- 모든 부서 MANAGER 직종의 급여합 ++ PRESIDENT 5000 -- 모든 부서 PRESIDENT 직종의 급여합 ++ SALESMAN 5600 -- 모든 부서 SALESMAN 직종의 급여합 ++ 2902 -- 모든 부서 모든 직종의 급여 합 */
- GROUPING SETS
--==> 위의 처리 내용은 직접적으로 원하는 결과를 얻지 못하거나(→ROLLUP()) -- 불필요한 너무 많은 결과물을 얻게되기 때문에(→ CUBE()) -- 다음의 쿼리 형태를 더 많이 사용하게 된다. -- 다음 작성하는 쿼리는 조회하고자 하는 그룹만 직접 지정하여 묶어주는 방식이다. -- → 『GROUPING SETS』
더보기SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴') ELSE '전체부서' END "부서번호" , CASE GROUPING(JOB) WHEN 0 THEN JOB ELSE '전체직종' END "직종" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY ROLLUP(DEPTNO, JOB) ORDER BY 1,2; --==>> /* 부서번호 직종 급여합 ---------------------------------------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 전체직종 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 전체직종 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 560 30 전체직종 9400 인턴 CLERK 2500 인턴 SALESMAN 5500 인턴 전체직종 8000 전체부서 전체직종 37025 */ SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴') ELSE '전체부서' END "부서번호" , CASE GROUPING(JOB) WHEN 0 THEN JOB ELSE '전체직종' END "직종" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY CUBE(DEPTNO, JOB) ORDER BY 1,2; --==>> /* 부서번호 직종 급여합 ---------------------------------------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 전체직종 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 전체직종 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 전체직종 9400 인턴 CLERK 2500 인턴 SALESMAN 5500 인턴 전체직종 8000 전체부서 ANALYST 6000 전체부서 CLERK 6650 전체부서 MANAGER 8275 전체부서 PRESIDENT 5000 전체부서 SALESMAN 11100 전체부서 전체직종 3702 */
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴') ELSE '전체부서' END "부서번호" , CASE GROUPING(JOB) WHEN 0 THEN JOB ELSE '전체직종' END "직종" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY GROUPING SETS((DEPTNO, JOB), (DEPTNO), ()) ORDER BY 1,2; --==>> -- ROLLUP() 과 같은 결과 /* 부서번호 직종 급여합 ---------------------------------------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 전체직종 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 전체직종 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 전체직종 9400 인턴 CLERK 2500 인턴 SALESMAN 5500 인턴 전체직종 8000 전체부서 전체직종 3702 */ SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴') ELSE '전체부서' END "부서번호" , CASE GROUPING(JOB) WHEN 0 THEN JOB ELSE '전체직종' END "직종" , SUM(SAL) "급여합" FROM TBL_EMP GROUP BY GROUPING SETS((DEPTNO, JOB) , (DEPTNO) , (JOB), ()) --// 부서번호, 직종이 같은거 묶기 1 // 부서번호 같은거 묶기 // 직종 같은거 묶기 // 전체 묶기 ORDER BY 1,2; --==>> -- CUBE() 와 같은 결과 /* 부서번호 직종 급여합 ---------------------------------------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 전체직종 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 전체직종 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 전체직종 9400 인턴 CLERK 2500 인턴 SALESMAN 5500 인턴 전체직종 8000 전체부서 ANALYST 6000 전체부서 CLERK 6650 전체부서 MANAGER 8275 전체부서 PRESIDENT 5000 전체부서 SALESMAN 11100 전체부서 전체직종 37025 */
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; --==>> Session이(가) 변경되었습니다. --○ TBL_EMP 테이블에서 입사년도별 인원수를 조회한다. /* ------------------------------ 입사년도 인원수 ------------------------------ 1980 1 1981 10 1982 1 1987 2 2023 5 전체 19 ------------------------------ */
더보기--내 풀이 -- ROLLUP() SELECT CASE GROUPING(T.입사년도) WHEN 0 THEN T.입사년도 ELSE '전체' END "입사년도" , COUNT(*) 인원수 FROM ( SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도" FROM TBL_EMP )T GROUP BY ROLLUP(T.입사년도); --==>> /* 입사년도 인원수 ----------- ------ 1980 1 1981 10 1982 1 1987 2 2023 5 전체 19 */ --CUBE() SELECT CASE GROUPING(T.입사년도) WHEN 0 THEN T.입사년도 ELSE '전체' END "입사년도" , COUNT(*) 인원수 FROM ( SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도" FROM TBL_EMP )T GROUP BY CUBE(T.입사년도); --==>> /* 입사년도 인원수 ----------- ------ 전체 19 1980 1 1981 10 1982 1 1987 2 2023 5 */ --GROUPING SETS() SELECT CASE GROUPING(T.입사년도) WHEN 0 THEN T.입사년도 ELSE '전체' END "입사년도" , COUNT(*) 인원수 FROM ( SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도" FROM TBL_EMP )T GROUP BY GROUPING SETS((T.입사년도),()); --==>> /* 입사년도 인원수 ----------- ------ 1980 1 1981 10 1982 1 1987 2 2023 5 전체 19 */
--//1. 기준이 하나여서 동일하게 처리됨 확인 SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY ROLLUP(EXTRACT(YEAR FROM HIREDATE)) ORDER BY 1; --==>> /* 1980 1 1981 10 1982 1 1987 2 2023 5 19 */ SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY CUBE(EXTRACT(YEAR FROM HIREDATE)) ORDER BY 1; --==>> /* 1980 1 1981 10 1982 1 1987 2 2023 5 19 */ SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY GROUPING SETS(EXTRACT(YEAR FROM HIREDATE),()) ORDER BY 1; --==>> /* 1980 1 1981 10 1982 1 1987 2 2023 5 19 */ --//2. 세가지와 관계 없이 모든 GROUP BY에 해당하는 사항 SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY ROLLUP(TO_CHAR(HIREDATE,'YYYY')) ORDER BY 1; --==>> /* ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: 936행, 26열에서 오류 발생 */ SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY CUBE(TO_CHAR(HIREDATE,'YYYY')) ORDER BY 1; --==>> /* ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: 950행, 26열에서 오류 발생 */ SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY GROUPING SETS(TO_CHAR(HIREDATE,'YYYY'),()) ORDER BY 1; --==>> /* ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: 964행, 26열에서 오류 발생 */ --> 그룹바이에서 문자 타입으로 찾는데 위의 EXTRACT 는 숫자 타입으로 가져오기 때문에 SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY ROLLUP(TO_CHAR(HIREDATE,'YYYY')) ORDER BY 1; --==>> /* 1980 1 1981 10 1982 1 1987 2 2023 5 19 */ SELECT TO_CHAR(HIREDATE,'YYYY') "입사년도" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY ROLLUP(EXTRACT(YEAR FROM HIREDATE)) ORDER BY 1; --==>> /* ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: 995행, 16열에서 오류 발생 */ --> 즉, GROUP BY의 절의 처리방식과 SELECT 의 처리방식이 같아야한다.
- HAVING
- 관찰
-■■■ HAVING ■■■-- --○ EMP 테이블에서 부서번호가 20, 30인 부서를 대상으로 -- 부서의 총 급여가 10000보다 적을 경우만 부서별 총 급여를 조회한다. SELECT DEPTNO "부서번호", SUM(SAL) "급여합" FROM EMP WHERE DEPTNO IN(20,30) GROUP BY DEPTNO; --==>> /* 30 9400 20 10875 */ SELECT DEPTNO "부서번호", SUM(SAL) "급여합" FROM EMP WHERE DEPTNO IN(20,30) AND SUM(SAL) < 10000 -- WHERE 조건 절에서 그룹함수(그룹조건)를 사용할 수 없음 GROUP BY DEPTNO; --==>> 에러 발생 /* ORA-00934: group function is not allowed here 00934. 00000 - "group function is not allowed here" *Cause: *Action: 1,036행, 21열에서 오류 발생 */ --업무상 위배되는 것이 아니라면 개별조건(WHERE)로 먼저 퍼올린 데이터를 처리하는 것이 좋다. SELECT DEPTNO "부서번호", SUM(SAL) "급여합" FROM EMP WHERE DEPTNO IN(20,30) GROUP BY DEPTNO HAVING SUM(SAL) < 10000; -- 그룹에 대한 조건절은 HAVING 에. --==>> 30 9400 SELECT DEPTNO "부서번호", SUM(SAL) "급여합" FROM EMP GROUP BY DEPTNO HAVING DEPTNO IN (20,30) AND SUM(SAL) < 10000; --==>> 30 9400
- 중첩 그룹함수 / 분석함수
- 관찰
- RANK(), DENSE_RANK() 버전 확인 필요
--■■■ 중첩 그룹함수 / 분석함수 ■■■-- -- 그룹 함수 2 LEVEL 까지 중첩해서 사용할 수 있다. -- 이마저도.. MS-SQL은 불가능하다. SELECT MAX(SUM(SAL)) "결과확인" -- 이렇게 두번 중첩하는 것이 2 LEVEL FROM EMP GROUP BY DEPTNO; --==>> 10875 -- RANK() -- DENSE_RANK() -- → ORACLE 9i 부터 적용... MS-SQL 2005 부터 적용
- 서브 상관 쿼리 (상관 서브 쿼리)
--※ 하위 버전에서는 RANK() 나 DENSE_RANK() 를 사용할 수 없기 때문에 -- 이를 대체하여 연산을 수행할 수 있는 방법을 강구해야 한다. -- 예를 들어, EMP 테이블에서 급여의 등수를 구하고자 한다면... -- 해당 사원의 급여보다 더 큰 값이 몇개인지 확인 후 -- 그 확인한 숫자에 +1 을 추가연산해주면 그것이 곧 등수가 된다. SELECT ENAME, SAL FROM EMP; SELECT COUNT(*) + 1 FROM EMP WHERE SAL > 800; --SMITH 의 급여 800 --==>> 14 --SMITH 의 급여 등수 SELECT COUNT(*) + 1 FROM EMP WHERE SAL > 1600; -- ALLEN 의 급여 1600 --==>> 7 -- ALLEN 의 급여 등수
--※ 서브 상관 쿼리 (상관 서브 쿼리) -- 메인 쿼리에 있는 테이블의 컬럼이 -- 서브 쿼리의 조건절(WHERE절, HAVING절) 에 사용되는 경우 -- 우리는 이 쿼리문을 서브 상관 쿼리 하고 부른다. SELECT ENAME "사원명", SAL "급여", (1) "급여등수" FROM EMP; SELECT ENAME "사원명", SAL "급여", (SELECT COUNT(*) + 1 FROM EMP WHERE SAL > 800) "급여등수" FROM EMP; -- 해당 쿼리문을 편하게 보기 위해 개행 SELECT ENAME "사원명", SAL "급여" , (SELECT COUNT(*) + 1 FROM EMP WHERE SAL > 800) "급여등수" FROM EMP; -- FROM 의 EMP 들이 동일 하기 때문에 구분하기 위해 별칭을 사용 -- 끌어다 사용할 수 있도록 만듦 SELECT ENAME "사원명", SAL "급여" , (SELECT COUNT(*) + 1 FROM EMP E2 WHERE SAL > 800) "급여등수" FROM EMP E1; -- 구분하기 쉽게 별칭을 적어둠 SELECT E1.ENAME "사원명", E1.SAL "급여" , (SELECT COUNT(*) + 1 FROM EMP E2 WHERE E2.SAL > 800) "급여등수" FROM EMP E1; SELECT E1.ENAME "사원명", E1.SAL "급여" , (SELECT COUNT(*) + 1 FROM EMP E2 WHERE E2.SAL > E1.SAL) "급여등수" FROM EMP E1; --==>> /* SMITH 800 14 ALLEN 1600 7 WARD 1250 10 JONES 2975 4 MARTIN 1250 10 BLAKE 2850 5 CLARK 2450 6 SCOTT 3000 2 KING 5000 1 TURNER 1500 8 ADAMS 1100 12 JAMES 950 13 FORD 3000 2 MILLER 1300 9 */ SELECT E1.ENAME "사원명", E1.SAL "급여" , (SELECT COUNT(*) + 1 FROM EMP E2 WHERE E2.SAL > E1.SAL) "급여등수" FROM EMP E1 ORDER BY 3; --==>> /* KING 5000 1 FORD 3000 2 SCOTT 3000 2 JONES 2975 4 BLAKE 2850 5 CLARK 2450 6 ALLEN 1600 7 TURNER 1500 8 MILLER 1300 9 WARD 1250 10 MARTIN 1250 10 ADAMS 1100 12 JAMES 950 13 SMITH 800 14 */
--○ EMP 테이블을 대상으로 -- 사원명, 급여, 부서번호, 부서내급여등수, 전체급여등수 항목을 조회한다. -- 단, RANK() 함수를 사용하지 않고, 서브 상관 쿼리를 활용할 수 있도록 한다.
더보기--○ EMP 테이블을 대상으로 -- 사원명, 급여, 부서번호, 부서내급여등수, 전체급여등수 항목을 조회한다. -- 단, RANK() 함수를 사용하지 않고, 서브 상관 쿼리를 활용할 수 있도록 한다. SELECT ENAME "사원명" , SAL "급여" , DEPTNO "부서번호" , () "부서내급여등수" , () "전체급여등수" FROM EMP; SELECT E1.ENAME "사원명" , E1.SAL "급여" , E1.DEPTNO "부서번호" , (SELECT COUNT(*) + 1 FROM EMP E2 WHERE E2.DEPTNO = E1.DEPTNO AND E2.SAL > E1.SAL) "부서내급여등수" , (SELECT COUNT(*) + 1 FROM EMP E2 WHERE E2.SAL > E1.SAL) "전체급여등수" FROM EMP E1 ORDER BY E1.DEPTNO, E1.SAL DESC; --==>> /* 사원명 급여 부서번호 부서내급여등수 전체급여등수 ---------- ---------- ---------- -------------- ------------ KING 5000 10 1 1 CLARK 2450 10 2 6 MILLER 1300 10 3 9 SCOTT 3000 20 1 2 FORD 3000 20 1 2 JONES 2975 20 3 4 ADAMS 1100 20 4 12 SMITH 800 20 5 14 BLAKE 2850 30 1 5 ALLEN 1600 30 2 7 TURNER 1500 30 3 8 MARTIN 1250 30 4 10 WARD 1250 30 4 10 JAMES 950 30 6 13 */
--○ EMP 테이블을 대상으로 다음과 같이 조회할 수 있도록 쿼리문을 구성한다. /* ----------------------------------------------------------------------- 사원명 부서번호 입사일 급여 부서내입사별급여누적 ----------------------------------------------------------------------- CLARK 10 1981-06-09 2450 2450 KING 10 1981-11-17 5000 7450 MILLER 10 1981-01-23 1300 8750 SMITH 20 1980-12-17 800 800 JONES 20 1981-04-02 2975 3775 : -----------------------------------------------------------------------
SELECT ENAME "사원명", DEPTNO "부서번호", HIREDATE "입사일", SAL "급여" , (1)"부서내입사별급여누적" FROM EMP ORDER BY 2, 3; -- E1, E2 와 같이 별칭을 붙이지 않아도 조회 된다. SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여" , (SELECT SUM(E2.SAL) FROM EMP E2)"부서내입사별급여누적" FROM EMP E1 ORDER BY 2, 3; -- 부서내 급여누적이 같아짐 SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여" , (SELECT SUM(E2.SAL) FROM EMP E2 WHERE E2.DEPTNO = E1.DEPTNO)"부서내입사별급여누적" FROM EMP E1 ORDER BY 2, 3; -- 과거가 미래보다 작고, 해당일을 포함해야하기 때문에 <= 로 표시 SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여" , (SELECT SUM(E2.SAL) FROM EMP E2 WHERE E2.DEPTNO = E1.DEPTNO AND E2.HIREDATE <= E1.HIREDATE) "부서내입사별급여누적" FROM EMP E1 ORDER BY 2, 3; --==>> /* CLARK 10 1981-06-09 2450 2450 KING 10 1981-11-17 5000 7450 MILLER 10 1982-01-23 1300 8750 SMITH 20 1980-12-17 800 800 JONES 20 1981-04-02 2975 3775 FORD 20 1981-12-03 3000 6775 SCOTT 20 1987-07-13 3000 10875 ADAMS 20 1987-07-13 1100 10875 ALLEN 30 1981-02-20 1600 1600 WARD 30 1981-02-22 1250 2850 BLAKE 30 1981-05-01 2850 5700 TURNER 30 1981-09-08 1500 7200 MARTIN 30 1981-09-28 1250 8450 JAMES 30 1981-12-03 950 9400 */
--○ TBL_EMP 테이블에서 입사한 사원 수가 가장 많았을 때의 -- 입사년월과 인원수를 조회할 수 있는 쿼리문을 구성한다. /* ------------------------ 입사년월 인원수 ------------------------ XXXX-XX XX ------------------------ */
SELECT ENAME, HIREDATE FROM TBL_EMP ORDER BY 2; --==>> /* SMITH 1980-12-17 ALLEN 1981-02-20 WARD 1981-02-22 JONES 1981-04-02 BLAKE 1981-05-01 CLARK 1981-06-09 TURNER 1981-09-08 MARTIN 1981-09-28 KING 1981-11-17 FORD 1981-12-03 JAMES 1981-12-03 MILLER 1982-01-23 SCOTT 1987-07-13 ADAMS 1987-07-13 ㅇㅇㅅ 2023-03-28 ㅊㅇㅅ 2023-03-28 ㅇㅅㅇ 2023-03-28 ㅊㅎㄱ 2023-03-28 ㅇㅎㅈ 2023-03-28 */ -- 입사년월 별 인원수 SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY TO_CHAR(HIREDATE,'YYYY-MM'); --==>> /* 1981-05 1 1981-12 2 1982-01 1 1981-09 2 2023-03 5 1981-02 2 1981-11 1 1980-12 1 1981-04 1 1987-07 2 1981-06 1 */ --// 위에서 확인한 가장 큰 값을 넣었을 때 SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY TO_CHAR(HIREDATE,'YYYY-MM') HAVING COUNT(*) = 5 ; -- 인원 수 확인 --==>> 2023-03 5 --// 직접 확인하지 않고 가장 큰 값을 확인 SELECT MAX(COUNT(*)) FROM TBL_EMP GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM'); --==>> 5 -- 가장 큰 COUNT 값 확인 --// 서브 상관 쿼리가 HAVING 절에도 들어갈 수 있음 SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월" , COUNT(*) "인원수" FROM TBL_EMP GROUP BY TO_CHAR(HIREDATE,'YYYY-MM') HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM TBL_EMP GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')) ; --==>> /* 입사년월 인원수 --------- ---------- 2023-03 5 */
- ROW_NUMBER() , SEQUENCE
- ROW_NUMBER()
관찰
더보기SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) "관찰" -- 레코드 넘버링..? , ENAME "사원명", SAL "급여", HIREDATE "입사일" FROM EMP; --==>> /* 관찰 사원명 급여 입사일 ---------- ---------- ---------- ---------- 1 KING 5000 1981-11-17 2 FORD 3000 1981-12-03 3 SCOTT 3000 1987-07-13 4 JONES 2975 1981-04-02 5 BLAKE 2850 1981-05-01 6 CLARK 2450 1981-06-09 7 ALLEN 1600 1981-02-20 8 TURNER 1500 1981-09-08 9 MILLER 1300 1982-01-23 10 WARD 1250 1981-02-22 11 MARTIN 1250 1981-09-28 12 ADAMS 1100 1987-07-13 13 JAMES 950 1981-12-03 14 SMITH 800 1980-12-17 */ SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) "관찰" -- 이미 퍼올렸을 때 급여를 기준으로 넘버링을 함 , ENAME "사원명", SAL "급여", HIREDATE "입사일" FROM EMP ORDER BY ENAME; -- 이후 다시 ORDER BY로 정렬할 때 넘버링의 숫자는 바뀌지 않음! --==>> /* 관찰 사원명 급여 입사일 ---------- ---------- ---------- ---------- 12 ADAMS 1100 1987-07-13 7 ALLEN 1600 1981-02-20 5 BLAKE 2850 1981-05-01 6 CLARK 2450 1981-06-09 2 FORD 3000 1981-12-03 13 JAMES 950 1981-12-03 4 JONES 2975 1981-04-02 1 KING 5000 1981-11-17 11 MARTIN 1250 1981-09-28 9 MILLER 1300 1982-01-23 3 SCOTT 3000 1987-07-13 14 SMITH 800 1980-12-17 8 TURNER 1500 1981-09-08 10 WARD 1250 1981-02-22 */
--※ 게시판의 게시물 번호를 -- SEQUENCE 나 IDNETITY 를 사용하게 되면 -- 게시물을 삭제했을 경우, 삭제한 게시물의 자리에 -- 다음 번호를 가진 게시물이 등록되는 상황이 발생하게 된다. -- 이는, 보안 측면에서나... 미관상... 바람직하지 않은 상황일 수 있기 때문에 -- ROW_NUMBER() 의 사용을 고려할 수 있다. -- 관리의 목적으로 사용할 때에는 SEQUENCE 나 IDNETITY 를 사용하지만 -- 단순히 게시물을 보기 편하도록 목록화하여 사용자에게 리스트 형식으로 보여줄 때는 -- 사용하지 않는 것이 좋다. --※ 관찰 CREATE TABLE TBL_AAA ( NO NUMBER(10) , NAME VARCHAR2(40) , GRADE CHAR ); --==>> Table TBL_AAA이(가) 생성되었습니다. INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(1,'ㅎㅇㅎ','A'); INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(2,'ㅊㅎㄱ','B'); INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(3,'ㄱㅂㅎ','A'); INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(4,'ㅈㅅㅇ','C'); INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(5,'ㅁㅅㅎ','A'); INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(6,'ㅎㅇㅎ','B'); INSERT INTO TBL_AAA(NO,NAME,GRADE) VALUES(7,'ㅎㅇㅎ','B'); --==>> 1 행 이(가) 삽입되었습니다.* 7 SELECT * FROM TBL_AAA; --==>> /* 1 ㅎㅇㅎ A 2 ㅊㅎㄱ B 3 ㄱㅂㅎ A 4 ㅈㅅㅇ C 5 ㅁㅅㅎ A 6 ㅎㅇㅎ B 7 ㅎㅇㅎ B */ COMMIT; --==>> 커밋 완료. UPDATE TBL_AAA SET GRADE='A' WHERE NAME='ㅎㅇㅎ' --> 모든 ㅎㅇㅎ 의 성적이 A로 변함 UPDATE TBL_AAA SET GRADE='A' WHERE GRADE='B' --> 7번 ㅎㅇㅎ 도 성적이 A로 변함 UPDATE TBL_AAA SET GRADE='A' WHERE NAME='ㅎㅇㅎ' AND GRADE='B' --> 둘 다 만족하는 것도 6번 7번이 동일하여, 이 경우에는 번호를 쓰지 않고서는 바꿀 수 없다.
- SQUENCE()
--○ SEQUENCE 생성(시퀀스, 주문번호) -- → 사전적인 의미 : 1.(일련의) 연속적인 사건들 2.(사건,행동 등의) 순서 CREATE SEQUENCE SEQ_BOARD; -- 시퀀스 기본 생성 구문(MS-SQL 의 IDENTITY 와 동일한 개념) --> 이와 같이 생성하면 기본적인 시퀀스가 생성된다. CREATE SEQUENCE SEQ_BOARD -- 시퀀스 기본 생성 구문(MS-SQL 의 IDENTITY 와 동일한 개념) START WITH 1 -- 시작값 INCREMENT BY 1 -- 증가값 NOMAXVALUE -- 최대값 (크기지정 / NOMAXVALUE → 제한 없음) NOCACHE; -- 캐시 사용 안함(없음) --// 미리 시퀀스들을 발행하여 나눠줄 수 있게 뽑아두는 것 --(장점 : 사용자들이 세션(번호표뽑는줄)에 머무르는 시간이 줄어듬) --(단점 : 사용자들이 몰리지 않으면 중간에 남은 번호들이 버려져 번호가 건너뛰어질 수 있다) --> //기본 구문 외에 붙는것은 모두 옵션이라고 한다. // 테이블과는 별개다 --==>> Sequence SEQ_BOARD이(가) 생성되었습니다.
--○ 테이블 생성(TBL_BOARD) // 사용자가 작성하는지 CREATE TABLE TBL_BOARD -- TBL_BOARD 이름의 테이블 생성 → 게시판으로 활용 ( NO NUMBER -- 게시물 번호 X , TITLE VARCHAR2(50) -- 게시물 제목 ○ , CONTENTS VARCHAR2(2000) -- 게시물 내용 ○ , NAME VARCHAR2(30) -- 게시물 작성자 △ , PW VARCHAR2(40) -- 게시물 패스워드 △ , CREATED DATE DEFAULT SYSDATE -- 게시물 작성일 X ); --==>> Table TBL_BOARD이(가) 생성되었습니다. --○ 데이터 입력 → 게시판에 게시물 작성 INSERT INTO TBL_BOARD VALUES (게시물번호, 게시물제목, 게시물내용, 작성자, 패스워드, 작성일);
728x90'SsY > Class' 카테고리의 다른 글
008. UNION / UNION ALL, INTERSECT / MINUS, NATURAL JOIN, USING (0) 2023.03.31 007. SEQUENCE 와 ROW_NUMBER, JOIN(SQL 1992/1999 CODE), UNION / UNION ALL (0) 2023.03.30 005. 서브쿼리, 인라인뷰, 등수 함수, 그룹 함수 와 주의점 (0) 2023.03.29 004. 문자열 / 숫자 데이터 / 날짜 데이터 관련 함수, 변환 함수, CASE문(조건문, 분기문) (0) 2023.03.28 003. 문자열 데이터 조회(와일드카드, ESCAPE), 커밋/롤백, 데이터 업데이트, 정렬, 문자열 관련 함수 (0) 2023.03.28