Programming/Microsoft SQL Server
1. 일반집계함수

(1) Avg

Select Avg(SickLeaveHours)
From HumanResources.Employee
▶HumanResources.Employee Table의 SickLeaveHours값 평균을 구합니다.


(2) Count, Count(*)

Select Count(EndDate)
From HumanResources.EmployeeDepartmentHistory
▶HumanResources.EmployeeDepartmentHistory Table의 EndDate열을 기준으로 총 행의 갯수를 구합니다. 이때 Null은 포함하지 않습니다.


Select Count(*)
From HumanResources.EmployeeDepartmentHistory
▶ HumanResources.EmployeeDepartmentHistory Table의 모든열을 기준으로 총 행의 갯수를 구합니다. 이때 Null도 포함하여 같이 집계합니다.


(3) Max, Min

Select Max(TerritoryID)
From Person.StateProvin
▶ Person.StateProvince Table의 TerritoryID열값중 가장 큰값을 표시합니다.


Select Max(StateProvinceCode)
From Person.StateProvince
Select Max(ModifiedDate)
From Person.StateProvinc
▶ Max집계대상은 숫자뿐 아니라 문자나 시간도 가능합니다.


Select Min(TerritoryID)
From Person.StateProvince
▶ Min은 Max와 반대로 최소값을 구합니다.


2. 요약함수

(1) Top

Select Top(10) *
From Production.BillOfMaterials
▶ Production.BillOfMaterials Table의 모든 열을 대상으로 상위 10번째 행을 가져옵니다.


참고:
Top과 비슷한 식으로 Set Rowcount라는 것이 있습니다. 이것도 행을 제한한다는 의미에서는 Top과 비슷하지만 Set Rowcount를 한번 지정해 놓으면 현재 Query가 실행되고 있는 세션에 한하여 모두 적용이 되고 Top은 실행될때의 Query에만 적용된다는 차이가 있습니다.(Set Rowcount가 Top기능을 구현하기 위해 등장한 것은 아닙니다.)

Set RowCount 10
Select *
From Production.BillOfMaterials
Select *
From Production.Document
▶ 맨위 'Set RowCount 10' 절로 행을 제한하였습니다. 이로 인해 같은 실행세션에 포함되는 모든 Query의 적용행을 10행으로 제한합니다.


Set RowCount에 의해 제한된 행을 풀고자 한다면 Set RowCount 0 으로 지정하시면 됩니다.

Select Top 10 Percent *
From Production.BillOfMaterials
Order By BillOfMaterialsID
▶ Production.BillOfMaterials Table에서 BillOfMaterialsID열값을 기준으로 상위 10%에 해당하는 행을 가져옵니다.


주의:
Top 구문사용시 굳이 Order By를 사용하지 않아도 Query를 작동하는데에는 문제가 없으나 이럴경우 집계되는 행이 일정한 순으로 정렬되어 있지 않은 상태이기 때문에 Top구문을 사용해 데이터를 집계하는 것 자체가 의미가 없을 수도 있습니다.

따라서 정확한 순으로 행을 집계하고자 하실경우 Order By를 통해 정렬이 되는 기준열을 지정하여 조회하는 것이 좋습니다.

 

Top에는 하위쿼리도 사용할 수 있으므로 위 예제는 아래 쿼리 결과와 일치합니다.

 

Select Top(Select COUNT(*) / 100 From Production.BillOfMaterials) *
From Production.BillOfMaterials
Order By BillOfMaterialsID

 

Select Top 10 Percent BillOfMaterialsID
From Production.BillOfMaterials
▶ Production.BillOfMaterials Table에서 BillOfMaterialsID열값을 기준으로 상위 10%에 해당하는 행을 가져오되 출력되는 열은 BillOfMaterialsID열로 제한합니다.


Select Top 1 Percent *
From Production.BillOfMaterials
Order By ComponentID
▶ Production.BillOfMaterials Table의 ComponentID열값을 기준으로 상위 1%에 해당하는 데이터를 가져옵니다.


Query결과를 보시면 맨 마지막 3행의 ComponentID열값이 모두 322라고 되어 있습니다. 그런데 실제 ComponentID가 322인 행은 총 4행입니다.

Select *
From Production.BillOfMaterials
Where ComponentID = 322


같은 값인데도 출력되지 못한 행이 존재하는 것입니다.

이렇게 되는 이유는 출력되는 행을 1%로 제한하다보니 값으로 1%범위에 포함되어 있다 하더라도 행(Row)수에서 뒤로 밀려 1%행에 포함되지 못하는 경우가 생기기 때문입니다.

이러한 문제점을 방지하려면 With Ties 구문을 사용하여야 합니다.

Select Top 1 Percent With Ties *
From Production.BillOfMaterials
Order By ComponentID
▶ 값으로 1%범위에 해당하는 모든 행을 가져옵니다.


주의:
Top구문만을 사용시에는 굳이 Order By를 지정하지 않아도 Query가 작동된다고 말씀 드렸으나 With Ties사용시에는 반드시 Order By가 지정되야 합니다. 그래야 순서대로 1%범위안에 드는가를 판단할 수 있기 때문입니다.

(2) Group By

