CODEWITHSUNDEEP
mysqlsqldatabasegaps-and-islands
babarizbak
babarizbak

Reputation: 93

MySQL: fill in the gaps with last known value

I have a table containing global sales data at different reporting dates like this:

    event_id   | date          | sales_on_date
    -----------+---------------+----------
    1          | 2015-01-01    | 1000
    1          | 2015-02-01    | 1500
    1          | 2015-03-01    | 1600
    2          | 2015-01-01    | 200
    2          | 2015-02-01    | 500
    3          | 2015-01-01    | 100

And I need to write a SELECT statement to output the sales (or the last known sales) for every date, like this:

    event_id   | date          | sales_on_date
    -----------+---------------+----------
    1          | 2015-01-01    | 1000
    1          | 2015-02-01    | 1500
    1          | 2015-03-01    | 1600
    2          | 2015-01-01    | 200
    2          | 2015-02-01    | 500
    2          | 2015-03-01    | 500
    3          | 2015-01-01    | 100
    3          | 2015-02-01    | 100
    3          | 2015-03-01    | 100

Currently, I have to make a SQL request to fetch all data from my table, and then use a PHP routine to fill in the gaps, but it seems a 'pure' SQL solution would be much more convenient and elegant.

Any idea how to do this in SQL?

Upvotes: 1

Views: 1012

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This is a bit complicated, because the query has two parts. The first is generating all the rows. The second is filling them in. The first is a CROSS JOIN. The second a correlated subquery:

select e.event_id, d.date,
       (select t.sales_on_date
        from t
        where t.event_id = e.event_id and t.date <= d.date
        order by t.date desc
        limit 1
       ) as sales_on_date
from (select distinct event_id from t) e cross join
     (select distinct date from t) d
order by e.event_id, d.date;

Upvotes: 1

Related Questions