sat, 11-jul-2009, 11:04

West weather station

West weather sensors

Fairbanks had some very hot weather earlier this week, including breaking a high temperature record at the airport on Wednesday afternoon. We got up to 94°F here on the Creek, but what was worse was that the low temperature on Wednesday night was 60°F, too warm to cool the house much. We were tempted to sleep out in the back cabin because the house was so warm.

It’s been cooler in the last couple days. But how much cooler?

The new version of PostgreSQL (8.4) has support for window functions, which make it easier to answer questions like this. Window functions allow you to calculate aggregates (average, sum, etc.) at one level, while display the data from another level. In this case, I want to compare the hourly average temperatures over the last 24 hours with the overall hourly average temperature over the past week. Without window functions, I’d need to combine a query that yields the hourly average temperature over the last 24 hours with a query that calculates overall seven-day hourly average temperatures. And if I want the difference between the two, the first two queries become a subquery of a third query.

Here’s the query:

SELECT dt, t_avg,
    seven_day_avg::numeric(4,1),
    (t_avg - seven_day_avg)::numeric(4,1) AS anomaly
FROM (
    SELECT dt, t_avg::numeric(4,1),
        avg(t_avg::numeric(4,1)) OVER (PARTITION BY extract(hour from dt)) AS seven_day_avg
    FROM hourly WHERE dt > current_timestamp - interval ’7 days’
    ) AS sevenday
WHERE dt > current_timestamp - interval ’24 hours’
ORDER BY dt;

And the result for the last 24 hours of temperature data:

         dt       | t_avg | seven_day_avg | anomaly
------------------+-------+---------------+---------
 2009-07-10 11:00 |  67.1 |          72.3 |    -5.2
 2009-07-10 12:00 |  70.8 |          75.8 |    -5.0
 2009-07-10 13:00 |  72.9 |          77.4 |    -4.5
 2009-07-10 14:00 |  74.1 |          78.5 |    -4.4
 2009-07-10 15:00 |  74.6 |          80.2 |    -5.6
 2009-07-10 16:00 |  75.9 |          80.4 |    -4.5
 2009-07-10 17:00 |  76.1 |          81.0 |    -4.9
 2009-07-10 18:00 |  76.9 |          80.4 |    -3.5
 2009-07-10 19:00 |  76.5 |          79.3 |    -2.8
 2009-07-10 20:00 |  73.1 |          77.0 |    -3.9
 2009-07-10 21:00 |  69.1 |          73.5 |    -4.4
 2009-07-10 22:00 |  63.7 |          68.2 |    -4.5
 2009-07-10 23:00 |  57.6 |          62.3 |    -4.7
 2009-07-11 00:00 |  52.1 |          56.5 |    -4.4
 2009-07-11 01:00 |  48.5 |          52.6 |    -4.1
 2009-07-11 02:00 |  45.5 |          49.3 |    -3.8
 2009-07-11 03:00 |  43.4 |          47.9 |    -4.5
 2009-07-11 04:00 |  42.2 |          47.1 |    -4.9
 2009-07-11 05:00 |  44.8 |          47.6 |    -2.8
 2009-07-11 06:00 |  47.5 |          49.7 |    -2.2
 2009-07-11 07:00 |  51.2 |          53.8 |    -2.6
 2009-07-11 08:00 |  55.3 |          59.2 |    -3.9
 2009-07-11 09:00 |  60.4 |          64.0 |    -3.6
 2009-07-11 10:00 |  65.5 |          68.3 |    -2.8

Conclusion? It’s been several degrees cooler in the last 24 hours compared with the last seven days.

And window functions are groovy.

Meta Photolog Archives