Frame clause should not be specified for ranking window functions

Question

I am getting this error message "Frame clause should not be specified for ranking window functions" when trying to run this code for a temp table in redshift:

CREATE TEMP TABLE BASE_DATE_DATA
AS
(
SELECT
CALENDAR_DATE AS TRANS_DATE,
ROW_NUMBER() OVER (ORDER BY (TRANS_DATE) ROWS BETWEEN CURRENT ROW AND 2404 FOLLOWING) AS DATE_TIME_KEY,
TRIM(EXTRACT(YEAR FROM CALENDAR_DATE))||' Q'||TRIM(QUARTER_OF_YEAR) AS QUARTER,
TRIM(EXTRACT(YEAR FROM CALENDAR_DATE))||' M'||TRIM(MONTH_OF_YEAR) AS "MONTH",
TRIM(EXTRACT(YEAR FROM CALENDAR_DATE))||' W'||TRIM(((DAY_OF_YEAR - MOD((DAY_OF_CALENDAR - (7 - 1)) , 7) + 1) / 7) + 1) AS WEEK,
MIN(CALENDAR_DATE) OVER (PARTITION BY QUARTER_OF_YEAR,YEAR_OF_CALENDAR ORDER BY CALENDAR_DATE ASC) AS QUARTER_START_DATE,
MAX(CALENDAR_DATE) OVER (PARTITION BY QUARTER_OF_YEAR,YEAR_OF_CALENDAR ORDER BY CALENDAR_DATE ASC) AS QUARTER_END_DATE,

--MIN(CALENDAR_DATE) OVER (PARTITION BY MONTH_OF_YEAR,YEAR_OF_CALENDAR ORDER BY CALENDAR_DATE ASC) AS MONTH_START_DATE,
--(MAX(CALENDAR_DATE) OVER (PARTITION BY MONTH_OF_YEAR,YEAR_OF_CALENDAR ORDER BY CALENDAR_DATE ASC) + INTERVAL '1' DAY) AS MONTH_END_DATE,
CAST(19 AS INT) AS ASSET_ID
FROM public.CALENDAR
WHERE CALENDAR_DATE BETWEEN '2021-04-01' AND '2021-10-31'
)
;
             

SELECT
ROW_NUMBER() OVER (ORDER BY TRANS_DATE ) AS DATE_TIME_KEY
from calendar             

Originally I did not specify a frame clause for the order by clause and got this error message "Aggregate window functions with an ORDER BY clause require a frame clause". The goal of the code is to create a date_time_key that is generated from the row_number() ranking function by ordered by the transaction date i.e the first transaction date will have a date_time_key of 1. Any suggestions to overcome this?

Answer 1

You have three window functions in this query - row_number() and max()/min(). I expect you saw the first error message and apply the change to the wrong window function.

Max & min window functions - it doesn't make sense to have an ORDER BY clause if there isn't a frame clause. If you want the max/min over the entire partition just remove the ORDER BY from these.

Row_number window function - a frame clause doesn't make any sense for this type of function. You cannot have row numbers that make sense if the frame (subset of row being used) keeps changing. Just remove "ROWS BETWEEN ..." from this window function.

Different window functions have different rules for which parts they need. I know, confusing.