Разделить значение из итоговой строки на несколько других строк, пока сумма не достигнет значения итоговой строки в REDSIFT

DB-рабочий пример

CREATE TABLE inbound (
    id SERIAL PRIMARY KEY,
    campaign VARCHAR,
    expected_inbound_date DATE,
    expected_inbound_quantity DECIMAL,
    received_inbound_quantity DECIMAL
);

INSERT INTO inbound
(campaign, expected_inbound_date, expected_inbound_quantity, received_inbound_quantity)
VALUES 
('C001', '2022-05-03', '500', '0'),
('C001', '2022-05-03', '800', '0'),
('C001', '2022-05-03', '400', '0'),
('C001', '2022-05-03', '200', '0'),
('C001', NULL, '0', '700'),

('C002', '2022-08-20', '3000', '0'),
('C002', '2022-08-20', '5000', '0'),
('C002', '2022-08-20', '2800', '0'),
('C002', NULL, '0', '4000');

ожидаемый результат

campaign |  expected_inbound_date |  expected_inbound_quantity  |  split_received_inbound_quantity
---------|------------------------|-----------------------------|----------------------------------
  C001   |        2022-05-03      |             200             |          200
  C001   |        2022-05-03      |             400             |          400
  C001   |        2022-05-03      |             500             |          100
  C001   |        2022-05-03      |             800             |            0
  C001   |                        |                             |          700
---------|------------------------|-----------------------------|----------------------------------
  C002   |       2022-08-20       |           3.800             |         3.800
  C002   |       2022-08-20       |           5.000             |           200
  C002   |       2022-08-20       |           2.800             |             0
  C002   |                        |                             |         4.000

Я хочу разделить received_inbound_quantity на каждую строку expected_inbound_quantity, пока не будет достигнуто общее количество received_inbound_quantity.
Со ссылкой на ответ в этот вопрос я попытался использовать это решение:

SELECT
i.campaign AS campaign,
i.expected_inbound_date AS expected_inbound_date,
i.expected_inbound_quantity AS expected_inbound_quantity,
i.received_inbound_quantity AS received_inbound_quantity,

(SELECT 
   GREATEST(
     LEAST(i.expected_inbound_quantity, 
          (SELECT 
           SUM(i3.received_inbound_quantity) 
           FROM inbound i3 
           WHERE i.campaign = i3.campaign)  -
           
            (
                SELECT 
                t1.cumulated_value AS cumulated_value 
                FROM
                
                   (SELECT
                    i2.campaign, 
                    i2.expected_inbound_date, 
                    i2.expected_inbound_quantity, 
                    i2.received_inbound_quantity,
                    SUM(i2.expected_inbound_quantity) OVER (PARTITION BY i2.campaign ORDER BY i2.expected_inbound_date, i2.expected_inbound_quantity, i2.received_inbound_quantity ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS cumulated_value
                    FROM inbound i2
                    GROUP BY 1,2,3,4) t1
                    
                WHERE (t1.campaign, t1.expected_inbound_date, t1.expected_inbound_quantity, t1.received_inbound_quantity) = (i.campaign, i.expected_inbound_date, i.expected_inbound_quantity, i.received_inbound_quantity)
            )
            
        ),
        0
   )
) AS split

FROM inbound i
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4

Однако в красном смещении я получаю сообщение об ошибке:

Invalid operation: This type of correlated subquery pattern is not supported yet;

Как мне изменить запрос, чтобы он также работал в красном смещении?

Принимает ли красное смещение CTE?

— 05.04.2022 14:54

sql amazon-web-services amazon-redshift

05.04.2022 14:22

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Оконные функции — ваш друг. Когда у вас есть запрос, который сравнивает строки, вы должны сначала обратиться к оконным функциям в Redshift. Это проще, чище и быстрее, чем любой самообъединяющийся шаблон.

select 
  campaign,
  expected_inbound_date,
  expected_inbound_quantity,
  received_inbound_quantity,
  case when (inbound_total - inbound_sum) >= 0 then expected_inbound_quantity
       else case when (expected_inbound_quantity + inbound_total - inbound_sum) >= 0 then expected_inbound_quantity + inbound_total - inbound_sum
                else 0 end
    end as split

from (SELECT
  campaign,
  expected_inbound_date,
  expected_inbound_quantity,
  received_inbound_quantity,
  sum(expected_inbound_quantity) over (partition by campaign order by expected_inbound_date, expected_inbound_quantity) as inbound_sum,
  max(received_inbound_quantity) over (partition by campaign) as inbound_total

  FROM inbound i
) subq
ORDER BY 1,2,3,4;

Обновлена рабочий пример здесь - https://dbfiddle.uk/?rdbms=postgres_13&fiddle=2381abdf5a90a997a4f05b809c892c40

Когда вы переносите это на Redshift, вы можете преобразовать операторы CASE в функции DECODE(), поскольку они более читабельны IMHO.

PS. Спасибо за настройку скрипки, так как это значительно ускоряет предоставление ответа.

Спасибо за ответ и объяснение. Мне нужно было только добавить ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW в оконную функцию суммы (expected_inbound_quantity), чтобы она работала в Redshift.

— 06.04.2022 08:38

Вот рабочий пример для этого: dbfiddle.uk/…

— 06.04.2022 08:38

Да, хороший улов.

— 06.04.2022 18:26

05.04.2022 17:57