데이터베이스 3일차_다중행함수(oracle, sql)

## SQL- 3일차 

## 다중행 함수
=> 그룹함수, 통계함수
=> 여러행의 값 => 처리 => 하나의 값을 반환
=> sum, avg, count, max, min.....
=> ** null 값은 통계에서 제외한다.

## count 함수
- * : row 의 수를 카운트
- 컬럼명 : count(COMMISSION_PCT)

select count(*) as 총사원수,  
         count(COMMISSION_PCT) as "받는 사원수",
         count(*) -  count(COMMISSION_PCT) as "받지 않는 사원수"
from  emp ;

최고급여, 최저급여

select max( salary ) , min ( salary )
from  emp ;

월급을 15000 이상 받는 사원의 수?

select  count(*) as "고액연봉자의 수"
from   emp 
where salary >= 15000 ;


select  count(*) as "수당을 받는 사원수"
from   emp 
where COMMISSION_PCT is not null ;

그룹 
=> group by 컬럼명 , 컬럼명 ....
=> select 절에 그룹함수 이외의 컬럼 또는 수식은 group by 절에 기술할 것!!


부서별 최고급여, 최저급여

select DEPARTMENT_ID , max( salary ) , min( salary )
from  emp  
group by DEPARTMENT_ID 
order by DEPARTMENT_ID  ; 

년도별 입사인원수 

select   to_char( hire_date, 'yyyy')  as "입사년도" , count(*)  as "입사인원수"
from  emp 
group by to_char( hire_date, 'yyyy')
order by 1 ;

부서별, 직군

select DEPARTMENT_ID , job_id,  max( salary ) , min( salary )
from  emp  
group by DEPARTMENT_ID , job_id
order by 1,2  ;

------------------------------------------------------------------------

부서별 평균급여

select DEPARTMENT_ID , round( avg(salary), 0 )
from  emp  
group by DEPARTMENT_ID 
order by DEPARTMENT_ID  ; 

## having 절 
=> 그룹을 제한
=> 원하는 그룹만 표시
=> where 절 과 유사 
=> having 조건식 and 조건식 or 조건식
=> group by 절이 있는 경우만 사용가능 !!
=> select 절의 컬럼별칭을 사용할 수 없다!!

부서별 평균급여
단, 평균급여 8000 이상인 부서만 출력 

select DEPARTMENT_ID , round( avg(salary), 0 ) as 부서평균급여
from  emp  
group by DEPARTMENT_ID 
having  round( avg(salary), 0 ) >= 8000
order by 부서평균급여   ; 


구매 ( 구매번호, 구매자, 상품번호, 상품갯수, 구매금액합계, 구매날짜 ) 
단, 2021년 자료만 구매에 있다고 가정 

1. 구매금액의 합계가 500000 이상은 구매자 명단 

select 구매자, sum ( 구매금액합계 )  as 총구매금액
from   구매
group by  구매자 
having sum ( 구매금액합계 )  >= 500000
order by 2 desc ; 


2. 지난달 100개 이상 팔린 상품번호 

select 상품번호 , sum ( 상품갯수) as 총팔린갯수
from  구매
where to_char( 구매날짜 , 'mm' ) = to_char( sysdate, 'mm' ) - 1 
group by 상품번호
having sum ( 상품갯수)  >= 100
order by 2 desc ; 

고객 ( 고객번호, 고객명, 생일, 성별,.... )


1. 고객 연령별 인원수 

힌트 ) 나이를 우선 구한다 -> 연령대 구한다 -> 연령별 인원수 구한다

나이 : to_char(sysdate, 'yyyy') - to_char( 생일, 'yyyy') + 1  => 27
연령대 : 27 -> 20  : trunc ( to_char(sysdate, 'yyyy') - to_char( 생일, 'yyyy') + 1, -1 ) => 20

select trunc ( to_char(sysdate, 'yyyy') - to_char( 생일, 'yyyy') + 1, -1 ) as 연령대, count(* ) as 인원수
from 고객
group by trunc ( to_char(sysdate, 'yyyy') - to_char( 생일, 'yyyy') + 1, -1 ) 
order by  1 ;

연령대 인원수, 비율 

select  trunc ( to_char(sysdate, 'yyyy') - to_char( 생일, 'yyyy') + 1, -1 ) as 연령대, 
          count(* ) as 인원수,
          trunc ( count(* )  /  ( select count(*) from 고객 ) , 1) as 비율
from 고객
group by trunc ( to_char(sysdate, 'yyyy') - to_char( 생일, 'yyyy') + 1, -1 ) 
order by  1 ;

----------------------------------------

CREATE TABLE EMP1
AS
select last_name, HIRE_DATE  from emp 
UNION
select last_name, add_months(hire_date, -300)  from emp 
UNION
select last_name, add_months(hire_date, -500)  from emp 
UNION
select last_name, add_months(hire_date, -600)  from emp 
UNION
select last_name, add_months(hire_date, -200)  from emp
UNION
select last_name, HIRE_DATE  from emp ;


SELECT * FROM EMP1;


select  trunc ( to_char(sysdate, 'yyyy') - to_char(  HIRE_DATE, 'yyyy') + 1, -1 ) as 연령대, 
          count(* ) as 인원수,
          trunc ( count(* )  /  ( select count(*) from EMP1 ) , 2) as 비율
from EMP1
group by trunc ( to_char(sysdate, 'yyyy') - to_char( HIRE_DATE, 'yyyy') + 1, -1 ) 
order by  1 ;


create table emp1
as
select * from ad30.emp1 ; 


이름, 나이, 연령대 

select  last_name, hire_date,
          to_char(sysdate, 'yyyy') - to_char(  HIRE_DATE, 'yyyy') + 1  as 나이,
          trunc ( to_char(sysdate, 'yyyy') - to_char(  HIRE_DATE, 'yyyy') + 1, -1 ) as 연령대
from emp1 ;


select  trunc ( to_char(sysdate, 'yyyy') - to_char(  HIRE_DATE, 'yyyy') + 1, -1 ) as 연령대, 
          count(* ) as 인원수,
          trunc ( count(* )  /  ( select count(*) from EMP1 ) , 2) as 비율
from EMP1
group by trunc ( to_char(sysdate, 'yyyy') - to_char( HIRE_DATE, 'yyyy') + 1, -1 ) 
order by  1 ;


select  trunc ( to_char(sysdate, 'yyyy') - to_char(  HIRE_DATE, 'yyyy') + 1, -1 ) as 연령대, 
          count(* ) as 인원수,
          to_char ( trunc ( count(* )  /  ( select count(*) from EMP1 ) * 100 , 2) , '99.00' ) || '%' as 비율
from EMP1
group by trunc ( to_char(sysdate, 'yyyy') - to_char( HIRE_DATE, 'yyyy') + 1, -1 ) 
order by  1 ;