순위 및 행 집합 함수 사용
순위 및 행 집합 함수는 단일 값을 반환하지 않으므로 스칼라 함수가 아니며, 이러한 함수는 행 집합을 입력으로 허용하고 행 집합을 출력으로 반환합니다.
순위 함수
순위 함수를 사용하면 사용자 정의 행 집합에 대해 계산을 수행할 수 있습니다. 순위 함수에는 순위, 오프셋, 집계, 분포 함수가 포함됩니다.
이 예제에서는 RANK 함수를 사용하여 가장 높은 가격이 1로 순위가 매겨진 ListPrice를 기반으로 순위를 계산합니다.
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
쿼리 결과는 다음과 같을 수 있습니다.
ProductID
Name
ListPrice
RankByPrice
749
Road-150 Red, 62
3578.27
1
750
Road-150 Red, 44
3578.27
1
751
Road-150 Red, 48
3578.27
1
771
Mountain-100 Silver, 38
3399.99
4
772
Mountain-100 Silver, 42
3399.99
4
775
Mountain-100 Black, 38
3374.99
6
...
...
...
...
OVER
OVER 절을 사용하여 파티션 또는 데이터 내 그룹화를 정의할 수 있습니다. 예를 들어 다음 쿼리는 이전 예제를 확장하여 각 범주 내 제품에 대한 가격을 기반으로 순위를 계산합니다.
SELECT c.Name AS Category, p.Name AS Product, ListPrice,
RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
JOIN Production.ProductCategory AS c
ON p.ProductCategoryID = c.ProductcategoryID
ORDER BY Category, RankByPrice;
이 쿼리의 결과는 다음과 같을 수 있습니다.
범주
제품
ListPrice
RankByPrice
빕 쇼츠
Men’s Bib-Shorts, S
89.99
1
빕 쇼츠
Men’s Bib-Shorts, M
89.99
1
자전거 랙
Hitch Rack - 4-Bike
120
1
자전거 스탠드
All-Purpose Bike Stand
159
1
물병 및 거치대
Mountain Bottle Cage
9.99
1
물병 및 거치대
Road Bottle Cage
8.99
2
물병 및 거치대
Water Bottle – 30 oz.
4.99
3
바텀 브라켓
HL Bottom Bracket
121.49
1
바텀 브라켓
ML Bottom Bracket
101.24
2
바텀 브라켓
LL Bottom Bracket
53.99
3
...
...
...
...
참고
여러 행의 순위 값이 동일하고 일부 값은 건너뜁니다. RANK만 사용하기 때문입니다. 요구 사항에 따라 동일한 순위 값에서 동률을 방지할 수 있습니다. 필요에 따라 다른 함수, DENSE_RANK, NTILE, ROW_NUMBER를 사용하여 순위 값을 제어할 수 있습니다. 이러한 함수에 대한 자세한 내용은 Transact-SQL 참조 설명서를 참조하세요.
행 집합 함수
행 집합 함수는 FROM 절에서 데이터 원본으로 사용할 수 있는 가상 테이블을 반환합니다. 행 집합 함수는 행 집합 함수 자체와 관련된 매개 변수를 사용합니다. 행 집합 함수에는 OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML, OPENJSON이 포함됩니다.
OPENDATASOURCE, OPENQUERY, OPENROWSET 함수를 사용하면 원격 데이터베이스 서버에 쿼리를 전달할 수 있습니다. 그러면 원격 서버에서 결과 행 집합을 반환합니다. 예를 들어 다음 쿼리는 OPENROWSET를 사용하여 SalesDB라는 SQL Server 인스턴스에서 쿼리 결과를 가져옵니다.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
원격 서버를 사용하려면 쿼리를 실행하는 SQL Server 인스턴스에서 몇 가지 고급 옵션을 사용하도록 설정해야 합니다.
OPENXML 및 OPENJSON 함수를 사용하면 XML 또는 JSON 형식의 정형 데이터를 쿼리하고 테이블 형식 행 집합으로 값을 추출할 수 있습니다.
행 집합 함수에 대한 자세한 내용은 이 모듈에서 다루지 않습니다. 자세한 내용은 Transact-SQL 참조 설명서를 참조하세요.