Oracle SQL DML : SELECT 03(ORDER BY, GROUP BY, OVER ...)

ORDER BY : 정렬

ORDER BY는 SQL에서 데이터를 정렬을 할때 사용한다.

(WHERE, HAVING 보다 뒤에 위치.)

--** scott 계정 **--
-- ORDER BY : 정렬

SELECT *
FROM emp
ORDER BY sal;

-- ORDER BY sal ASC    -- 오름차순 정렬 
-- ORDER BY sal DESC   -- 내림차순 정렬

GROUP BY : 그룹

GROUP BY 로 COLUMN항목을 묶을 수 있으며, GRUOP으로 선정이되면 특정 함수 사용이 가능해진다.

COUNT, SUM, AVG, MAX, MIN 이 대표적이다. 하지만 GROUP BY로 묶게 되면,

해당 COLUMN에 대한 값 이외의 선택이 불가능해지는데,

이 불편함을 없애기 위해서는 OVER(PARTITION BY )를 사용해 주는것이 한 방법이다.

--** scott 계정 **--

SELECT deptno
FROM emp
GROUP BY deptno
ORDER BY deptno ASC; -- 그룹으로 묶은 후 정렬도 가능하다.

--** hr 계정 **--

SELECT COUNT(salary), COUNT(*), SUM(salary), AVG(salary), SUM(salary)/COUNT(*), job_id
FROM employees
WHERE job_id = 'IT_PROG';

SELECT COUNT(salary), COUNT(*), SUM(salary), AVG(salary), SUM(salary)/COUNT(*), job_id
FROM employees
GROUP BY job_id;

-- HAVING <- GROUP BY 에서의 WHERE절 이라고 보면 이해가 빠르다.
SELECT job_id, SUM(salary)
FROM employees
GROUP BY job_id
HAVING SUM(salary) >= 100000
ORDER BY SUM(salary) DESC;

OVER(PARTITION BY ) : 

SELECT job_id, COUNT(*)OVER()
FROM employees;

SELECT job_id, COUNT(*)OVER(PARTITION BY job_id)
FROM employees;

SELECT job_id, salary, COUNT(*)OVER(PARTITION BY job_id ORDER BY salary DESC)
FROM employees;

SELECT first_name, salary, 

    RANK()OVER(ORDER BY salary DESC) AS RANK,
    DENSE_RANK()OVER(ORDER BY salary DESC) AS DENSE_RANK,
    ROW_NUMBER()OVER(ORDER BY salary DESC) AS ROW_NUMBER
    
FROM employees;

ROWNUM : 순위? 시퀀스?

sequence 처럼 사용이 가능하다.

--** 10명의 ROW만을 산출하는 경우 **--
SELECT ROWNUM, employee_id, first_name, salary
FROM employees
WHERE ROWNUM <= 10;

--** 11 ~ 20의 ROW만을 산출하는 경우 **--

SELECT ROWNUM, employee_id, first_name, salary  -- 2. 후실행
FROM employees
WHERE ROWNUM > 10 AND ROWNUM <= 20; -- 1. 선실행

-- WHERE절 에 있는 1번ROWNUM을 실행 후 SELECT에 있는 2번 ROWNUM을 실행하기 때문에 결과값이 없다.
-- 그러므로 다음과 같이 작성해야 문제없이 실행이 가능하다.

SELECT RNUM, employee_id, first_name, salary  
FROM
    (SELECT ROWNUM AS RNUM, employee_id, first_name, salary      -- 2. ROWNUM 완성   
    FROM 
        (SELECT employee_id, first_name, salary  -- 1.DATA 설정
		FROM employees
		ORDER BY salary DESC) 
	)
WHERE RNUM > 10 AND RNUM <= 20;    -- 3. 범위를 설정

-- 1. 설정
-- 2. ROWNUM 완성
-- 3. 범위를 설정
-- 위 순서대로 차근차근 코드를 작성하는것이 논리적이며, 꼬이지 않게되므로 참고하자.

CASE, DECODE : 

CASE와 DECODE는 거의 동일하며, CASE는 JAVA에서 SWITCH문과 흡사한 구조를 갖는것이 특징이다.

특정 문자를 다른문자로 변경하거나, 조건에 해당하는 값을 등급으로 표현할때 유용하다.

ex) 전화번호의 지역번호, 등급계산 등

SELECT employee_id, first_name, phone_number,
    CASE SUBSTR(phone_number, 1, 3)
        WHEN '515' THEN '서울'
        WHEN '590' THEN '부산'
        WHEN '659' THEN '광주'
        WHEN '603' THEN '대전'
        ELSE '기타'
    END AS "지역"  
FROM employees;

SELECT employee_id, first_name, phone_number,
    CASE
        WHEN SUBSTR(phone_number, 1, 3) = '515' THEN '서울'
        WHEN SUBSTR(phone_number, 1, 3) = '590' THEN '부산' 
        WHEN SUBSTR(phone_number, 1, 3) = '659' THEN '광주'
        WHEN SUBSTR(phone_number, 1, 3) = '603' THEN '대전'
        ELSE '기타'
    END AS 지역
FROM employees;

SELECT employee_id, first_name, phone_number,
    DECODE( SUBSTR(phone_number, 1, 3),
            '515', '서울',
            '590', '부산',
            '659', '광주',
            '603', '대전', '기타') AS "지역"
FROM employees;