Использование sql-функции generate_series() в redshift

Я хотел бы использовать функцию генерации в redshift, но не был успешным.

Документация красного смещения говорит, что она не поддерживается. Следующий код работает:

select *
from generate_series(1,10,1)

выходы:

1
2
3
...
10

Я хотел бы сделать то же самое с датами. Я пробовал несколько вариантов, в том числе:

select *
from generate_series(date('2008-10-01'),date('2008-10-10 00:00:00'),1)

выходит:

 ERROR: function generate_series(date, date, integer) does not exist
 Hint: No function matches the given name and argument types.
 You may need to add explicit type casts. [SQL State=42883]

Также попытался:

select *
from generate_series('2008-10-01 00:00:00'::timestamp,
'2008-10-10 00:00:00'::timestamp,'1 day')

И попробовал:

select *
from generate_series(cast('2008-10-01 00:00:00' as datetime),
cast('2008-10-10 00:00:00' as datetime),'1 day')

оба выхода:

ERROR: function generate_series(timestamp without time zone, timestamp without time zone, "unknown") does not exist
Hint: No function matches the given name and argument types.
You may need to add explicit type casts. [SQL State=42883]

Если не похоже, что я буду использовать этот код из другого сообщения:

SELECT to_char(DATE '2008-01-01'
+ (interval '1 month' * generate_series(0,57)), 'YYYY-MM-DD') AS ym

PostgreSQL generate_series() с функцией SQL в качестве аргументов

Ответ 1

Amazon Redshift, похоже, основан на PostgreSQL 8.0.2. Аргументы timestamp для generate_series() были добавлены в 8.4.

Что-то вроде этого, что обходит эту проблему, может работать в Redshift.

SELECT current_date + (n || ' days')::interval
from generate_series (1, 30) n

Он работает в PostgreSQL 8.3, который является самой ранней версией, которую я могу проверить. Он задокументирован в 8.0.26.

Позже.,.

Кажется, что generate_series() не поддерживается в Redshift. Но, учитывая, что вы подтвердили, что select * from generate_series(1,10,1) работает, синтаксис, по крайней мере, дает вам шанс на бой. (Хотя тип данных интервала также документируется как неподдерживаемый в Redshift.)

Еще позже.,.

Вы также можете создать таблицу целых чисел.

create table integers (
  n integer primary key
);

Заполните его, как вам нравится. Возможно, вы сможете использовать generate_series() локально, выгрузить таблицу и загрузить ее в Redshift. (Я не знаю, я не использую Redshift.)

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

select (current_date + n)
from integers
where n < 31;

Это работает как минимум в 8.3.

Ответ 2

Используя Redshift сегодня, вы можете создать диапазон дат, используя функции datetime и подавая в таблицу чисел.

select (getdate()::date - generate_series)::date from generate_series(1,30,1)

Создает это для меня

Ответ 3

Функция 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 дней

Ссылка: функция generate_series в Amazon Redshift

Ответ 4

Мне нужно было сделать что-то подобное, но с 5-минутными интервалами в течение 7 дней. Итак, здесь взломанный на CTE (уродливый, но не слишком многословный)

INSERT INTO five_min_periods
WITH 
periods  AS (select 0 as num UNION 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 10 UNION select 11),
hours    AS (select num from periods UNION ALL select num + 12 from periods),
days     AS (select num from periods where num <= 6),
rightnow AS (select CAST( TO_CHAR(GETDATE(), 'yyyy-mm-dd hh24') || ':' || trim(TO_CHAR((ROUND((DATEPART (MINUTE, GETDATE()) / 5), 1) * 5 ),'09')) AS TIMESTAMP) as start)
select  
  ROW_NUMBER() OVER(ORDER BY d.num DESC, h.num DESC, p.num DESC) as idx
  , DATEADD(minutes, -p.num * 5, DATEADD( hours, -h.num, DATEADD( days, -d.num, n.start ) ) ) AS period_date
from days d, hours h, periods p, rightnow n

Должен иметь возможность распространять это на другие схемы генерации. Фокус здесь заключается в использовании декартового соединения (т.е. Без предложения JOIN/WHERE), чтобы умножить обработанный вручную CTE для получения необходимых приращений и применить к дате привязки.

Ответ 5

Как насчет времени? с 00:00:00 до 23:00:00

Ответ 6

Согласно комментариям @Ryan Tuck и @Slobodan Pejic generate_series() не работает в Redshift при присоединении к другой таблице.

Обходной путь, который я использовал, заключался в том, чтобы записать каждое значение в серии в запросе:

SELECT
'2019-01-01'::date AS date_month
UNION ALL
SELECT
'2019-02-01'::date AS date_month

Использование такой функции Python:

import arrow

def generate_date_series(start, end):
    start = arrow.get(start)
    end = arrow.get(end)

    months = list(
        f"SELECT '{month.format('YYYY-MM-DD')}'::date AS date_month"
        for month in arrow.Arrow.range('month', start, end)
    )

    return "\nUNION ALL\n".join(months)