0

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?

7
  • Did you try it? What was the issue? Apr 21 at 10:53
  • How would NULL contribute to the sum? Apr 21 at 10:53
  • You can have a partition where s.LocationId is 1, s.PartId is 2 and s.SupplierFacilityId is NULL. So where is the problem? Apr 21 at 10:55
  • 1
    Please edit and add this in the question, preferably as text, not image Apr 21 at 12:36
  • 1
    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 Apr 22 at 0:14
3

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

2
  • 1
    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 Apr 22 at 8:50
  • @ypercubeᵀᴹ: Yup, that's another way this might pan out. Or the query always has said WHERE condition anyway. Apr 22 at 8:57
1

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"
2
  • 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.
    – vmachacek
    Apr 21 at 12:31
  • @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.
    – J.D.
    Apr 21 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.