Washing Window

[beararmy]

I'd like to be able to see a (user defined) length window of the cheapest electric for the day.

Just a simple "Today's cheapest 4 hour window is 1800-2200" kind of thing.

Consider using LEAD and LAG for this.

[beararmy]

I think we're good with the below, it doesn't look like LEAD gives us the option to say "give me the next n results", so going this way.

4hR_Hour_wndw named thus as a tribute to @andyj30

SELECT *, ( (
        value_inc_vat +
        LEAD(value_inc_vat, 1) OVER (ORDER BY valid_to) +
        LEAD(value_inc_vat, 2) OVER (ORDER BY valid_to) +
        LEAD(value_inc_vat, 3) OVER (ORDER BY valid_to) +
        LEAD(value_inc_vat, 4) OVER (ORDER BY valid_to) +
        LEAD(value_inc_vat, 5) OVER (ORDER BY valid_to) +
        LEAD(value_inc_vat, 6) OVER (ORDER BY valid_to) ) / 6 )
        AS 4hR_Hour_wndw
       FROM AgileOctopus.ElectricPrices
       WHERE valid_to > NOW();

[AndyJ30]

You can condense that down to: AVG(value_inc_vat) OVER(ORDER BY valid_to ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING)

[AndyJ30]

Also that name is appropriately awful

[beararmy]

Considering using this instead of your suggestion. The LIMIT and subquery doesn't work as I would expect. Because the final results of your suggested query don't actually have six results they should be skipped. Which is what I'm trying to do.

SELECT entry_id, valid_from,
AVG(value_inc_vat)OVER(ORDER BY valid_to ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING) AS 4hR_Hour_wndw,
((SELECT COUNT(*) FROM ElectricPrices WHERE valid_to > NOW() )-5) AS test
FROM ElectricPrices
WHERE valid_to > NOW()
LIMIT ((SELECT COUNT(*) FROM ElectricPrices WHERE valid_to > NOW() )-5);```

[AndyJ30]

You could do something like this for "All in the future except the last 5":

SELECT 
    entry_id, valid_from,
    AVG(value_inc_vat) OVER(ORDER BY valid_to ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING) AS 4hR_Hour_wndw
FROM 
    ElectricPrices
    LEFT JOIN (
    SELECT entry_id
        FROM ElectricPrices
        ORDER BY valid_to DESC
        LIMIT 5
    ) AS Last_5 ON Last_5.entry_id = ElectricPrices.entry_id
WHERE 
    valid_to > NOW()
    AND Last_5.entry_id IS NULL

[beararmy]

Thanks Andy, with some minor tweaks that does what I wanted!

You could do something like this for "All in the future except the last 5":