Sum a sequence of device-labeled, data using most recent data for device

Question

I am using Postgres 9.2.

I have the following problem:

Time | Value | Device   --  Sum should be
1      v1      1              v1 
2      v2      2              v1 + v2 
3      v3      3              v1 + v2 + v3 
4      v4      2              v1 + v4 + v3
5      v5      2              v1 + v5 + v3
6      v6      1              v6 + v5 + v3
7      v7      3              v6 + v5 + v3

Essentially, the sum needs to be across the most recent value in time for each of the N devices. In the example above, there are 3 devices.

I have tried several approaches using window functions and have been unsuccessful. I have written a stored procedure that does what I need, but it is SLOW. The SLOWness could be my lack of experience with plpgsql.

CREATE OR REPLACE FUNCTION timeseries.combine_series(id int[], startTime timestamp, endTime timestamp) 
RETURNS setof RECORD AS $$
DECLARE
    retval double precision = 0;
    row_data timeseries.total_active_energy%ROWTYPE;
    maxCount integer = 0;
    sz integer = 0;
lastVal double precision[];
v_rec RECORD;
BEGIN   
    SELECT INTO sz array_length($1,1);

    FOR row_data IN SELECT * FROM timeseries.total_active_energy  WHERE time >= startTime AND time < endTime AND device_id = ANY($1) ORDER BY time
       LOOP
    retval = row_data.active_power;
    for i IN 1..sz LOOP
        IF $1[i]=row_data.device_id THEN
            lastVal[i] = row_data.active_power;
        ELSE
            retval = retVal + COALESCE(lastVal[i],0);
        END IF;
    END LOOP;

    SELECT row_data.time, retval into v_rec;

    return next v_rec;
     END LOOP;

      return ;
  END;
$$ LANGUAGE plpgsql;

Call:

select * from timeseries.combine_series('{552,553,554}'::int[], '2013-05-01'::timestamp, '2013-05-02'::timestamp) 
    AS (t timestamp with time zone, val double precision);

Sample data

CREATE OR REPLACE TEMP TABLE t (ts int, active_power real, device_id int, should_be int);

INSERT INTO t VALUES
 (1,2,554,2)
,(2,3,553,5)
,(3,9,553,11)
,(4,7,553,9)
,(5,6,552,15)
,(6,8,554,21)
,(7,5,553,19)
,(8,7,553,21)
,(9,6,552,21)
,(10,7,552,22)
;

Erwin Brandstetter · Accepted Answer · 2022-03-28T00:11:24.630

I am building on my answer to your previous question, where you presented a simpler case. Read there for an explanation of the window functions aspect of the solution:

Sum across partitions with window functions

This question presents a "unpivoted" data-set. To get to where you want to be, you could run a cross tabulation first, reducing the case to its simpler, previous form.
PostgreSQL has the additional module tablefunc providing very fast functions for that. Run this command once per database to install:

CREATE EXTENSION tablefunc;

Then all you need is this (including redundant columns in the result for debugging):

SELECT ts, active_power, device_id, should_be
     , COALESCE(max(a) OVER (PARTITION BY grp_a), 0)
     + COALESCE(max(b) OVER (PARTITION BY grp_b), 0)
     + COALESCE(max(c) OVER (PARTITION BY grp_c), 0) AS special_sum
FROM  (
   SELECT *
        , count(a) OVER w AS grp_a
        , count(b) OVER w AS grp_b
        , count(c) OVER w AS grp_c
   FROM   crosstab(
            'SELECT ts, active_power, device_id, should_be
                  , device_id, active_power
             FROM   t
             ORDER  BY 1,2'

            ,'VALUES (552), (553), (554)'
         ) AS t (ts int, active_power int, device_id int, should_be int
               , a int, b int, c int)
   WINDOW w AS (ORDER BY ts)
   ) sub
ORDER  BY ts;

Returns the desired result, and should perform well.
Note that this solution builds on a small, given list of devices - (552, 553, 554) in your example.

Basics about crosstab():

PostgreSQL Crosstab Query

About extra columns:

Pivot on Multiple Columns using Tablefunc

Advanced crosstab-foo:

Dynamic alternative to pivot with CASE and GROUP BY

I must say, the tablefunc functionality is really helpful. I wish I had know about this earlier. — user2505340, Jun 21 '13 at 13:54
@user2505340: `crosstab()` deals with complex logic that's confusing to some. But once you can wrap your mind around it, you can get fast and compact queries. If you have more use cases - I posted [quite a few comprehensive answers](http://stackoverflow.com/search?q=user%3A939860+[crosstab]) over the past year or so. — Erwin Brandstetter, Jun 21 '13 at 14:41

score 0 · Answer 2 · answered Jun 20 '13 at 17:21

The following approach works if you know the value of "N". It calculates the maximum time value for each of the devices, then joins back in the original records, and sums them using aggregation:

select tae.time, tae.value, taw.device,
       SUM(coalesce(dev.value)) as sumvalue
from (select t.*,
             MAX(case when device = 1 then time end) over (order by time) as dev1time,
             MAX(case when device = 2 then time end) over (order by time) as dev2time,
             MAX(case when device = 3 then time end) over (order by time) as dev3time
      from timeseries.total_active_energy tae
     ) tae left outer join
     timeseries.total_active_energy dev
     on dev.time in (dev1time, dev2time, dev3time)
group by tae.time, taw.value, tae.device;

Sum a sequence of device-labeled, data using most recent data for device

2 Answers2