I have a large table (in Snowflake) of amounts for multiple entities in multiple currencies across a variety of time periods. However, instances where the amount is 0 are missing, and need to be added- starting from the first date up to the current. A sample of the data looks something like this:

The problem with most of the solutions I have seen that work by generating a table of dates and joining is that:

• they will only fill in dates once, rather than filling in dates for all of the partitions/pairs (A and USD, A and CAD, B and USD, B and CAD)
• they use a fixed starting date from a pre-generated table, while the earliest date varies depending on which pair of Name and Currency are being considered

Oracle DB supports something like a partitioned outer join, which would get me at least part way there. However Snowflake has no such ability.

I am not too concerned with efficiency.

CodePudding user response：

WITH fake_data AS (
    SELECT * FROM VALUES
        ('A','USD','2020-01-01'::date,3)
        ,('A','USD','2020-01-03'::date,4)
        ,('A','USD','2020-01-04'::date,2)
        ,('A','CAD','2021-01-04'::date,5)
        ,('A','CAD','2021-01-06'::date,6)
        ,('A','CAD','2020-01-07'::date,1)
        ,('B','USD','2019-01-01'::date,3)
        ,('B','USD','2019-01-03'::date,4)
        ,('B','USD','2019-01-04'::date,5)
        ,('B','CAD','2017-01-04'::date,3)
        ,('B','CAD','2017-01-06'::date,2)
        ,('B','CAD','2017-01-07'::date,2)
    d(Name,Currency,Date,Amount)
), partition_ranges AS (
    SELECT name,
        currency, 
        min(date) as min_date, 
        max(date) as max_date,
        datediff('days', min_date, max_date) as span
    FROM fake_data
    GROUP BY 1,2
), huge_range as (
    SELECT ROW_NUMBER() OVER(order by true)-1 as rn
    FROM table(generator(ROWCOUNT => 10000000))
), in_fill as (
    SELECT pr.name,
        pr.currency,
        dateadd('day', hr.rn, pr.min_date) as date
    FROM partition_ranges as pr
    JOIN huge_range as hr ON pr.span >= hr.rn
)
SELECT 
    i.name, 
    i.currency, 
    i.date,
    nvl(d.amount, 0) as amount
from in_fill as i
left join fake_data as d on d.name = i.name and d.currency = i.currency and d.date = i.date
order by 1,2,3;