Grouping Grouping intuitively means to partition a relation into - PDF document

Grouping • Grouping intuitively means to partition a relation into several groups, based on the value of some Database Usage attribute(s). (and Construction) – ”All courses with this teacher go in this group, all courses with that teacher go in that group, …” • Each group is a sub-relation, and aggregations More SQL Queries and Relational Algebra can be computed over them. • Within each group, all rows have the same value for the attribute(s) grouped on, and therefore we can project that value as well! Grouping Example: List the average number of students that each teacher has on his or her courses. • Grouping = given a relation R, a set of attributes SELECT teacher, course per teacher nrSt. X, and a set of aggregation expressions G; AVG(nrStudents) TDA357 4 Rogardt Heldal 130 SQL? partition R into groups R 1 …R n such that all rows FROM GivenCourses TDA590 2 Rogardt Heldal 70 GROUP BY teacher; TIN090 1 Devdatt Dubhashi 62 in R i have the same value on all attributes in X, and project X and G for each group. teacher AVG(nrSt.) SELECT X,G � X,G ( R ) Result? Rogardt Heldal 100 FROM R Devdatt Dubhashi 62 GROUP BY X; – ”For each X, compute G” � teacher, AVG(nrStudents) ( GivenCourses ) – � = gamma = greek letter g = g rouping Relational Algebra? Summary – grouping and Specialized renaming of attributes aggregation • General renaming operator, rename R to • Aggregation functions: MAX, MIN, COUNT, A and its attributes to X : AVG, SUM – Compute a single value over a whole relation, or a partition of a ρ A(X) ( R ) relation (i.e. a group). – If no grouping attributes are given, the aggregation affects the • More convenient alternative for grouping, whole relation (and no ordinary attributes can be projected). rename the result of expression G to B: • Can’t put aggregation directly in the WHERE clause (since it’s not a function on values). � X,G→B ( R ) • Can’t mix aggregation and normal projection! � teacher, AVG(nrStudents)→avgStudents ( GivenCourses ) – e.g. – If an aggregation function is used in the SELECT clause, then the only other things that may be used there are other – Works in normal projection ( π ) as well. aggregation functions, and attributes that are grouped on. 1

Tests on groups Quiz! • Aggregations can’t be put in the WHERE clause List all teachers who have an average – they’re not functions on rows but on groups. number of students of >100 in their • Sometimes we want to perform tests on the courses. result of an aggregation. – Example: List all teachers who have an average SELECT teacher number of students of >100 in their courses. FROM GivenCourses • SQL allows us to put such tests in a special GROUP BY teacher HAVING clause after GROUP BY. HAVING AVG(nrStudents) > 100; Example Quiz! • There is no correspondence in relational SELECT teacher FROM GivenCourses algebra to the HAVING clause of SQL. GROUP BY teacher Why? HAVING AVG(nrStudents) > 100; – Because we can express it with an extra code period teacher #students AVG(nrSt.) renaming and a selection. Example: TDA357 2 Niklas Broberg 130 130 SELECT teacher TIN090 1 Devdatt Dubhashi 95 95 FROM GivenCourses GROUP BY teacher TDA357 4 Rogardt Heldal 135 102.5 HAVING AVG(nrStudents) > 100; TDA590 2 Rogardt Heldal 70 σ avgSt > 100 ( � teacher, AVG(nrStudents) as avgSt ( GivenCourses )) Sorting relations Example • Relations are unordered by default. SELECT * FROM Courses • Operations could potentially change any existing ORDER BY name; ordering. � X ( R ) ORDER BY X [ASC] code name – Sort relation R on attributes X. TIN090 Algorithms – Ordering only makes sense at the top level, or if only TDA357 Databases a given number of rows are sought, e.g. the top 5. TDA590 OOSD – Oracle: Use the implicit attribute rownum to limit how many rows should be used. • � = tau = greek letter t = sor t (s is taken) 2

