Oracle - CASE, DECODE, GROUP BY 활용 통계쿼리문

통계 쿼리는 조금 어려울 수도 쉬울 수도 있을 것 같습니다

하지만 대부분의 사람들은 어렵다고 할 것입니다

저 또한 그렇게 느끼고 있고요

 

한번 같이 공부하면서 알아보도록 하겠습니다

 

 

결과셋을 하나의 행으로 데이터 조회하기

 

통계 쿼리의 가장 기본인 결과셋을 하나의 행으로 만드는 것을 우선 해보겠습니다

행 그룹에서 값을 가져와서 그룹당 단일 행의 열로 변환하려고 합니다.

예를 들어 각 부서의 사원수를 표시하는 결과 셋이 있다고 해봅시다

 

 

아래와 같은 결과 셋을 하나행으로 보이도록 출력을 다시 재구성하려고 합니다.

결과셋을 하나의행으로 만들어보자

 

이 방법은 통계 쿼리에 자주 사용하는 방법이며 

가장 기초적인 방법의 예문으로 만들려고 합니다

 

이것을 바탕으로 응용해서 적용해보시길 바랍니다

 

 

우선 테이블에 어떤 데이터가 들어있는지 한번 확인해봅시다.

 

SELECT deptno FROM emp;

 

아래와 같이 데이터가 들어있으며, 총 14개의 행이 있습니다

 

원본 테이블 데이터 정보

 

이 데이터를 각 부서의 사원수를 구하기 위해서는

아래와 같이 일반적으로 생각하게 될 것입니다

하지만 이 데이터는 한 행에 출력이 않아 통계 쿼리로는 부적합합니다

그래서 하나 행으로 조회하는 방법을 알아보는 것이 최우선입니다 

 

SELECT deptno, coount(*) AS CNT FROM EMP GROUP BY deptno;

 

 

하나의 행으로 출력하는 과정을 알아보도록 하겠습니다

아래의 코드를 분석해 봅시다

 

 

select deptno,
       case when deptno=10 then 1 else 0 end as deptno_10,
       case when deptno=20 then 1 else 0 end as deptno_20,
       case when deptno=30 then 1 else 0 end as deptno_30
  from emp
 order by 1

 

 

위의 코드로 아래와 같이 조회되는 것을 확인할 수 있습니다

위의 DEPTNO 컬럼은 가독성을 위한 값이니 참고하시기 바랍니다.

CASE WHEN THEN ELSE END를 활용해서 한 컬럼에 대해서 값을 출력합니다 

그 값은 간단합니다 DEPTNO가 10이면 1을 주고 나머지는 0을 부여하게 됩니다.

그리고 두 번째 컬럼은 DEPTNO가 20, 세번째 컬럼은 DEPTNO가 30으로

아래의 데이터가 조회됩니다  그러나 아직은 조금 미흡합니다

아직 행이 하나가 아니기 때문이지요

 

그럼 조금 수정해보겠습니다

 

case문으로 쿼리 조회

 

 

아래의 코드로 합계결과 값은 하나의 행으로 반환하였습니다

위의 결과 값에서 집계 함수 SUM을 사용하여 각 DEPTNO의 발생 횟수를 계산합니다

 

 

 select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
       sum(case when deptno=20 then 1 else 0 end) as deptno_20,
       sum(case when deptno=30 then 1 else 0 end) as deptno_30
  from EMP;

 

아래와 같이 최종 결과가 출력됩니다 

 

 

최종 결과

 

 

하지만 아직 조금 부족합니다 그 이유는 한 테이블에서 CASE문을 전체를 적용해서 

시스템상 성능 부하가 올 수 있습니다 그리고 단순히 CASE 표현식을 합산하는 방법을 이기 때문입니다

지금은 데이터가 많이 없기 때문에 이 같은 방법을 해도 문제가 없지만 데이터가 몇만 건씩 넘어간다면

문제가 발생할 것입니다

다른 방법을 알아보도록 하겠습니다

 

 

select case when deptno=10 then empcount else null end as deptno_10,
  case when deptno=20 then empcount else null enD as deptno_20,
  case when deptno=30 then empcount else null end as deptno_30
from (
        select deptno, count(*) as empcount
          from emp
         group by deptno
     ) x;

 

 

아래의 코드를 자세히 보시면

처음에 COUNT 함수를 사용했던 것을 기억하실 것입니다

그 쿼리를 인라인 뷰로 사용하여 부서당 사원수를 생성합니다

