In this part 2 we discuss what kind of aggregates can be obtained from a temporal database and how to express these aggregations in the SQL language. The examples in the part 2 are based on table definitions and data from part 1 of this article.<\/p>\n
Before we start with discussion of aggregates, we remind that a row of the temporal table has a period of validity represented as a pair of timestamps. The lower boundary (start of the period is included into period and the upper boundary (the end of the period) is not. A validity period is associated with every value, but we assume that periods of all attributes of a row are the same. We also assume that the periods represent valid<\/em> time (rather than system time), that is, the time when the information was or is correct in the reality but not necessarily stored in the database.<\/p>\n Note: If you want to restart with a clean database, the code that creates the structure and data from Chapter 1 is located in this file<\/a> on the Simple Talk site.<\/em><\/p>\n An aggregate is a value calculated using column values from several rows. Usually aggregates represent information about large amount of data in a compact form, typically as a single value that is easy for human interpretation.<\/p>\n A single numeric value of an aggregate may represent thousands or even millions of values in the OLAP queries. The most common aggregates defined in the SQL standard are As discussed in the part 1, any non-temporal query, of course, including queries containing aggregates, can be easily converted into a temporal point-in-time query. So, what is the problem with aggregates?<\/p>\n As we mentioned in part 1, a point-in-time query produces non-temporal output because rows of the result have associated periods. Therefore, the output of such query is not a temporal table. To obtain the full power of SQL, we need to combine query output with other temporal tables in more complex queries. Also, sometimes aggregated values are naturally associated with certain periods in the real world.<\/p>\n However, an attempt to calculate aggregates for a period may result in unexpected and\/or misleading results. For example, the following query sums the salary of each person as many times as there are rows for that person that overlap with the desired target period:<\/p>\n The output of this is silly, first, because inflates the salaries of people who changed salary amounts as time goes on. Second, there is little value to add such data together in the first place.<\/p>\n A more meaningful result can be obtained if we split the target period (the whole year) into smaller periods such that the value of salary does not change during any small period. The result will contain a separate row for each small period.<\/p>\n In general, the period associated with a value is calculated as intersections of all validity periods used to calculate the output value. This does not produce any difficulties for joins, unions and other relational operations that combine small number of arguments (usually two). However, for aggregates combining thousands or millions of rows the calculation of periods becomes challenging:<\/p>\n We need to define temporal aggregates that produce a single value for a given period even if the values being aggregated have different validity periods not necessarily containing the target period. The target period may be specified as a part of a query or calculated from other rows (for example, joined from another table). In both cases the period for the aggregate is not based on periods of aggregated values.<\/p>\n For example, the company management may be interested in average salary of employees during the second quarter of 2023. However, the salary of Eja has been changed on April 20, so the function avg(salary) will produce wrong value, based on when the salary was changed.<\/p>\n Management does not want two values: one before April 20 and another after, just a single value is needed. Instead of averaging the values stored in the table, we compute a single value characterizing the salary during the period requested in the query (that is, second quarter) for each employee and then compute average of these values.<\/p>\n To make aggregation more useful we need to define more carefully how to calculate the aggregate values.<\/p>\n Although it is possible to express these calculations directly in SQL, we would like to define new aggregate functions that produce single number of any target period Our aggregate functions should return the same result as the corresponding SQL function if the values of the column do not change during the query period. In particular, if the query period is a point, then our functions will return the same output as standard SQL functions.<\/p>\n Further, if the intersection of a row period with the query period is smaller than the query period, we use only a fraction of the aggregated value. For example, in some scenarios, if the duration of the query period is 3 months, but a person left the project after one month, we would like to count this person as 1\/3, rather than 1 against the cost of a project. If this person worked on another project for remaining two months, we\u2019ll count 2\/3 to the second project.<\/p>\n Similarly, if a person has salary S<\/em>1<\/sub> for two months and S<\/em>2<\/sub> for remaining month of the target period, we use the value S<\/em>1<\/sub>*2\/3+S<\/em>2<\/sub>*1\/3 as a single number representing the salary of the person during the whole period. Of course, this value is not precise, but it gives a reasonable estimation.<\/p>\n So, we need to calculate fractions. Unfortunately, some of widely used calendar periods may have varying duration: neither months nor quarters are equal, and even leap years give us different lengths of year. This makes our function approximate, but we must measure duration of periods in the same units no matter how long or short periods are. Fortunately, it is possible to express duration of any period, even down to the second.<\/p>\n To do this, we will use SQL function Note that the difference of timestamps is an interval in the PostgreSQL type system. The duration in seconds may be very large for long periods, but we need only ratio of durations which will be always less than 1.<\/p>\n Similarly, we can calculate the duration of the intersection, using greatest, least, and To avoid long constant values of timestamps and to make queries more readable, we define psql variables that contain the start and the end of the target query period:<\/p>\n These variables are proceeded with \u2018:\u2019when used in queries. Please note that the values are substituted before the query is sent to the server (so, they are NOT bind variables). For example, the string<\/p>\n is sent to the DBMS server as<\/p>\n Editor note: in DBeaver, I was able to use the following:<\/em><\/p>\n For more information on the settings required, this stackoverflow post<\/a> covers it well.<\/em><\/p>\n If your client does not support any similar kind of variables, you can just substitute them with constants. We are now ready to write an SQL query that computes an aggregated value for the target period:<\/p>\n The output of this query is:<\/p>\n The query above computes only one aggregate, but we need much more in our examples. To make subsequent queries shorter, we wrap the code into PostgreSQL functions and define PostgreSQL user-defined aggregates The complete code of these aggregates can be found in the appendix<\/a>.<\/p>\n All queries below produce temporal results, that is, each row of the result has associated pair of timestamps. However, to avoid too long rows, we do not include these timestamps into the list of output columns if the periods for all output rows are the same.<\/p>\n Our first query uses temporal aggregates with This returns:<\/p>\n The number of returned rows (6) is correct, but one of the values for count is 0.5. This is because Antti joined the company right in the middle of the period of the qery. So, our aggregate calculates a weighted average for each business key and then compute sum, average, or count.<\/p>\n A more interesting (from a business perspective) query calculates the aggregates with grouping by project:<\/p>\n This returns:<\/p>\n This output shows how intensive (and how expensive) the projects are. The non-integer values for count may look contra-intuitive. However, if a person worked for 3 different projects during 3 months, our calculation will show 1\/3 for each project and the total 1 for all 3 projects. Counting this person as 1 for each project would be misleading. Our calculation is not cutting the body of a person into fractions, it only shows that the person\u2019s effort wat not fully dedicated to a single project.<\/p>\n The reader is encouraged to run this query for different query periods. The results will be only slightly different. If you expect that salary for period of 4 months will be approximately 2 times lagrger than for 2 months, this will not happen. The reason is that the salary in the table is per month, no matter how long the query period is.<\/p>\n How to calculate the total to be paid during the period? We have yet another aggregate, Consider completely different application domain. Let the table store values of speed (for example, GPS readings) expressed in kilometers (or miles) per hour, then the interval will be one hour and the aggregate Let\u2019s now return to our table This returns the following.<\/p>\n The first column accumulates the value 1 with the interval on month, so the values in this column are mythical person-months spent on the project during the query period.<\/p>\n According to the title of a famous book \u2018The Mythical Man-Month<\/a>\u2019 by F. Brooks, the man-months were mythical in 1970-ies. Man-months are now renamed with the more appropriate person-months, but we think they are still mythical. Note that the unit of measure is also important here: if we replace the interval parameter with one year instead of month, we\u2019ll get mythical person-years.<\/p>\n Our last query calculates aggregates for each quarter of 2023. There is no query period for this query because different rows must have different periods (quarters). Therefore, the periods for which the aggregates are calculated are coming from the database (table Our example tables do not contain any data for periods outside of 2023. In more realistic scenarios we should also explicitly specify the query period so that only quarters of required year would be selected.<\/p>\n We rounded all values except count using This should return something like this:<\/p>\n The above query produces an overall summary for all projects. Of course, All values produced by our aggregates are approximate. The reason is that different months differ in duration. Of course, the problem is in the calendar rather than in the implementation of aggregates. So, these aggregates are probably good for planning, reporting, and analytics, but not for accounting.<\/p>\n We discussed Aggregation in temporal databases over time periods requires special attention. Straightforward use of standard SQL aggregates may produce unexpected or incorrect output.<\/p>\n The aggregates described in this article produce compact output that have associated meaningful periods of validity. Consequently, these aggregates can be used in queries producing temporal tables.<\/p>\n Finally, although our implementation uses PostgreSQL-specific features, we tried to reduce dependency on the specific DBMS.<\/p>\n This section is identical to the corresponding section in the part 1 of this article.<\/p>\n During first decades of research on temporal database a complete bibliography was maintained. More information on this bibliography is available in [1]. The book [2] highlights the major outcome of that research. A systematic presentation of theoretical viewpoint on temporal databases can be found in [3].<\/p>\n An article [4] provides an overview of temporal features in SQL Stanard 2011 (that weren\u2019t significantly changed in subsequent editions of the Standard). It also contains rationale for decisions made in the Standard.<\/p>\n One of several practical approaches to implementation of temporal features is described in [5]. The authors introduce asserted time dimension and describe advantages of bi-temporal data model based on effective time dimensions.<\/p>\n An article [6] introduces an alignment operation that provides an extension of relational algebra supporting temporal operations for one-dimensional time.<\/p>\n Finally, an emotionally rich annotated bibliography is available at [7].<\/p>\nWhy Aggregates are Special?<\/h2>\n
count<\/code>, sum<\/code>, avg<\/code> (average), max<\/code>, and min<\/code>.<\/p>\nselect sum(salary),'2023-01-01', '2024-01-01' \nfrom emp_temporal;<\/pre>\n
\n
Aggregating Temporal Data<\/h2>\n
[query_ts, query_te]<\/code> specified in a query and passed to the aggregate function as an argument.<\/p>\nextract <\/code>with a first parameter of(epoch \u2026)<\/code> (that returns the duration of an interval in seconds) to calculate durations of periods. For example, the duration of the query period in seconds is calculated by the following expression:<\/p>\nextract(EPOCH FROM query_te - query_ts)<\/pre>\n
EXTRACT<\/code> functions, take some precautions to avoid a zero_divide<\/code> exception, multiply by the value of an aggregated column and use all that as an argument for SQL sum function.<\/p>\n\\set query_ts $2023-04-01$::timestamptz\n\\set query_te $2023-08-01$::timestamptz<\/pre>\n
select :query_ts;<\/pre>\n
select $2023-04-01$::timestamptz;<\/pre>\n
\n@set query_ts = $2023-04-01$::timestamptz\n@set query_te = $2023-08-01$::timestamptz<\/pre>\n
\nset search_path to temp_agg;\n\nselect project, sum(\n case\n when salary is not null \n then salary * \n extract(EPOCH FROM least(emp_te, :query_te) -\n greatest(emp_ts,:query_ts)) \/\n extract(EPOCH FROM :query_te - :query_ts)\n else null\n end) as temporal_sum\nfrom emp_temporal\nwhere (emp_ts, emp_te) overlaps (:query_ts, :query_te)\ngroup by project;<\/pre>\n
project | temporal_sum \n--------+------------------------\np11 | 5940.9836065573770492\np20 | 3360.6557377049180328\np15 | 13853.2786885245901640\n(3 rows)<\/pre>\n
temporal_sum<\/code>, temporal_count<\/code>, and temporal_avg<\/code>. These functions have four additional arguments specifying the boundaries of the validity period of the aggregated value and the query period.<\/p>\nGROUP BY name<\/code> clause. Names are unique in our table, so such query would include exactly one row into each group and the value of count<\/code> would be 1.<\/p>\n-- Note, you must create the objects in the Appendix or\n-- the function code will give you erros.\nselect \n name, \n temporal_count(salary, emp_ts, emp_te, \n :query_ts, :query_te) AS temporal_cnt,\n temporal_avg(salary, emp_ts, emp_te, \n :query_ts, :query_te) AS temporal_avg,\n temporal_sum(salary, emp_ts, emp_te, \n :query_ts, :query_te) AS temporal_sum\nfrom emp_temporal\nwhere (emp_ts,emp_te) overlaps (:query_ts, :query_te) \ngroup by name;<\/pre>\n
name | temporal_cnt | temporal_avg | temporal_sum \n-------+--------------+--------------------+------------------\n Antti | 0.5 | 3600 | 1800\n Merja | 1 | 4200 | 4200\n Anne | 1 | 4000 | 4000\n Timo | 1 | 4850.819672131147 | 4850.819672131147\n Esa | 1 | 3249.180327868852 | 3249.180327868852\n Eja | 1 | 4753.2786885245905 |4753.2786885245905\n(6 rows)<\/pre>\n
select \n project, \n temporal_count(salary, emp_ts, emp_te,\n :query_ts, :query_te) AS temporal_cnt,\n temporal_avg(salary, emp_ts, emp_te,\n :query_ts, :query_te) AS temporal_avg,\n temporal_sum(salary, emp_ts, emp_te,\n :query_ts, :query_te) AS temporal_sum\nfrom emp_temporal\nwhere (emp_ts,emp_te) overlaps (:query_ts, :query_te) \ngroup by project;<\/pre>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/06\/a-number-and-text-on-a-white-background-descripti.png\")
<\/p>\ntemporal_accum<\/code> for that. This aggregate has one more argument: the interval of time for which the aggregated value is specified. In our case this interval is equal to one month because the table stores salary per month. If you changed that to annual or per week salalry, the interval must be one year or one week.<\/p>\ntemporal_accom<\/code> will calculate the distance covered during the query period.<\/p>\nemp_temporal<\/code>. The following query calculates the totals for the query period:<\/p>\nselect project,\n temporal_accum(1, make_interval(months => 1),\n emp_ts, emp_te, :query_ts, \n :query_te) AS person_months,\n temporal_accum(salary, make_interval(months => 1),\n emp_ts, emp_te, :query_ts, \n :query_te) AS total_cost\nfrom emp_temporal\nwhere (emp_ts,emp_te) overlaps (:query_ts, :query_te) \ngroup by project;<\/pre>\n
project|person_months |total_cost \n-------+------------------+------------------\np11 | 6.6|22733.333333333336\np20 |3.0999999999999996|13866.666666666668\np15 |12.666666666666666|56336.666666666664\n(3 rows)<\/pre>\n
qu<\/code>), rather than from query parameters (as in previous queries).<\/p>\nceil<\/code> function to make the output more readable, which rounds up the specified number where needed. The value of temporal_count<\/code> is rouned using the function round<\/code>.<\/p>\nselect q.code,\n ceil(temporal_avg(e.salary, e.emp_ts, e.emp_te, \n q.qu_ts,q.qu_te)) per_mon_avg,\n ceil(temporal_sum(e.salary, e.emp_ts, e.emp_te, \n q.qu_ts,q.qu_te)) per_mon,\n ceil(temporal_accum(e.salary, make_interval(months=>1),\n e.emp_ts, e.emp_te, q.qu_ts,q.qu_te)) per_qu,\n round(temporal_count(e.salary, e.emp_ts, e.emp_te, \n q.qu_ts,q.qu_te)::decimal,3) cnt\nfrom emp_temporal e\n join qu q \n on (e.emp_ts, e.emp_te) overlaps (q.qu_ts,q.qu_te)\ngroup by q.code;<\/pre>\n
code | per_mon_avg | per_mon | per_qu | cnt \n------+-------------+---------+--------+-------------------\n q1 | 4140 | 20700 | 62072 | 5.000\n q2 | 4214 | 22459 | 68124 | 5.330\n q4 | 4500 | 22500 | 69032 | 5.000\n q3 | 4330 | 23107 | 70860 | 5.337\n(4 rows)<\/pre>\n
GROUP BY q.code, e.project clause<\/code> will produce more detailed data on each project per quarter. Probably ORDER BY<\/code> clause is also welcome to make the management completely happy.<\/p>\ncount<\/code>, sum<\/code>, and avg<\/code> functions so far. What about in<\/code> and max<\/code>? These two functions are much easier: the values from all rows that overlap with the target period produces meaningful result.
Read also:\u00a0<\/strong>SQL Server temporal tables<\/a><\/p>\nConclusions<\/h2>\n
References<\/h2>\n
\n
<\/a>Appendix<\/h2>\n