System and method of client server aggregate transformation

1. A method of client/server aggregate transformation, the method comprising the steps of:

given a binary representation of a structured query language/online analytical programming query (SQL/OLAP):

analyzing a client/server select list to determine that a transformation is to be performed, the step of determining that a transformation is to be performed comprising the steps of:

assigning a group index to all aggregate nodes;

storing control breaks in an ordered list; and

determining whether certain criteria are met, the certain criteria comprising:

a keyword DISTINCT is present in the select list;

all aggregates in the select list are eligible;

all control breaks in the ordered list are compatible with other control breaks in the ordered list, where when the longest control break in the ordered list is of the form (C 1 , C 2 , . . . , C n ), then all other control breaks are of the form (C 1 , C 2 , . . . , C k ), where 0≦k≦n.; and one of:

 any simple column reference in the select list that is not part of an aggregate specification is contained in the longest control break; or

 any simple column reference in the select list that is not part of an aggregate specification is part of an expression contained in the longest control break;

generating a derived table comprising standard aggregate nodes and a GROUP BY clause;

traversing all expressions in a main select list;

adding expression nodes that are grouping column nodes of a longest control break in the query to a derived table select list; and

converting and moving eligible aggregate nodes from the main select list into the derived table select list, said eligible aggregate nodes being windowed aggregate nodes that:

do not have a window frame specification; and

have standard aggregate counterparts;

wherein the step of converting and moving comprises the steps of:

applying the following transformation:

replacing an aggregate of the form AVG (C 0 ) OVER( ) with an expression of the form SUM (SUM(C 0 )) OVER( )/SUM (COUNT(C 0 )) OVER( ) and moving nested aggregates into an inner select list; and

replacing an aggregate of the form AVG (C 0 ) OVER (PARTITION BY C 1 , C 2 , . . . , C k ) with an expression of the form SUM (SUM(C 0 )) OVER (PARTITION BY C 1 , C 2 , . . . , C k )/SUM (COUNT(C 0 )) OVER (PARTITION BY C 1 , C 2 , . . . , C k ) and moving nested aggregates into the inner select list;

wherein:

m equals a total number of unique control breaks;

k equals a total number of grouping columns of a control break of an online analytical programming function in the query;

n equals a total number of columns in the longest control break;

one of:

 m>1 or

 k≠n; and

 C 0 , C 1 , C 2 , . . . , C k represent indexed columns in a control break in the query.

2. A method of client/server aggregate transformation, the method comprising the steps of:

given a binary representation of a structured query language/online analytical programming query (SQL/OLAP):

analyzing a client/server select list to determine that a transformation is to be performed;

generating a derived table comprising standard aggregate nodes and a GROUP BY clause;

traversing all expressions in a main select list;

adding expression nodes that are grouping column nodes of a longest control break in the query to a derived table select list; and

converting and moving eligible aggregate nodes from the main select list into the derived table select list, said eligible aggregate nodes being windowed aggregate nodes that:

do not have a window frame specification; and

have standard aggregate counterparts;

wherein the step of converting and moving comprises the steps of:

applying the following transformation:

replacing an aggregate of the form AVG (C 0 ) OVER( ) with an expression of the form SUM (SUM(C 0 )) OVER( )/SUM (COUNT(C 0 )) OVER( ) and moving nested aggregates into an inner select list; and

replacing an aggregate of the form AVG(C 0 ) OVER (PARTITION BY C 1 , C 2 , . . . , C k ) with an expression of the form SUM (SUM(C 0 )) OVER (PARTITION BY C 1 , C 2 , . . . , C k )/SUM (COUNT(C 0 )) OVER (PARTITION BY C 1 , C 2 , . . . , C k ) and moving nested aggregates into the inner select list;

wherein:

m equals a total number of unique control breaks;

k equals a total number of grouping columns of a control break of an online analytical programming function in the query;

n equals a total number of columns in the longest control break;

one of:

 m<1 or

 k≠n; and

 C 0 , C 1 , C 2 , . . . , C k represent indexed columns in a control break in the query;

wherein the step of applying a client/server transformation comprises the steps of:

replacing the expression AGG (C 0 ) OVER(PARTITION BY C 1 , C 2 , . . . , C n ) with an expression of the form AGG (C 0 ); and

moving the expression of the form AGG (C 0 );

wherein AGG(C 0 ) represents a standard form of an aggregate node AGG (C 0 )OVER( . . . ).

3. A method of client/server aggregate transformation, the method comprising the steps of:

given a binary representation of a structured query language/online analytical programming query (SQL/OLAP):

analyzing a client/server select list to determine that a transformation is to be performed;

generating a derived table comprising standard aggregate nodes and a GROUP BY clause;

traversing all expressions in a main select list;

adding expression nodes that are grouping column nodes of a longest control break in the query to a derived table select list; and

converting and moving eligible aggregate nodes from the main select list into the derived table select list, said eligible aggregate nodes being windowed aggregate nodes that:

do not have a window frame specification; and

have standard aggregate counterparts;

wherein the step of converting and moving comprises the steps of:

applying the following transformation:

replacing an aggregate of the form AVG (C 0 ) OVER( ) with an expression of the form SUM (SUM(C 0 )) OVER( )/SUM (COUNT (C 0 )) OVER( ) and moving nested aggregates into an inner select list; and

replacing an aggregate of the form AVG(C 0 ) OVER (PARTITION BY C 1 , C 2 , . . . , C k ) with an expression of the form SUM (SUM(C 0 )) OVER (PARTITION BY C 1 , C 2 , . . . , C k )/SUM (COUNT(C 0 )) OVER (PARTITION BY C 1 , C 2 , . . . , C k ) and moving nested aggregates into the inner select list;

wherein:

m equals a total number of unique control breaks;

k equals a total number of grouping columns of a control break of an online analytical programming function in the query;

n equals a total number of columns in the longest control break;

one of:

 m>1 or

 k≠n; and

 C 0 , C 1 , C 2 , . . . , C k represent indexed columns in a control break in the query;

wherein the step of applying a client/server transformation comprises the steps of:

replacing AGG (C 0 ) OVER (PARTITION BY C 1 , C 2 , . . . , C k ) where k≠n, with an expression of the form AGG (AGG (C 0 )) OVER (PARTITION BY C 1 , C 2 , . . . , C k ); and

moving nested aggregate nodes into the inner select list wherein AGG (C 0 ) represents a standard form of an aggregate node AGG (C 0 ) OVER( . . . ).