How to cross join generate_series and table with Redshift?

Question

I would like to cross join a table with a series of data generated by the generate_series() function to generate 36 month.

I don't know how to make a cross join that works with this configuration.

Knowing that:

• I can't create a new table to store the result of the generate_series()

• And using Redshift I don't have a recent version of PostgreSQL I'm forced to make my generated_series() like this for it to work.

I have tried this without success.

WITH sales AS (
SELECT 
   department
  ,product
  ,count(*) as invoice 

FROM table 
  WHERE  product SIMILAR TO '%(Apple|Lemon|Salt)%' 
  AND department is not NULL 
  group by department , product order by department , product ASC
),


date_gen as( SELECT (date_trunc('month', CURRENT_DATE::TIMESTAMP))  - (i * interval '1 month') as date_datetime 
FROM generate_series(1,36) i 
)

SELECT * FROM date_gen
CROSS JOIN sales;

If you have pro tips :)

Answer 1

You can use ROW_NUMBER() over an internal table to simulate generate_series.

date_gen AS ( 
    SELECT DATE_ADD('month', - i.i, DATE_TRUNC('month', CURRENT_DATE)) AS mnth 
    FROM (
          SELECT ROW_NUMBER() OVER() i 
          FROM stl_scan 
          LIMIT 36
         ) i
)