[Oracle]SQL Group by, 그룹 함수 쿼리 문제와 예시

안녕하세요.

지난번에는 SELECT 쿼리 시 ORDER BY 를 사용하여 정렬하는 방법과 FETCH FIRST ROWS ONLY 나 ROWMUM 을 사용하여 건수를 제한하여 조회하는 방법을 알아보았습니다.

2022.04.12 - [SW교육/DB] - [Oracle]select , order by, fetch first rows only, rownum 에 대해서 알아보자.

 

오늘은 그룹 함수에 대해서 알아보고 그룹 함수를 응용하여 열을 행으로 변환하는 방법도 알아보도록 하겠습니다.

 

- 그룹 함수란? SUM, COUNT, AVG, MAX, MIN 등의 함수인데요. 

전체 데이터의 합계, 건수, 평균, 최대값, 최소값을 구할 수도 있고 Group by 를 이용하여 그룹별 합계 등을 조회할 수도 있습니다.

 

- 예시를 통해서 알아보도록 하겠습니다.

 : 실습할 테이블은 LOTTO_AMT 라는 테이블로 로또 회차별(1~1,007) 당첨금액, 당첨 건수로 실제 로또 당첨 데이터를 업로드 한 데이터입니다. 

LOTTO_AMT

2022.03.24 - [SW교육/DB] - [DB활용]로또 데이터를 이용한 오라클 테이블 만들기

 

문제 1) 역대 로또 최고당첨금액 및 당첨금액 합계, 당첨자 수합계를 조회하라. 

해답 ==> 그룹 함수 사용하면 간단히 조회할 수 있습니다.

SELECT MAX(A.LOTTO_AMT), SUM(A.LOTTO_AMT), SUM(A.LOTTO_CNT)
 FROM JUNG.LOTTO_AMT A ;

결과 ==>

헉! 로또 당첨금액이 400억이 넘었던 적이 있었던가요.? 1077회까지 당첨금의 총합계는 약 2조 7천9백억 원 정도 되는군요. 당첨자 수는 14억 건 정도 됩니다.  

 

문제 2) 400억이 넘었던 회차가 언제인지 너무 궁금하군요. 최고 당첨금액 회차와 등수 정보 등을 조회하라.

해답 ==> 서브 쿼리와 그룹 함수를 사용하였습니다.

SELECT *
 FROM JUNG.LOTTO_AMT A
WHERE A.LOTTO_AMT = ( SELECT MAX(B.LOTTO_AMT)
                        FROM JUNG.LOTTO_AMT  B )  ;

결과 ==> 19회 차에서 

너무 궁금해서 회차의 날짜도 조회하여 보았습니다.

SELECT *
 FROM JUNG.LOTTO_AMT A
        , JUNG.LOTTO_DATE C
WHERE A.LOTTO_AMT = ( SELECT MAX(B.LOTTO_AMT)
                        FROM JUNG.LOTTO_AMT  B ) 
  AND A.LOTTO_NO = C.LOTTO_NO

지금으로부터 무려 19년 전인 2003년 4월 12일 이군요. 당첨되신 분은 지금 잘 살고 계시겠죠.? ^^

 

문제 3) 등수별로 역대 로또 최고 당첨금액 및 당첨금액 합계, 당첨자수 합계를 조회하라.

해답 ==> 그룹 함수와 GROUP BY를 사용하면 됩니다.

SELECT 문과  GROUP BY 에 그룹 하려는 항목을 넣어줍니다.

이때 정렬이 필요하면 ORDER BY 는 GROUP BY 다음에 위치합니다.

SELECT A.LOTTO_CFCD, MAX(A.LOTTO_AMT), SUM(A.LOTTO_AMT), SUM(A.LOTTO_CNT)
 FROM JUNG.LOTTO_AMT A 
GROUP BY A.LOTTO_CFCD

ORDER BY A.LOTTO_CFCD ;

결과 ==> 각 등수별 최고 당첨금액 및 당첨금액 합계, 당첨자수 합계를 조회하였습니다.

문제 4) 각 등수별 최고 금액을 다음 레이아웃으로 조회하라.

해답 ==>그룹 함수와 CASE WHEN 문을 사용하여 조회합니다.

SELECT MAX(CASE WHEN A.LOTTO_CFCD = '01' THEN A.LOTTO_AMT ELSE 0 END) AS "1등MAX"
        , MAX(CASE WHEN A.LOTTO_CFCD = '02' THEN A.LOTTO_AMT ELSE 0 END) AS "2등MAX"
        , MAX(CASE WHEN A.LOTTO_CFCD = '03' THEN A.LOTTO_AMT ELSE 0 END) AS "3등MAX"
        , MAX(CASE WHEN A.LOTTO_CFCD = '04' THEN A.LOTTO_AMT ELSE 0 END) AS "4등MAX"
        , MAX(CASE WHEN A.LOTTO_CFCD = '05' THEN A.LOTTO_AMT ELSE 0 END) AS "5등MAX"
 FROM JUNG.LOTTO_AMT A ;

결과 ==> 

오늘 간단하게 그룹 함수 사용하여 쿼리를  작성해 보았는데요.

이밖에도 그룹 함수를 사용하는 경우는 너무 많습니다.

 

예를 들면 회차를 증가하기 위해서 MAX(회차) + 1 을 사용하는 경우 라든지 COUNT(1) 을 하여 데이터 존재 여부를 확인하는 경우인데요. 

- 18회 차에 1등 당첨자가 있었는지 조회해 보겠습니다.

 SELECT COUNT(1)
 FROM JUNG.LOTTO_AMT A
WHERE A.LOTTO_NO   = 18 -- 18회차 
  AND A.LOTTO_CFCD = '01' -- 1등
  AND A.LOTTO_AMT  <> 0  -- 금액 0이 아닌 ;

===> 결과

CASE 문을 사용하여 결과를 여부(YN)로 변경해 보겠습니다.

SELECT CASE WHEN COUNT(1) = 0 THEN 'N' ELSE 'Y' END AS "당첨자존재여부"
 FROM JUNG.LOTTO_AMT A
WHERE A.LOTTO_NO   = 18
  AND A.LOTTO_CFCD = '01'
  AND A.LOTTO_AMT  <> 0 ;

===> 결과

다음에 뵙겠습니다. 감사합니다. 

 

2022.04.12 - [SW교육/DB] - [Oracle]select , order by, fetch first rows only, rownum 에 대해서 알아 보자.

[Oracle]select , order by, fetch first rows only, rownum 에 대해서 알아 보자.

2022.04.11 - [SW교육/DB] - [Oracle]오라클 pk 2개 이상 컬럼으로 지정하기(복합키)

[Oracle]오라클 pk 2개 이상 컬럼으로 지정하기(복합키)

2022.04.10 - [SW교육/DB] - [Oracle]Dbeaver에서 테이블 생성 및 데이터 가져오기(import,insert)

[Oracle]Dbeaver에서 테이블 생성 및 데이터 가져오기(import,insert)