choice blog

OVER() : GROUP BY 하지않아도 그룹함수 사용가능

형식

 SELECT 컬럼명, 그룹함수OVER() FROM 테이블명;

참고

GROUP BY 를 보강하기 위해서 나온 함수
SELECT 절에서만 사용 가능
그룹함수를 사용하려면 GROUP BY를 사용해야 하지만, OVER()를 사용하면 GROUP BY를 하지 않아도 된다.
PARTITION BY(그룹핑) 한 데이터를 정렬해서 뽑는다.

예시

-- hr 스키마

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

PARTITION BY : 특정 컬럼 기준으로 그룹을 만든다.  ★★★★★

형식

 SELECT 컬럼명, 그룹함수() OVER ( PARTITION BY 그룹컬럼명) FROM 테이블명;

참고

SELECT 절 안에 GROUP BY

예시

SELECT department_id, first_name, salary,
        -- department_id 묶어서 카운트로 씌워라
        COUNT(*)OVER(PARTITION BY department_id) 
FROM employees;

분석함수(순위함수)

특정 집합 내에서 결과 건수의 변화없이 해당 집합안에서 합계 및 카운트 등을 계산할 수 있는 함수이다.

RANK() :  중복 순번 후 순위만큼 이동한 순번 구하기

형식 

1. RANK()OVER(ORDER BY 정렬컬럼명) AS RANK 

2. RANK() OVER (PARTITION BY 그룹컬럼명 ORDER BY 정렬컬럼명) AS RANK

참고

1형식은 정렬컬럼명으로 정렬해 같은 값이면 중복순위를 부여하고 그 이후는 해당 순위만큼 이동한 순위를 구한다.

2형식은 그룹컬럼으로 그룹을 만들고 정렬컬럼명으로 정렬해 같은 값이면 중복순위를 부여하고 그 이후는 해당 순위만큼 이동한 순위를 구한다.

예시 : 1 2 3 3 5 6

-- 1형식
SELECT employee_id, first_name, salary,department_id, 
       RANK()OVER(ORDER BY salary DESC) AS RANK
FROM employees;


-- 2형식
SELECT employee_id, first_name, salary,department_id, 
       RANK()OVER(PARTITION BY department_id ORDER BY salary DESC) AS RANK
FROM employees;

DENSE_RANK() : 중복 순번 후 순차 순번 구하기

형식

 1. DENSE_RANK()OVER(ORDER BY 정렬컬럼명) AS RANK

 2. DENSE_RANK() OVER (PARTITION BY 그룹컬럼명 ORDER BY 정렬컬럼명) AS RANK

참고

1형식은 정렬컬럼명으로 정렬해 같은 값이면 중복순위를 부여하고 그 이후는 순차적인 순위를 구한다.

2형식은 그룹컬럼으로 그룹을 만들고 정렬컬럼명으로 정렬해 같은 값이면 중복순위를 부여하고 그 이후는 순차적인 순위를 구한다.

예시 : 1 2 3 3 4 5

-- 1형식
SELECT employee_id, first_name, salary,department_id, 
       DENSE_RANK()OVER(ORDER BY salary DESC) AS RANK
FROM employees;


-- 2형식
SELECT employee_id, first_name, salary,department_id, 
       DENSE_RANK()OVER(PARTITION BY department_id ORDER BY salary DESC) AS RANK
FROM employees;

ROW_NUMBER() : 중복없는 순번 구하기

형식

 1. ROW_NUMBER()OVER(ORDER BY 정렬컬럼명) AS RANK

 2. ROW_NUMBER() OVER (PARTITION BY 그룹컬럼명 ORDER BY 정렬컬럼명) AS RANK

참고

1형식은 정렬컬럼명으로 정렬해 중복 없는 순번을 구한다.

2형식은 그룹컬럼으로 그룹을 만들고 정렬컬럼명으로 정렬해 중복 없는 순번을 구한다.

예시 : 1 2 3 4 5 6

-- 1형식
SELECT employee_id, first_name, salary,department_id, 
       ROW_NUMBER()OVER(ORDER BY salary DESC) AS RANK
FROM employees;


-- 2형식
SELECT employee_id, first_name, salary,department_id, 
       ROW_NUMBER()OVER(PARTITION BY department_id ORDER BY salary DESC) AS RANK
FROM employees;

ROWNUM :  조회한 행 번호 구하기  ★★★★★

형식

 SELECT ROWNUM, 컬럼명 FROM 테이블명; 

참고

게시판에서 글들을 한페이지에 글을 1~20번까지 불러올 때 사용한다.

테이블 전체 컬럼을 출력하고 싶을 땐 FROM 테이블명에 별명을 사용해주고 SELECT 절에 별명.*해주면 된다.

예시 : 1 2 3 4 5 6

-- ROWNUM의 1~10까지 출력하라

SELECT ROWNUM, employee_id, first_name
FROM employees
WHERE ROWNUM <= 10;               		-- ROWNUM이 10보다크고 20보다 작은경우를 구해보자.
-- WHERE ROWNUM > 10 AND ROWNUM <= 20;  -- 실행이 되지 않는다.


-- 급여의 순위를 구한다

-- 1. 정렬        많음 -> 적음
-- 2. 번호 지정    1 2 3 4
-- 3. 범위 지정    1~10 11~20

SELECT RNUM, employee_id, first_name, salary
FROM
    (SELECT ROWNUM AS RNUM, employee_id, first_name, salary
    FROM
        (SELECT employee_id, first_name, salary  -- 급여순위
        FROM employees
        ORDER BY salary DESC)
    )
WHERE RNUM >= 1 AND RNUM <= 10;