System and method of client server aggregate transformation

• US 7,428,532 B2
• Filed: 05/27/2004
• Issued: 09/23/2008
• Est. Priority Date: 05/27/2003
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: International Business Machines Corporation
• Inventors: Styles, Michael E.
• Primary Examiner(s): ABEL JALIL, NEVEEN
• Application Number:
  US10/856,306
• Publication Number:
  US 20050038778A1
• Time in Patent Office: 1,580 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/244 Grouping and aggregation
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A system for transforming client/server aggregates is provided. The system comprises a client/server analysis component for analyzing one or more queries that are not supported by a target database system, and a client/server transformation component for transforming the one or more queries into semantically equivalent queries that are supported by the target database system.

Claims

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₁, C₂, . . . , C_n), then all other control breaks are of the form (C₁, C₂, . . . , 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₀) OVER( ) with an expression of the form SUM (SUM(C₀)) OVER( )/SUM (COUNT(C₀)) OVER( ) and moving nested aggregates into an inner select list; and

replacing an aggregate of the form AVG (C₀) OVER (PARTITION BY C₁, C₂, . . . , C_k) with an expression of the form SUM (SUM(C₀)) OVER (PARTITION BY C₁, C₂, . . . , C_k)/SUM (COUNT(C₀)) OVER (PARTITION BY C₁, C₂, . . . , 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₀, C₁, C₂, . . . , C_krepresent 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₀) OVER( ) with an expression of the form SUM (SUM(C₀)) OVER( )/SUM (COUNT(C₀)) OVER( ) and moving nested aggregates into an inner select list; and

replacing an aggregate of the form AVG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_k) with an expression of the form SUM (SUM(C₀)) OVER (PARTITION BY C₁, C₂, . . . , C_k)/SUM (COUNT(C₀)) OVER (PARTITION BY C₁, C₂, . . . , 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₀, C₁, C₂, . . . , C_krepresent 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₀) OVER(PARTITION BY C₁, C₂, . . . , C_n) with an expression of the form AGG (C₀); and

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

wherein AGG(C₀) represents a standard form of an aggregate node AGG (C₀)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₀) OVER( ) with an expression of the form SUM (SUM(C₀)) OVER( )/SUM (COUNT (C₀)) OVER( ) and moving nested aggregates into an inner select list; and

replacing an aggregate of the form AVG(C₀) OVER (PARTITION BY C₁, C₂, . . . , C_k) with an expression of the form SUM (SUM(C₀)) OVER (PARTITION BY C₁, C₂, . . . , C_k)/SUM (COUNT(C₀)) OVER (PARTITION BY C₁, C₂, . . . , 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₀, C₁, C₂, . . . , C_krepresent 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₀) OVER (PARTITION BY C₁, C₂, . . . , C_k) where k≠

n, with an expression of the form AGG (AGG (C₀)) OVER (PARTITION BY C₁, C₂, . . . , C_k); and

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