728x90
순위 함수의 부재로 인해 MSSQL2000에서는 실행될 수 없었지만 MSSQL2005에서 가능해진 기능을 소개하고자 합니다.

Transact-SQL은 다음과 같은 순위 함수를 제공합니다.

 
RANK NTILE
DENSE_RANK ROW_NUMBER

순위함수의 내용은 http://msdn2.microsoft.com/ko-kr/library/ms189798.aspx 링크의 온라인북을 참고하셔도 됩니다.

MSSQL2000에서는 오라클에서 쓰는 ROW_NUMBER() 같은 함수를 쓸 수 없어 유저들의 불만이 많았습니다. 다음과 같이 동적으로 카운트를 하는 서브쿼리를 통하여 비슷하게 구현할 수 있었습니다. 다음의 예제를 통해서 MSSQL2000에서 여러 단계를 거쳐야 했던 방법을 MSSQL2005에서 한 문장으로 해결되는 예제를 소개합니다.

 
create table TEST1 (user_id char(3),num int)

insert TEST1 values('aaa',3)
insert TEST1 values('aaa',2)
insert TEST1 values('bbb',15)
insert TEST1 values('bbb',2)
insert TEST1 values('aaa',6)
insert TEST1 values('aaa',4)
insert TEST1 values('aaa',3)
리스트 1 기초 데이터 입력

구분되는 일련번호가 없는 데이터를 다음과 같은 결과물을 얻으려면 어떻게 해야 되는지에 대한 문제입니다. 데이터는 user_id로 구분이 되어 있고 구분이 없는 num 값으로 구성되어 있습니다.

 
user_id   num1        num2        num3        num4        num5       
------- ----------- ----------- ----------- ----------- ----------
aaa         3             2              6              4               3
bbb        15            2             NULL          NULL           NULL
리스트 2 원하는 결과

MSSQL 2000을 이용할 경우

리스트 2 의 결과 처럼 user_id 별로 num1~num5 컬럼으로 나누어 데이터를 가져오는 것입니다. 각 로우를 구분하기 위해 indentity()함수를 이용해서 임시테이블에 id 라는 일련번호 컬럼을 만듭니다.

 
--------------------------------------------------------
-- 일련번호생성하여 #temp_1 만들기

select identity(int,1,1) id,* into #temp_1 from TEST1
리스트 3 일련번호 생성하기 위해 identity()함수를 이용한 임시테이블 만들기

각 로우가 구분되는 id라는 컬럼이 만들어 졌으므로, user_id 별로 일련번호를 Group by및 case 문을 사용하여 원하는 결과를 쿼리합니다.

 
select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select (select count(*)
 
from #temp_1
where user_id = a. user_id
and id <= a.id) id,a.user_id,num
from #temp_1 a) t1
group by user_id
리스트 4 MSSQL2000의 임시테이블을 이용한 쿼리

 
/* user_id     num1    num2    num3    num4     num5
------- ----------- ----------- ----------- ----------- ----------
aaa             3           2          6           4           3
bbb           15          2        NULL       NULL       NULL

(2개행적용됨)
*/
리스트 5 MSSQL2000의 임시테이블을 이용한 쿼리 결과

MSSQL 2005 ROW_NUMBER( ) OVER 절 이용

ROW_NUMBER ( )  구문

 
ROW_NUMBER ( ) OVER
( [ <partition_by_clause> ] <order_by_clause> )
리스트 6 ROW_NUMBER ( )  구문

인수

<partition_by_clause>
 
from 절이 생성한 결과 집합을 ROW_NUMBER 함수가 적용되는 파티션으로 나눕니다.

<order_by_clause>
 
파티션에서 ROW_NUMBER 값이 행에 할당되는 순서를 결정합니다. 자세한 내용은 ORDER BY 절(Transact-SQL)를 참조하십시오. 순위 함수에 <order_by_clause>가 사용된 경우 정수는 열을 나타낼 수 없습니다.

MSSQL2000의 쿼리에서는 원하는 결과를 위해서 각 로우를 구분하는 id열을 가진 임시테이블을 만들고 또한, 구분되어진 id 열을 user_id에 따라 서브쿼리를 하고, count 하여야 원하는 결과를 구할수 있었습니다. MSSQL 2005에서 등장한 순위 함수를 이용하면, 다음과 같은 한 문장으로 원하는 결과를 구할수 있습니다.

 
select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select row_number()
 