SELECT-FROM-WHERE- Example: GROUPBY-HAVING-ORDERBY SELECT name, AVG(nrStudents) AS avSt FROM Courses, GivenCourses • Full structure of an SQL query: WHERE code = course GROUP BY code, name SELECT attributes Only the SELECT HAVING AVG(nrStudents) > 100 FROM tables and FROM clauses ORDER BY avSt; WHERE tests over rows must be included. GivenCourses GROUP BY attributes Courses course per teacher nrSt HAVING tests over groups code name TDA357 2 Niklas Broberg 130 ORDER BY attributes TDA357 Databases TDA357 4 Rogardt Heldal 95 SELECT X,G TIN090 Algorithms TIN090 1 Devdatt Dubhashi 62 FROM T � Z’ (π X,G’ (σ D’ ( � Y,G’ (σ C (T))))) WHERE C � avSt (π name, avSt (σ avSt > 100 GROUP BY Y ( � code, name, AVG(nrStudents) � avSt HAVING D X must be a subset of Y. ORDER BY Z; Primes ’ mean we need some renaming. (σ code = course ( Courses x GivenCourses ))))) Example: Example: SELECT name, AVG(nrStudents) AS avSt SELECT name, AVG(nrStudents) AS avSt FROM Courses, GivenCourses FROM Courses, GivenCourses WHERE code = course WHERE code = course GROUP BY code, name GROUP BY code, name HAVING AVG(nrStudents) > 100 HAVING AVG(nrStudents) > 100 ORDER BY avSt; ORDER BY avSt; code name course per teacher nrSt code name course per teacher nrSt TDA357 Databases TDA357 2 Niklas Broberg 130 TDA357 Databases TDA357 2 Niklas Broberg 130 TDA357 Databases TDA357 4 Rogardt Heldal 95 TDA357 Databases TDA357 4 Rogardt Heldal 95 TDA357 Databases TIN090 1 Devdatt Dubhashi 62 TDA357 Databases TIN090 1 Devdatt Dubhashi 62 TIN090 Algorithms TDA357 2 Niklas Broberg 130 code name course per teacher nrSt TIN090 Algorithms TDA357 2 Niklas Broberg 130 TIN090 Algorithms TDA357 4 Rogardt Heldal 95 TIN090 Algorithms TDA357 4 Rogardt Heldal 95 TDA357 Databases TDA357 2 Niklas Broberg 130 TIN090 Algorithms TIN090 1 Devdatt Dubhashi 62 TDA357 Databases TDA357 4 Rogardt Heldal 95 TIN090 Algorithms TIN090 1 Devdatt Dubhashi 62 TIN090 Algorithms TIN090 1 Devdatt Dubhashi 62 � avSt (π name,avSt (σ avSt>100 ( � code,name,AVG(nrStudents) � avSt (σ code=course ( Courses x GivenCourses ))))) � avSt (π name,avSt (σ avSt>100 ( � code,name,AVG(nrStudents) � avSt ( σ ( Courses x GivenCourses ) σ code=course σ σ code=course ( ( ( ) ) )))) ) code=course code=course Example: Example: SELECT name, AVG(nrStudents) AS avSt SELECT name, AVG(nrStudents) AS avSt FROM Courses, GivenCourses FROM Courses, GivenCourses WHERE code = course WHERE code = course GROUP BY code, name GROUP BY code, name HAVING AVG(nrStudents) > 100 HAVING AVG(nrStudents) > 100 ORDER BY avSt; ORDER BY avSt; AVG(nrSt) code name course per teacher nrSt code name AVG(nrSt) TDA357 Databases TDA357 2 Niklas Broberg 130 112.5 TDA357 Databases 112.5 TDA357 Databases TDA357 4 Rogardt Heldal 95 TIN090 Algorithms 62 TIN090 Algorithms TIN090 1 Devdatt Dubhashi 62 62 code name AVG(nrSt) code name AVG(nrSt) TDA357 Databases 112.5 TDA357 Databases 112.5 TIN090 Algorithms 62 � avSt (π name,avSt ( σ ( � code,name,AVG(nrStudents) � avSt (σ code=course ( Courses x GivenCourses )) ) � avSt (π name,avSt (σ avSt>100 ( � code,name,AVG(nrStudents) � avSt ( σ avSt>100 σ σ avSt>100 ( ( ( ) ) ) )) avSt>100 avSt>100 ( σ code=course ( Courses x GivenCourses ) ) ( ( ) ))) ) ) 3