Data:

Desired output:

So, basically, I want to expand the data set for the missing dates and insert the latest of preceding amount value for every newly added date (with an exception mentioned in the last paragraph).

In fact, the links below addresses the issue:

How do I fill in missing dates by group in Oracle with changing count value

However, unlike the post above the date ranges of the ID groups are not predetermined.

Plus, fill value should be 0 for the unobserved values in the month associated with the first observation (e.g. Amount column takes the value of 0 between 01.12.2020 and 28.12.2020, since the first observation of twelfth month of 2020 is on 29th day).

CodePudding user response：

You can use a recursive query to generate the dates:

SELECT d.id,
       d.dt AS "DATE",
       COALESCE(
         LAST_VALUE(t.amount)
           IGNORE NULLS OVER (PARTITION BY d.id ORDER BY d.dt),
         0
       ) AS amount
FROM   table_name t
       RIGHT OUTER JOIN (
         WITH date_ranges (id, dt, max_dt) AS (
           SELECT id,
                  TRUNC(MIN("DATE"), 'MM'),
                  LAST_DAY(TRUNC(MAX("DATE"), 'MM'))
           FROM   table_name
           GROUP BY id
         UNION ALL
           SELECT id, dt   1, max_dt
           FROM   date_ranges
           WHERE  dt < max_dt
         )
         SELECT id, dt
         FROM   date_ranges
       ) d
       ON (t.id = d.id AND t."DATE" = d.dt)
ORDER BY id, "DATE"

db<>fiddle here