Data Base/Oracle SQL

231023 Oracle SQL 분석함수

잇꼬 2023. 10. 23. 17:33
728x90
반응형
SMALL

■ 분석함수 ex)차이검증

SELECT trunc(avg(salary))
FROM hr.employees;    

SELECT employee_id, salary, 6461
FROM hr.employees;


# 분석함수 문법 : AVG(컬럼명) OVER()

SELECT 
    employee_id ID, 
    salary 급여,  
    trunc(AVG(salary) OVER()) 전체평균, 
    salary - trunc ( AVG(salary) OVER() ) 차이
FROM hr.employees;


# 전체 합

SELECT 
    employee_id ID, 
    salary 급여, 
    trunc( SUM(salary) OVER() ) 전체합, 
    salary - trunc ( SUM(salary) OVER() ) 차이
FROM hr.employees;


# 최고 급여

SELECT 
    employee_id ID, 
    salary 급여, 
    MAX(salary) OVER() 최고급여, 
    salary - MAX(salary) OVER() 차이
FROM hr.employees;


# 최소 급여

SELECT 
    employee_id ID, 
    salary 급여, 
    MIN(salary) OVER() 최소급여, 
    salary - MIN(salary) OVER() 차이
FROM hr.employees;


# 중앙값

SELECT 
    employee_id ID, 
    salary 급여, 
    MEDIAN(salary) OVER() 중앙값, 
    salary - MEDIAN(salary) OVER() 차이
FROM hr.employees;


# 분산, 표준편차

SELECT 
    employee_id ID, 
    salary 급여, 
    VARIANCE(salary) OVER() 분산, 
    STDDEV(salary) OVER() 표준편차
FROM hr.employees;


# 종합

SELECT 
    employee_id , 
    salary , 
    TRUNC(SUM(salary) OVER()) 합,
    TRUNC(AVG(salary) OVER()) 평균, 
    MAX(salary) OVER() 최고값,
    MIN(salary) OVER() 최소값,
    MEDIAN(salary) OVER() 중앙값, 
    TRUNC( VARIANCE(salary) OVER()) 분산, 
    TRUNC( STDDEV(salary) OVER()) 표준편차
FROM hr.employees;


# 누적합 

SELECT 
    employee_id , 
    salary , 
    SUM(salary) OVER() 합,
    SUM(salary) OVER(ORDER BY employee_id) 누적합
FROM hr.employees;


# PARTITION BY 컬럼 : '컬럼'별

SELECT 
    employee_id , 
    salary , 
    department_id , 
    SUM(salary) OVER() 전체합,
    SUM(salary) OVER(partition by department_id) 부서별합 , 
    SUM(salary) OVER(partition by department_id ORDER BY employee_id) 부서별_누적합
FROM hr.employees;


# COUNT

SELECT 
    employee_id , 
    salary , 
    department_id , 
    count(*) OVER() 전체인원수,
    count(*) OVER(partition by department_id) 부서별_인원수 , 
    MAX(salary) OVER(partition by department_id) 부서별_최고급여 , 
    MIN(salary) OVER(partition by department_id) 부서별_최소급여 
FROM hr.employees;


[문제] 자신의 부서평균급여보다 많이 받는 사원의 employee_id, salary, department_name 를 출력하세요.
     - JOIN&서브쿼리, 스칼라서브쿼리, INLINE VIEW, 분석함수&CASE WHEN&INLINE VIEW

JOIN&서브쿼리 SELECT e.employee_id , e.salary, d.department_name 
FROM hr.employees e , hr.departments d
WHERE e.department_id = d.department_id 
AND e.salary > ( SELECT AVG(salary)
                                 FROM  hr.employees 
                                 WHERE  department_id = e.department_id);
SCALAR
SUBQUERY
(스칼라 
서브쿼리)		 SELECT 
             e.employee_id,
             e.salary,
             e.department_id,
             ( SELECT  department_name
              FROM  hr.departments
              WHERE  department_id = e.department_id ) dept_name
FROM  hr.employees e 
WHERE  e.salary > ( SELECT  AVG(salary)
                                        FROM  hr.employees 
                                      WHERE  department_id = e.department_id);
INLINE VIEW SELECT 
              e2.employee_id, 
              e2.salary, 
              ( SELECT  department_name 
                FROM  hr.departments 
                WHERE  department_id = e2.department_id ) dept_name
FROM  ( SELECT  department_id, avg(salary) avg_sal
                 FROM   hr.employees 
                 GROUP BY department_id) e1, hr.employees e2
WHERE  e1.department_id = e2.department_id 
AND e2.salary > e1.avg_sal;
분석함수
case		 SELECT 
              e.employee_id, 
              e.salary, 
              e.department_id, 
              ( SELECT  department_name 
                FROM  hr.departments 
                  WHERE   department_id = e.department_id ) dept_name
