On Wednesday 24 November 2010 1:08:27 am Mark Morgan Lloyd wrote: > What is best practice when extracting both current and average from a > table? Demonstration table here contains data from a cheap weather station. > > I can obviously get the current reading like this: > > select temp_out, dewpoint > from weather > where datetime between (now() - '10 minutes'::interval) and now() > order by datetime desc > limit 1; > > and I can get averages like this: > > select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint > from weather > where datetime between (now() - '45 minutes'::interval) and now(); > > In both cases there are a dozen or so columns in total. How are these > best merged to yield a single row? Some form of join, or window functions? I am not seeing a dozen columns, maybe rows? I quick and dirty solution(testing needed): select temp_out,dewpoint,atbl.avg_temp_out,atbl.avg_dewpoint from (select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now()) as atbl, weather order by datetime desc limit 1; > > -- > Mark Morgan Lloyd > markMLl .AT. telemetry.co .DOT. uk > > [Opinions above are the author's, not those of his employers or colleagues] -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
