Scalar Aggregation
Aggregation 은 큰 행집합을 하나의 작은 하나의 행집합으로 만드는 것을 말한다. 전형적인 Aggregate 펑션
은 count, min, max, sum avg 가 있다. SQL Server 는 STDEV 이나 VAR 도 지원한다.
나는 이 포스트를 여러개의 포스트로 나누어 쓸것이며, 이번 포스트에서는 "scalar aggregates" 를 다
룬다. Scalar aggregates 는 select list 에서 aggregate 펑션이 사용되고 group by 절이 없는 것을 말한다
Scalar aggregate 는 항상 하나의 단일행을 반환한다.
Scalar Aggregation
Scalar Aggregation 에 대한 하나의 연산자 : Stream aggregate 예제
select count(*) from t
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|--Table Scan(OBJECT:([t]))
이 Aggregation 은 언어에서 처음 학습하는 "Hello World" 와 같은 것이다. stream aggregation든 단지 입
력된 행수를 더하고 그 결과를 반환한다. 이 stream aggregate 는 count([Expr1005]) 를 하는데 bigint 형
으로 계산한다. compute scalar 연산은 int 형태의 output 형태를 만들기 위해 필요하다. 주의할 것은
scalar stream aggregate 연산은 빈 입력셋을 주더라도 하나의 아웃풋을 내는 유일한 연산자 이다. (아마도
유일한 예제이다. 지금 다른것은 생각나지 않는다.)
min max sum 과 같은, 다른 간단한 scalar aggregate funtion 들을 어떻게 쓰는지 쉽게 볼 수 있다. 또한
다중 scalar aggregate 를 한번에 계산 할 수도 있다.
select min(a), max(b) from t
|--Stream Aggregate(DEFINE:([Expr1004]=MIN([t].[a]), [Expr1005]=MAX([t].[b])))
|--Table Scan(OBJECT:([t]))
이 계획은 t 라는 테이블을 읽어 a 컬럼의 최소값 b 컬럼의 최대값을 찾는다. 주의깊게 볼 것은 min max
aggregate 들은 결과를 형변환 할 필요가 없다는 것이다. 이러한 aggregate 들은 컬럼 a b 의 기본 type들
에 기초해 계산된다.
avg 와 같은 aggregate들은 실제로는 sum 과 count 로 부터 계산 되어진다.
select avg(a) from t
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE
[Expr1006]/CONVERT_IMPLICIT(int,[Expr1005],0) END))
|--Stream Aggregate(DEFINE:([Expr1005]=COUNT_BIG([t].[a]), [Expr1006]=SUM([t].[a])))
|--Table Scan(OBJECT:([t]))
위 average 값을 계산하기 위해 sum 과 count 로 부터 계산되어진다. CASE 표현은 0으로 나누지 않기 위해
서 필요하다.
반면 sum 은 이렇게 각각 계산되어질 필요가 없으나, 역시 count 가 필요하다.
select sum(a) from t
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))
|--Stream Aggregate(DEFINE:([Expr1005]=COUNT_BIG([t].[a]), [Expr1006]=SUM([t].[a])))
|--Table Scan(OBJECT:([t]))
이 CASE 표현은 행이 없을때, 0 대신 null 을 표현하기 위해 필요하다.
Scalar Distinct
이제는 aggregate 에 DISTINCT 키워드를 추가하면 어떤일이 일어나는지 살펴보자.
select count(distinct a) from t
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
|--Stream Aggregate(DEFINE:([Expr1007]=COUNT([t].[a])))
|--Sort(DISTINCT ORDER BY:([t].[a] ASC))
|--Table Scan(OBJECT:([t]))
이 쿼리는 컬럼 a 의 unique 값만의 행수를 계산하는 것이다. sort 연산자를 사용해 컬럼 a 의 중복되는 값을 제외했다. sort 하면 중복된 값이 서로 인접해 있기 때문에 중복행을 제거하는 쉬운 방법이다.
distinct aggregate 연산들이 모두 중복값 제거가 필요한 것은 아니다. 예를들어, min max 는 distinct 가 있던 없던 상관 없다.
select min(distinct a), max(distinct b) from t
|--Stream Aggregate(DEFINE:([Expr1004]=MIN([t].[a]), [Expr1005]=MAX([t].[b])))
|--Table Scan(OBJECT:([t]))
만약, unique 인덱스가 있으면, 중복값 제거절차를 수행하지 않는다. 왜냐하면, unique 인덱스는 중복값이 없다는 것을 보장하기 때문이다.
create unique index ta on t(a)
select count(distinct a) from t
drop index t.ta
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
|--Stream Aggregate(DEFINE:([Expr1007]=COUNT([t].[a])))
|--Index Scan(OBJECT:([t].[ta]))
Multiple Distinct
아래 쿼리를 고려해 보자.
select count(distinct a), count(distinct b) from t
위에서 보았듯이 "count(distinct a)"는 컬럼 a 에서 중복값을 제거해 구한다. 유사하게 "count(distinct b)" 컬럼 b 의 중복값을 제거해서 구한다. 그러나, 두 집합이 다르다. 어떻게 동시에 구할수 있을까? 답은 못한다 이다. 첫번째 aggregate 결과를 구하고, 나머지를 구하고, 그런후 두개의 결과를 하나의 결과 row 에 결합한다.
|--Nested Loops(Inner Join)
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))
| |--Stream Aggregate(DEFINE:([Expr1010]=COUNT([t].[a])))
| |--Sort(DISTINCT ORDER BY:([t].[a] ASC))
| |--Table Scan(OBJECT:([t]))
|--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0)))
|--Stream Aggregate(DEFINE:([Expr1011]=COUNT([t].[b])))
|--Sort(DISTINCT ORDER BY:([t].[b] ASC))
|--Table Scan(OBJECT:([t]))
원래의 쿼리에서 구개의 count 값을 구해 nested loops join 을 수행한다. 입력중 하나는 컬럼 a 의 중복값을 제거하고 나머지는 컬럼 b 의 중복값을 제거한다. 이 nested loop join 은 join 조건이 없다. 이것은 cross join 이다. 왜냐하면 두개의 input rows 는 각각 하나의 결과를 만들고 그것의 cross join 결과 역시 단일 row 이다.
만약 두개이상의 distinct aggregate 연산이 다른 컬럼에 있으면, 하나 이상의 cross join 이 사용된다. 이러한 계획은 non-distinct 와 distinct aggregate 가 같이 섞여 있어도 사용된다. 이럴경우 cross join 의 한쪽에는 sort 가 없다.
다음은 group by 가 포함된 aggregation 이다.
by Craig Freedman