Метод generate_series() не работает в Redshift

Функция generate_series() не полностью поддерживается Redshift. См. раздел Неподдерживаемые функции PostgreSQL. раздел руководства разработчика:

В конкретных примерах второй запрос выполняется полностью на ведущем узле, поскольку ему не нужно сканировать какие-либо фактические данные таблицы, в то время как первый пытается выбрать данные и поэтому будет выполняться на вычислительном узле (узлах).

ОБНОВЛЕНИЕ:

generate_series теперь работает с Redshift.

SELECT CURRENT_DATE::TIMESTAMP  - (i * interval '1 day') as date_datetime 
FROM generate_series(1,31) i 
ORDER BY 1

Это будет генерировать дату за последние 30 дней

Вы можете использовать оконную функцию для достижения аналогичного результата. Для этого требуется существующая таблица (например, stv_blocklist), которая содержит как минимум необходимое вам количество строк, но не слишком много, что может замедлить работу.

with days as (
    select (dateadd(day, -row_number() over (order by true), sysdate::date)) as day 
    from [other_existing_table] limit 30
)
select day from days order by 1 asc

Вы можете использовать этот метод для получения других временных диапазонов, а также для группировки. Эта версия генерирует все минуты за предыдущий день, чтобы вы могли выполнить левое соединение с ней и разделить свои данные.

with buckets AS (
    select (dateadd(minute, -row_number() over (order by true), sysdate::date)) as minute 
    from [other_table] limit 1440
)
select minute from buckets order by 1 asc

Возможно, я впервые увидел это здесь.

Вы правы, что это не работает на Redshift. См. здесь.

Вы могли бы использовать что-то вроде этого

with ten_numbers as (select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0)
,generted_numbers AS
(
    SELECT (1000*t1.num) + (100*t2.num) + (10*t3.num) + t4.num-5000 as gen_num
    FROM ten_numbers AS t1
      JOIN ten_numbers AS t2 ON 1 = 1
      JOIN ten_numbers AS t3 ON 1 = 1
      JOIN ten_numbers AS t4 ON 1 = 1
)
select  gen_num from generted_numbers
where gen_num between -10 and 0
order by 1;

Вы не используете PostgreSQL. Вы используете Amazon Redshift.

Amazon Redshift не поддерживает generate_series при использовании с таблицами Redshift. Об этом прямо сказано в сообщении об ошибке.

Либо используйте настоящий PostgreSQL, либо, если вам нужны функции Redshift, вы также должны работать в рамках ограничений Redshift.

Ваш второй пример работает, потому что в нем не используются таблицы Redshift.

Почему это не работает, было объяснено выше. Тем не менее, вопрос "что мы можем с этим поделать?" открыт.

Если вы разрабатываете систему BI на любой платформе (с поддержкой генераторов или без), очень удобно иметь таблицы измерений с последовательностями чисел и дат. Как вы можете создать его в Redshift?

1. в Postgres создайте необходимую последовательность с помощью генератора
2. экспортировать в CSV
3. создать таблицу с той же схемой в Redshift
4. импортировать CSV из шага 2 в Redshift

Представьте, что вы создали очень простую таблицу с именем calendar:

 id, date
 1, 2017-01-01
 2, 2017-01-02
 ..., ...
 xxx, 2020-01-01

Таким образом, ваш запрос будет выглядеть так:

SELECT t.id, t.date_1, t.date_2, c.id as date_id, c.date
FROM mytable t
JOIN calendar c
ON c.date BETWEEN t.date_1::date AND t.date_2::date
ORDER BY 1,4

В календарной таблице вы также можете указать первые даты недели, месяца, квартала, дни недели (понедельник, вторник и т. д.), что делает такую ​​таблицу очень эффективной для агрегирования на основе времени.

Вам нужно будет использовать функции, поддерживаемые ведущим узлом. Хитрость заключается в использовании функции row_number() из любой таблицы, которую вы хотите. Допустим, мы хотим сгенерировать ряд дат от 10 дней назад до текущего момента:

   SELECT DATEADD('day', -n, (CURRENT_DATE+1)) AS generated_date
   FROM (SELECT ROW_NUMBER() OVER () AS n FROM my_table LIMIT 10) n
   ORDER BY generated_date DESC

И мы получаем:

generated_date
2020-06-24 00:00:00
2020-06-23 00:00:00
2020-06-22 00:00:00
2020-06-21 00:00:00
2020-06-20 00:00:00
2020-06-19 00:00:00
2020-06-18 00:00:00
2020-06-17 00:00:00
2020-06-16 00:00:00
2020-06-15 00:00:00

Это работает здесь (pg-9.3.3) Может быть, ваша проблема является результатом «функции» Redshift?

CREATE TABLE mytable
        ( date1 timestamp
        , date2 timestamp
        );
INSERT INTO mytable(date1,date2) VALUES
( '2014-03-30 12:00:00' , '2014-04-01 12:00:00' );

SELECT  generate_series(0, ss.g) FROM
   ( SELECT date(date2) - date(date1) AS g
     FROM mytable
   ) ss ;