Postgres SUM over PARTITION BY "tolerate" null values

Question

I have a view which calculates rolling sum taking in value form previous row based on PARTITION BY clause, and I need to indicate one field in PARTITION BY clause can have nulls in them.

sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId", s."SupplierFacilityId" ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "RunningSum"

Sometimes SupplierFacilityId is null and that's fine, it should still be included in the sum() calculation.

Is this possible with Postgres?

Answer 1

Your image from the comment (which should be text in the question) shows that you run the query with this WHERE clause:

WHERE ... AND ("SupplierFacilityId" IS NULL OR "SupplierFacilityId" = 647)

So you can just drop "SupplierFacilityId" from PARTITION BY to achieve what you want: rows with "SupplierFacilityId" IS NULL are treated as equal peers of the partition:

sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId"
                              ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "RunningSum"

Also, since the default framing option is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and assuming that (s."LocationId", s."PartId", s."DueDate") is defined unique (which makes RANGE and ROWS equivalent in this context), you can just drop the custom framing options:

sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId"
                              ORDER BY s."DueDate") AS "RunningSum"

Without said WHERE clause, your goal would be unachievable with a simple window function as you would have to use the same rows with NULL values in multiple partitions. You would have to multiply those rows first ...

Answer 2

As ypercube mentioned in the comments, it should still work even when the SupplierFacilityId is null. But if you're running into issues or want to force it to default to a specific value, you can use the COALESCE() function to replace the NULL value with something else.

---

Per your updated comments, here's an example query that uses a simple CASE statement to hopefully achieve the logic of your image:

CASE 
    WHEN s."SupplierFacilityId" IS NULL THEN s."QuantityChange" 
    ELSE sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId", s."SupplierFacilityId" ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
END AS "RunningSum"