분석함수- ORDER BY, PARTITION BY,GROUP BY / IF문(DECODE) / 정렬 OVER

 

부서별 평균 구하기

SELECT * FROM PERSONNEL ORDER BY DNO;

 

 

 

SELECT DISTINCT DNO, TRUNC(AVG(PAY) OVER (PARTITION BY DNO))  
FROM PERSONNEL;  

-- OVER는 정렬하고 그 다음에 어떤 작업을 하라는 뜻 -> PAY의 평균을 구하고, DNO는 하나씩만 보여라
-- 부서번호는 하나씩만 보여지게 DISTINCT 씀

 

 

 

월급 전체 등수

SELECT PNAME, PAY, RANK() OVER(ORDER BY PAY DESC) AS RANK
FROM PERSONNEL; 

-- DESC 한 결과 위에 RANK를 붙여라 (PAY를 찍은 다음에 RANK인 1,2,3,4로 덮어씌운거라고 생각하면 됨)

 

 

 

DECODE(IF문)
--각 사원의 급여를 부서가 10인 경우 10%, 부서가 20인 경우 20% 나머지는 30% 더해서 출력

-- 마치 if~else문 생각하기

SELECT PNAME,BONUS,DNO,PAY, 
DECODE (DNO,10,PAY*1.1,20,PAY*1.2,PAY*1.3) "인상분"  
FROM PERSONNEL; 

 

 

 

각 부서의 평균급여가 전체 평균 급여보다 크면 'GOOD', 작으면 'POOR'를 출력

(전체평균급여-2972.5)

SELECT DNO,AVG(PAY) 부서평균,
DECODE(SIGN(AVG(PAY)-
(SELECT AVG(PAY) FROM PERSONNEL)),1,'GOOD','POOR') 상태 
FROM PERSONNEL
GROUP BY DNO;

※ SIGN함수는 0보다 크면 1, 작으면 -1, 0이면 0을 반환함

 

 

 

부서별 평균 급여 구하기 (~별 나오면 무조건 GROUD BY)

GROUP BY (컬럼명) : 컬럼으로 그룹을 지어라

 SELECT DNO, AVG(PAY) FROM PERSONNEL
 GROUP BY DNO;

 

 

※ ORDER BY만 별칭으로 쓸 수 있고, GROUP BY 는 뒤에 무조건 컬럼명으로 써줘야 함

 

 

 

 

직업별 나이 평균 (OVER)

SELECT * FROM CUSTOM ORDER BY JOB;

 

 

SELECT DISTINCT JOB, TRUNC(AVG(AGE) OVER (ORDER BY JOB)) 평균
FROM CUSTOM;

 

주의 - 다음과 같이 쓰면 틀림!! ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

SELECT DISTINCT JOB, AVG(AGE) 평균 
FROM CUSTOM 
ORDER BY JOB;

 

왜냐하면, SELECT AVG(AGE) FROM CUSTOM ORDER BY JOB; 로 구했을 때 평균은 다음과 같이 단일 결과가 나온다.

 

SELECT DISTINCT JOB FROM CUSTOM ORDER BY JOB; 으로 구했을 대는 다음과 같이 여러 결과가 나온다.

 

그러므로 두 컬럼을 합쳐버리면 오류가 나는 것!

 

 

 

 

성별에 따른 나이의 평균  (OVER)

SELECT DISTINCT SEX,TRUNC(AVG(AGE) OVER (ORDER BY SEX)) 평균
FROM CUSTOM;

 

 

 

 

직업별 평균 나이,인원수 구하기

SELECT JOB,ROUND(AVG(AGE)) 평균나이,COUNT(*) 인원수 
FROM CUSTOM
GROUP BY JOB;

 

※ GROUP BY 뒤에 있는 컬럼은 반드시 SELECT에도 써줘야 함

 

 

 

졸업자격별 50이 넘는 인원수만 출력하기

- HAVING 인원수 > 50;  -> 이렇게 하면 에러 난다. HAVING 뒤에는 별칭 오면 안 됨. 
- WHERE - 순수하게 저장돼있는 데이터를 꺼낼 때  /  GROUP BY - 변형해서 데이터를 가져올 때

SELECT SCHOL, COUNT(*) 인원수 FROM CUSTOM
GROUP BY SCHOL
HAVING COUNT(*) > 50;