Test Weighted Average, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

DROP TABLE IF EXISTS mytable;

CREATE TABLE mytable (
    stid VARCHAR(10),
    e5 DECIMAL(10,2),
    e10 DECIMAL(10,2),
    diesel DECIMAL(10,2),
    "date" TIMESTAMP
);
    
INSERT INTO mytable (stid, e5, e10, diesel, "date")
VALUES  ('e850', 1300, 1400, 1500, '2016-05-02 05:30:01+02'),
        ('e850', 1400, 1500, 1700, '2016-05-02 08:30:01+02'),
        ('e850', 1300, 1400, 1500, '2016-05-02 21:00:01+02'),
        ('e850', 1200, 1300, 1350, '2016-05-03 10:30:01+02'),
        ('e850', 1300, 1400, 1500, '2016-05-03 21:00:01+02'),
        ('954d', 1200, 1100, 1300, '2016-05-02 03:30:01+02'),
        ('954d', 1300, 1100, 1300, '2016-05-02 15:00:01+02'),
        ('954d', 1400, 1800, 1400, '2016-05-02 22:30:01+02'),
        ('954d', 1700, 1900, 1400, '2016-05-03 09:30:01+02'),
        ('954d', 1500, 1900, 1200, '2016-05-03 23:30:01+02');
        
SELECT * FROM mytable;

DROP TABLE IF EXISTS mytable2;

CREATE TABLE mytable2 AS SELECT * FROM mytable;

WITH c AS (
SELECT
    *,
    LAG(date) OVER(PARTITION BY stid ORDER BY date) AS lag_date,
    LAG(e5) OVER(PARTITION BY stid ORDER BY date) AS lag_e5,
    LAG(e10) OVER(PARTITION BY stid ORDER BY date) AS lag_e10,
    LAG(diesel) OVER(PARTITION BY stid ORDER BY date) AS lag_diesel
FROM mytable
)

INSERT INTO mytable2
SELECT
    stid,
    lag_e5 AS e5,
    lag_e10 AS e10,
    lag_diesel AS diesel,
    date_trunc('day', date) + '0 hours'::interval AS date
FROM c WHERE lag_date < date_trunc('day', date) + '0 hours'::interval
AND date > date_trunc('day', date) + '8 hours'::interval;

WITH d AS (
SELECT
    *,
    LEAD(date) OVER(PARTITION BY stid ORDER BY date) AS lead_date
FROM mytable
)

INSERT INTO mytable2
SELECT
    stid,
    e5,
    e10,
    diesel,
    date_trunc('day', date) + '23 hours'::interval AS date
FROM d WHERE lead_date >= date_trunc('day', date) + '24 hours'::interval
AND date < date_trunc('day', date) + '20 hours'::interval;

SELECT * FROM mytable2 ORDER BY stid desc, date;

with a as (
select *
, case
  when date < date_trunc('day', date) + '8 hours'::interval then date_trunc('day', date) + '8 hours'::interval
  when date > date_trunc('day', date) + '20 hours'::interval then date_trunc('day', date) + '20 hours'::interval
  else date
end d
, date_trunc('day', date) dt
from mytable2
)
, b as (
select stid, e5, e10, diesel,date,d, dt
, extract(epoch from lead(d) over (partition by stid,dt order by stid,d) - d) diff
from a
)
select DISTINCT
 stid, dt,sum(e5*diff*1.0) over (partition by stid,dt)/sum(diff) over (partition by stid,dt) e5_weight_avg
from b
order by stid desc, dt;


absolute service time: 0,49 sec