신기한 연구소

안녕하세요. 신기한 연구소입니다.

윈도우함수를 공부하다 보니 정말 편하게 사용할 수 있는 함수들이 많음을

알게 되었습니다.

개발할 때 필요한 블로그가 되었으면 좋겠네요. ㅎㅎ

이번 포스팅은 윈도우함수 중 집계함수에 대해 알아봅니다.

SUM()

보통 SUM()은 우리가 GROUP BY와 함께 사용하는 함수인데요.

윈도우함수를 사용하면 GROUP BY를 사용하지 않고

다양한 기능을 사용할 수 있답니다.

기본적으로 사용하는 방법으로 조회했습니다.

SAL 컬럼을 SUM 해서 역순으로 정렬했습니다.

결과는 ROW가 진행되면서 합산한 금액을 확인할 수 있습니다. (S_DEF_SAL)

특이한 점은 8행부터 12행까지 합산 금액이 같은데 이유는 SAL이 같기에 RANGE 처럼

같은 SAL을 전부 합산해서 보여주는 구조입니다.

결국 RANGE를 사용한 S_RAN_SAL과 같은 결과를 보여줍니다.

같은 금액과 관계없이 그냥 하나씩 더하는 결과를 만들 수 있습니다.

바로 ROWS를 사용하면 됩니다. (S_ROW_SAL)

쿼리의 UNBOUNDED PRECEDING은 처음부터 합산하라는 의미입니다. 

PARTITION BY JOB을 사용해서 JOB별로 묶을 수 있습니다.

기본 S_PAR_ORD_SAL을 보면 JOB별로 합계를 구하는 결과를 확인할 수 있습니다.

같은 SAL 구간인 11행부터 14행의 합산은 묶음 합산되었음을 확인할 수 있습니다.

ROWS/RANGE에 대한 합산도 확인할 수 있습니다.

ROWS는 SAL이 같아도 순차적으로 합산합니다.

마지막으로 S_PAR_ROW_BT_SAL의 결과를 보면

BETWEEN을 사용해서 범위를 정했음을 볼 수 있습니다.

1 PRECEDING AND 1 FOLLOWING의 의미는

현재 행에서 앞의 1행부터 다음 1행 총 3개의 행에 대한 합산을 표시하라는 의미입니다.

MAX(), MIN()

MAX() 함수는 최대 값을 구하는 함수이고 MIN() 함수는 최소 값을 구하는 함수입니다.

그래서 OVER 옵션에 ORDER BY를 사용할 필요가 없습니다.

PARTITION BY로 소그룹을 만들어 최고 값을 확인할 때 용이합니다.

위 쿼리를 확인하면 JOB별로 최고 값과 최소 값을 확인할 수 있습니다.

간단하게 사용하니 좋네요.

AVG()

평균값을 구하는 AVG() 함수는 그룹 함수에서 자주 사용합니다.

GROUP BY와 함께 사용해야 하지만 윈도우함수를 사용하면

간단하게 SELECT문에서 사용할 수 있습니다.

PARTITON BY를 이용해서 JOB별 평균을 구할 수 있습니다.

또한 BETWEEN을 사용해서 JOB별에서 행의 구간을 정해서 평균을 구할 수

있습니다.

COUNT()

조건에 맞는 행의 건수를 보여주는 COUNT도 윈도우함수를 사용할 수 있습니다.

위 쿼리를 확인해 보겠습니다.

CNT1의 경우는 값의 범위를 정할 수 있는 RANGE BETWEEN을 사용했습니다.

현재 선택된 행의 SAL 값을 기준으로 -500보다 작은 값부터 현재 값에서 500만큼

큰 값을 범위로 잡아서 해당 범위에 만족하는 행들의 건수를 보여줍니다.

CNT2는 행의 범위를 정할 수 있는 ROW BETWEEN을 사용했습니다.

그래서 현재 행을 기준으로 앞의 행과 그다음 행의 존재를 확인 후

건수를 보여줍니다.

