통계 쿼리는 조금 어려울 수도 쉬울 수도 있을 것 같습니다
하지만 대부분의 사람들은 어렵다고 할 것입니다
저 또한 그렇게 느끼고 있고요
한번 같이 공부하면서 알아보도록 하겠습니다
결과셋을 하나의 행으로 데이터 조회하기
통계 쿼리의 가장 기본인 결과셋을 하나의 행으로 만드는 것을 우선 해보겠습니다
행 그룹에서 값을 가져와서 그룹당 단일 행의 열로 변환하려고 합니다.
예를 들어 각 부서의 사원수를 표시하는 결과 셋이 있다고 해봅시다
아래와 같은 결과 셋을 하나행으로 보이도록 출력을 다시 재구성하려고 합니다.
이 방법은 통계 쿼리에 자주 사용하는 방법이며
가장 기초적인 방법의 예문으로 만들려고 합니다
이것을 바탕으로 응용해서 적용해보시길 바랍니다
우선 테이블에 어떤 데이터가 들어있는지 한번 확인해봅시다.
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으로
아래의 데이터가 조회됩니다 그러나 아직은 조금 미흡합니다
아직 행이 하나가 아니기 때문이지요
그럼 조금 수정해보겠습니다
아래의 코드로 합계결과 값은 하나의 행으로 반환하였습니다
위의 결과 값에서 집계 함수 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 회원 통계 쿼리 월별 분기별 년도별
회원 월별 분기별 년도별 통계 쿼리 오늘은 회원별 통계 쿼리를 알아보도록 하겠습니다 mybatis를 활용해서 여러 조건문을 한번에 처리하는 쿼리를 작성하도록 하겠습니다 selectType을 프론트 단에
tantangerine.tistory.com
2022.08.09 - [IT_Web/Oracle] - oracle 회원 가입 증가 현황 DECODE 통계 쿼리 CASE, GROUP BY 활용
oracle 회원 가입 증가현황 DECODE 통계 쿼리 CASE, GROUP BY 활용
회원 가입 증가 현황 통계 쿼리 회원 가입 통계 쿼리를 만들어 보려고 합니다 저도 아직 익숙하지 않아서 조금 더 좋은 방법 있다면 댓글 남겨주세요~ 등록일(REG_DT)을 기준으로 신규회원 가입현
tantangerine.tistory.com
2022.08.10 - [IT_Web/Oracle] - oracle 상품 판매 통계 쿼리 union all case group by 활용
oracle 상품 판매 통계 쿼리 union all case group by 활용
상품 판매 통계 쿼리 오늘은 상품 판매 통계 쿼리를 만들어 보겠습니다 상품은 여러 종류가 있습니다 그것을 어떻게 구분할지는 스스로가 정해야겠지요 아래에서는 상품구분을 단순하게 알파
tantangerine.tistory.com
사례로 보는 통계 쿼리 문
* 아래의 문제는 쿼리문을 집중해서 보시고 이렇게도 쿼리를 작성할 수 있구나 하고
생각해보는 시간이 되었으면 합니다
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를 판매하고 있는 사업장의 총판매 금액을 출력하고 그 나머지를 기타로 묶어 총판매금액을 출력하시오
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에 해당하는 값의 비율을 구하여라
SELECT C1, C2
, ROUND(RATIO_TO_REPORT(C2) OVER(),2) * 100 AS PER
FROM TEST100
TEST35 테이블을 참조하여 KEY1은 부서라고 생각하고 KEY2의 A는 판매액 B는 매입액이라고 할 때
부서별 판매액과 매입액의 차에 대한 값(수익)을 구하고 차액의 비율을 구하시오
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컬럼의 전체 부서 급여 합계 비율을 구하여라
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를 읽어오는 문장을 출력하시오
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
이렇게 통계 쿼리를 작성법을 알아보았습니다
조금이나마 도움이 되었으며 합니다
저도 통계 쿼리를 접할 때 많이 힘들었던 적이 있어서 이렇게 준비하게 되었습니다
그럼 앞으로 공부할 날이 너무나도 많기에 힘내시고
같이 파이팅합시다
그럼 다음 포스팅도 기대해주세요!
'IT_Web > Oracle' 카테고리의 다른 글
| oracle 상품 판매 통계 쿼리 union all case group by 활용 (0) | 2022.08.10 |
|---|---|
| oracle 회원 가입 증가현황 DECODE 통계 쿼리 CASE, GROUP BY 활용 (0) | 2022.08.09 |
| Oracle mybatis 활용 DECODE CASE GROUP BY ROLLUP 회원 통계 쿼리 월별 분기별 년도별 (0) | 2022.08.08 |
| 오라클 트리구조 완벽 이해하기 oracle start with connect by 사용법 (0) | 2022.07.13 |
| 오라클 한번에 여러 테이블 데이터 삽입, oracle INSERT ALL하기 (0) | 2022.07.11 |
