Re: lag() default value ignored for some window partition depending on table records count?

On Wed, 27 Apr 2022 at 10:48, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Tue, Apr 26, 2022 at 3:22 PM Loïc Revest <l(dot)revest(at)apc(dot)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.

There can only be qual pushdown when the column in the qual is present
in the PARTITION BY clause. In this case, there's no PARTITION BY
clause, so can't be pushdowns.

And yeah, due to lack of a PARTITION BY, LAG's default value is only
applied to exactly 1 row, the one with the lowest peer_id and
graph.date. The example one showing the NULL is peer_id 10043, and
one showing 0.0 is peer_id 10015. So 10043 is certainly not the lowest
peer_id in the recordset. That indicates the NULL is not from the LAG
but actually just a NULL returned by jsonb_to_recordset().

Loïc, If the required behaviour is to replace NULL with 0.0, then
COALESCE is what you need.

David