How do I fill gaps in weekly data?

Question

I have a table with 4 fields (id, Year, Week, Totals).

I need a query, I guess using join, to fill zero values based on the year and week fields.

In my example I need to fill zero values for the weeks 3 and 4 / Year 2013

Rec Id, Year, Week, Totals
1, '2012', '52', '23'
2, '2013', '1' , '9'
3, '2013', '2' , '4'

Missing record from DB -> null, '2013', '3' , '0'
Missing record from DB -> null, '2013', '4' , '0'

4, '2013', '5' , '5'
5, '2013', '6' , '6'
6, '2013', '7' , '5'

By the way, to help people give you good answers to mysql questions, it's useful to put code in your question that will actually generate the example. IE: CREATE TABLE and INSERT INTO table. That way they can test their responses without having to manually copy your example. — Nathan Stretch, Feb 20 '13 at 00:55

score 1 · Accepted Answer · edited May 23 '17 at 11:49

That was a fun one! OK, here we go. First off, I'll give you the simple version, which relies on a couple assumptions:

You have at least one entry in your table already for each year
You have at least one of each week in your table, for any given year. IE: this query returns all numbers from 1 to 52:

SELECT DISTINCT week FROM your_table

Given those constraints, this query should do what you want:

INSERT INTO your_table (id, year, week, totals)
    SELECT null, y, w, 0 FROM (
        SELECT DISTINCT week w FROM your_table
    ) weeks
    CROSS JOIN 
    (
        SELECT DISTINCT year y FROM your_table
    ) years
    WHERE
        (y > (select min(year) from your_table) OR w > (select min(week) from your_table where `year`=y))
        AND
        (y < (select max(year) from your_table) OR w < (select max(week) from your_table where `year`=y))
        AND 
        NOT EXISTS (select year, week from your_table where `year`=y AND `week`=w)

If condition 2 might not be satisfied - if there are some weeks that are missing in every year, you can replace this line

SELECT DISTINCT week w FROM your_table

with

SELECT
    (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue) w
FROM
    (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
HAVING w >= 1 AND w <= 52

Giving this more general case:

INSERT INTO your_table (id, year, week, totals)
    SELECT null, y, w, 0 FROM (
        SELECT
            (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue) w
        FROM
            (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
        HAVING w >= 1 AND w <= 52
    ) weeks
    CROSS JOIN 
    (
        SELECT DISTINCT year y FROM your_table
    ) years
    WHERE
        (y > (select min(year) from your_table) OR w > (select min(week) from your_table where `year`=y))
        AND
        (y < (select max(year) from your_table) OR w < (select max(week) from your_table where `year`=y))
        AND 
        NOT EXISTS (select year, week from your_table where `year`=y AND `week`=w)

(You can use a similar technique to generate the list of years if condition 1 isn't satisfied, but I'm guessing you don't have entire year-long holes.)

Finally, this could be simplified a bit if you have a unique index on year and week. If you do not yet have such an index, you could create it like so:

ALTER TABLE `your_table` ADD CONSTRAINT date UNIQUE (
`year`,
`week`
)

and if you want, you could remove it when you're done, like so:

ALTER TABLE `your_table` DROP INDEX date;

In that case, the final part of the where clause can be removed:

AND 
NOT EXISTS (select year, week from your_table where `year`=y AND `week`=w)

because the INSERT IGNORE will skip any rows for which that unique year/week combination already exists.

Kudos to this answer for the range-generating code: https://stackoverflow.com/a/8349837/160565

Of course, you can generate the range of 1-52 in any number of ways. I thought this one was the coolest, but you could also use a simple loop, or several other techniques, as given by the other answers to that question I linked to. — Nathan Stretch, Feb 20 '13 at 00:59
Nathan, I really appreciate your taking the time to help me; I am very impressed with your answer! thank you so much for your help ! — Vangelis, Feb 20 '13 at 20:33

How do I fill gaps in weekly data?

1 Answers1