OVER, PARTITION BY

--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;
 
--PARTITION 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의 수를 반환