From: | Clodoaldo Neto <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Access a window's frame_end row from a window function |
Date: | 2013-02-22 14:26:31 |
Message-ID: | CA+Z73LGq=TK_7w5m8nz=+FxSTuxb=wrWUkL9jgtX9_GngCO9rQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Suppose there is the need to get the average of a value v over a 6 hours
time window starting 7 hours before the current row's time.
create table t (ts timestamp, v integer);
insert into t (ts, v) values
('2013-01-01 00:46', 2),
('2013-01-01 03:54', 4),
('2013-01-01 06:28', 4),
('2013-01-01 11:19', 2),
('2013-01-01 14:44', 1),
('2013-01-01 15:56', 5),
('2013-01-01 18:01', 4),
('2013-01-01 19:40', 0),
('2013-01-01 20:38', 5),
('2013-01-01 21:22', 0);
I can do it with a correlated subquery:
select ts, v,
(
select avg(v)
from t s
where ts between
t.ts - interval '7 hours'
and t.ts - interval '1 hour'
) average
from t
order by ts
;
ts | v | average
---------------------+---+--------------------
2013-01-01 00:46:00 | 2 |
2013-01-01 03:54:00 | 4 | 2.0000000000000000
2013-01-01 06:28:00 | 4 | 3.0000000000000000
2013-01-01 11:19:00 | 2 | 4.0000000000000000
2013-01-01 14:44:00 | 1 | 2.0000000000000000
2013-01-01 15:56:00 | 5 | 1.5000000000000000
2013-01-01 18:01:00 | 4 | 2.6666666666666667
2013-01-01 19:40:00 | 0 | 3.3333333333333333
2013-01-01 20:38:00 | 5 | 3.3333333333333333
2013-01-01 21:22:00 | 0 | 2.5000000000000000
But if I could access a window's frame_end row as a record from a window
function:
select ts,
avg(case when ts between
frame_end.ts - interval '7 hours'
and frame_end.ts - interval '1 hour'
then v else null end
) over(order by ts)
from t
order by ts
I'm naively posting this as I have no idea how complex would it be to add
this feature. Would it perform better than the correlated subquery?
Regards, Clodoaldo
From | Date | Subject | |
---|---|---|---|
Next Message | Christian Schröder | 2013-02-22 14:32:55 | Re: Perl function leading to out of memory error |
Previous Message | Stefan Andreatta | 2013-02-22 13:57:28 | autoanalyze criteria |