Further GroupBy & Extend Operations : Further GroupBy & Extend Operations Objectives of the Lecture : To consider “whole relation” Grouping;
To consider the SQL Grouping option Having;
To consider the Extend operator & its implementation in SQL.
Example : Whole Relation as a Group : Example : Whole Relation as a Group EMPLOYEE GroupBy[ ] With[ Total ? Bag[ Sal ] Sum ] Get the total salary bill. No attribute
Whole-Relation ‘GroupBy’ Procedure : Whole-Relation ‘GroupBy’ Procedure 1. Remove irrelevant attribute(s). AS USUAL.
2. Add an empty set of attributes to the operand as the grouping attributes. It does not alter the operand, since it is empty ! The empty ‘attribute’ has the same value (i.e. the empty set) in every tuple.Group on this attribute; this yields only one group.
3. Create one result tuple / group consisting of the grouping attribute.Thus there is one tuple initially with no attributes, i.e. an attribute containing the empty set.
4. Carry out aggregations. AS USUAL.This will add aggregation result attribute(s) to the one tuple.
SQL : Whole-Relation Group : SQL : Whole-Relation Group The SQL syntax for a whole-relation GroupBy is :
Select “aggregation” As Result-Name
From RELATION_NAME ; Repeat this foreach aggregation. The syntax of “aggregation“ remains unchanged.
Example : “Get the total salary bill”. (Previous example).
Select Sum( All Sal ) As TotalFrom EMPLOYEE ; The Group By phrase is omitted -no grouping attributes to put in it.
No grouping attributes inthe Select phrase.
After a GroupBy ? : After a GroupBy ? Sometimes it is useful to carry out another operation on the result of a GroupBy.
There is no need to do a Projection on the result since it can be incorporated into the GroupBy.
To do a Restriction on the result, SQL provides a Having phrase. This is because SQL’s built-in sequence of operations has already executed all Restrictions. There is no way to do another without introducing a special phrase, i.e. the Having phrase.
The Having restriction condition is limited to using :
a grouping attribute,
an aggregation results. Because the result of a GroupBycontains nothing but these.
Executing an SQL ‘Select’ Statement : Executing an SQL ‘Select’ Statement Order By Where Group By Having From Select Restrictions done here. AnotherRestrictionhere ! Grouping done here. The phrases are executed in the following order. Joins /CartesianProductsdone here. Sequencing done here. Projections done here.
‘Having’ Phrase : ‘Having’ Phrase The SQL syntax for a Group By with a Having phrase is :
Select GroupingAttribute(s), “aggregation” As Result_Name
From RELATION_NAMEGroup By GroupingAttribute(s)Having condition ; The Having phrasemust immediatelyfollow a Group Byphrase, and not beanywhere else. “condition” can contain only grouping attribute(s)and aggregate function applications in a Booleancombination of comparisons.
Example of the ‘Having’ Phrase (1) : Example of the ‘Having’ Phrase (1) “Get the total salary paid to each marital-status group, for groups where the total exceeds £50,000.” Select M-S, Sum( All Sal ) As Total From EMPLOYEEGroup By M-SHaving Sum( Sal ) > 50000 ; EMPLOYEE GroupBy[ M-S ] With[ Total ? Bag[ Sal ] Sum ] Restrict[ Total > 50000 ] A GroupBy followed by a Restriction. Cannot use the name Total.
Example of the ‘Having’ Phrase (2) : Example of the ‘Having’ Phrase (2) “How many different shipment sizes are there per supplier, for suppliers who ship more than 2 shipment sizes ?” Select SNo, Count( Distinct Qty ) As Sizes From SHIPGroup By SNoHaving Count( Distinct Qty ) > 2 ; SHIP GroupBy[ SNo ] With[ Sizes ? Project[ Qty ] Count ] Restrict[ Sizes > 2 ]
‘Before’ and ‘After’ Restrictions : ‘Before’ and ‘After’ Restrictions It is possible to write algebraic expressions like :
( … expression … )Restrict[ … ] GroupBy[ .. ] With[ … ] Restrict[ … ] The equivalent in SQL is :
Select …From …Where …Group By …Having … ; Before After After Before
Example of Extend : Example of Extend BANK Extend[ Euro ? Amt/0.65 ; MaxB ? Amt + O/D ]
Definition of ‘Extend’ : Definition of ‘Extend’ Creates a new relation formed from its operand by adding one or more named attributes to it.
Each new attribute(s) contain values specified in a ‘scalar expression’. The expression’s type is the type of the new attribute.
An expression can contain one or more of :
a constant;
an attribute name : this yields that attribute’s value in the same tuple;
scalar functions & operators (of the relevant data type);
parentheses to control the execution sequence.
SQL : Extend : SQL : Extend The SQL equivalent of an Extension has the syntax :-
Select RELATION_NAME.*, calculation As Result_NameFrom RELATION_NAME ; This yields allthe relation’sexisting attributes. Repeat foreach newattribute. An ‘*’ on its own isonly allowed if thereis nothing else in theSelect phrase.
SQL Examples : SQL Examples The previous example is written in SQL as follows :
Select BANK.*, Amt/0.65 As Euro, Amt + O/D As MaxBFrom BANK ;
“In addition to the details of all bank accounts, show how much the amount in each account would be above an overdraft limit that was 5 times greater than the current overdraft limit”.
Select BANK.*, Amt + (5 * O/D) As PossibleFrom BANK ;
Combining Algebra Operations : Combining Algebra Operations SQL treats an Extension as the reverse of a Projection, by putting both in the Select phrase.
As the Select phrase is the last to be executed by SQL, then an Extension is always the last operation to be executed, in parallel with a Projection.
This applies even when there is a Group By (with / without Having).In this case, since Extend applies scalar expressions and an aggregation can be embedded in a scalar expression, the Extension comes after the GroupBy.