I want to generate Yearmonth (YYYYMM) for each quarters between two years and associate a rank number for each line.

I retrieve a yearmonthrank as below:

SELECT  GENERATE_ARRAY(1,((Date_End-Date_Begin )*4)) as period_rank
...
UNNEST(period_rank) AS period_yearmonthrank

Which returns:

Now, I have no idea how I could generate this output for yearmonth part?

Thank you for your time!

CodePudding user response：

Try this one:

SELECT RANK() OVER (ORDER BY y, q) AS period_rank,
       FORMAT('%dd', y, q) AS yearmonth, 
  FROM UNNEST(GENERATE_ARRAY(2021, 2023)) y, UNNEST([3, 6, 9, 12]) q
;

output: