更新表记录 将字段设置为有条件地在两行中添加字段

Question

表名：交易者资本

列：

Trader (varchar),   
Currency(varchar),   
Date(int i.e. yyyymmdd format),    
Capital(int)  

主键字段：交易者、货币

Trader, Currency, Date, Capital  
A, USD, 20190605, 100  
A, USD, 20190606, 100  
B, INR, 20190605, 200   
B, INR, 20190606, 200   
C, USD, 20190606, 100  
D, USD, 20190601, 100    

我想在将20190606资本汇总到20190605资本后更新我的表格。因此，所需的输出应如下所示：

Trader, Currency, Date, Capital  
A, USD, 20190605, 200  
B, INR, 20190605, 400  
C, USD, 20190606, 100 
D, USD, 20190601, 100  

你能帮我写同样的sql查询吗？

Answer 1

您可以使用

LAG()来实现它。在第一步中，我创建了一个列，用于检查行是否有前一天。后来我用这个列(previousDay(对行进行分组。

with previousDay as (
    select
        *
        ,LAG(date) over (partition by trader, currency order by date asc) as PreviousDay
    from TraderCapital
)
select
     trader
    ,currency
    ,case when PreviousDay is not null then dateadd(day, -1, date) else date end as date
    ,sum(Capital) as capital
from previousDay 
group by case when PreviousDay is not null then dateadd(day, -1, date) else date end
    ,trader
    ,currency

Answer 2

尝试如下窗口函数：

select b.Trader,b.Currency, b.Date, b.running_total as Capital 
from (
select a.Trader,a.Currency, a.Date, a.running_total
, row_number() over (partition by a.Trader, a.Currency order by a.running_total desc) rnk
from (
select * ,sum(capital) over (partition by Trader, Currency order by Date desc) as running_total
from TraderCapital )a )b
where b.rnk = 1

测试结果：

dd<>小提琴

Answer 3

感谢 @M. Kanarkowski 和 @Gen Wan 的回答。根据您的建议，以下是最终对我有用的方法：

with PreviousDayTable as (  
select *, LAG(Date) over (partition by Trader, Currency order by Date) 
          as previousDay
from TraderCapital
),
TotalTable as (
select Trader, Currency, 
       case when previousDay is not null then previousDay else Date end as PreviousDay,
       sum(Capital) as agg
  from PreviousDayTable 
  group by Trader, Currency,
           case when previousDay is not null then previousDay else Date end
)
update TraderCapital 
set TraderCapital.Capital = B.agg
from TraderCapital as A JOIN TotalTable as B
ON A.Trader = B.Trader
AND A.Currency = B.Currency
AND A.Date = B.previousDay

with PreviousDayTable as (
select *, LAG(Date) over (partition by Trader, Currency order by Date) 
          as previousDay
from TraderCapital
)
delete A
from TraderCapital as A JOIN PreviousDayTable as B
ON A.Trader = B.Trader
AND A.Currency = B.Currency
AND A.Date = B.Date
where B.previousDay is not null

请随时提出改进建议。