over (partition by user_id order by user_id) id,* 
from TEST1) t1
group by user_id
리스트 7 MSSQL2005의 row_number() 함수를 이용한 쿼리

 
/*
user_id                                                
------- ----------- ----------- ----------- ----------- --------
aaa     3           2           6           4           3
bbb    15         2         NULL      NULL      NULL
경고: 집계또는다른SET 연산에의해NULL 값이제거되었습니다.

(2개행적용됨)
*/
리스트 8 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과

리스트 9 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과
리스트 9 MSSQL2000과 MSSQL2005의 row_number() 함수를 이용한 쿼리 결과 실행계획

임시테이블을 생성하는 로직을 포함하지 않더라도, MSSQL2005의 순위 함수를 사용하는 것이 성능상 유리합니다.

 
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
select identity(int,1,1) id,* into #temp_1 from TEST1

SQL Server 구문분석및컴파일시간:
 
CPU 시간= 13ms, 경과시간= 13ms.
 
테이블'TEST1'. 검색수1, 논리적읽기수1, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

SQL Server 실행시간:
 
CPU 시간= 0ms, 경과시간= 13ms.
 

(7개행적용됨)

select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select (select count (*)
from #temp_1
where user_id = a.user_id
and id <= a.id) id,a. user_id ,num
from #temp_1 a) t1
group by user_id

user_id                                                
------- ----------- ----------- ----------- ----------- ----------
aaa     3           2           6           4           3
bbb    15         2         NULL       NULL       NULL

경고: 집계또는다른SET 연산에의해null 값이제거되었습니다.

(2개행적용됨)

테이블'#temp_1'. 검색수8, 논리적읽기수16, 물리적읽기수0, 미리읽기수0, LOB
논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기 수0.

SQL Server 실행시간:
 
CPU 시간= 0ms, 경과시간= 1ms.

select user_id,
 
max(case id when 1 then num else null end),
max(case id when 2 then num else null end),
max(case id when 3 then num else null end),
max(case id when 4 then num else null end),
max(case id when 5 then num else null end)
from (select row_number( )
over (partition by user_id order by user_id) id,*  from TEST1) t1
group by user_id

user_id                                                
------- ----------- ----------- ----------- ----------- ----------
aaa     3           2           6           4           3
bbb     15          2           null        null        null

경고: 집계또는다른SET 연산에의해null 값이제거되었습니다.

(2개행적용됨)

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

SQL Server 실행시간:
 
CPU 시간= 0ms, 경과시간= 1ms.
리스트 10 실행통계

이상과 같이 실행통계의 성능을 통하여 MSSQL 2005를 이용하면 성능상 더 좋고 간단한 방법을 알아보았습니다. 다른 랭크 함수 (RANK,NTITLE,DENSE_RANK) 들의 이용과 응용은 여러분의 몫입니다. 

[출처] 제 2강 : MSSQL2005에 등장한 각종 순위 함수 |작성자 윈마스터

'데이터베이스 > SQL Server' 카테고리의 다른 글

Oracle의 connect by 를 Mssql2005 의 CTE(WITH common_table_expression)로 변환  (0) 2008.04.28
MSSQL2005 사용자 계정추가 방법  (0) 2008.04.28
인덱스 다시 구성 및 다시 작성 mssql2005  (0) 2008.04.28
[MSSQL2005] CTE(공통 테이블 식)  (0) 2008.04.28
[MSSQL2005] 스키마테이블을 이용하여 테이블 정보 알아내기  (0) 2008.04.28
728x90

조각화 검색

다음 예에서는 sys.dm_db_index_physical_stats 동적 관리 함수를 쿼리하여 Production.Product 테이블의 모든 인덱스에 대한 평균 조각화를 반환합니다. 이전 테이블을 사용하여 PK_Product_ProductID를 다시 구성하고 다른 인덱스를 다시 작성하는 것이 좋습니다.

USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO



1. 인덱스 다시 작성

다음 예에서는 단일 인덱스를 다시 작성합니다.

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

2. 테이블의 모든 인덱스 다시 작성 및 옵션 지정

다음 예에서는 ALL 키워드를 지정합니다. 이 키워드는 테이블에 연결된 인덱스를 모두 다시 작성합니다. 3개의 옵션이 지정됩니다.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

3. 인덱스 다시 구성과 LOB 압축

