ROLLUP & CONNECT BY

COUNT比較

--1. COUNT(*) / COUNT(컬럼명) / COUNT(DISTINCT 컬럼명) 실행결과와 차이점 
--COUNT(*)는 NULL값 까지 카운트를 하는데 COUNT(컬럼)은 NULL값은 세지 않는다.그리고 COUNT(DISTINCT)는 고유개수(중복제거) 값만을 세준다.
SELECT COUNT(*)
FROM EMP;

SELECT COUNT(MGR)
FROM EMP;

SELECT COUNT(DISTINCT MGR)
FROM EMP;
--2. SELECT AVG(COMM) FROM EMP 와 SELECT AVG(NVL(COMM, 0)) FROM EMP

実行結果

NVL()

--실행결과와 차이점. 왜 결과가 다른지?
-- NVL()을 안해주면 NULL인 애들이 0처리가 안되어 카운트도 같이 안된다.
SELECT AVG(COMM) FROM EMP;
 SELECT AVG(NVL(COMM, 0)) FROM EMP;

実行結果

ROLLUP

--3. GROUP BY ROLLUP 을 활용한 소계 연습 
--  부서별 급여 합계 금액을  아래와 같이 표시
 
SELECT CASE WHEN JOB IS NOT NULL THEN TO_CHAR(DEPTNO) WHEN TO_CHAR(DEPTNO) IS NULL AND JOB IS NULL THEN '총계' ELSE NULL END AS DEPTNO
      ,CASE WHEN DEPTNO IS NOT NULL THEN (CASE WHEN (CASE WHEN JOB IS NOT NULL THEN DEPTNO ELSE NULL END) IS NOT NULL THEN JOB ELSE NVL(JOB, '소계(' || DEPTNO || '번부서)') END) ELSE NULL END AS JOB
      ,SUM(SAL) AS SAL
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB);
 

実行結果

数値の時間の平均値を求めます

--4. EMP_CHULGYEOL테이블에서 2019년 11월 사원별 평균 출근 시간을 구하기.
-- 사원별 토,일요일을 제외한 평일(21)일에 대한 데이터가 모두 들어가있다.
-- 출근시간이 비어있는 데이터는 해당일자에 결근한 사람이다.
-- 결근시에는 18시에 출근한 것으로 처리한다.
-- 출근시간이 빠른순으로 정렬.

SELECT EMPNO
      ,CASE WHEN SUBSTR(LPAD(ROUND(AVG(NVL(CHULGEUN_SIGAN,'1800'))),4,0),3,2)>=60 THEN LPAD(SUBSTR(LPAD(ROUND(AVG(NVL(CHULGEUN_SIGAN,'1800'))),4,0),1,2)+1,2,0) || LPAD(MOD(SUBSTR(LPAD(ROUND(AVG(NVL(CHULGEUN_SIGAN,'1800'))),4,0),3,2),60),2,0) 
      ELSE LPAD(ROUND(AVG(NVL(CHULGEUN_SIGAN,'1800'))),4,0) END AS AVG_MIN
FROM EMP_CHULGYEOL
GROUP BY EMPNO
ORDER BY EMPNO;

実行結果

CONNECT BY LEVEL

--1. CONNECT BY LEVEL 을 활용하여 1 ~ 10까지 출력하시오
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <=10;
--2. CONNECT BY LEVEL 을 활용하여 0.1 ~ 0.5까지 출력하시오
SELECT LEVEL*0.1
FROM DUAL
CONNECT BY LEVEL <=5;

実行結果

エラーの原因

--3. 다음 쿼리가 실행되지 않는 이유?
 --WHERE절에서는 집계함수를 사용하지 못한다.
 --사용할려면 HAVING절에서 사용

SELECT * FROM EMPFAMILY
WHERE AVG(AGE) > 40;

最後に、クリア

WITH T AS(
  SELECT '100,101,102,103,' AS TXT FROM DUAL
)
SELECT RTRIM(TXT,',') AS TXT
FROM T;
--위와 같은 임시테이블의데이터에서 마지막 ',' 만 빼고 출력하려면?

実行結果

区切り文字の位置に切り取り

WITH TMP AS
(
SELECT '10.100.10.1' AS IP FROM DUAL
UNION ALL SELECT '10.100.1.10' FROM DUAL
UNION ALL SELECT '10.10.10.10' FROM DUAL
UNION ALL SELECT '1.10.1.20' FROM DUAL
UNION ALL SELECT '3.10.1.140'  FROM DUAL
)
SELECT IP
     , LPAD( SUBSTR(IP,1,INSTR(IP,'.',1,1)-1) ,3,0) AS IP_1
     , LPAD( SUBSTR(IP,INSTR(IP,'.',1,1)+1,INSTR(IP,'.',1,2)-INSTR(IP,'.',1,1)-1) ,3,0) AS IP_2
     , LPAD( SUBSTR(IP, INSTR(IP,'.',1,2)+1, INSTR(IP,'.',1,3)-INSTR(IP,'.',1,2)-1) ,3,0) AS IP_3
     , LPAD( SUBSTR(IP, INSTR(IP,'.',1,3)+1),3,0) AS IP_4
FROM TMP
ORDER BY IP_1,IP_2,IP_3,IP_4;
-- 위와 같은 임시테이블의 데이터를 '.' 기준으로 분리하여 다음과 같이 출력하시오.

実行結果