SQL 문법 정리 : 분석 함수(AVG,ROW_NUMBER,RANK,DENSE_RANK)

SQL 문법 정리 : 분석 함수(AVG,ROW_NUMBER,RANK,DENSE_RANK)

 

분석 함수란

특정 집합 내에서 결과 건수의 변화 없이 해당 집합안에서 합계 및 카운트 등을 계산할수 있는 함수이다.

SELECT

        C1

        분석함수(C2,C3,.....)OVER(PARTITION BY C4 ORDER BY C5)

FROM TABLE_NAME

;

(#사용하고자 하는 분석함수를 쓰고 대상 컬럼을 기재 후PARTITION BY에서 값을 구하는 기준 컬럼을 쓰고 ORDER BY에 정렬 컬럼을 기재한다.)

 

 

SELECT 
         COUNT(*)
FROM 
         PRODCUT;

(#집계함수는 집계의 결과만을 출력한다.)

(#만약 PRODUCT 데이터가10개있다면 COUNT 결과값 10 만나옴-> 집계함수의 한계)

SELECT
       COUNT(*) OVER(),  A.*
  FROM
      PRODUCT A
(#분석 함수는 집계의 결과 및 집합을 함께 출력한다.)

(#집계의 결과와 테이블의 내용도 함께 보여준다)

 

AVG

AVG - 집계함수, 집계결과만 출력

SELECT 

       AVG(PRICE) 

FROM

       PRODUCT;

 

AVG - GROUP BY _AVG구하기

SELECT

        B.GROUP_NAME

        ,AVG(PRICE)

FROM PRODCUT A

INNER JOIN PRODUCT_GROUP B

        ON (A.GROUP_ID = B.GROUP_ID)

GROUP BY

B.GROUP_NAME;

(#GROUP_NAME컬럼을 기준으로 PRICE컬럼의 평균값을 구한다.)

 

AVG - 분석함수 사용

SELECT

        A.PRODUCT_NAME

       ,A.PRICE

       ,B.GROUP_NAME

       ,AVG(A.PRICE) OVER (PARTITION BY B.GROUP_NAME)

        (#GROUP_NAME컬럼 기준의 PRICE의 평균값을 출력한다.)

FROM

PRODUCT A

INNER JOIN PRODUCT_GROUP B

ON (A.GROUP_ID = B.GROUP_ID);

(#분석함수를 사용하여 결과집합을 그대로 출력하면서 GROUP NAME 기준의 평균을 출력하였다.)

 

#누적집계- 누적평균을 구할수 있다.

SELECT

        A.PRODUCT_NAME

       ,A.PRICE

       ,B.GROUP_NAME

       ,AVG(A.PRICE) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE) #---누적집계

        (#GROUP_NAME컬럼 기준의 PRICE의 평균값을 출력한다.)

FROM

PRODUCT A

INNER JOIN PRODUCT_GROUP B

ON (A.GROUP_ID = B.GROUP_ID);

 

ROW_NUMBER

특정 집합 내에서 결과 건수의 변화 없이 해당 집합안에서 특정 컬럼의 순위를 구하는 함수이다.

ROW_NUMBER는 같은 순위가 있어도 무조건 순차적으로 순위를 매긴다.

(1,2,3,4,5,....순으로 나간다.)

 

SELECT

         A.PRODUCT_NAME

        ,B.GROUP_NAME

        ,A.PRICE

        ,ROW_NUMBER() OVER

         (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)

FROM PRODUCT A

INNER JOIN PRODUCT_GROUP B

             ON (A.GROUP_ID = B.GROUP_ID);

(#해당 집합내에서 순위르르 구한다. 순위를 구할때 GROUP_NAME 컬럼 기준으로 구히고 GROUP_NAME기준의 각 순위는 PRICE컬럼 기준으로 정렬한다.)

 

RANK

 

특정 집합 내에서 결과 결과 건수의 변화 없이 해당 집합안에서 특정 컬럼의 순위를 구하는 함수이다.

같은 순위가 있으면 동일 순위로 매기고 그 다음 순위로 건너뛴다.(1,1,3,4,...순으로 나간다.)

SELECT

          A.PRODUCT_NAME

         ,B.GROUP_NAME

         ,A.PRICE

         ,RANK() OVER

           (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)

 FROM PRODUCT A

INNER JOIN PRODUCT_GROUP B

             ON (A.GROUP_ID = B.GROUP_ID);

 

DENSE_RANK 

 

특정 집합 내에서 결과 결과 건수의 변화 없이 해당 집합안에서 특정 컬럼의 순위를 구하는 함수이다

DENSE_RANK는 같은 순위가 있으면 동일 순위로 매기고 그 다음 순위 건너뛰지 않음(1,1,2,3,...)

SELECT 

        A.PRODCUT_NAME

       ,B.GROUP_NAME

       ,A.PRICE

       ,DENSE_RANK() OVER

         (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)

FROM PRODUCT A

 INNER JOIN PRODUCT_GROUP B

              ON (A.GROUP_ID = B.GROUP_ID);

(#해당 집합내에서 순위를 구한다.)

(#순위를 구할때 GROUP_NAME 컬럼 기준으로 구하고 GROUP_NAME기준의 각 순위는 PRICE컬럼 기준으로 정렬한다.)