redshift windowing function not working

Question

need to port over a snowflake windowing function to redshift. getting unsupported function errors.

need to rewrite to achieve same outcome

here is snowflake query:
	select
        true as cumulative,
		(min(start_date) over (order by start_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))::varchar(10) || ' - ' ||
        (max(end_date) over (order by start_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))::varchar(10) reporting_date
		-- AS: For the cumulative period numbers, that's based on the end date
        max(period_nbr) over (order by start_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as period_nbr,
        min(reverse_period) over (order by start_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as reverse_period,
        sum(period_days) over (order by start_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as period_days,
        min(start_date) over (order by start_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) start_date,
       max(end_date) over (order by start_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) end_date
 
    from periods

and here is Redshift query :

SELECT
    true AS cumulative,
    (MIN(start_date) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))::varchar(10) || ' - ' ||
    (MAX(end_date) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))::varchar(10) AS reporting_date,
    MAX(period_nbr) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS period_nbr,
    MIN(reverse_period) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS reverse_period,
    SUM(period_days) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS period_days,
    MIN(start_date) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS start_date,
    MAX(end_date) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS end_date
FROM
    periods;

Answer

I created a table as follows

```
create table periods
(
start_date date,
end_date date,
period_days numeric,
period_nbr numeric,
reverse_period numeric
);
```

I loaded some data

```
insert into periods 
values(sysdate-1/24, sysdate, 1, 2, 3);
```

I was able to run the Redshift query you have provided without any errors.

```
SELECT 
    true AS cumulative, 
    (MIN(start_date) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))::varchar(10) 
    || ' - ' || 
    (MAX(end_date) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))::varchar(10) AS reporting_date, 
    MAX(period_nbr) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS period_nbr, 
    MIN(reverse_period) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS reverse_period, 
    SUM(period_days) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS period_days, 
    MIN(start_date) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS start_date, 
    MAX(end_date) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS end_date 
FROM periods;
```

![sql screenshot](/media/postImages/original/IMHbODK95WSdyzMQwsSZ-gvA)

redshift windowing function not working

相关内容