[Oracle] FETCH 구문 사용하기

페이징 처리할 때 total값이 필요한데... 2번 조회하지 않는 방법 찾다가 보게됨..

그러나..11g라 사용 불가

 

select ... rnum , total

from (

        select

              ....rownum as row

                 , count(*) over as total

           from ...

)

where rnum between 1 and 20

 

=> 칼럼 끝에..total이 붙어나오긴하지만.......................2번 조회하는 것 보다 낫지 않을까..

 

 

 

 

 

 

 

 

 

 

---

https://rimkongs.tistory.com/158

[Oracle] FETCH 구문 사용하기

 

Oracle 12c release부터 사용할 수 있는 구문이다.

 

 

다음과 같이 다대일 관계를 형성하고 있는 INVENTORIES 와 PRODUCTS 테이블을 보자

 

 

quantity 컬럼에 대해 top-5를 리턴해준다.

SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH NEXT 5 ROWS ONLY;

 

 

 

 

쓸 수 있는 Option 을 알아보자.

[ OFFSET offset ROWS]
FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]

 

1. OFFSET 옵션

OFFSET 자리에는 원하는 row limits 갯수를 뽑기 이전에 스킵하고 싶은 row 갯수가 있을 시에 써주면 된다.

OFFSET에 음수가 오는 경우 Oracle은 0으로 인식 / NULL 이 오거나 리턴되는 row 갯수보다 높은 수를 쓸 경우 아무 행도 리턴되지 않는다.

SELECT
 product_name,
 quantity
FROM
 inventories
INNER JOIN products
 USING(product_id)
ORDER BY
 quantity DESC 
OFFSET 10 ROWS 
FETCH NEXT 10 ROWS ONLY;

 

 

 

2. ONLY | WITH TIES 옵션

ONLY - 내가 지정한 딱 row limits 갯수만 리턴

WITH TIES - 내가 지정한 row limits 에서 맨 마지막 행과 same sort key인 row들도 줄줄이 보여줌

 

 

예를 들어, ONLY 옵션의 경우 다음과 같이 딱 10개의 행을 리턴해준다.

SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH NEXT 10 ROWS ONLY;

 

 

WITH TIES 옵션의 경우 맨 마지막 행과 같은 것들도 추가로 보여준다

SELECT
 product_name,
 quantity
FROM
 inventories
INNER JOIN products
 USING(product_id)
ORDER BY
 quantity DESC 
FETCH NEXT 10 ROWS WITH TIES;

 

3. percent 옵션

 

아래 예제의 경우 top 5% products 리턴해준다. 그러니깐 총 결과 rows가 몇개냐에 따라 보여주는 갯수가 다름

SELECT
    product_name,
    quantity
FROM
    inventories
INNER JOIN products
        USING(product_id)
ORDER BY
    quantity DESC 
FETCH FIRST 5 PERCENT ROWS ONLY;

The inventories table has 1112 rows, therefore, 5% of 1112 is 55.6 which is rounded up to 56 (rows).

 

---

https://programdev.tistory.com/15

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

 

 

 

 

 

출처: https://programdev.tistory.com/15 [개발자생각:티스토리]