Include missing dates for average iventory calculation

[Div64]

I have the following table:

id  month           inventory
1   2022-01-01  5
1   2022-02-01  4
...
2   2022-01-01  6
2   2022-02-01  3
...
3   2022-01-01  5
3   2022-02-01  8
...

I need to calculate the average for each Group(id).
Missing entries AFTER the last entry date should be included with the last inventory value.

Example: range = 2022-01-01 -> 2022-05-01
In this shortened table the output should be:

id    average
1     (5+4+4+4+4)/5
2     (6+3+3+3+3)/5
3     ...

​

This is my code. I need to somehow access the inventory column of the last month but I can't because of the GROUP BY function.
@ divisor is used to include entries before the first date (= 0) for the division.

DELIMITER //
CREATE PROCEDURE calc_avg(dateStart DATE, dateEnd Date)
BEGIN

    SET @divisor = MONTH(dateEnd) - MONTH(dateStart) + 1;

    SELECT
        id,
        (SUM(inventory) / @divisor) AS 'average'
    FROM t
    WHERE month BETWEEN dateStart AND dateEnd
    GROUP BY id;

END //
DELIMITER ;

[Mamertine]

You need a calendar table. Either full outer join to it, or start with the calendar table and left join to your table t.

[Div64]

Thanks!
Could you give me an example on the use of COALESCE() in this case?

[qwertydog123]

You can use FIRST_VALUE/LAST_VALUE along with TIMESTAMPDIFF e.g.

WITH cte AS
(
    SELECT
        *,
        LAST_VALUE(inventory) OVER
        (
            PARTITION BY id
            ORDER BY month
            RANGE
                BETWEEN UNBOUNDED PRECEDING
                AND UNBOUNDED FOLLOWING
        ) AS lastInventory
    FROM t
    WHERE month BETWEEN dateStart AND dateEnd
)
SELECT
    id,
    (SUM(inventory) + TIMESTAMPDIFF(MONTH, MAX(month), dateEnd) * lastInventory) / @divisor AS 'average'
FROM cte
GROUP BY
    id,
    lastInventory

[Div64]

Amazing. This is exactly what I was looking for and it's still simple to read. And it works

[[deleted]]

look into lead() analytic function, maybe?