Redshift: создание последовательного диапазона чисел

В настоящее время я переношу код PostgreSQL из нашего существующего DWH в новый Redshift DWH, и некоторые запросы несовместимы. У меня есть таблица, в которой есть id, start_week, end_week и orders_each_week в одной строке. Я пытаюсь создать последовательный ряд между start_week и end_week, чтобы я разделял строки для каждой недели на временной шкале.

Напр., Вот как он присутствует в таблице

+----+------------+----------+------------------+
| ID | start_week | end_week | orders_each_week |  
+----+------------+----------+------------------+
|  1 |      3     |     5    |        10        | 
+----+------------+----------+------------------+

Вот как я хочу это иметь

+----+------+--------+
| ID | week | orders |
+----+------+--------+
| 1  | 3    | 10     |
+----+------+--------+
| 1  | 4    | 10     |
+----+------+--------+
| 1  | 5    | 10     |
+----+------+--------+

Код ниже выдает ошибку.

SELECT 
id,
generate_series(start_week::BIGINT, end_week::BIGINT) AS demand_weeks
FROM client_demand
WHERE createddate::DATE >= '2021-01-01'

[0A000] [500310] Amazon Invalid operation: указанные типы или функции (по одной на сообщение INFO) не поддерживаются в таблицах Redshift; [01000] Функция generate_series (bigint, bigint) не поддерживается.

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


sql amazon-redshift generate-series
person the_data_analyst    schedule 05.03.2021    source источник
comment
stackoverflow.com/questions/54357061/   -  person a_horse_with_no_name    schedule 05.03.2021

Ответы (2)


arrow_upward
1
arrow_downward

Гордон Линофф показал очень распространенный метод для этого, и этот подход имеет то преимущество, что процесс не генерирует строки, которые еще не существуют. Это может сделать это быстрее, чем подходы, которые генерируют данные «на лету». Однако вам нужна таблица с правильным количеством лежащих вокруг строк, а это не всегда так. Он также показывает, что этот числовой ряд необходимо соединить с вашими данными для выполнения нужной вам функции.

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

WITH twofivesix AS (
SELECT
    p0.n
    + p1.n * 2
    + p2.n * POWER(2,2)
    + p3.n * POWER(2,3)
    + p4.n * POWER(2,4)
    + p5.n * POWER(2,5)
    + p6.n * POWER(2,6)
    + p7.n * POWER(2,7)
    as n
  FROM
    (SELECT 0 as n UNION SELECT 1) p0,
    (SELECT 0 as n UNION SELECT 1) p1,
    (SELECT 0 as n UNION SELECT 1) p2,
    (SELECT 0 as n UNION SELECT 1) p3,
    (SELECT 0 as n UNION SELECT 1) p4,
    (SELECT 0 as n UNION SELECT 1) p5,
    (SELECT 0 as n UNION SELECT 1) p6,
    (SELECT 0 as n UNION SELECT 1) p7
),
fourbillion AS (
  SELECT (a.n * POWER(256, 3) + b.n * POWER(256, 2) + c.n * 256 + d.n) as n 
  FROM twofivesix a, 
       twofivesix b, 
       twofivesix c,
       twofivesix d
)
SELECT ...

В этом примере создается целая группа чисел (4B), но вы можете увеличить или уменьшить число в серии, изменив количество раз, когда таблицы перекрестно соединяются, и добавив предложения where (как это сделал Гордон Линофф). Я не думаю, что вам понадобится список, близкий к этому длинному, но я хотел показать, как его можно использовать для создания очень длинных серий. (Вы также можете писать с основанием 10, если это имеет для вас больше смысла.)

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

person Bill Weiner    schedule 05.03.2021

arrow_upward
0
arrow_downward

Среди множества функций Postgres, которые Redshift не поддерживает, есть generate_series() (кроме главного узла). Вы можете создать его самостоятельно.

Если у вас есть таблица с достаточным количеством строк в Redshift, я считаю, что этот подход работает:

with n as (
      select row_number() over () - 1 as n
      from client_demand cd
     )
select cd.id, cd.start_week + n.n as week, cd.orders_each_week
from client_demand cd join
     n
     on n.n <= (end_week - start_week);

Это предполагает, что у вас есть таблица с достаточным количеством строк, чтобы сгенерировать достаточно чисел для предложения on. Если таблица действительно большая, добавьте что-то вроде limit 100 в n CTE, чтобы ограничить размер.

Если есть только несколько значений, вы можете использовать:

select 0 as n union all
select 1 as n union all
select 2 as n
person Gordon Linoff    schedule 05.03.2021