Skip to content
Advertisement

How to carrying over values for missing dates in time series using last value windows analytical functions in mysql

How to carrying over values for missing dates postcode/indicator_category to create full monthly time series. Im trying to use last_value to carry over values but not able to make it. Is my approach correct? Any help would by highly appreciated.

Example given a table:

INSERT INTO value to indicator_data table

INPUT:

postcode month_ts indicator_cat measure
sw5 2017-07-01 2 99212.231
sw5 2018-02-01 2 232.215
sw5 2017-11-01 3 1523.2576
sw5 2017-12-01 3 152.16
sw5 2018-02-01 3 142.981
sw5 2018-07-01 3 142.1361
sw59 2018-03-01 2 821.21
sw59 2018-02-01 2 1182.19

EXPECTED OUTPUT:

postcode month_ts indicator_cat measure
sw5 2017-07-01 2 99212.231
sw5 2017-08-01 2 99212.231
sw5 2017-09-01 2 99212.231
sw5 2017-10-01 2 99212.231
sw5 2017-11-01 2 99212.231
sw5 2017-12-01 2 99212.231
sw5 2018-01-01 2 99212.231
sw5 2018-02-01 2 232.215
sw5 2017-11-01 3 1523.2576
sw5 2017-12-01 3 152.16
sw5 2018-01-01 3 152.16
sw5 2018-02-01 3 142.981
sw5 2018-03-01 3 142.981
sw5 2018-04-01 3 142.981
sw5 2018-05-01 3 142.981
sw5 2018-06-01 3 142.981
sw5 2018-07-01 3 142.1361
sw59 2018-02-01 2 1182.19
sw59 2018-03-01 2 821.21

Tried SOLUTION:

Created Calender table using below procedue:

Calling Procedure

Query tried to missing dates but its failing to get last_values

Advertisement

Answer

You can create the calendar table’s resultset with a recursive CTE.
Then do a LEFT join of the calendar to the table and use SUM() window function to create groups of rows with null in measure.
Finally use MAX() window function to pick the last non-null measure:

See the demo.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement