Time slider operator for temporal data aggregation
Calculation of aggregated values in a history database table can be optimized using an approach in which an ordered history table is accessed. The ordered history table can include a sequential listing of commit identifiers associated with updates, insertions, and/or deletions to values in the database table. The ordered history table can be traversed in a single pass to calculate an aggregation function using an optimized algorithm. The optimized algorithm can enable calculation of an aggregated metric of the values based on a selected method for tracking invalidated values to their corresponding commit identifiers. The calculated metric is generated for a current version of the database table; and promoted.
1. A non-transitory computer program product storing instructions that, when executed by at least one programmable processor, cause the at least one programmable processor to perform operations comprising:
accessing an ordered history table of a database table of a relational database, the ordered history table comprising a plurality of commit identifiers associated with one or more of updates, insertions, and deletions to values in the database table, the ordered history table comprising a sequential listing of the commit identifiers associated with first occurrences of changes to one or more of the values;
traversing the ordered history table in a single pass to calculate an aggregation function using an optimized algorithm, the optimized algorithm comprising:
identifying the values in the ordered history table that have been invalidated by deletion or update to another value,
wherein calculating the aggregation function comprises calculating an aggregated metric of the values by tracking the identified invalidated values to their corresponding commit identifiers, and
wherein the traversing comprises one or more of incrementing a variable based on changes to the values at each commit identifier, creating a chained list corresponding to each commit identifier, and creating a sorted list corresponding to each commit identifier;
generating the calculated metric for a current version of the database table; and
promoting the calculated metric.
2. A computer program product as in claim 1 , wherein the promoting comprises one or more of storing the calculated metric, presenting the calculated metric via a user interface display, and sending an electronic message comprising the calculated metric.
3. A computer program product as in claim 1 , wherein the optimized algorithm comprises one or more of generating an invalidation index, generating a separate bitlist for each of the commit identifiers, and generating a previous version array.
4. A computer program product as in claim 3 , wherein the operations further comprise selecting the optimized algorithm dependent on the aggregation function from a plurality of algorithms, the selecting comprising assessing one or more attributes of the database table in relation to a set of optimization criteria for each of the plurality of algorithms.
5. A computer program product as in claim 1 , wherein the calculated metric comprises one or more of a sum, a count, an average, a minimum value, a maximum value, a median, a mode, and skewness.
6. A system comprising:
at least one programmable processor; and
a machine-readable medium storing instructions that, when executed by the at least one programmable processor, cause the at least one programmable processor to perform operations comprising:
accessing an ordered history table of a database table of a relational database, the ordered history table comprising a plurality of commit identifiers associated with one or more of updates, insertions, and deletions to values in the database table, the ordered history table comprising a sequential listing of the commit identifiers associated with first occurrences of changes to one or more of the values;
traversing the ordered history table in a single pass to calculate an aggregation function using an optimized algorithm, the optimized algorithm comprising:
identifying the values in the ordered history table that have been invalidated by deletion or update to another value,
wherein calculating the aggregation function comprises calculating an aggregated metric of the values by tracking the identified invalidated values to their corresponding commit identifiers, and
wherein the traversing comprises one or more of incrementing a variable based on changes to the values at each commit identifier, creating a chained list corresponding to each commit identifier, and creating a sorted list corresponding to each commit identifier;
generating the calculated metric for a current version of the database table; and
promoting the calculated metric.
7. A system as in claim 6 , wherein the promoting comprises one or more of storing the calculated metric, presenting the calculated metric via a user interface display, and sending an electronic message comprising the calculated metric.
8. A system as in claim 6 , wherein the optimized algorithm comprises one or more of generating an invalidation index, generating a separate bitlist for each of the commit identifiers, and generating a previous version array.
9. A system as in claim 8 , wherein the operations further comprise selecting the optimized algorithm from a plurality of algorithms, the selecting comprising assessing one or more attributes of the database table in relation to a set of optimization criteria for each of the plurality of algorithms.
10. A system as in claim 6 , wherein the calculated metric comprises one or more of a sum, a count, an average, a minimum value, a maximum value, a median, a mode, and a skewness.
11. A computer-implemented method comprising:
accessing, by at least one programmable processor, an ordered history table of a database table of a relational database, the ordered history table comprising a plurality of commit identifiers associated with one or more of updates, insertions, and deletions to values in the database table, the ordered history table comprising a sequential listing of the commit identifiers associated with first occurrences of changes to one or more of the values;
traversing, by the at least one programmable processor, the ordered history table in a single pass to calculate an aggregation function using an optimized algorithm, the optimized algorithm comprising:
identifying the values in the ordered history table that have been invalidated by deletion or update to another value,
wherein calculating the aggregation function comprises calculating an aggregated metric of the values by tracking the identified invalidated values to their corresponding commit identifiers, and
wherein the traversing comprises one or more of incrementing a variable based on changes to the values at each commit identifier, creating a chained list corresponding to each commit identifier, and creating a sorted list corresponding to each commit identifier;
generating, by the at least one programmable processor, the calculated metric for a current version of the database table; and
promoting, by the at least one programmable processor, the calculated metric.
12. A computer-implemented method as in claim 11 , wherein the promoting comprises one or more of storing the calculated metric, presenting the calculated metric via a user interface display, and sending an electronic message comprising the calculated metric.
13. A computer-implemented method as in claim 11 , wherein the optimized algorithm comprises one or more of generating an invalidation index, generating a separate bitlist for each of the commit identifiers, and generating a previous version array.
14. A computer-implemented method as in claim 13 , further comprising selecting the optimized algorithm from a plurality of algorithms, the selecting comprising assessing one or more attributes of the database table in relation to a set of optimization criteria for each of the plurality of algorithms.
15. A computer-implemented method as in claim 11 , wherein the calculated metric comprises one or more of a sum, a count, an average, a minimum value, a maximum value, a median, a mode, and a skewness.