[SQL] 함수/집계함수(Having, Groupby)

▶ 함수

1. 단일행 함수 : 각 행별로 처리하는 함수 
    - use in select and where 
2. 다중행 함수 : 전달된 컬럼들의 값을 묶어서 처리 
    - 집계함수(평균,합계,최대,최소값,분산,표준편차), 그룹함수 
    - select절, having절 (where절에는 사용 불가, use 'sub query'

 

▶ 문자열관련 함수 

• UPPER()/ LOWER() : 대문자/소문자 로 변환 
  
• INITCAP(): 단어 첫글자만 대문자 나머진 소문자로 변환 
  
• LENGTH() : 글자수 조회 
  
• LPAD(값, 크기, 채울값) : "값"을 지정한 "크기"의 고정길이 문자열로 만들고 모자라는 것은 왼쪽부터 "채울값"으로 채운다. 
  
• RPAD(값, 크기, 채울값) : "값"을 지정한 "크기"의 고정길이 문자열로 만들고 모자라는 것은 오른쪽부터 "채울값"으로 채운다. 
  
• SUBSTR(값, 시작index, 글자수) - "값"에서 "시작index"번째 글자부터 지정한 "글자수" 만큼의 문자열을 추출. 글자수 생략시 끝까지.
•  substr('123456789', 2, 5) : 2번째 글자(index는 1부터 시작), 5: 글자수 ->2번째 글자부터 5개를 뽑아내라 
  
• REPLACE(값, 찾을문자열, 변경할문자열) - "값"에서 "찾을문자열"을 "변경할문자열"로 바꾼다.  
• LTRIM(값): 왼공백 제거 
  
• RTRIM(값): 오른공백 제거 
  
• TRIM(값): 양쪽 공백 제거

 

▶ 함수안에 함수 호출 -> 안쪽 함수를 처리한 결과를 바깥쪽 함수에 전달, 

select length(trim('    abc     ')) 
from dual;

 

▶ 숫자관련 함수 
- round(값, 자릿수) : 자릿수에서 반올림 (양수 - 실수부, 음수 - 정수부) 
- trunc(값, 자릿수) : 자릿수에서 절삭(양수 - 실수부, 음수 - 정수부) 
- ceil(값) : 올림 
- floor(값) : 내림 
- mod(나뉘는수, 나누는수) : 나눗셈의 나머지 연산 
cf) ceil/floor() : 결과가 정수로 나옴

 

▶ 날짜관련 계산 및 함수 
- Date +- 정수 : 날짜 계산. 
- months_between(d1, d2) -경과한 개월수(d1이 최근, d2가 과거) 
- add_months(d1, 정수) - 정수개월 지난 날짜. 마지막 날짜의 1개월 후는 달의 마지막 날이 된다.  
- next_day(d1, '요일') - d1에서 첫번째 지정한 요일의 날짜. 요일은 한글(locale)로 지정한다. 
- last_day(d) - d 달의 마지막날. 
- extract(year|month|day from date) - date에서 year/month/day만 추출

 

▶ 변환 함수 
- to_char() : 숫자형, 날짜형을 문자형으로 변환 ->특정형식(2000000)의 문자열로 만들기 위해 
- to_number() : 문자형을 숫자형으로 변환 -> 문자열로 저장된 값을 실제타입에 맞게 처리하기 위해(함수, 연산을 위해) 
- to_date() : 문자형을 날짜형으로 변환 
- 호출구문 
    -함수(변환할값, 형식) 
    -형식 : 변환할 값이 어떤 형식으로 되어 있는지를 지정. 

 

- 형식문자  
     - 숫자 : 0, 9 -> 자리수 지정 
        , . , ',', 'L', '$' 
     - 일시 : yyyy, mm, dd, hh24, mi, ss, day(요일), am 또는 pm )

 

- L : local currency

select to_char(3000,'L9,999') 
from dual;

 

▶ 문자열 -> number

ex.) 

select to_number('2,000,000','9,999,999') + 100000 
from dual;

 

▶ 자동형변환

- '10'을 10(number)로 변환 후 계산 = > 자동 형변환 

select '10'+20 from dual;  -- 알아서 오라클에서 형식을 바꿔줌

 

▶ 글자 붙이기 "" 이용

select sysdate, to_char(sysdate, 'yyyy"년"') from dual;

 

▶ null 관련 함수  
- NVL(expr) - 값이 없으면 지정한 값을 보여줌 
- NVL2(expr, nn, null) - expr이 null이 아니면 nn, null이면 세번째 
- nullif(ex1, ex2) 둘이 같으면 null, 다르면 ex1

 

 

▶ DECODE함수와 CASE 문 

decode(컬럼, [비교값, 출력값, ...] , else출력)  

- case문 동등비교 

case 컬럼 when 비교값 then 출력값
              [when 비교값 then 출력값]
              [else 출력값]
              end

               
