Aggregation Operators Aggregation operators are not operators of relational algebra. Rather,they apply to entire columns of a table and produce a single result. The most important examples:SUM, AVG,COUNT,MIN,and MAX
Aggregation Operators Aggregation operators are not operators of relational algebra. Rather, they apply to entire columns of a table and produce a single result. The most important examples: SUM, AVG, COUNT, MIN, and MAX
Example:Aggregation R= A B 3 4 3 2 SUM(A)=7 COUNT(A)=3 MAX(B)=4 AVG(B)=3
Example: Aggregation R = A B 1 3 3 4 3 2 SUM(A) = 7 COUNT(A) = 3 MAX(B) = 4 AVG(B) = 3
Grouping Operator Y(R)where L is a list of elements that are either 1. Individual(grouping)attributes or 2. Of the form 0(A),where 0 is an aggregation operator and A the attribute to which it is applied,computed by: Grouping R according to all the grouping attributes on list L. Within each group,compute 0(A),for each elementθ(A)on list L Result is the relation whose column consist of one tuple for each group.The components of that tuple are the values associated with each element of L for that group
Grouping Operator ϒ L(R ) where L is a list of elements that are either 1. Individual (grouping) attributes or 2. Of the form θ(A), where θ is an aggregation operator and A the attribute to which it is applied, computed by: Grouping R according to all the grouping attributes on list L. Within each group, compute θ(A), for each element θ(A) on list L Result is the relation whose column consist of one tuple for each group. The components of that tuple are the values associated with each element of L for that group
Example: compute Ybeer,AVG(price)(R) R= Bar Beer price Joe's Bud 2.00 Joe's Miller 2.75 Sue's Bud 2.5 Sue's Coors 3.00 Mel's Miller 3.25 1.Group by the grouping attributes,beer in this case: Bar Beer price Joe's Bud 2.00 Sue's Bud 2.5 Joe's Miller 2.75 Mel's Miller 3.25 Sue's Coors 3.00
Example: compute γbeer, AVG(price)(R ) R= Bar Beer price Joe’s Bud 2.00 Joe’s Miller 2.75 Sue’s Bud 2.5 Sue’s Coors 3.00 Mel’s Miller 3.25 1. Group by the grouping attributes, beer in this case: Bar Beer price Joe’s Bud 2.00 Sue’s Bud 2.5 Joe’s Miller 2.75 Mel’s Miller 3.25 Sue’s Coors 3.00
Example (cont.) 2.Computer average of price with groups: Beer AVG (price) Ybeer, Bud 2.25 AVG(price)(R Miller 3.00 Coors 3.00
Example (cont.) 2. Computer average of price with groups: Beer AVG (price) Bud 2.25 Miller 3.00 Coors 3.00 γbeer, AVG(price)(R )