5 ms·

I think my only real complaint about window functions is that you can't access the 'current' row in aggregates and 'filter' conditions. It means very complex lo

by thom 10y ago

I think my only real complaint about window functions is that you can't access the 'current' row in aggregates and 'filter' conditions. It means very complex look aheads or look behinds aren't really possible. It would also be great if windows could be function parameters in Postgres. I like to abstract complicated conditionals and calculations into functions, but it falls down if you're operating over a window.

lukaseder 10y ago
Would you mind providing an example of what you'd like to do, which is too complex for window functions?
- thom 10y ago
  Apologies for contrived examples. First, it'd be nice to be able to use both rows inside some aggregates, e.g.: min(ST_Distance(this.point, foo.point)) over (blah) That is, for each 'foo', get me the minimum distance to another foo in the current window. The second peeve is to do with filters, e.g.: count(*) filter (where this.time - foo.time < 5 and type_id = 42) over (blah) That is, how many events of type 42 are there up to 5 seconds before the current event. In both cases, it's sad not being able to address the current row as well as the row in the window. I'm not saying either form is completely impossible to get working somehow, I just think it'd be a nice feature.
  - lukaseder 10y ago
    Thanks for clarifying. Oh, I see yes indeed. We can only nest aggregate functions inside of window functions, not the other way round (unless resorting to derived tables, views, ctes, etc.). Indeed, it's logical to know why it's not possible (the way the language was designed), but the language could have been designed differently. Do note that Oracle has the KEEP clause that might probably solve your issue with aggregate functions only... But it's a rather esoteric, vendor-specific extension. Anyway, I do agree, what you're describing would be a very nice feature.

by thom 10y ago

lukaseder 10y ago

Would you mind providing an example of what you'd like to do, which is too complex for window functions?

thom 10y ago
Apologies for contrived examples. First, it'd be nice to be able to use both rows inside some aggregates, e.g.: min(ST_Distance(this.point, foo.point)) over (blah) That is, for each 'foo', get me the minimum distance to another foo in the current window. The second peeve is to do with filters, e.g.: count(*) filter (where this.time - foo.time < 5 and type_id = 42) over (blah) That is, how many events of type 42 are there up to 5 seconds before the current event. In both cases, it's sad not being able to address the current row as well as the row in the window. I'm not saying either form is completely impossible to get working somehow, I just think it'd be a nice feature.
- lukaseder 10y ago
  Thanks for clarifying. Oh, I see yes indeed. We can only nest aggregate functions inside of window functions, not the other way round (unless resorting to derived tables, views, ctes, etc.). Indeed, it's logical to know why it's not possible (the way the language was designed), but the language could have been designed differently. Do note that Oracle has the KEEP clause that might probably solve your issue with aggregate functions only... But it's a rather esoteric, vendor-specific extension. Anyway, I do agree, what you're describing would be a very nice feature.