Access a window's frame_end row from a window function

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

Responses

Browse pgsql-general by date

  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