将值从总行拆分到多个其他行，直到和达到总行的值为止。
将值从总行拆分到多个其他行，直到和达到总行的值为止。
提问于 2022-04-05 10:05:22
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 split_received_inbound_quantity
FROM inbound i
GROUP BY 1,2,3,4
ORDER BY 1,2,3,4;我不知道如何做到这一点。
你有什么想法吗？
回答 2
Stack Overflow用户
回答已采纳
发布于 2022-04-05 10:48:55
我想出了以下几点：
select i.campaign, i.expected_inbound_date, i.expected_inbound_quantity, i.received_inbound_quantity, (
select greatest(
least(
i.expected_inbound_quantity,
(select sum(iii.received_inbound_quantity) from inbound iii where i.campaign = iii.campaign) -
(
select cum_sum
from (
select sum(ii.expected_inbound_quantity) over (partition by ii.campaign order by ii.expected_inbound_date, ii.expected_inbound_quantity, ii.received_inbound_quantity rows between unbounded preceding and 1 preceding) cum_sum, ii.campaign, ii.expected_inbound_date, ii.expected_inbound_quantity, ii.received_inbound_quantity
from inbound ii
) tmp
where (tmp.campaign, tmp.expected_inbound_date, tmp.expected_inbound_quantity, tmp.received_inbound_quantity) = (i.campaign, i.expected_inbound_date, i.expected_inbound_quantity, i.received_inbound_quantity)
)
),
0
)
) split
from inbound i
order by i.campaign, i.expected_inbound_date, i.expected_inbound_quantity, i.received_inbound_quantity这是一个db小提琴。
这样做的目的是以cum_sum的形式计算当前行之前行的累积和，然后选择更少的内容：received_inbound_quantity之和减去cum_sum或expected_inbound_date。为了避免值< 0，我使用了greatest。
Stack Overflow用户
发布于 2022-04-06 06:41:23
参考这个问题中的答案，解决这个问题的另一种方法也适用于redshift
SELECT
t1.campaign AS campaign,
t1.expected_inbound_date AS expected_inbound_date,
t1.expected_inbound_quantity AS expected_inbound_quantity,
t1.received_inbound_quantity AS received_inbound_quantity,
t1.quantity_accumulated AS quantity_accumulated,
t1.quantity_total AS quantity_total,
(CASE WHEN (t1.quantity_total - t1.quantity_accumulated) >=0
THEN t1.expected_inbound_quantity ELSE
(CASE WHEN (t1.expected_inbound_quantity + t1.quantity_total - t1.quantity_accumulated) >=0
THEN (t1.expected_inbound_quantity + t1.quantity_total - t1.quantity_accumulated)
ELSE 0 END)
END) AS split
FROM
(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,
SUM(i.expected_inbound_quantity) OVER (PARTITION BY i.campaign ORDER BY i.expected_inbound_date, i.expected_inbound_quantity ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS quantity_accumulated,
MAX(i.received_inbound_quantity) OVER (PARTITION BY i.campaign) AS quantity_total
FROM inbound i) t1
GROUP BY 1,2,3,4,5,6
ORDER BY 1,2,3,4,5,6;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71749840
复制相关文章
相似问题
腾讯云开发者