그럼 그 결과 값은 아래와 같이 출력되는 것을 확인할 수 있습니다

 

이 방법은 인라인 뷰로 먼저 생성된 데이터가 3개의 행이므로

CASE문 실행할 경우 전체 행을 읽지 않고 3개의 행만으로

결과를 출력할 수 있습니다 

 

 

인라인 뷰 활용 예

 

그런 다음 MAX 함수로 열을 하나의 행으로 축소를 합니다

아래의 코드를 확인해 봅시다

 

select max(case when deptno=10 then empcount else null end) as new_deptno_10,
       max(case when deptno=20 then empcount else null end) as new_deptno_20,
       max(case when deptno=30 then empcount else null end) as new_deptno_30
from (
        select deptno, count(*) as empcount
          from emp
         group by deptno
     ) x;

 

결과 값은 아래와 같이 출력되는 것을 확인할 수 있습니다

 

 

성능강화 쿼리 적용 결과값

 

아래는 사례별로 통계 쿼리를 작성하는 것을 포스팅 연결하였습니다

그리고 마지막에는 통계쿼리 응용문제가 있으니

확인하셔서 공부하는데 도움이 되셨으면 합니다 감사합니다

 

 

2022.08.08 - [IT_Web/Oracle] - Oracle mybatis 활용 DECODE CASE GROUP BY ROLLUP 회원 통계 쿼리 월별 분기별 연도별

Oracle mybatis 활용 DECODE CASE GROUP BY ROLLUP 회원 통계 쿼리 월별 분기별 년도별

 

 

2022.08.09 - [IT_Web/Oracle] - oracle 회원 가입 증가 현황 DECODE 통계 쿼리 CASE, GROUP BY 활용

oracle 회원 가입 증가현황 DECODE 통계 쿼리 CASE, GROUP BY 활용

 

2022.08.10 - [IT_Web/Oracle] - oracle 상품 판매 통계 쿼리 union all case group by 활용

oracle 상품 판매 통계 쿼리 union all case group by 활용

 

---

 

 

---

 

 

사례로 보는 통계 쿼리 문

* 아래의 문제는 쿼리문을 집중해서 보시고 이렇게도 쿼리를 작성할 수 있구나 하고

생각해보는 시간이 되었으면 합니다

 

 

 

 

HP라는 제품은 너무 많은 등급을 가지고 있어 제품별로 분석하기를 원하고, ‘LD’라는 제품은 등급의 수가 적고 중요하므로 등급별로 분석하기를 원하며, ‘PP’라는 제품은 다양한 등급을 가지고 있으나 ‘P530C’라는 등급은 전략적으로 관리하고자 하여 등급별로 분석하고, 나머지는 기타로 모아주기를 원한다고 생각하자

 

이런 경우에는 CASE와 GROUP BY를 활용하여 풀어보자

 

SELECT

  CASE

    WHEN 제품 = ‘HP’ THEN 제품

    WHEN 제품 = ‘LD’ THEN 등급 ,

    WHEN 등급 = ‘P530C’ THEN 등급

    ELSE ’기타‘

  END

  , SUM(총매출), SUM(총수량).....

FROM 매출테이블

WHERE 매출일자 LIKE ’9808%’

GROUP BY

  CASE

    WHEN 제품 = ‘HP’ THEN 제품

    WHEN 제품 = ‘LD’ THEN 등급 ,

    WHEN 등급 = ‘P530C’ THEN 등급

    ELSE ’기타‘

  END

 

---

 

SALE_HIST 테이블을 참조하여 ‘PENCIL’ 총판매금액과 01번 사업장의 총판매금액과

ERASER를 판매하고 있는 사업장의 총판매 금액을 출력하고 그 나머지를 기타로 묶어 총판매금액을 출력하시오

SALE_HIST 테이블 데이터 정보

 

SELECT

  CASE

    WHEN SALE_ITEM = 'PENCIL' THEN SALE_ITEM

    WHEN SALE_ITEM = 'ERASER' THEN SALE_SITE

    WHEN SALE_SITE = 01 THEN SALE_SITE

    ELSE '기타'

  END AS 특별관리항목

  , SUM(SALE_AMT) AS 총판매금액

FROM SALE_HIST

  GROUP BY

    CASE

      WHEN SALE_ITEM = 'PENCIL' THEN SALE_ITEM

      WHEN SALE_ITEM = 'ERASER' THEN SALE_SITE

      WHEN SALE_SITE = 01 THEN SALE_SITE

      ELSE '기타'

    END

 

상품별 총판매금액 통계쿼리

 

 