그런데 행의 건수는 별 의미가 없겠네요. ㅎㅎ

마지막은 PARTITION BY를 사용하는 경우입니다.

JOB 별로 그룹을 만들어 COUNT를 할 수 있습니다.

GROUP BY를 사용하지 않고 컬럼 기준으로

원하는 집계를 표현할 수 있게 되었습니다.

잘못된 정보나 수정이 필요하면 댓글 주세요.

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.

안녕하세요. 신기한 연구소입니다.

이번 포스팅은 윈도우함수(WINDOW FUNCTION) 중 순위함수에 대해 알아봅니다.

순위함수를 잘 사용한다면 다양한 그룹 데이터, 집계 데이터를 산출하는 경우

유용하게 사용할 수 있습니다.

그럼 윈도우함수 중 순위 함수는 어떤 것이 있는지 확인해보겠습니다.

먼저 RANK() 함수를 알아봅니다.

위 쿼리를 보면 RANK() 함수를 사용하는 방법을 알 수 있습니다.

RANK() 함수는 OVER 내 옵션에서 ORDER BY를 필수로 사용하면서 특정 항목에 대한 순위를

정할 수 있습니다.

PARTITION BY의 여부에 따라 전체 순위를 정할지 아니면 특정 컬럼마다 순위를 정할지

결정할 수 있습니다.

R_SAL을 보면 1부터 SAL의 높은 금액순으로 순위를 정하고 있습니다.

특징은 같은 급여에 대해 같은 순위를 설정했습니다. 9위를 보세요.

그리고 그 다음은 10위가 아닌 같은 순위의 개수만큼 건너뛰고 14위가 나왔습니다.

DENSE_RANK()도 순위를 정하는 함수입니다.

마찬가지로 OVER 내 옵션으로 ORDER BY를 사용해야 합니다.

DR_SAL을 보면 RANK()와 다른 점을 찾을 수 있습니다.

같은 급여에 대해 같은 순위를 정하는 건 같지만

그다음 순위를 정할 때 같은 순위의 개수만큼 건너뛰지 않고 바로 다음 순위인

10위를 보여줍니다.

마지막 ROW_NUMBER()를 보면 이 또한 순위를 정하는 함수입니다.

사용법은 기존 RANK(), DENSE_RANK()와 같으며 결과는 RN_SAL을 보면

같은 급여와 상관없이 그냥 쭉~ 1위부터 순서를 정했습니다.

전체 데이터에 대한 순위를 정하는 방법에 대해 알아봤습니다.

이제 PARTITION BY 컬럼을 사용해서 소그룹 순위를 정해보겠습니다.

위 쿼리를 보면 2가지 다른 점이 있습니다.

먼저 PARTITION BY JOB이라는 옵션을 추가했습니다.

그리고 ORDER BY절에 JOB이 추가되었습니다.

JOB별로 정렬하고 JOB별로 순위를 정한다고 보면 됩니다.

RANK()를 보면 JOB별로 SAL 순위를 정했습니다.

마찬가지로 중복된 SAL에 대해서는 공동 순위를 정했습니다.

그리고 중복 순위 다음은 중복 개수만큼 건너뛰고 7위가 나왔습니다.

DENSE_RANK()도 같은 개념으로 보이는데

공동 순위 다음의 순위에 대한 개념만 다릅니다.

중복 개수가 있어도 바로 다음 순위 4위를 보여줍니다.

마지막 ROW_NUMBER()도 JOB별로 순위를 정했는데

SAL 금액이 같아도 공동 순위 없이 순위를 정하고 있습니다.

오라클 윈도우함수에서 순위 함수에 대해 알아봤는데요.

이제는 RANK(), DENSE_RANK, ROW_NUMBER() 함수가 나와도

문제없이 사용할 수 있다고 생각됩니다.

윈도우함수는 SELECT문에서만 사용할 수 있다는 것은 잊지 마세요.

잘못된 내용이나 수정이 필요하면 댓글 주세요.

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.