over 절 count 또는 row_number()문과 함께 사용

over 함수는 쿼리의 결과에서 다른 카운트 값을 얻고자 할때 사용한다.

 

 

[전체 카운트 필요한 경우]

---

Select

count(*) OVER () AS total_COUNT,

* from 

my_table 

where my_value='a'

OFFSET 0 ROW
FETCH FIRST 50 ROW ONLY

---

이 경우 쿼리를 실행하면 my_value컬럼값이 'a'인  0~50번째 데이터만 가져오는 쿼리이다.

하지만 해당 조건(컬럼값이 a 인)에 일치하는 전체 데이터가 몇건인지 필요한 경우가 가끔있다.

(리스트나 게시판등에서 현재 페이지에 10개의 값이 나오는데 전체 글갯수가 필요한 경우라든가...)

 

이 경우 over절을 count 와 함께 사용하면 전체 조회건이 카운트 된다.

 

 

 

 

[개별 카운트 및 순번 필요한 경우]

---

SELECT 
 POP_YEAR, TB1.pop_month, TO_NAME,FROM_CODE, SUM(CAST(POP_TOTAL_MAN AS INT)   ) AS TOTAL_MAN 
 ,ROW_NUMBER() OVER (PARTITION BY POP_YEAR ORDER BY SUM(POP_TOTAL_MAN)  DESC) AS RankNo
FROM 
APT_POP_MOVE TB1
WHERE  
1=1
and pop_month = '12'
and to_name = '전국' and  from_code in ('00','11','41','26')
GROUP BY TB1.POP_YEAR, TB1.pop_month, TB1.TO_NAME, TB1.FROM_CODE

---

 

 

위 쿼리의 경우 년도별 12월의 전국 인구이동에 대한 쿼리인데 RankNo 부분이 각 년도별 정렬기준이 되게 하기 위해 over 절과 함께 row_number 사용하였다.

partition by에는 순번의 기준이 컬럼을 order by 부분에는 정렬 기준을 정해주면 된다.

 

 

 

 

[파티션 기준에 따른 집계함수 사용]

---

SELECT SalesOrderNumber AS OrderNumber, ProductKey,

          OrderQuantity AS Qty,

          SUM(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Total,

          AVG(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Avg,

          COUNT(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Count,

          MIN(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Min,

          MAX(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Max

FROM dbo.FactResellerSales

WHERE SalesOrderNumber IN(N'SO43659',N'SO43664') AND ProductKey LIKE '2%'

ORDER BY SalesOrderNumber,ProductKey;

---

이 경우 OrderNumber 값을 기준으로 묶어 각각의 집계함수(sum, avg, count, min, max)에 대한 값을 반환환다.

OrderNumber이 [SO43659] 인 경우만 살펴보면 각각의 product 값에 따른 Qty값은 존재하지만 total은 총합(sum)인 16이 , 평균은 3이 product의 종류는 5종(count)이 반환된다. 

 

OrderNumber  Product  Qty  Total  Avg  Count  Min  Max  
-----------       -------    ---    -----  ---    -----   ---    ---  
SO43659          218       6     16     3       5       1     6  
SO43659          220       4     16     3       5       1     6  
SO43659          223       2     16     3       5       1     6  
SO43659          229       3     16     3       5       1     6  
SO43659          235       1     16     3       5       1     6  
SO43664          229       1      2     1       2       1     1  
SO43664          235       1      2     1       2       1     1