OVER()를 이용한 집계 처리

OVER()를 이용한 집계 처리

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

금번 주제는 크게 복잡한 내용은 아니지만, SQL 2005의 기능을 이용한 간단한 쿼리 구현 예입니다. 참고하세요^^

 

Question

use northwind go   select         productname,         unitprice,         (select avg(unitprice) from dbo.products) as average,         unitprice - (select avg(unitprice) from dbo.products) as diff from dbo.products   위 결과와 동일하게, dbo.products라는 테이블을 한번만 읽고 동일한 결과를 만들어 낼수 있을까요? 위 쿼리 경우 dbo.products 테이블이 3번 스캔을 하잖아요? 뭐~ 두번째 부터는 메모리에서 가져온다고 하더라도요... 두번까지는 줄일수 있을것 같은데, 한번 테이블 스캔하고, 위 내용을 만들어 낸다는게....가능할지? 아~ 그리고, SQL도 한방으로 처리해야 합니다.

 

 

1) Products 테이블 3회 읽기

 

SELECT

        productname,

        unitprice,

        (SELECT avg(unitprice) FROM dbo.products) AS average,

        unitprice - (SELECT avg(unitprice) FROM dbo.products) AS diff

FROM dbo.products

 

--테이블 'Products'. 검색 수 3, 논리적 읽기 수 6, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

 

 

 

2) Products 테이블 2회 읽기

 

 

SELECT

        productname,

        unitprice,

        average,

                           (unitprice - average) AS diff

FROM dbo.products

             join (SELECT avg(unitprice) AS average FROM dbo.products) b on 1=1

 

--테이블 'Products'. 검색 수 2, 논리적 읽기 수 4, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

 

 

 

3) Products 테이블 1회 읽기 (SQL Server 2005 이상)

 

SELECT

        productname,

        unitprice,        

        (avg(unitprice) OVER())  AS average,

                           unitprice - (avg(unitprice) OVER()) AS diff       

FROM dbo.products

 

--테이블 'Worktable'. 검색 수 3, 논리적 읽기 수 159, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

--테이블 'Products'. 검색 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

 

 

차주언 효율은 3번이(일괄처리와 비교 56% 44%) IO는 2번이 좋은건가요? 2008/01/18

김대춘 3번의 경우에는 본 테이블에 대해서는 한번의 스캔만 이루어지지만 Table Spool이 3번이 발생했군요..대용량 테이블인 경우에는 좀 문제가 생기지 않을까요?... 2008/01/22

신익준 3번은 좀 문제가 있어 보이네용...Worktable 을 만드는것 자체가 크... 2008/05/14

석이 음 이런게 한방의 환상 아닐까 라고 생각 합니다. 경우에 따라 다 틀리겠지만 저라면 이런 경우에는 과감히 2번 full scan 을 선택 하겠습니다. declare @avg 라고 한뒤 한번 @avg 를 구하고 나중에 full select 하면서 상수 비교만 해서 구하겠습니다. 이것보다 더 빠른 방법이 있을까요? 다 이것을 구현하기 위한 옵티마이져의 장난질 일 뿐이지 않을까요? 수학적으로 불가능하지 않나요? 2008/08/12

ngal 신 역시 생각을 하게 하는 내용입니다. 감사합니다. 2008/09/09