다음 예에서는 단일 클러스터형 인덱스를 다시 구성합니다. 인덱스에 리프 수준의 LOB 데이터 형식이 포함되어 있으므로 해당 문은 LOB(Large Object) 개체 데이터를 포함하는 페이지도 모두 압축합니다. 기본값이 ON이기 때문에 WITH (LOB_Compaction) 옵션을 지정할 필요가 없습니다.

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

[출처] 인덱스 다시 구성 및 다시 작성 mssql2005 |작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

MSSQL2005 사용자 계정추가 방법  (0) 2008.04.28
MSSQL2005에 등장한 각종 순위 함수  (0) 2008.04.28
[MSSQL2005] CTE(공통 테이블 식)  (0) 2008.04.28
[MSSQL2005] 스키마테이블을 이용하여 테이블 정보 알아내기  (0) 2008.04.28
특정 기간(datetime형식)을 주어 조회할 경우 주의  (0) 2008.04.28
728x90

☞ CTE(Common Table Expression)란?

임시로 이름이 지정된 결과 집합이다.

하위에서 서브쿼리 형식으로 사용할 부분을 미리 정의해 놓은것이다.


☞ 구문

WITH CTE명(컬럼, 컬럼) AS
(
    CTE 쿼리 정의

)
CTE를 포함하여 실행할 쿼리


☞ 주의

CTE를 정의하면 쿼리에서 여러 차례 참조할 수 있다.

참조 여부와 상관없이 CTE 정의 다음에 외부 쿼리가 나와야 한다.

하나의 WITH절에 여러개의 CTE를 정의할수 있다. (컴마로 구분)

이전에 정의된 CTE를 참조하는 CTE를 정의할 수 있다.

그러나 이후에 정의된 CTE는 참조할 수 없다. (훔.... 예제에서 다시 설명하겠다!)


☞ 사용 예제

1. 한개의 CTE 정의

USE AdventureWorks;
GO

WITH DirReps_CTE(ManagerID, DirectReports) AS
(
    SELECT ManagerID, COUNT(*)
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports

FROM DirReps_CTE

WHERE ManagerID <= 50

ORDER BY ManagerID;


CTE를 정의하고 하위에서 검색조건, 정렬조건을 다시 지정하거나

데이터를 가공하여 조회 할 수 있다.

사용자 삽입 이미지

 
 
 




2. 두개 이상의 CTE 정의

USE AdventureWorks;
GO

WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
  SELECT EmployeeID, COUNT(*)
  FROM Purchasing.PurchaseOrderHeader

  GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
  SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
  FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE


EmpOrdersCTE : EmployeeID별 주문수 목록

MinMaxCTE : EmpOrdersCTE를 참조하여 최소, 최대 주문수와 차이


마지막 쿼리에서 MinMaxCTE를 참조하여 최소, 최대, 차이를 구할수 있다.


사용자 삽입 이미지
 



이전에 정의된 CTE는 하위에서 참조할 수 있지만 반대로 이후에 정의된 CTE는 참조할 수 없다.

예를 들어 WITH 문에 CTE1, CTE2, CTE3를 정의할때

CTE2는 CTE1과 CTE2를 참조할 수 있지만 CTE3는 참조할 수 없다.

다음 쿼리를 실행하믄 위와같은 이유로 오류가 발생한다.


WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
  SELECT EmployeeID, COUNT(*)
  FROM Purchasing.PurchaseOrderHeader

  GROUP BY EmployeeID
),

MinCTE(MN)
AS
(

 -- MinMaxCTE가 정의되기 전에 참조할수 없다.
  SELECT MN
  FROM MinMaxCTE
),
MinMaxCTE(MN, MX, Diff)
AS
(
  SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
  FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE


재귀쿼리나... 다양한 예제를 보려면 다음을 참조하자 (귀차니즘.. -_-)

참조 : http://msdn2.microsoft.com/ko-kr/library/ms175972.aspx

[출처] [MSSQL2005] CTE(공통 테이블 식)|작성자 필립박

'데이터베이스 > SQL Server' 카테고리의 다른 글

MSSQL2005에 등장한 각종 순위 함수  (0) 2008.04.28
인덱스 다시 구성 및 다시 작성 mssql2005  (0) 2008.04.28
[MSSQL2005] 스키마테이블을 이용하여 테이블 정보 알아내기  (0) 2008.04.28
특정 기간(datetime형식)을 주어 조회할 경우 주의  (0) 2008.04.28
[MSSQL2005] LIKE 검색시 [ ] 와일드카드사용하기  (0) 2008.04.28

+ Recent posts

티스토리툴바