[Oracle]그룹함수_1104

예시

  문제 : 연도별 분기별 입사자수를 집계하세요

    select to_char(hiredate, 'YYYY') as year, to_char(hiredate, 'Q') as quarter
    from emp;

    select year, quarter, quarter, quarter, quarter
    from (select to_char(hiredate, 'YYYY') as year, to_char(hiredate, 'Q') as quarter
          from emp);

    select year, decode(quarter, 1, quarter) as q1, quarter, quarter, quarter
    from (select to_char(hiredate, 'YYYY') as year, to_char(hiredate, 'Q') as quarter
          from emp);

    select year,
           decode(quarter, 1, quarter) as q1,
           decode(quarter, 2, quarter) as q2,
           decode(quarter, 3, quarter) as q3,
           decode(quarter, 4, quarter) as q4
    from (select to_char(hiredate, 'YYYY') as year, to_char(hiredate, 'Q') as quarter
          from emp);

    select count(decode(quarter, 1, quarter)) as q1,
           count(decode(quarter, 2, quarter)) as q2,
           count(decode(quarter, 3, quarter)) as q3,
           count(decode(quarter, 4, quarter)) as q4
    from (select to_char(hiredate, 'YYYY') as year, to_char(hiredate, 'Q') as quarter
          from emp);

    select year,
           count(decode(quarter, 1, quarter)) as q1,
           count(decode(quarter, 2, quarter)) as q2,
           count(decode(quarter, 3, quarter)) as q3,
           count(decode(quarter, 4, quarter)) as q4
    from (select to_char(hiredate, 'YYYY') as year, to_char(hiredate, 'Q') as quarter
          from emp)
    group by year
    order by year desc;

 

select 에서 복수행함수로 감싼 칼럼이 있는데 그거 외의 모든 칼럼은 group by에 반드시 명시되어야 한다.

 

---

그룹함수

 

1. sum : 합계
2. avg : 평균
3. max : 최대값
4. min : 최소값
5. count : select 되어서 나온 결과물의 행의 개수
6. variance : 분산

-- 분산의 제곱근이 표준편차이다. (평균에서 떨어진 정도)
7. stddev : 표준편차

-- 표준편차의 제곱승이 분산이다. (평균과의 차액)

 

 

group by절

 

인자별로 그룹을 지어준다.

 

 

having 절

group by 절과 함께 사용하는 것으로써 그룹함수에 대한 조건을 사용시 쓰인다.

 

 

Roll UP 함수

roll up은 뭐다? extension이다!!

 

ROLLUP함수를 쓰면

 

첫번째 인자(상품 ID)별 두번째 인자(월)

+

첫번째 인자(상품 ID)별 두번째 인자(월)의 소계(SUBTOTAL)

+

총계(GRAND TOTAL)

 

의 결과가 나온다.

=> 따라서 ROLLUP함수는 인수의 순서에도 영향을 받게 된다.

 

컬럼 나열 순서가 중요하며

컬럼 갯수가 n개면 결과는 n+1가지 경우가 리턴됨

 

예)

depetno, job

 

depetno      depetno, job               ()

부서버노      부서번호,잡            전체

 

 

CUBE 함수

 

CUBE 함수는 그룹핑 컬럼이 가질 수 있는 모든 경우의 수에 대하여 소계(SUBTOTAL), 총계(GRAND TOTAL)를 생성한다. 따라서 ROLLUP 함수와는 다르게 인자의 순서가 달라도 결과는 같다.

위와 다르게 단순한 월별 소계(SUBTOTAL)도 생성되었으며, 그룹핑 컬럼의 개수를 N이라고 한다면

2의 N승의 소계(SUBTOTAL)을 생성한다.

 

 

CUBE   2의 n개가 나옴

=> 모든 경우의 수

depetno              job                depetno, job                   ()

 

 

 

 

 

  - ROLLUP vs CUBE

    -> ROLLUP ~ 컬럼 나열 순서가 중요함
              ~ 컬럼 갯수가 n개면 결과는 n+1가지 경우가 리턴됨
    -> CUBE   ~ 컬럼 나열 순서가 중요하지 않음
              ~ 컬럼 갯수가 n개면 결과는 2^n가지 경우가 리턴됨

 

 

GROUPING SETS

 

첫번째 인자(상품ID)별 소계(SUBTOTAL), 두번째 인자(월)별 소계(SUBTOTAL)이 나오는 것을 확인할 수 있다.

ROLLUP과 CUBE와 달리 계층 구조가 나타나지 않으며 따라서 인자의 순서가 달라도 결과는 똑같다.

 

 

GROUPING 함수

 

GROUPING 함수는 직접 그룹별 집계를 구하지는 않지만 앞서 말한 ROLLUP, CUBE, GROUPING SETS를 지원하는 역할을 한다. 집계가 계산된 결과에 GROUPING(표현식) = 1이 되며, 그 외에는 GROUPING(표현식) = 0이 된다.

 

 

 

예시

select deptno,job,sal
from emp
order by 1;

 

 

select deptno, job, sum(sal) as sum_sal
from emp
group by deptno, job
order by 1;

 

 

select deptno, job, sum(sal) as sum_sal
from emp
group by ROLLUP(deptno, job)
order by 1, 2;

depetno, job  일때 아래와 같이 3가지 경우가 나옴

 

 

depetno,job          depetno,                  ()

부서번호,잡        부서번호                  전체

 

하나씩 줄이면서 생각 하면 된다.

 

depetno,job 

depetno

()

 

 

인자가 4가지인경우에는 아래와 같이 나온다.

depetno,job ,to_char(hiredate,'q'),sum(sal)

 

depetno,job ,to_char(hiredate,'q')

depetno,job 

depetno

()                                                     전체를 의미

 

 

stored null이 있으면

select 절에 grouping 함수를 만들면된다...

 

 

 

 

 

LAG / LEAD

 

LAG는 나를 기준으로 앞

 

LEAD는 나를 기준으로 바로 뒤 값

 

 

 

 

 

where vs having

 

where가 더 좋다.

where에서는 미리 거를 수 있기 때문이다.

 

데이터가 10만건 있으면 where에서 7만건으로 줄일 수 있다.

but

having에서는 10만건 데이터로 다 계산 하고 나중에 7만건으로 줄이는거다.

 

 

 

오늘의 tip

 

어려운 sql문이 있으면

한단계씩 역으로 분해해서

위로 올라가면서 분석해야한다.

(무궁화 꽃이 피었습니다 랑 비슷하다.)

 

모든 복수행 함수는 null을 무시한다.