First/last aggregation operator on multiple keyfigures with a single table scan

Patent metadata

Field	Value
Publication number	US-2017147636-A1
Application number	US-201514947627-A
Country	US
Kind code	A1
Filing date	Nov 20, 2015
Priority date	Nov 20, 2015
Publication date	May 25, 2017
Grant date	—

Abstract

Methods and apparatus, including computer program products, are provided for first and last aggregation. In one aspect, there is provided a method, which may include receiving, by a calculation engine, a query; detecting, by the calculation engine, whether the query includes a first aggregation and/or a last aggregation over at least one group and at least one keyfigure; optimizing the received query, when the detecting indicates the received query includes the first aggregation and/or the last aggregation, wherein the optimizing further comprises initiating execution of the received query by at least: performing a single read of a table, detecting, from the single table read, at least one group, and indicating, in the detected at least one group, the first aggregation in the at least one keyfigure and/or the last aggregation in the at least one keyfigure; and returning, for the at least one detected group, the indicated first aggregation and/or the indicated second aggregation. Related apparatus, systems, methods, and articles are also described.

First claim

What is claimed:

1 . A method comprising:
receiving, by a calculation engine, a query; detecting, by the calculation engine, whether the query includes a first aggregation and/or a last aggregation over at least one group and at least one keyfigure; optimizing the received query, when the detecting indicates the received query includes the first aggregation and/or the last aggregation and further includes the at least one group and the at least one keyfigure, wherein the optimizing further comprises initiating execution of the received query by at least:
performing a single read of a table,
detecting, from the single table read, at least one group, and
indicating, in the detected at least one group, the first aggregation in the at least one keyfigure and/or the last aggregation in the at least one keyfigure; and
returning, for the at least one detected group, the indicated first aggregation and/or the indicated second aggregation.

2 . The method of claim 1 , wherein the keyfigure represents a numerical value, and wherein the at least one group is defined by at least one group by command.

3 . The method of claim 1 , wherein the detecting further comprises:
replacing group by attribute values by a hash determined for each of the group by attribute values.

4 . The method of claim 3 , wherein the detecting further comprises:
detecting, from the single table read, the at least one group based on the hash value.

5 . The method of claim 3 , wherein the detecting further comprises:
detecting, from the single table read, the at least one group, when a hash value in a row differs from another hash value in a prior row.

6 . The method of claim 5 , wherein the detecting further comprises:
indicating, from the single table read, the at least one group, when a hash value in a row differs from another hash value in a prior row.

7 . The method of claim 5 , wherein the first aggregation represents a first aggregation command, and the last aggregation represents a last aggregation command.

8 . The method of claim 1 , wherein the query includes a plurality of first aggregations and a plurality of last aggregations over a plurality of groups and a plurality keyfigures.

9 . The method of claim 1 , wherein the optimizing is part of a query optimization.

10 . A system comprising:
at least one processor and at least one memory including code which when executed causes operations comprising:
receiving, by a calculation engine, a query;
detecting, by the calculation engine, whether the query includes a first aggregation and/or a last aggregation over at least one group and at least one keyfigure;
optimizing the received query, when the detecting indicates the received query includes the first aggregation and/or the last aggregation and further includes the at least one group and the at least one keyfigure, wherein the optimizing further comprises initiating execution of the received query by at least:
performing a single read of a table,
detecting, from the single table read, at least one group, and
indicating, in the detected at least one group, the first aggregation in the at least one keyfigure and/or the last aggregation in the at least one keyfigure; and
returning, for the at least one detected group, the indicated first aggregation and/or the indicated second aggregation.

11 . The system of claim 10 , wherein the keyfigure represents a numerical value, and wherein the at least one group is defined by at least one group by command.

12 . The system of claim 10 , wherein the detecting further comprises:
replacing group by attribute values by a hash determined for each of the group by attribute values.

13 . The system of claim 12 , wherein the detecting further comprises:
detecting, from the single table read, the at least one group based on the hash value.

14 . The system of claim 12 , wherein the detecting further comprises:
detecting, from the single table read, the at least one group, when a hash value in a row differs from another hash value in a prior row.

15 . The system of claim 13 , wherein the detecting further comprises:
indicating, from the single table read, the at least one group, when a hash value in a row differs from another hash value in a prior row.

16 . The system of claim 13 , wherein the first aggregation represents a first aggregation command, and the last aggregation represents a last aggregation command.

17 . The system of claim 10 , wherein the query includes a plurality of first aggregations and a plurality of last aggregations over a plurality of groups and a plurality keyfigures.

18 . The system of claim 10 , wherein the optimizing is part of a query optimization.

19 . A non-transitory computer-readable storage medium including program code which when executed causes operations comprising:
receiving, by a calculation engine, a query; detecting, by the calculation engine, whether the query includes a first aggregation and/or a last aggregation over at least one group and at least one keyfigure; optimizing the received query, when the detecting indicates the received query includes the first aggregation and/or the last aggregation and further includes the at least one group and the at least one keyfigure, wherein the optimizing further comprises initiating execution of the received query by at least:
performing a single read of a table,
detecting, from the single table read, at least one group, and
indicating, in the detected at least one group, the first aggregation in the at least one keyfigure and/or the last aggregation in the at least one keyfigure; and
returning, for the at least one detected group, the indicated first aggregation and/or the indicated second aggregation.

Assignees

• Sap Se

Inventors

• Weyerhaeuser Christoph
• Mindnich Tobias
• Merx Johannes
• Schwing Julian

Classifications

• G06F16/24554 Primary

  Unary operations; Data partitioning operations · CPC title

• G06F17/30442 Primary

  Physics · mapped topic

• G06F17/30477

  Physics · mapped topic

• G06F17/3033

  Physics · mapped topic