FROM  ( SELECT 
                            employee_id, 
                            salary, 
                           department_id, 
                           AVG(salary) OVER(PARTITION BY department_id) ,
                CASE WHEN salary > AVG(salary) OVER(PARTITION BY department_id) THEN 'x' END case_sal
                FROM  hr.employees ) e
WHERE   e.case_sal = 'x';

[순서]

1# JOIN & 서브쿼리 

SELECT e.employee_id , e.salary, d.department_name 
FROM hr.employees e , hr.departments d
WHERE e.department_id = d.department_id ;


1-2# JOIN & 서브쿼리

SELECT e.employee_id , e.salary, d.department_name 
FROM hr.employees e , hr.departments d
WHERE e.department_id = d.department_id 
AND e.salary > ( SELECT AVG(salary)
                 FROM hr.employees 
                 WHERE department_id = e.department_id);


2# SCALAR SUBQUERY(스칼라 서브쿼리)

SELECT e.employee_id , e.salary, e.department_id 
FROM hr.employees e 
WHERE e.salary > ( SELECT AVG(salary)
                 FROM hr.employees 
                 WHERE department_id = e.department_id);



2-1# SCALAR SUBQUERY(스칼라 서브쿼리)

SELECT e.employee_id, e.salary, e.department_id,(SELECT department_name
                                                 FROM hr.departments
                                                 WHERE department_id = e.department_id ) dept_name
FROM hr.employees e 
WHERE e.salary > ( SELECT AVG(salary)
                   FROM hr.employees 
                   WHERE department_id = e.department_id);


3# INLINE VIEW

SELECT e2.employee_id, e2.salary, e2.department_id 
FROM ( SELECT department_id, avg(salary) avg_sal
       FROM hr.employees 
       GROUP BY department_id) e1, hr.employees e2
WHERE e1.department_id = e2.department_id 
AND e2.salary > e1.avg_sal;


3-1# INLINE VIEW : 동일한 테이블을 2번이상 

SELECT e2.employee_id, e2.salary, ( SELECT department_name 
                                    FROM hr.departments 
                                    WHERE department_id = e2.department_id ) dept_name
FROM ( SELECT department_id, avg(salary) avg_sal
       FROM hr.employees 
       GROUP BY department_id) e1, hr.employees e2
WHERE e1.department_id = e2.department_id 
AND e2.salary > e1.avg_sal;


4# 분석함수

SELECT employee_id, salary, department_id
FROM hr.employees;


4-1# avg() over() 분석함수

SELECT 
    employee_id, 
    salary, 
    department_id, 
    avg(salary) over(partition by department_id) avg_sal
FROM hr.employees;


4-2# case 함수

SELECT 
    employee_id, 
    salary, 
    department_id, 
    avg(salary) over(partition by department_id) ,
    case when salary >  avg(salary) over(partition by department_id) then 'X' end case_sal
FROM hr.employees;


4-3# inline view

SELECT e.employee_id, e.salary, e.department_id, ( SELECT department_name FROM hr.departments WHERE department_id = e.department_id )dept_name
FROM ( SELECT 
            employee_id, 
            salary, 
            department_id, 
            avg(salary) over(partition by department_id) ,
            case when salary > avg(salary) over(partition by department_id) then 'x' end case_sal
       FROM hr.employees ) e
where case_sal = 'x';


4-4# 분석함수 & inline view & case 함수 

SELECT e.employee_id, e.salary, e.department_id, ( SELECT department_name 
                                                   FROM hr.departments 
                                                   WHERE department_id = e.department_id ) dept_name
FROM ( SELECT 
            employee_id, 
            salary, 
            department_id, 
            AVG(salary) OVER(PARTITION BY department_id) ,
            CASE WHEN salary > AVG(salary) OVER(PARTITION BY department_id) THEN 'x' END case_sal
       FROM hr.employees ) e
WHERE e.case_sal = 'x';


5# with 문 이용

WITH 부서별평균급여 AS (
    SELECT d.department_id, AVG(e.salary) AS 평균급여
    FROM hr.departments d, hr.employees e 
    WHERE d.department_id = e.department_id
    GROUP BY d.department_id
)
SELECT e.employee_id, e.salary, d.department_name
FROM hr.employees e
JOIN hr.departments d ON e.department_id = d.department_id
JOIN 부서별평균급여 a ON e.department_id = a.department_id
WHERE e.salary > a.평균급여
ORDER BY e.employee_id asc;
728x90
반응형
LIST
저작자표시 비영리 동일조건

'Data Base > Oracle SQL' 카테고리의 다른 글

231024 Oracle SQL SAVEPOINT  (0) 2023.10.24
231023 Oracle SQL TOP_N 분석 rownum, rank(), dense_rank()  (0) 2023.10.23
231023 Oracle SQL 날짜타입  (1) 2023.10.23
231023 Oracle SQL SYNONYM(동의어)  (0) 2023.10.23
231023 Oracle SQL SEQUENCE  (1) 2023.10.23

'Data Base/Oracle SQL'의 다른글

  • 현재글231023 Oracle SQL 분석함수

관련글

티스토리툴바