Generate individual rows from date ranges

Question

I have some information that's stored like this:

DROP TABLE IF EXISTS `demographics`;
CREATE TABLE IF NOT EXISTS `demographics` (
  `row_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `city_id` int(10) unsigned NOT NULL,
  `until_year` int(10) unsigned DEFAULT NULL,
  `population` int(10) unsigned NOT NULL,
  PRIMARY KEY (`row_id`)
) ENGINE=InnoDB;
INSERT INTO `demographics` (`row_id`, `city_id`, `until_year`, `population`) VALUES
    (1, 1, NULL, 1300),
    (2, 1, 2009, 1250),
    (3, 1, 2008, 1000),
    (4, 1, 2004, 800);

So:

SELECT until_year, population
FROM demographics
WHERE city_id=1
ORDER BY until_year IS NOT NULL, until_year DESC;

... displays:

+------------+------------+
| until_year | population |
+------------+------------+
|       NULL |       1300 |
|       2009 |       1250 |
|       2008 |       1000 |
|       2004 |        800 |
+------------+------------+

The information needs to be displayed like this:

+------+------------+
| Pop. 2000-Present |
+------+------------+
| year | population |
+------+------------+
| 2010 |       1300 |
| 2009 |       1250 |
| 2008 |       1000 |
| 2007 |       1000 |
| 2006 |       1000 |
| 2005 |       1000 |
| 2004 |        800 |
| 2003 |        800 |
| 2002 |        800 |
| 2001 |        800 |
| 2000 |        800 |
+------+------------+

My first step was to hard-code the years (other techniques looked like overcomplicating it):

DROP TABLE IF EXISTS `year`;
CREATE TABLE IF NOT EXISTS `year` (
  `year` int(10) unsigned NOT NULL,
  PRIMARY KEY (`year`)
) ENGINE=InnoDB;
INSERT INTO `year` (`year`) VALUES
    (2000),
    (2001),
    (2002),
    (2003),
    (2004),
    (2005),
    (2006),
    (2007),
    (2008),
    (2009),
    (2010),
    (2011),
    (2012),
    (2013),
    (2014),
    (2015);

Assuming that 2000 is a parameter and 2010 is YEAR(NOW()), I think I have all the information required to start joining tables. But I'm lost with the details. How can I relate each row with the years they represent?

Answer 1

Ahh! This is a fun problem.

Your demographics tables doesn't appear to contain every 'year' possible.
Because of this very important detail you need 2 things, 1) produce a contiguous sequence of years,
2) use @vars to "remember" a previous record's value so that you can handle gaps in your data.

Part 1 of the problem is creating a contiguous sequence of years: SQL does not have a 'for-loop' construct and thus it is difficult to create a sequence of numbers/values unless you have a table with ALL the potential desired values.

  Technique A) use a simple sub select:   
     FROM (SELECT 2001 AS yr UNION SELECT 2002 UNION SELECT 2003 UNION ... SELECT 2011 ) AS years_list
  Technique B) Create a table that is populated with a list of years and is joined against your data: 
     CREATE TEMPORARY TABLE years_list (INT yr); 
     INSERT INTO years_list (yr) VALUES (2001),(2002),(2003),(2004),...,(2011);

Part 2 of the problem is "remembering" values from a previous record. The best technique I've found to do this is with MySQL is using @var.

Now, your example output has the util_year in DESCending sequence. I wrapped the 'core' SQL into a sub-select and to reverse the sort with DESC. This is because until_year within the 'core' sql must be processed in ascending year sequence for @vars to work in your context.

  SET @prev_pop:=0;  -- MUST always initalize @variables
  SELECT * FROM (
    -- Start 'core' SQL to produce a sequence of years

    SELECT years_list.yr AS until_year, (@prev_pop := IFNULL(demog.population, @prev_pop)) AS population
      FROM years_list 
      LEFT JOIN  demographics AS demog ON demog.until_year = years_list.yr AND demog.city_id = ? 
     ORDER BY until_year ASC

    -- End of 'core' SQL 
  ) AS t ORDER BY until_year DESC

The 'left join' on your demographics table goes back to my assessment of gaps in your source data. When there is NOT a record for 'demog.until_year = years_list.yr' you'll get a NULL value for any demog fields referenced in the SELECT. That NULL field is handled with the function IFNULL().

IMPORTANT: The '@prev_pop :=' construct assigns the @prev_pop variable for EVERY record encountered.

When the demog.pop is not null, @prep_pop is assigned a new value. When demog.pop is null, then @prep_pop is assigned its own current value, which is actually the value from the previous record.

Enjoy.

-- J Jorgenson --