PostgreSQL views on views on views... is this the correct way?-CodePudding

I have a timescaledb receiving data from two families of energy sensors. The update frequency of ones is 5', for the other is 10", with different time offsets. The counters are cumulative, and I am good with the 5' time samples for the aggregated data. The solution I have come with makes use of 5 views: one for each sensor family to get the difference on natural samples, one for the time bucketing, and a final one to outer join both families:

CREATE VIEW sampled_net_energy AS
    SELECT
      time AS "time",
      energy_t1_in   energy_t2_in - lag(energy_t1_in) OVER(ORDER BY time) - lag(energy_t2_in) OVER(ORDER BY time) as net_in,
      energy_t1_out   energy_t2_out - lag(energy_t1_out) OVER(ORDER BY time) - lag(energy_t2_out) OVER(ORDER BY time) as net_out
    FROM electricity
    ORDER BY 1;

CREATE VIEW bucketized_net_energy AS
    SELECT
      time_bucket('5 minutes', time) AS five_min,
      sum(net_in) as bkt_net_in,
      sum(net_out) as bkt_net_out
    FROM sampled_net_energy
    GROUP BY five_min
    ORDER BY 1;

CREATE VIEW sampled_solar_energy AS
    SELECT
      time AS "time",
      pvenergytotal-lag(pvenergytotal) OVER(ORDER BY time) as solar_in
    FROM t040504
    ORDER BY 1;

CREATE VIEW bucketized_solar_energy AS
    SELECT
      time_bucket('5 minutes', time) AS five_min,
      sum(solar_in) as bkt_solar_in
    FROM sampled_solar_energy
    GROUP BY five_min
    ORDER BY 1;

CREATE VIEW energy_balance AS
    SELECT
        a.five_min, bkt_net_in, bkt_net_out, bkt_solar_in
    FROM bucketized_net_energy a
    INNER JOIN bucketized_solar_energy b
    ON a.five_min = b.five_min
    ORDER BY 1;

I tried to summarize the two views for each family onto a single one, but seems I cannot use lagged series in aggregation functions (the sum) and my postgresql knowledge does not go further than that. My question, if there is any postgresql expert in the room is: Is this the correct way to tackle this issue?

UPDATE: by "I cannot user lagged series in aggregation functions" I mean that when I 'select sum(a - lag(a)) as b', I am returned the following error: 'ERROR: aggregate function calls cannot contain window function calls'.

CodePudding user response：

The solution to your problem is a subquery. If you cannot do

SELECT sum(a - lag() OVER (...))
FROM ...
GROUP BY ...

do this instead:

SELECT sum(b)
FROM (SELECT a - lag(a) OVER (...) AS b
      FROM ...) AS subq
GROUP BY ...

But I want to caution you against defining views on views. There is no technical problem with that, but it makes it very hard to maintain and debug your queries. That is because in the actual queries, all views are resolved, and you end up with a rather complicated query that is hard to understand. In particular, you run the risk of joining extra unnecessary tables, or joining with the same table twice because it occurs in two views. I have repeatedly been asked to tune such queries because the performance was bad, and very often I just have to give up because the Babylonian tower of views is just too much for my simple brain.