OVER(), PARTITION BY

 

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

 

 

--OVER() 함수

SELECT 절에서만 사용

GROUP BY를 보강하기 위해 사용

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

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

 

 

--PARTITION BY == GROUP BY

파티션으로 묶어 그룹함수로 확인할 수 있게 만듬

 

SELECT department_id, first_name, salary,
    COUNT(*)OVER(PARTITION BY department_id)    -- 파티션으로 묶어 인원수가 출력
FROM employees;