How to divide annual amount to months in postgresql-CodePudding

I have an annual amount for each subscription with start_date

structure;

userid startDate(dd-mm-yyyy)     amount
1      01-10-2020                120
1      01-10-2021                240
2      01-08-2020                60

I want to divide annual amount to months equally.

Output table should be like;

userid startDate(dd-mm-yyyy)     amount
1      01-10-2020                10
1      01-11-2020                10
1      01-12-2020                10
1      01-01-2021                10
1      01-02-2021                10
1      01-03-2021                10
1      01-04-2021                10
1      01-05-2021                10
1      01-06-2021                10
1      01-07-2021                10
1      01-08-2021                10
1      01-09-2021                10

1      01-10-2021                20
1      01-11-2021                20
1      01-12-2020                20
1      01-01-2021                20
1      01-02-2021                20
1      01-03-2021                20
1      01-04-2021                20
1      01-05-2021                20
1      01-06-2021                20
1      01-07-2021                20
1      01-08-2021                20
1      01-09-2021                20

2      01-08-2020                5
2      01-09-2020                5
2      01-10-2020                5
2      01-11-2020                5
2      01-12-2020                5
2      01-01-2021                5
2      01-02-2021                5
2      01-03-2021                5
2      01-04-2021                5
2      01-05-2021                5
2      01-06-2021                5
2      01-07-2021                5

Where should I start, how can I achieve this on postgresql?

CodePudding user response：

select userid
      ,generate_series(startdate, startDate   interval '11 month', '1 month')
      ,amount/12
from   t

userid	generate_series	?column?
1	2020-10-01 00:00:00	10
1	2020-11-01 00:00:00	10
1	2020-12-01 00:00:00	10
1	2021-01-01 00:00:00	10
1	2021-02-01 00:00:00	10
1	2021-03-01 00:00:00	10
1	2021-04-01 00:00:00	10
1	2021-05-01 00:00:00	10
1	2021-06-01 00:00:00	10
1	2021-07-01 00:00:00	10
1	2021-08-01 00:00:00	10
1	2021-09-01 00:00:00	10
1	2021-10-01 00:00:00	20
1	2021-11-01 00:00:00	20
1	2021-12-01 00:00:00	20
1	2022-01-01 00:00:00	20
1	2022-02-01 00:00:00	20
1	2022-03-01 00:00:00	20
1	2022-04-01 00:00:00	20
1	2022-05-01 00:00:00	20
1	2022-06-01 00:00:00	20
1	2022-07-01 00:00:00	20
1	2022-08-01 00:00:00	20
1	2022-09-01 00:00:00	20
2	2020-08-01 00:00:00	5
2	2020-09-01 00:00:00	5
2	2020-10-01 00:00:00	5
2	2020-11-01 00:00:00	5
2	2020-12-01 00:00:00	5
2	2021-01-01 00:00:00	5
2	2021-02-01 00:00:00	5
2	2021-03-01 00:00:00	5
2	2021-04-01 00:00:00	5
2	2021-05-01 00:00:00	5
2	2021-06-01 00:00:00	5
2	2021-07-01 00:00:00	5

Fiddle

CodePudding user response：

If you don't want to use a calendar table, you may try a recursive query as the following:

WITH RECURSIVE CTE AS
  (
    SELECT userid, startDate, amount/12 AS amount, 1 AS cn
    FROM table_name
    UNION ALL
    SELECT userid, startDate   interval '1 month', amount, cn 1
    FROM CTE WHERE cn 1 <= 12
  )
SELECT userid, startDate, amount FROM CTE
ORDER BY userid, startDate, cn

See a demo.