generate_series的数据缺口问题

Question

我需要一个查询，返回当月每天所有已支付账单的累计金额。

我试过一些代码，包括这个：

SELECT DISTINCT 
month.day,
sum(bills.value) OVER (ORDER BY month.day)
FROM generate_series(1,31) month(day)
LEFT JOIN bills ON date_part('day',bills.payment_date) = month.day
WHERE
(
(date_part('year',bills.payment_date)=date_part('year',CURRENT_DATE)) AND
(date_part('month',bills.payment_date)=date_part('month',CURRENT_DATE))
)
GROUP  BY month.day, bills.value, bills.payment_date
ORDER  BY month.day

我得到：

day  | value  
1    |  1000   
4    |  3000   
5    |  5000

总和是正确的，但我并没有从generate_series函数中得到所有的31天。此外，当我删除DISTINCT命令时，查询只会重复几天，比如：

day  | value  
1    |  1000   
4    |  3000   
4    |  3000  
4    |  3000  
4    |  3000  
5    |  5000   
5    |  5000   

我想要的是：

day  | value  
1    |  1000   
2    |  1000   
3    |  1000  
4    |  3000  
5    |  5000  
6    |  5000   
...  |  5000   
31   |  5000  

有什么想法吗？

Answer 1

通过从组中删除bills.value, bills.payment_date，那么您也不再需要distinct。您还可以简化WHERE子句。但您需要将该条件移动到JOIN条件中，否则它将把外部联接变回内部联接。

SELECT month.day,
sum(sum(bills.value)) over (order by month.day) as total_value
FROM generate_series(1,31) month(day)
LEFT JOIN bills
ON date_part('day',bills.payment_date) = month.day
AND to_char(bills.payment_date, 'yyyymm') = to_char(current_date, 'yyyymm')
GROUP  BY month.day
ORDER  BY month.day