I have not used Redshift for the past few years, having migrated from AWS Redshift to Google BigQuery.
However, Redshift Serverless, which is a pay-as-you-go Redshift, finally appeared and I started to get curious about it.
Sure enough, I had completely forgotten how to use it....
When I was writing SQL in Redshift, I sometimes wanted sequential numbering and calendar tables, but it took me a long time to create them, so here is a note.
sequential table
Redshift SQL is PostgreSQL compliant. PostgreSQL has a function generate_series() to generate sequential numbers, but Redshift does not support generate_series().
However, this is only for calls when a table row is read, and can actually be used as a stand-alone call without FROM.
Example
Sequential numbering table from 0 to 2
SELECT generate_series(0, 2) AS cnt
| cnt |
|---|
| 0 |
| 1 |
| 2 |
calendar table
Now that the sequential numbering table has been created, the calendar table can be created using it.
Example
Calendar table for 3 days from 2022-01-01
SELECT DATEADD(DAY, cnt, DATE('2022-01-01')) AS dt FROM (SELECT generate_series(0, 2) AS cnt)
| dt |
|---|
| 2022-01-01 00:00:00 |
| 2022-01-02 00:00:00 |
| 2022-01-03 00:00:00 |
Impressions, etc.
Redshift Serverless is pay-as-you-go, so you can leave it alone when you don't use it, which is good for your mental health.
I was going back and forth between Redshift and BigQuery and my head was getting tired.
I might make a Redshift/BigQuery correspondence table of frequently used syntax.
