dimanche 11 octobre 2015

Comparing data in a SQL table with historical data in the same table

I have a table containing some data aggregated by month and year:

Date             Value
----------------------
2014-03             24
2014-04             23
2014-05             24
2014-06             23
... (data rows for 2014-07 through 2015-07)
2015-08             28
2015-09             27
2015-10             19

I want to construct a view that for given year shows the value from that year, along with the corresponding data from one year in the past. For the months that have no data (like November and December 2015) I still want to have rows, with a zero value for the current year, and the historical value for the previous year. The converse also applies, if there is no data from the previous year from that month, the view should still show the months (like January and February 2014 in the example), but with 0 as the value:

Date             Value        Value last year
---------------------------------------------
2015-12              0                     12
2015-11              0                     11
2015-10             19                     29
2015-09             27                     11
2015 08             28                     18
2015-07             25                     17
2015-06             23                     23
2015-05             18                     24
2015-04             24                     23
2015-03             24                     24
2015-02             23                      0
2015-01             11                      0

Is there a straightforward way to create such a view? I am having trouble getting the view to include the last two months for which there is not data from 2015. The naïve approach with a left outer join does not include rows for which there is now data in 2015. How do I 'fake' data for months in the current year that have no data?

In SQLITE syntax, the naïve query looks like this.

select A.DATE, A.VALUE, B.VALUE FROM
AGGREGATOR A
LEFT OUTER JOIN AGGREGATOR B 
ON A.DATE = date(B.DATE, '+1 year');

Any pointers would be appreciated.

Aucun commentaire:

Enregistrer un commentaire