PostgreSQL - Unexpected Results with Window Functions and Partitioning on NULL values
I'm wondering if anyone has experience with I need some guidance on I'm relatively new to this, so bear with me. I'm stuck on something that should probably be simple. I'm working on a PostgreSQL 13.4 database and trying to compute a running total using a window function, but I'm getting some unexpected results when the partitioned column contains NULL values. Here’s a simplified version of my query: ```sql SELECT customer_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total FROM orders ORDER BY customer_id, order_date; ``` The scenario arises when I have orders for some customers without any entries for certain dates (which results in NULL in `customer_id`). I expect the running total to reset for each customer, but it seems to carry over values from previous customers. For instance, I have a customer with ID NULL that has orders on different dates, but it also includes orders from other customers with non-NULL IDs in the total. I've tried adding a filter for NULLs in my PARTITION BY clause: ```sql SELECT customer_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id WHERE customer_id IS NOT NULL ORDER BY order_date) AS running_total FROM orders ORDER BY customer_id, order_date; ``` However, this results in an behavior: `SQL behavior [42601]: syntax behavior at or near "WHERE"`. I also considered using COALESCE, but that doesn't seem to fit my needs because I want to maintain the NULLs as part of my analysis. Is there a way to correctly partition the data for running totals while handling NULLs in PostgreSQL without carrying over results from one partition to another? Any insights or workarounds would be greatly appreciated! This is part of a larger CLI tool I'm building. Could someone point me to the right documentation? This is part of a larger REST API I'm building. What's the best practice here? This issue appeared after updating to Sql 3.10. Any ideas what could be causing this?