Select SafetyStockLevel, Sum(ListPrice)
From Production.Product
Group By SafetyStockLevel
▶ Production.Product Table의 ListPrice값의 합계와 SafetyStockLevel열값을 같이 출력합니다.


Select Sum(ListPrice) From Production.Product 라고만 조회하는 경우 전체행을 대상으로 ListPrice열값을 더한 값만 보여줄 것입니다. 이때 Sum이나 기타 다른 집계함수를 사용시 집계항목이 아닌 열을 조회항목에 포함시키는 경우 그 열을 기준으로 집계데이터를 생성하도록 Group By를 사용해 합니다.

Select ProductID, Sum(ListPrice)
From Production.ProductListPriceHistory
Where ProductID In (707, 708)
Group By ProductID
▶ Production.ProductListPriceHistory Table에서 ProductID가 707이나 708인것만을 기준으로 ListPrice의 합계를 구합니다.


만약 ProductID값이 집계대상에 포함되지 않고 ProductID열항목을 모두 표시해야 하는 상황이라면 All구문을 사용하십시오.

Select ProductID, Sum(ListPrice)
From Production.ProductListPriceHistory
Where ProductID In (707, 708)
Group By All ProductID


Select ProductID, Sum(ListPrice)
From Production.ProductListPriceHistory
Group By ProductID
Having Sum(ListPrice) > 100
▶ Production.ProductListPriceHistory Table의 ProductID열을 기준으로 ListPrice값의 합계를 구하되 합계한 결과가 100이상인 것만 표시합니다.


참고:
Having은 집계결과에 관한 조건문에 해당하는 것으로 Where에서 조건을 지정하는 것과는 다릅니다.
Where에서는 집계함수 자체를 조건으로 사용하는것은 불가능할 뿐더러 Where ListPrice > 100 처럼 조건을 지정한다 하더라도 Sum통해 집계한 결과가 아닌 ListPrice값이 100이상인 것만을 조회대상으로 하게 되므로 Having과 같은 효과를 낼 수 없습니다.

(3) Rollup

Select ReferenceOrderLineID, Sum(ActualCost)
From Production.TransactionHistoryArchive
Group By ReferenceOrderLineID With Rollup
▶ Production.TransactionHistoryArchive Table의 ReferenceOrderLineID를 기준으로 ActualCost값의 합계와 ReferenceOrderLineID별 전체합계를 구합니다.


Select ReferenceOrderLineID, ReferenceOrderID, Sum(ActualCost)
From Production.TransactionHistoryArchive
Group By ReferenceOrderLineID, ReferenceOrderID With Rollup
▶ 집계기준열이 두개 이상일 경우에는 왼쪽열 기준으로 오른족 각열의 부분합을 구하게 됩니다.


(4) Cube

Select ReferenceOrderLineID, ReferenceOrderID, Sum(ActualCost)
From Production.TransactionHistoryArchive
Group By ReferenceOrderLineID, ReferenceOrderID With Cube
▶ Rollup의 경우 왼쪽부터 오른쪽 기준열순으로 각 부분합을 구하지만 Cube는 모든 열의 부분합을 구합니다.



참고:
Rollup이나 Cube를 사용시에 어떤행이 Rollup이나 Cube에 의해 집계된 행인지 출력 결과에 명시적으로 표시하고자 하시려면 Grouping구문을 이용합니다.

Select ReferenceOrderLineID, ReferenceOrderID, Sum(ActualCost),
 Grouping(ReferenceOrderLineID) As 'OrderLine별',
 Grouping(ReferenceOrderID) As 'Order별'
From Production.TransactionHistoryArchive
Group By ReferenceOrderLineID, ReferenceOrderID With Rollup
Order By ReferenceOrderLineID, ReferenceOrderID


Grouping에서 1로 표시된 것이 해당 열별로 집계처리된 행이라는 것을 의미합니다.

(5) Compute, Compute By

Select ProductID, OrderQty, StockedQty, ScrappedQty
From Production.WorkOrder
Order By ProductID
Compute Sum(OrderQty), Sum(StockedQty), Sum(ScrappedQty)
▶ Production.WorkOrder Table에서 OrderQty, StockedQty, ScrappedQty 열에 대한 합계를 별도로 표시하도록 합니다.


Select ProductID, StandardPrice, LastReceiptCost
From Purchasing.ProductVendor
Where ProductID BetWeen 452 And 454
Order By ProductID
Compute Sum(StandardPrice), Sum(LastReceiptCost) By ProductID
▶ Purchasing.ProductVendor Table에서 StandardPrice, LastReceiptCost 열의 합계를 ProductID별로 구합니다. 이때 ProductID열값이 바뀔때마다 그 값에 해당하는 전체합계를 구해서 별도로 출력하게 되는데 이러한 특징이 모든 행의 전체합계를 구하는 Compute구문과 다른점입니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] XQuery로 XML Data에 접근하기  (0) 2010.08.09
[SQL] Identity  (0) 2010.07.20
[SQL] Data 집계하기  (0) 2010.07.19
[SQL] 0x84B10001 오류  (0) 2010.06.09
[SQL] Link Server의 Data조회 관련 오류  (0) 2010.06.02
우편번호 DB (MSSQL)  (0) 2010.05.07
0 0