One pass SELECT to compute the difference between 2 item tables

Tutorials & Examples Solutions
,
1

I am using Postgresql and Grafana for monitoring. One thing I frequently need is the power consumption, which in my case is the difference between my power meter values (coming from vzlogger) and the power from 2 solar panels. The difference is computed between the power meter values an linearly interpolated solar panel power.

I am using a one pass SELECT with window functions because it is faster than using a self join.

CREATE OR REPLACE FUNCTION VZLogger_Verbrauch_bereinigt(TIMESTAMPTZ, TIMESTAMPTZ) RETURNS SETOF VZLogger_Verbrauch AS $$
    -- See https://mattboegner.com/improve-your-sql-skills-master-the-gaps-islands-problem/
SELECT time,
       value 
       + COALESCE(
           w.value0*EXTRACT(EPOCH FROM time - w.time0)
                    /NULLIF(EXTRACT(EPOCH FROM w.time1 - w.time0), 0), 0) 
       + COALESCE(
           value1*EXTRACT(EPOCH FROM time1 - time)
                    /NULLIF(EXTRACT(EPOCH FROM w.time1 - w.time0), 0), 0) 
       AS value
  FROM (
SELECT tbl,
       time, value,
       LAST_VALUE(v.time1) OVER(PARTITION BY seq_grp ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS time1, 
       LAST_VALUE(v.value1) OVER(PARTITION BY seq_grp ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS value1, 
       FIRST_VALUE(v.time0) OVER(PARTITION BY seq_grp ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS time0, 
       FIRST_VALUE(v.value0) OVER(PARTITION BY seq_grp ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS value0 
  FROM (
SELECT tbl,
       time, value,
	   LEAD(time) OVER(ORDER BY time) AS time1, 
       LEAD(value) OVER(ORDER BY time) AS value1,  
	   LAG(time) OVER(ORDER BY time) AS time0, 
       LAG(value) OVER(ORDER BY time) AS value0,  
       DENSE_RANK() OVER(ORDER BY time)
       		- DENSE_RANK() OVER(PARTITION BY tbl ORDER BY time) 
			AS seq_grp 
  FROM (
SELECT 'V' as tbl,
       time,
       value
  FROM VZLogger_Verbrauch
 UNION ALL
SELECT 'E' as tbl,
       time,
       value
  FROM PV_Tasmota_ENERGY_Power) u
WHERE u.time BETWEEN $1 AND $2) v
WHERE v.tbl = 'V') w
$$ LANGUAGE SQL;

Note that the tables are alias views for item tables. Using a table valued function is necessary because in the presence of window functions the parameters can not be replaced by a WHERE condition on a view.