On Tue, Apr 26, 2022 at 3:22 PM Loïc Revest <l.revest@apc.fr> wrote:
- For every of this date/numeric couple within their "window partitioning", we need to determine the preceding value, thus the use of lag(<value>, 1, 0::numeric), since it's relevant for us here to get the very first record of the partition having "0.00" as its preceding value;
I'm a bit out of my league on the promises that window functions give with respect to qual pushdown and the like (and your nested view structure's impact on that): but the fact that your window doesn't do partitioning would seem to be a factor here. It is perfectly fine for lag to return a null if, in this example, the previous peer_id's LAST graph.date has a null value for graph.agg_points and that is computed before removing all peer_id values except the one the in the query where clause.
- Except that the "first" row of some partitions gets NULL instead of 0::numeric as the result of lag(<value>, 1, 0::numeric), while other get 0::numeric as expected;
- Things get stranger when the table containing the data get "purged" from every record except those corresponding to the "window partition" whose first record gets NULL as lag() output: now it also gets 0::numeric...
- "Odd" behavior may be seen for particular window partition when row count ∼ 600k, but not when only relevant rows (58 records) are kept;
Exactly, your under-specified window clause in the view doesn't match up with the usage. It seems like operator-error to me. You want the first record to be relative to the final output, which is per-id, but the window doesn't actually compute that.
