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?

• ypercubeᵀᴹ: Did you try it? What was the issue?
• Laurenz Albe: How would NULL contribute to the sum?
• ypercubeᵀᴹ: You can have a partition where s.LocationId is 1, s.PartId is 2 and s.SupplierFacilityId is NULL. So where is the problem?
• ypercubeᵀᴹ: Please edit and add this in the question, preferably as text, not image
• Charlieface: Which grouping would you want it to be part of e.g values in SupplierFacilityId are null, 1, 1, 2, 3 which of those values do you want to add its sum to? Or do you want a separate group for the null? From the screenshot you link above, it's unclear why SupplierFacilityId is being grouped at all

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 ...

• ypercubeᵀᴹ: Perhaps they don't want to multiply the rows with null values but have a layer first where each null is assigned to a value (say with a rule to find the previous non-null value per PARTITION BY s."LocationId", s."PartId" ORDER BY s."DueDate") and then do a rolling sum with PARTITION BY s."LocationId", s."PartId", s."SupplierFacilityId". Just another possibility, the OP has to clarify of course
• Erwin Brandstetter: @ypercubeTM: Yup, that's another way this might pan out. Or the query always has said WHERE condition anyway.

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"

• vmachacek: this wont help me, I need the mechanism which calculates the sum on partition by column which are certain value OR they are null (to include them in the calculation) imgur.com/KtU2DrX this is showing what I have in mind. Running sum should be 445 on the last row.
• J.D.: @vmachacek The only thing I was able to interpret from your image is that when the SupplierFacilityId is null then the RunningSum should be the QuantityChange (-20 in your image's example). I've updated my code to reflect this with a simple CASE statement. If you need the rules to be different than that, then please specify the exact rules you want it to follow.