从包含累积和增量条目的表中查找滞后的累积总数
我有一个带有模式的SQL表，其中的值要么是特定类别的累积值，要么是上一个值的增量。尽管我认为这不是一个特别好的设计，但它来自外部来源，因此我无法进行任何更改。
该表如下所示：
日期类别AmountSoldType AmountSold
-------------------------------------------------- ---
1月1日，苹果累积量100
1月1日，香蕉累积50
1月2日：Apple Delta 20
1月2日：香蕉三角洲10
1月3日：Apple Delta 25
1月3日，香蕉累积75
对于此示例，我想生成每天开始时按项目出售的水果的累计总数：
日期类别金额已售
--------------------------------
1月1日苹果0
1月1日香蕉0
1月2日，苹果100
1月2日，香蕉50
1月3日，苹果170
1月3日，香蕉60
1月4日，苹果195
1月4日，香蕉75
直观地讲，我想获取最新的累计总数，并添加自该条目以来出现的所有增量。
我想像的东西
SELECT Date, Category
LEAD((subquery??), 1) OVER (PARTITION BY Category ORDER BY Date) AS Amt
FROM Fruits
GROUP BY Date, Category
ORDER BY Date ASC
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
要实现您的需求，可以使用窗口函数SUM()结合OVER()子句来计算累积和。同时，我们需要处理“累积值”和“增量值”的区别。这里是一个可能的SQL查询示例：
WITH CategorizedAmounts AS (
SELECT
Date,
Category,
CASE WHEN AmountSoldType = '累积量' THEN AmountSold ELSE 0 END AS CumulativeAmount,
CASE WHEN AmountSoldType = 'Delta' THEN AmountSold ELSE 0 END AS IncrementalAmount
FROM Fruits
),
CumulativeSums AS (
SELECT
Date,
Category,
SUM(CumulativeAmount) OVER (PARTITION BY Category ORDER BY Date) +
SUM(IncrementalAmount) OVER (PARTITION BY Category ORDER BY Date) AS TotalAmountSold
FROM CategorizedAmounts
)
SELECT
Date,
Category,
COALESCE(LAG(TotalAmountSold) OVER (PARTITION BY Category ORDER BY Date), 0) AS AmountSold
FROM CumulativeSums
ORDER BY Date, Category;
这个查询分为几个步骤： 1. CategorizedAmounts: 首先，我们创建一个CTE（公共表表达式），将原始数据分为累积金额(CumulativeAmount)和增量金额(IncrementalAmount)。 2. CumulativeSums: 然后，我们对每个类别按日期顺序计算累积总金额。这里，我们对每个类别的每一天都累加了累积金额，并且也累加了从第一天到当前日期的所有增量。 3. 最后，我们使用LAG()窗口函数获取前一行的累计销售总额作为当天的起始销售总额。注意，我们用COALESCE确保了在每个类别的第一天没有前驱时返回0。
这样，您就可以得到每天开始时每种水果的累计销售总额。
