Extended Aggregation (Cont.) Multiple rollups and cubes can be used in a single group by clause Each generates set of group by lists,cross product of sets gives overall set of group by lists E.g., select item-name,color,size,sum(number) from sales group by rollup(item-name),rollup(color,size) generates the groupings (item-name,())X f(color,size),(color),() ={(item-name,color,size),(item-name,color),(item-name) (color,size),(color),() Database System Concepts-5th Edition,Aug 26,2005 18.17 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 18.17 ©Silberschatz, Korth and Sudarshan th Edition, Aug 26, 2005 Extended Aggregation (Cont.) Multiple rollups and cubes can be used in a single group by clause Each generates set of group by lists, cross product of sets gives overall set of group by lists E.g., select item-name, color, size, sum(number) from sales group by rollup(item-name), rollup(color, size) generates the groupings {item-name, ()} X {(color, size), (color), ()} = { (item-name, color, size), (item-name, color), (item-name), (color, size), (color), ( ) }
Ranking Ranking is done in conjunction with an order by specification. Given a relation student-marks(student-id,marks)find the rank of each student. select student-id,rank()over (order by marks desc)as s-rank from student-marks An extra order by clause is needed to get them in sorted order select student-id,rank (over (order by marks desc)as s-rank from student-marks order by s-rank Ranking may leave gaps:e.g.if 2 students have the same top mark,both have rank 1,and the next rank is 3 dense_rank does not leave gaps,so next dense rank would be 2 Database System Concepts-5th Edition,Aug 26,2005 18.18 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 18.18 ©Silberschatz, Korth and Sudarshan th Edition, Aug 26, 2005 Ranking Ranking is done in conjunction with an order by specification. Given a relation student-marks(student-id, marks) find the rank of each student. select student-id, rank( ) over (order by marks desc) as s-rank from student-marks An extra order by clause is needed to get them in sorted order select student-id, rank ( ) over (order by marks desc) as s-rank from student-marks order by s-rank Ranking may leave gaps: e.g. if 2 students have the same top mark, both have rank 1, and the next rank is 3 dense_rank does not leave gaps, so next dense rank would be 2