峨媚山峨媚派掌门人兼董事长
问题描述
我在 sql server 中有一个表，其中的数据类似于此示例.
i have a table in sql server with data looking like this example.
id flag art.no amount 1 u a1000 -100 2 u b2000 -5 3 v b2000 900 4 u b2000 -10 5 i b2000 50 6 u b2000 -20 7 u a1000 -50 8 i a1000 1000 9 v a1000 3600 10 u a1000 -500 11 u a1000 -100 12 u a1000 -2000 13 i a1000 2000 14 u a1000 -1000 15 i c3000 10000 16 u c3000 -4000 17 u b2000 -5 18 u b2000 -5 19 i b2000 40 20 v b2000 200 21 u a1000 -500 22 u b2000 -50 23 u c3000 -1000
我想根据交易计算累积价值.我的问题是该表包含 3 种类型的交易.
i want to calculate ackumulated value based on the transactions. my problem is that the table contains 3 types of transactions.
- 标记 u - 销售
- flag i - 进货
- flag v - 盘点
当标志 u 和 i 出现时，数量代表变化出现flag v时数量代表盘点时的总量
when flag u and i appears the amount represent the change when flag v appears the amount represent the total amount when stocktaking
换句话说，我想找到每个 unice art.no 的最新 v-transaction，然后添加或减去 u 和 i 交易以获得每行的累计总和.如果没有 v-transaction 则遍历整个数据集.
in words i want to find the latest v-transaction for each unice art.no and then add or subtract u and i transactions to get a cummulativ sum for each row. if there is no v-transaction go through the whole dataset.
我已经为每个艺术制作了具有预期结果的示例.no
i have made examples with expected result for each art.no
a1000
id flag art.no amount a1000 example 1 u a1000 -100 7 u a1000 -50 8 i a1000 1000 9 v a1000 3600 3600 10 u a1000 -500 3100 11 u a1000 -100 3000 12 u a1000 -2000 1000 13 i a1000 2000 3000 14 u a1000 -1000 2000 21 u a1000 -500 1500
b2000
id flag art.no amount b2000 example 2 u b2000 -5 3 v b2000 900 4 u b2000 -10 5 i b2000 50 6 u b2000 -20 17 u b2000 -5 18 u b2000 -5 19 i b2000 40 20 v b2000 200 200 22 u b2000 -50 150
c3000
id flag art.no amount c3000 example 15 i c3000 10000 10000 16 u c3000 -4000 6000 23 u c3000 -1000 5000
为了在数据集中获得更多历史记录，在像这样的最新 v-transaction 之前有值会很好
to get more history in the dataset there would be nice to have values before the latest v-transaction like this
b2000
id flag art.no amount b2000 example 2 u b2000 -5 150 3 v b2000 900 140 4 u b2000 -10 140 5 i b2000 50 190 6 u b2000 -20 170 17 u b2000 -5 165 18 u b2000 -5 160 19 i b2000 40 200 20 v b2000 200 200 22 u b2000 -50 150
考虑每个 i 和 u 事务但忽略 v 事务.
where each i and u transaction is taken in consideration but v-transactions is ignored.
推荐答案
with cte as
(
select *,
-- find the latest 'v' id per artno
max(case when flag = 'v' then id end)
over (partition by artno) as last_v_id
from mytable
)
select *,
-- cumulative sum, but ignore all rows before the latest 'v' id
-- includes rows when there's no 'v' id for this artno
sum(case when id < last_v_id then null else amount end)
over (partition by artno
order by id
rows unbounded preceding)
from cte
order by artno, id
参见 fiddle
要包含上次盘点之前的数据并忽略所有之前的盘点，您可以使用以下方法:
to include the data before the last stocktaking and to ignore all previous stocktakings you can use this approach:
with cte as
(
select *,
-- find the latest 'v' id per artno
max(case when flag = 'v' then id end)
over (partition by artno) as last_v_id
from [dbo].[warehouse]
)
select *,
-- cumulative sum, but ignore all rows before the latest 'v' id
-- includes rows when there's no 'v' id for this artno
sum(case when id < last_v_id then null else amount end)
over (partition by artno
order by id
rows unbounded preceding)
-- calculate in-stock based on last 'v' id, discarding all previous 'v' rows
,sum(case when (id < last_v_id and flag <> 'v') then -amount
when id = last_v_id then amount
end)
over (partition by artno
order by id
rows between 1 following and unbounded following)
from cte
order by artno, id
两种计算都是互斥的，因此您可以使用 coalesce 轻松地将它们组合起来.
both calculations are mutually exlusive, so you can easily combine them using coalesce.
参见 fiddle
