Thread: date_trunc to aggregate values?
I am looking for suggestions on aggregation techniques using a timestamp =
column. In my case I have tried:
date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),
but date_truck only seems to aggregate the timestamp. I thought I could =
use=20
AVG(derived_tsoil_fifteen_min_stacked.value)
in combination with date_trunk, but I still get 15 minute values, not =
the hourly average from the four 15 minute records.
rowid date_truck =
time2 site canopy plot =
variable name value avg
2010-07-07_00:00:00_b4warm_a 2010-07-07 00:00:00 1 =
2010-07-07 00:00:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 21.06 21.0599994659424
2010-07-07_00:15:00_b4warm_a 2010-07-07 00:00:00 1 =
2010-07-07 00:15:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.96 20.9599990844727
2010-07-07_00:30:00_b4warm_a 2010-07-07 00:00:00 1 =
2010-07-07 00:30:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.88 20.8799991607666
2010-07-07_00:45:00_b4warm_a 2010-07-07 00:00:00 1 =
2010-07-07 00:45:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.8 20.7999992370605
2010-07-07_01:00:00_b4warm_a 2010-07-07 01:00:00 1 =
2010-07-07 01:00:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.72 20.7199993133545
2010-07-07_01:15:00_b4warm_a 2010-07-07 01:00:00 1 =
2010-07-07 01:15:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.64 20.6399993896484
2010-07-07_01:30:00_b4warm_a 2010-07-07 01:00:00 1 =
2010-07-07 01:30:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.55 20.5499992370605
2010-07-07_01:45:00_b4warm_a 2010-07-07 01:00:00 1 =
2010-07-07 01:45:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.47 20.4699993133545
I was tying to get two records out of this set, with the 'avg" column =
representing the mean of the first and last four of each 15 minute =
records.=20
Suggestions?
2013/2/4 Kirk Wythers <wythe001@umn.edu>:
> I am looking for suggestions on aggregation techniques using a timestamp column. In my case I have tried:
>
> date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),
>
> but date_truck only seems to aggregate the timestamp. I thought I could use
>
> AVG(derived_tsoil_fifteen_min_stacked.value)
>
> in combination with date_trunk, but I still get 15 minute values, not the hourly average from the four 15 minute
records.
>
> rowid date_truck time2
site canopy plot variable name value avg
> 2010-07-07_00:00:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:00:00 cfc closed a2
tsoil_sc tsoil_avg1_sc 21.06 21.0599994659424
> 2010-07-07_00:15:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:15:00 cfc closed a2
tsoil_sc tsoil_avg1_sc 20.96 20.9599990844727
> 2010-07-07_00:30:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:30:00 cfc closed a2
tsoil_sc tsoil_avg1_sc 20.88 20.8799991607666
> 2010-07-07_00:45:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:45:00 cfc closed a2
tsoil_sc tsoil_avg1_sc 20.8 20.7999992370605
> 2010-07-07_01:00:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:00:00 cfc closed a2
tsoil_sc tsoil_avg1_sc 20.72 20.7199993133545
> 2010-07-07_01:15:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:15:00 cfc closed a2
tsoil_sc tsoil_avg1_sc 20.64 20.6399993896484
> 2010-07-07_01:30:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:30:00 cfc closed a2
tsoil_sc tsoil_avg1_sc 20.55 20.5499992370605
> 2010-07-07_01:45:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:45:00 cfc closed a2
tsoil_sc tsoil_avg1_sc 20.47 20.4699993133545
>
> I was tying to get two records out of this set, with the 'avg" column representing the mean of the first and last
fourof each 15 minute records.
>
> Suggestions?
Are you using an explicit GROUP BY?
--
Jason Dusek
pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B
On Feb 4, 2013, at 3:26 PM, Jason Dusek <jason.dusek@gmail.com> wrote:
> 2013/2/4 Kirk Wythers <wythe001@umn.edu>:
>> I am looking for suggestions on aggregation techniques using a =
timestamp column. In my case I have tried:
>>=20
>> date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),
>>=20
>> but date_truck only seems to aggregate the timestamp. I thought I =
could use
>>=20
>> AVG(derived_tsoil_fifteen_min_stacked.value)
>>=20
>> in combination with date_trunk, but I still get 15 minute values, not =
the hourly average from the four 15 minute records.
>>=20
>> rowid date_truck =
time2 site canopy =
plot variable name value avg
>> 2010-07-07_00:00:00_b4warm_a 2010-07-07 00:00:00 1 =
2010-07-07 00:00:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 21.06 21.0599994659424
>> 2010-07-07_00:15:00_b4warm_a 2010-07-07 00:00:00 1 =
2010-07-07 00:15:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.96 20.9599990844727
>> 2010-07-07_00:30:00_b4warm_a 2010-07-07 00:00:00 1 =
2010-07-07 00:30:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.88 20.8799991607666
>> 2010-07-07_00:45:00_b4warm_a 2010-07-07 00:00:00 1 =
2010-07-07 00:45:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.8 20.7999992370605
>> 2010-07-07_01:00:00_b4warm_a 2010-07-07 01:00:00 1 =
2010-07-07 01:00:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.72 20.7199993133545
>> 2010-07-07_01:15:00_b4warm_a 2010-07-07 01:00:00 1 =
2010-07-07 01:15:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.64 20.6399993896484
>> 2010-07-07_01:30:00_b4warm_a 2010-07-07 01:00:00 1 =
2010-07-07 01:30:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.55 20.5499992370605
>> 2010-07-07_01:45:00_b4warm_a 2010-07-07 01:00:00 1 =
2010-07-07 01:45:00 cfc closed a2 tsoil_sc =
tsoil_avg1_sc 20.47 20.4699993133545
>>=20
>> I was tying to get two records out of this set, with the 'avg" column =
representing the mean of the first and last four of each 15 minute =
records.
>>=20
>> Suggestions?
>=20
> Are you using an explicit GROUP BY?
>=20
Here is what I have in the GROUP BY clause
GROUP BY
date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),
derived_tsoil_fifteen_min_stacked.time2,
data_key.site,
data_key.canopy,
data_key.variable_name,
data_key.plot