将值从总行拆分为多个其他行，直到之和达到红移中的总行的值为止。
将值从总行拆分为多个其他行，直到之和达到红移中的总行的值为止。
提问于 2022-04-05 12:22:23
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;如何修改查询以使其在红移中工作？
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-04-05 15:57:27
窗口功能是你的朋友。当您有一个比较行的查询时，您应该首先查找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; 更新小提琴- 13&fiddle=2381abdf5a90a997a4f05b809c892c40
当您将其移植到Redshift时，您可能希望将CASE语句转换为DECODE()函数，因为这些函数具有更高的可读性。
PS。感谢您设置小提琴，因为这大大加快了提供一个答案。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71751696
复制相关文章
相似问题
腾讯云开发者
