Complex dimension functions in pivot aggregations
Systems and methods described herein relate to techniques for performing a pivot aggregation on a set of data using intermediate maps and intermediate pages. The intermediate maps and the intermediate pages reduce the computational complexity of pivot aggregations into a series of operations that are less computationally complex and may be performed in parallel. The techniques described herein improve efficiency in database technology by reducing computational complexity of pivot aggregations.
1 . A system comprising:
at least one hardware processor; and
a computer-readable medium storing instructions that, when executed by the at least one hardware processor, cause the at least one hardware processor to perform operations comprising:
receiving a first pivot aggregation command to generate a first output table based on an input table;
generating a first intermediate hash map based on the first pivot aggregation command and the input table;
generating a first intermediate lookup map based on the first pivot aggregation command and query conditions;
generating a first intermediate page based on the first intermediate hash map and the first intermediate lookup map;
generating the first output table based on the first intermediate page;
receiving a second pivot aggregation command to generate a second output table based on the input table, wherein the second pivot aggregation command comprises a different filter condition from the first pivot aggregation command or a different grouping from the first pivot aggregation command; and
generating the second output table by reusing the first intermediate hash map and generating a second intermediate lookup map to apply the different filter condition or by reusing the first intermediate lookup map and generating a second intermediate hash map to apply the different grouping.
2 . The system of claim 1 , wherein generating the first intermediate hash map comprises:
identifying a group for data entries of the input table based on the first pivot aggregation command;
identifying a filter condition value for the data entries of the input table based on the first pivot aggregation command; and
aggregating the data entries with a same group and a same filter condition value, the first intermediate hash map comprising entries based on the aggregated data entries.
3 . The system of claim 2 , wherein aggregating the data entries comprises summing numeric measures of the data entries.
4 . The system of claim 1 , wherein the first intermediate hash map comprises a column for each group in the first pivot aggregation command.
5 . The system of claim 1 , wherein generating the first intermediate lookup map comprises:
identifying filter conditions based on the first pivot aggregation command;
identifying filter condition values of the query conditions; and
listing, for each filter condition value, the filter conditions satisfied by the filter condition value.
6 . The system of claim 1 , wherein generating the first intermediate page comprises:
for each entry in the first intermediate hash map:
identifying a filter condition that satisfies a filter condition value for the entry based on the first intermediate lookup map; and
generating a corresponding entry in the first intermediate page based on the entry and the filter condition.
7 . The system of claim 1 , wherein the first intermediate page and the first output table have a same number of columns.
8 . The system of claim 1 , wherein the first intermediate page includes a column for each filter condition in the first pivot aggregation command.
9 . The system of claim 1 , wherein generating the first output table comprises:
identifying entries in the first intermediate page that belong to a group based on group by values of the entries; and
aggregating the entries that belong to the group.
10 . The system of claim 1 , wherein the first intermediate hash map is generated based on a first iteration through the input table, the first intermediate page is generated based on a second iteration through the first intermediate hash map, and the first output table is generated based on a third iteration through the first intermediate page.
11 . The system of claim 1 , the operations further comprising:
generating a second intermediate hash map based on the first pivot aggregation command and the input table, the first intermediate hash map based on a first portion of the input table, the second intermediate hash map based on a second portion of the input table; and
generating a second intermediate page, the first intermediate page based on the first intermediate hash map, the second intermediate page based on the second intermediate hash map.
12 . The system of claim 11 , wherein the second intermediate page is generated in parallel with the first intermediate hash map.
13 . The system of claim 1 , wherein the first intermediate hash map and the first intermediate lookup map are generated in parallel.
14 . The system of claim 1 , the operations further comprising:
generating the second intermediate hash map based on the second pivot aggregation command and the input table;
reusing the first intermediate lookup map based on a compatibility between the first pivot aggregation command and the second pivot aggregation command; and
generating a second intermediate page based on the second intermediate hash map and the first intermediate lookup map.
15 . A method comprising:
receiving a first pivot aggregation command to generate a first output table based on an input table;
generating a first intermediate hash map based on the first pivot aggregation command and the input table;
generating a first intermediate lookup map based on the first pivot aggregation command and query conditions;
generating a first intermediate page based on the first intermediate hash map and the first intermediate lookup map;
generating the first output table based on the first intermediate page;
receiving a second pivot aggregation command to generate a second output table based on the input table, wherein the second pivot aggregation command comprises a different filter condition from the first pivot aggregation command or a different grouping from the first pivot aggregation command; and
generating the second output table by reusing the first intermediate hash map and generating a second intermediate lookup map to apply the different filter condition or by reusing the first intermediate lookup map and generating a second intermediate hash map to apply the different grouping.
16 . The method of claim 15 , wherein generating the first intermediate hash map comprises:
identifying a group for data entries of the input table based on the first pivot aggregation command;
identifying a filter condition value for the data entries of the input table based on the first pivot aggregation command; and
aggregating the data entries with a same group and a same filter condition value, the first intermediate hash map comprising entries based on the aggregated data entries.
17 . The method of claim 15 , wherein generating the first intermediate lookup map comprises:
identifying filter conditions based on the first pivot aggregation command;
identifying filter condition values of the query conditions; and
listing, for each filter condition value, the filter conditions satisfied by the filter condition value.
18 . One or more non-transitory computer-readable media storing computer-executable instructions that, when executed by a computing system, cause the computing system to perform operations comprising:
receiving a first pivot aggregation command to generate a first output table based on an input table;
generating a first intermediate hash map based on the first pivot aggregation command and the input table;
generating a first intermediate lookup map based on the first pivot aggregation command and query conditions;
generating a first intermediate page based on the first intermediate hash map and the first intermediate lookup map;
generating the first output table based on the first intermediate page;
receiving a second pivot aggregation command to generate a second output table based on the input table, wherein the second pivot aggregation command comprises a different filter condition from the first pivot aggregation command or a different grouping from the first pivot aggregation command; and
generating the second output table by reusing the first intermediate hash map and generating a second intermediate lookup map to apply the different filter condition or by reusing the first intermediate lookup map and generating a second intermediate hash map to apply the different grouping.
19 . The one or more non-transitory computer-readable media of claim 18 , wherein generating the first intermediate hash map comprises:
identifying a group for data entries of the input table based on the first pivot aggregation command;
identifying a filter condition value for the data entries of the input table based on the first pivot aggregation command; and
aggregating the data entries with a same group and a same filter condition value, the first intermediate hash map comprising entries based on the aggregated data entries.
20 . The one or more non-transitory computer-readable media of claim 18 , wherein generating the first intermediate lookup map comprises:
identifying filter conditions based on the first pivot aggregation command;
identifying filter condition values of the query conditions; and
listing, for each filter condition value, the filter conditions satisfied by the filter condition value.