Clodoaldo Neto <clodoaldo.pinto.neto@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
