Horizontal aggregations in a relational database management system

• US 8,712,993 B1
• Filed: 06/09/2004
• Issued: 04/29/2014
• Est. Priority Date: 06/09/2004
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Ordonez, Carlos
• Primary Examiner(s): Mofiz, Apu
• Assistant Examiner(s): Khoshnoodi, Fariborz
• Application Number:
  US10/864,154
• Time in Patent Office: 3,611 Days
• Field of Search: 707/103, 707/713, 707/718, 395/600, 364/283
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination

Abstract

A database system is able to receive a query containing a horizontal aggregate function. In response to the query containing the horizontal aggregate function, aggregate values are produced in a horizontal format. In general, the query can contain both horizontal (new) aggregate functions and vertical (standard) aggregate functions.

Claims

1. A method of computing aggregates in a database system, comprising:

receiving a query that specifies a grouping operation and an aggregate operation; and

producing, by one or more processors, plural aggregate values in an output row in response to the query, wherein receiving the query comprises receiving a query containing a horizontal aggregate function and a GROUP BY clause specifying grouping columns D₁, . . . , D_j, the horizontal aggregate function including an argument list that specifies an aggregate column A and sub-grouping columns D_j+1, . . . , D_k that specify groups in the output row.

2. The method of claim 1, wherein the output row has plural columns, and wherein producing the plural aggregate values comprises providing the aggregate values in corresponding columns of the output row.

3. The method of claim 1, further comprising producing plural output rows based on the grouping columns D₁, . . . , D_j,

wherein producing the aggregate values comprises producing aggregate values in plural columns of each output row, the plural columns defined by the sub-grouping columns D_j+1, . . . , D_k.

4. The method of claim 1, wherein receiving the query comprises receiving the query that contains another horizontal aggregate function.

5. The method of claim 1, wherein receiving the query comprises receiving the query that contains a vertical aggregate function in addition to the horizontal aggregate function.

6. An article comprising at least one non-transitory machine-readable storage medium storing instructions that when executed cause a database system to:

receive a query containing a GROUP BY clause specifying grouping columns D₁, . . . , D_j, and a horizontal aggregate function having an argument list specifying aggregate attribute A and sub-grouping columns D_j+1, . . . , D_k; and

in response to the query containing the GROUP BY clause and the horizontal aggregate function, produce aggregate values in a horizontal format according to the argument list of the horizontal aggregate function, wherein the aggregate values are produced by applying the horizontal aggregate function on the aggregate attribute A.

7. The article of claim 6, wherein producing the aggregate values in the horizontal format comprises providing plural aggregate values in each of one or more output rows generated as a result of processing the query.

8. The article of claim 7, wherein the instructions when executed cause the database system to further:

output plural rows for plural groups defined by the grouping columns of the GROUP BY clause, wherein each of the plural rows contains plural aggregate values.

9. The article of claim 8, wherein each of the output rows contains plural columns in the horizontal format, the plural columns containing respective aggregate values, wherein the sub-grouping columns of the horizontal aggregate function define the plural columns of each output row.

10. The article of claim 6, wherein the received query specifies a table on which horizontal aggregation is to be performed, the instructions when executed causing the database system to perform horizontal aggregation directly from the table.

11. The article of claim 6, wherein the received query specifies a table on which horizontal aggregation is to be performed, the instructions when executed causing the database system to:

perform vertical aggregation based on the table and inserting results of the vertical aggregation into a first table;

transpose content of the first table and inserting results of the transposing into a second table.

12. The article of claim 6, wherein the instructions when executed cause the database system to further produce plural output rows based on the grouping columns D₁, . . . , D_j,

wherein producing the aggregate values comprises producing aggregate values in plural columns of each output row, the plural columns defined by the sub-grouping columns D_j+1, . . . , D_k.

13. A database system comprising:

a storage to store a table; and

a controller comprising at least one processor to;

receive a query containing a GROUP BY clause specifying grouping columns D₁, . . . , D_j, and a horizontal aggregate function specifying an aggregate attribute A and sub-grouping columns D_j+1, . . . , D_k;

in response to the horizontal aggregate function, calculate an aggregation to produce aggregate values; and

produce one or plural output rows, each of the one or plural output rows containing plural aggregate values corresponding to groups specified by the sub-grouping columns of the horizontal aggregate function.

14. The database system of claim 13, wherein the controller comprises plural access modules, and the storage comprises plural storage modules, the plural access modules to concurrently access respective plural storage modules, and

wherein the table is distributed across the plural storage modules.