24일차//[Oracle] SUB QUERY , CASE 문 (3)

ORACLE 오라클 CASE문 사용법 CASE WHEN THEN END

--특수 쿼리
-- CASE == switch
SELECT employee_id, first_name, phone_number,
    CASE SUBSTR(phone_number,1,3)
    WHEN '515' THEN '서울'
    WHEN '590' THEN '부산'
    WHEN '650' 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) ='650' THEN '광주'
    ELSE '기타'
    END AS 지역
FROM employees;

--DECODE
SELECT employee_id, first_name, phone_number,
    DECODE(SUBSTR(phone_number,1,3),
    '515','서울',
    '590','광주',
    '650','광주',
    '기타')  AS 지역
FROM employees;

/*
집합
    합집합: UNION - FULLOUTER
    교집합: INTERSECT - INNER
    차집합: MINUS
*/

--UNION
SELECT job_id
FROM employees
WHERE job_id IN('AD_VP', 'FI_ACCOUNT')
UNION ALL
SELECT job_id
FROM jobs
WHERE job_id IN('AD_VP', 'FI_ACCOUNT');

--INNTERSECT
--매니저에 대한 정보만 추린다(교집합으로)
--sorting  이 자동적으로 된다
-- 중복이 없다
SELECT employee_id
FROM employees
INTERSECT 
SELECT manager_id
FROM employees;
--inner join 으로 해본것, 위와동일
SELECT DISTINCT b.employee_id
FROM employees a, employees b
WHERE a.manager_id = b.manager_id ;

--MINUS

SELECT employee_id
FROM employees
MINUS
SELECT manager_id
FROM employees;

-- join
SELECT *
FROM employees a, departments b
WHERE a.department_id = b.department_id(+) 
    and a.manager_id IS NOT NULL;

--OVER() 함수
--select절에서만 사용
--GROUP BY 를 보강하기 위해 나온 함슈
--select 절에서 GrOUP만들지 않고 사용가능

SELECT count(*), department_id --에러
FROM employees;


SELECT count(*), department_id --에러
FROM employees
GROUP BY department_id;


SELECT  department_id, COUNT(*)OVER() --에러
FROM employees;

SELECT first_name, department_id, COUNT(DISTINCT department_id)OVER()
FROM employees;

--PATITION BY == SELECT  안의 GROUP BY
SELECT department_name,
    count(*)OVER(PARTITION BY department_id)
FROM departments;

SELECT department_id, first_name, salary
    ,COUNT(*)OVER(PARTITION BY department_id)
FROM employees;

---COUNT(*) OVER() :전체 함수를 반환
---COUNT(*) OVER(PARTITION BY 컬럼) : 컬럼을 그룹으로 나누어 ROW의 수를 반환

-- 분석 함수
/*
순위함수
RANK         1 2 3 3 5 6 
DENSE_RANK() 1 2 3 3 4 5
ROW_NUMBER() 1 2 3 4 5 6 
ROWNUM

필요한 이유: 순서를 번호로 설정하기위해

*/

SELECT employee_id, first_name
FROM employees
WHERE employee_id >= 100 AND employee_id <=109;

SELECT employee_id, first_name, 
    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;

SELECT ROWNUM ,employee_id, first_name
FROM employees
WHERE ROWNUM <=10;


SELECT ROWNUM ,employee_id, first_name
FROM employees
WHERE ROWNUM >10 AND ROWNUM <=20;



--월급의 랭킹 1~10
--1.급여의순서
--2.번호설정
--3.범위지정
SELECT RNUM, employee_id, first_name, salary   --3.범위지정
FROM
    (SELECT ROWNUM AS RNUM, employee_id, first_name, salary   --2.번호설정
FROM
(       SELECT employee_id, first_name, salary   --1.급여의순서
        FROM employees
        ORDER BY salary DESC)
)
WHERE RNUM > 0 AND RNUM <=10;

/*
SELECT 절
        SELECT  컬럼, 표준함수, 그룹함수, 서브쿼리, OVER() PARTITON BY
        FROM 테이블, 서브쿼리
        (WHERE) 조건 IN AND OR ANY LIKE > <  = >=  <= != 서브쿼리
        (GROUP BY) 컬럼
        (HAVING) 그룹핑 조건
        (ORDER BY)  컬럼 ASC, DESC
        (START BY) 계층형
        (CONNECT BY) 연결형

        JOIN

        INNER
        FULL OUTER
        CROSS
        OUTER
        SELF
*/

https://lnsideout.tistory.com/entry/ORACLE-%EC%98%A4%EB%9D%BC%ED%81%B4-CASE-WHEN-THEN-%EC%A1%B0%EA%B1%B4%EB%AC%B8-%EC%82%AC%EC%9A%A9%EB%B2%95

[ORACLE] 오라클 CASE WHEN THEN 조건문 사용법

 

ㄴ 자세히 참고