Re: Access a window's frame_end row from a window function

Clodoaldo Neto <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com> writes:
> 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.
> ...
> 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?

Doubt it. As stated, it'd likely perform worse, since it's not obvious
in this construction that rows outside the desired time window need not
be scanned to compute the avg(). But even if you rearranged the SQL to
avoid that pitfall, I'm not sure how the implementation could look
noticeably different from a correlated subselect. It'd still end up
scanning all the desired rows for each row of the outer query.

regards, tom lane