我是postgresql的新手，遇到了一个新问题，从subscribe_start_date的月份开始，到当前月份结束，对每个月的subscription_qty进行求和
given table is
account_id. |. subscribe_start_date. | subscription_qty
123 2021-01-29 00:00:00.0 26
112 2021-10-27 00:00:00.0 261
---
now() i.e. today's date is 10/07/2021.
我已经研究了' generate_series()函数，它做的工作，但日期值是固定的，它无法处理以下边缘情况(这是预期的)
伪逻辑如下:
- if subscription_date is between 1 and 28 then month-to-month cycle works great but
- if subscription_date is 29, 30 or 31 then there are two issues
- 1st issue: feb month is of 29 in leap year and if we just consider 2021 which has 28 days in feb then if subscription_date was 2021-01-29 00:00:00.0 then next billing date will be 2021-02-28 00:00:00.0 but then march will become 2021-01-29 00:00:00.0 again and it will continue.
- 2nd issue: if day of subscription_date is 2021-01-31 00:00:00.0 then we have months of 30 and feb issue as listed above.
我想用cte生成start_date和end_date，然后在主查询to sum(subscription_qty) where usage_date >=start_date and usage_date<=end_date中使用它。有人可以建议如果在sub_query中生成start和end_date是好主意，以及我们如何在考虑边缘情况时做到这一点。这是一个很好的方法，你也可以分享样例代码来生成start_date和end_date列范围每月计费周期从subscription_start_date到现在。样品可能是:
account_id. |. subscribe_start_date. | start_date. | end date
123 2021-01-29 00:00:00.0 2021-01-29. 2021-02-28
123 2021-01-29 00:00:00.0 2021-03-01. 2021-03-31
...
...
123 2021-01-29 00:00:00.0 2021-09-01. 2021-09-30
123 2021-01-29 00:00:00.0 2021-10-01. 2021-10-31
提前感谢您的帮助!
日历混乱，月份之间甚至在同一个月(2月)内的天数不同。幸运的是，Postgres意识到了这种不规律(几乎总是如此)。所以使用它，让Postgres来处理它们。通过1 month和1 day的间隔，你可以使2月的不规则性和每个月的30/31天完全消失。
第一个start_date是subscription_date，它的结束日期是start_date月份的月底或下一个月的月底。从这里开始，每件事都遵循一个特定的模式:start_date在end_date + 1天之前，end_date是该月的最后一天。决定一个月的最后一天的问题。但这很简单:将Start_Date截断为月份加1个月减去1天。注意这完全忽略了实际月的天数。然后将所有这些都包装在一个递归CTE中，该CTE从一行到下一行(迭代地)进行步进。如下:
with recursive billing( account_id, subscription_start, start_date, end_date) as
( select s.account_id
, s.subscription_start
, s.subscription_start::date
, case when extract(day from s.subscription_start) > 28
then (date_trunc('month', s.subscription_start+interval '2 month' ) - interval '1 day')::date
else (date_trunc('month', s.subscription_start+interval '1 month' ) - interval '1 day')::date
end
from subscriptions s
union all
select b.account_id
, b.subscription_start
, (b.end_date + interval '1 day')::date
, ((b.end_date + interval '1 day')::date + interval '1 month' - interval '1 day')::date
from billing b
where date_trunc('month',end_date) <= date_trunc('month',now())
)
select * from billing
order by account_id desc, start_date;
查看这里的演示。查看2020年2月和2021年2月的时差