- case문 조건문 

case when 조건 then 출력값
       [when 조건 then 출력값]
       [else 출력값]
       end

 

- decode는 오라클에서만 지원

 

EX)

select decode(dept_name, null, 'no department'
                             , 'IT', 'IT room'
                             , 'Finance', 'Finance department'
                             , dept_name)
            , dept_name
from emp;

select case dept_name
            when 'IT' then 'Computer Room'
            when 'Finance' then 'Finance department'
            else nvl(dept_name, 'no department') end as "DEPT_NAME"
    ,dept_name
From emp;
                 

 

 

▶ 집계함수, 그룹함수, 다중행 함수 => select/having절에서 사용가능 but where절에서는 사용불가
- 인수(argument)는 컬럼.
  - sum(): 전체합계
  - avg(): 평균
  - min(): 최소값
  - max(): 최대값
  - stddev(): 표준편차
  - variance(): 분산
  - count(): 개수
        - 인수: 
            - 컬럼명: null을 제외한 개수
            -  *: 총 행수(null을 포함)

- count(*) 를 제외하고 모든 집계함수는 null은 빼고 계산한다.
- sum, avg, stddev, variance: number 타입에만 사용가능.
- min, max, count :  모든 타입에 다 사용가능.

 

▶ group by 절
- 특정 컬럼(들)의 값별로 나눠 집계할 때 나누는 기준컬럼을 지정하는 구문.
- 예) 업무별 급여평균. 부서-업무별 급여 합계. 성별 나이평균

group by 컬럼명 [, 컬럼명] 

 

- 컬럼: 분류형(범주형, 명목형) - 부서별 급여 평균, 성별 급여 합계
- select의 where 절 다음에 기술한다.
- select 절에는 group by 에서 선언한 컬럼들만 집계함수와 같이 올 수 있다

 

EX)

-- 업무(job)별 급여의 총합계, 평균, 최소값, 최대값, 표준편차, 분산, 직원수를 조회
select job, sum(salary)
       ,avg(salary)
       ,min(salary)
       ,max(salary)
       ,round(stddev(salary),2)
       ,round(variance(salary),2)
       ,count(*)
from emp
group by job;

 

▶ having 절
- 집계결과에 대한 행 제약 조건
- group by 다음 order by 전에 온다.

having 제약조건  --연산자는 where절의 연산자를 사용한다. 피연산자는 집계함수(의 결과)

EX)

-- 직원수가 10 이상인 부서의 부서명(dept_name)과 직원수를 조회
select dept_name, count(*)
from emp
group by dept_name
having count(*)>10;

 

▶  rollup : group by의 확장.
  - 두개 이상의 컬럼을 group by로 묶은 경우 누적집계(중간집계나 총집계)를 부분 집계에 추가해서 조회한다.

group by rollup(컬럼명 [,컬럼명,..]) 

EX)

select job
    ,round(avg(salary),2) "평균급여"
from emp
group by rollup(job); -- 전체직원평균 추가

▶  grouping(), grouping_id()->결론적으로는 grouping-ID를 씀 
  - rollup 이용한 집계시 컬럼이 각 행의 집계에 참여했는지 여부를 반환하는 함수.
  - case/decode를 이용해 레이블을 붙여 가독성을 높일 수 있다.
  - 반환값
- 0 : 참여한 경우
- 1 : 참여 안한 경우.
 

▶  grouping() 함수 

grouping(groupby컬럼) 

 

 - select 절에 사용되며 rollup이나 cube와 함께 사용해야 한다.
 - group by의 컬럼이 집계함수의 집계에 참여했는지 여부를 반환
 - 반환값 0 : 참여함(부분집계함수 결과), 반환값 1: 참여 안함(누적집계의 결과)
 - 누적 집계인지 부분집계의 결과인지를 알려주는 알 수 있다. 


▶  grouping_id

grouping_id(groupby 컬럼, ..) 

 

  - 전달한 컬럼이 집계에 사용되었는지 여부 2진수(0: 참여 안함, 1: 참여함)로 반환 한뒤 10진수로 변환해서 반환한다.
 

- cube --> group by 했을때 모든 경우의수 다나옴

 

EX)

-- EMP 테이블에서 업무(JOB) 별 급여(salary)의 평균과 평균의 총계도 같이나오도록 조회.
-- 업무 컬럼에  소계나 총계이면 '총평균'을  일반 집계이면 업무(job)을 출력
select job
       ,grouping(job)
       , decode(grouping(job),0,job,1,'총평균')
       ,round(avg(salary),2) "평균급여"
from emp
group by rollup(job);

select job
       ,grouping(job)
       , decode(grouping_id(job),0,job,1,'총평균')
       ,round(avg(salary),2) "평균급여"
from emp
group by rollup(job);

 

 

▶실행순서
5. select
1. from
2. where
3. group by
4. having
6. order by