REDSHIFT: Как я могу сгенерировать серию чисел, не создавая таблицу с именем "numbers" в красном смещении (Postgres 8.0.2)?

Question

Мне нужно создать пустую серию временных таблиц для отчета, чтобы я мог оставить в ней объединение действий из нескольких таблиц. Каждый час дня не обязательно имеет данные, но я хочу, чтобы он показывал ноль или ноль для неактивности, а не пропускал этот час дня.

В более поздних версиях Postgres (post 8.0.2) это легко сделать несколькими способами:

SELECT unnest(array[0,1,2,3,4...]) as numbers

ИЛИ ЖЕ

CROSS JOIN (select generate_series as hours from generate_series(now()::timestamp, now()::timestamp + interval '1 day', '1 hour'::interval )) date_series

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

ЧТО МНЕ НУЖНО:

Надежный способ генерации серии чисел (например, 0-23) в качестве подзапроса, который будет выполняться в режиме красного смещения (использует postgres 8.0.2).

Answer 1

Пока у вас есть таблица, в которой больше строк, чем в нужных сериях, есть номера, это то, что работало для меня в прошлом:

select
    (row_number() over (order by 1)) - 1 as hour
from
    large_table
limit 24
;

Который возвращает числа 0-23,

Answer 2

Рекурсия была выпущена для Redshift в апреле 2021 года. Теперь эта рекурсия возможна в Redshift. Вы можете сгенерировать серию чисел (или даже таблицу) с помощью кода ниже

      with recursive numbers(NUMBER) as
(
select 1 UNION ALL
select NUMBER + 1 from numbers where NUMBER < 28
)

Answer 3

К сожалению, Amazon Redshift не позволяет использовать generate_series() для табличных функций. Обходной путь, кажется, создает таблицу чисел.

Смотрите также:

• Использование функции sql generate_series() в красном смещении
• Создать серию в Redshift и MySQL, которая не кажется правильной, но привносит некоторые интересные идеи

Answer 4

Я не большой поклонник запросов к системной таблице, чтобы получить список номеров строк. Если бы это было чем-то постоянным и достаточно маленьким, как часы дня, я бы пошел с простой старой UNION ALL:

WITH 
  hours_in_day AS (
    SELECT 0 AS hour
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    ...
    UNION ALL SELECT 23
  )

А потом присоединитьсяhours_in_day к тому, что вы хотите.