---

 

 

 

TEST100 테이블을 참조하여 C2에 해당하는 값의 비율을 구하여라

 

TEST100 테이블 데이터 정보

 

SELECT C1, C2

, ROUND(RATIO_TO_REPORT(C2) OVER(),2) * 100 AS PER

FROM TEST100

 

TEST100 상품별 비율

 

---

 

 

TEST35 테이블을 참조하여 KEY1은 부서라고 생각하고 KEY2의 A는 판매액 B는 매입액이라고 할 때

부서별 판매액과 매입액의 차에 대한 값(수익)을 구하고 차액의 비율을 구하시오

 

TEST35 테이블 데이터 정보

 

 

SELECT

  CASE WHEN KEY1 IS NOT NULL THEN KEY1 ELSE '합계' END AS KEY1

  ,  SUM(CASE WHEN KEY2 = 'A' THEN AMT ELSE 0 END) AS 판매액

  ,  SUM(CASE WHEN KEY2 = 'B' THEN AMT ELSE 0 END) AS 매입액

  ,  SUM(CASE WHEN KEY2 = 'A' THEN AMT ELSE -AMT END) AS 수익

  ,  ROUND(SUM(CASE WHEN KEY2='A' THEN AMT ELSE -AMT END)

                        / SUM(CASE WHEN KEY2='A' THEN AMT END)*100 ) AS PER

FROM (SELECT KEY1, KEY2, SUM(AMT) AMT

               FROM TEST35

               GROUP BY ROLLUP(KEY1),  KEY2

               )

GROUP BY KEY1;

 

 

부서 판매액 차액 비율

 

---

 

 

Temp를 이용한 문제를 풀어보자

Temp에 있는 직원들을 부서별 직급별로 SALARY 합을 보려 한다 이때 부서별로 직급들이 동일행에 나오도록

보여주고 부서별 전체 급여 그리고 PER컬럼의 전체 부서 급여 합계 비율을 구하여라

 

 

TEMP 테이블 데이터 정보

 

 

SELECT CASE WHEN DEPTNO IS NOT NULL THEN DEPTNO ELSE '합계' END DEPTNO

                 , SUM(A.부장) 부장, SUM(A.차장) 차장, SUM(A.과장) 과장

                 , SUM(A.대리) 대리, SUM(A.사원) 사원, SUM(A.수습) 수습, SUM(A.PER) 전체급여비율

                 , SUM(A.SAL) 부서별급여합계

FROM (SELECT DEPT_CODE DEPTNO, SALARY SAL

                               , SUM(CASE WHEN LEV = '부장' THEN SALARY ELSE 0 END) 부장

                               , SUM(CASE WHEN LEV = '차장' THEN SALARY ELSE 0 END) 차장

                               , SUM(CASE WHEN LEV = '과장' THEN SALARY ELSE 0 END) 과장

                               , SUM(CASE WHEN LEV = '대리' THEN SALARY ELSE 0 END) 대리

                               , SUM(CASE WHEN LEV = '사원' THEN SALARY ELSE 0 END) 사원

                               , SUM(CASE WHEN LEV = '수습' THEN SALARY ELSE 0 END) 수습

                               , ROUND(RATIO_TO_REPORT(SALARY) OVER (),2) * 100 AS PER

                               , SUM(SALARY)

              FROM TEMP

              GROUP BY DEPT_CODE, SALARY, LEV

              ) A

GROUP BY ROLLUP(DEPTNO)

ORDER BY DEPTNO

 

 

부서별 전체 급여 비율

 

---

 

 

TEST02 테이블의 자료를 이용해 최대 CRATE를 가지는 일자의 AMT와 최소 CREATE를 가지는 일자의 AMT를 읽어오는 문장을 출력하시오

 

TEST02 테이블 정보

 

 

SELECT distinct

  LAST_VALUE(AMT) OVER( ORDER BY CRATE

                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_VAL

  , FIRST_VALUE(AMT) OVER( ORDER BY CRATE

                                       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MIN_VAL

FROM TEST02

 

 

최대 최소 금액출력

 

                    

---

 

 

이렇게 통계 쿼리를 작성법을 알아보았습니다

조금이나마 도움이 되었으며 합니다

저도 통계 쿼리를 접할 때 많이 힘들었던 적이 있어서 이렇게 준비하게 되었습니다

그럼 앞으로 공부할 날이 너무나도 많기에 힘내시고 

같이 파이팅합시다

 

그럼 다음 포스팅도 기대해주세요!