SQL Server 查询，用于在当前日期不存在值时从前一天继承值

Question

我有一个查询，可以跟踪每个交易时段的风险交易者资金。当某一天没有交易时，我需要将日终值从最后一个活动日结转到下一个活跃日。这是我到目前为止所拥有的：

DECLARE @Start DATETIME = '2019-08-20'
DECLARE @End   DATETIME = '2019-08-27'
DECLARE @history TABLE( Id INT, AccountId INT, AllocatedCapital MONEY, RunningAllocatedCapital MONEY,RN INT,SessionDate DATETIME) 
INSERT INTO @history(Id, AccountId, AllocatedCapital, RunningAllocatedCapital,RN,SessionDate) 
VALUES (362082,    1182,   -170150.0000,   -170150.0000,   1,  '2019-08-20'),
(362090,    1182,   -4167.9600,     -199466.4600,   1,  '2019-08-21'),
(362088,    1182,   -10330.0000,    -195298.5000,   2,  '2019-08-21'),
(362086,    1182,   -9454.5000,     -184968.5000,   3,  '2019-08-21'),
(362084,    1182,   -5364.0000,     -175514.0000,   4,  '2019-08-21'),
(362094,    1182,   -4140.0000,     -203606.4600,   1,  '2019-08-22'),
(362092,    1182,   -4140.0000,     -207746.4600,   2,  '2019-08-22'),
(362105,    1182,    4140.0000,     -187052.4800,   1,  '2019-08-27')

;WITH tradingdays  as (
SELECT TOP (DATEDIFF(DAY, @Start, @End) + 1)
DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,@Start) SessionDate 
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
SELECT -MIN(RunningAllocatedCapital) MaxCapitalAtRisk,
-MAX(CASE H.RN WHEN 1 THEN H.RunningAllocatedCapital END)EodCapitalAtRisk,
C.SessionDate
FROM tradingdays C
LEFT JOIN @history H ON H.SessionDate = C.SessionDate
WHERE DATENAME(dw,C.SessionDate) NOT IN ('Saturday','Sunday')
GROUP BY C.SessionDate, H.SessionDate
ORDER BY C.SessionDate

取而代之的是：

MaxCapitalAtRisk    EodCapitalAtRisk    SessionDate
170150.00           170150.00           2019-08-20 00:00:00.000
199466.46           199466.46           2019-08-21 00:00:00.000
207746.46           203606.4600         2019-08-22 00:00:00.000
NULL                NULL                2019-08-23 00:00:00.000
NULL                NULL                2019-08-26 00:00:00.000
187052.48           187052.48           2019-08-27 00:00:00.000

我的结果集应如下所示：

MaxCapitalAtRisk    EodCapitalAtRisk    SessionDate
170150.00           170150.00           2019-08-20 00:00:00.000
199466.46           199466.46           2019-08-21 00:00:00.000
207746.46           203606.46           2019-08-22 00:00:00.000
203606.46           203606.46           2019-08-23 00:00:00.000
203606.46           203606.46           2019-08-26 00:00:00.000
187052.48           187052.48           2019-08-27 00:00:00.000 

我知道在SQL Server中有一些干净的方法可以在不使用子查询或游标的情况下执行此操作，但是我不记得如何执行此操作。

Answer 1

基本上，您正在寻找带有ignore nulls选项的lag()。SQL Server不支持这一点，但我们可以使用间隙和孤岛技术来模拟它。

这个想法是使用条件的总和或计数构建由一个"常规"记录(岛(后跟 0 到 N 个"缺失"重新编码(间隙(组成的记录组。然后，我们可以用first_value()来填补岛屿价值的空白：

with tradingdays as (
select @start SessionDate 
union all select dateadd(day, 1, SessionDate) from tradingdays where SessionDate < @end
)
select
SessionDate,
first_value(MaxCapitalAtRisk) over(partition by grp order by SessionDate) MaxCapitalAtRisk,
first_value(EodCapitalAtRisk) over(partition by grp order by SessionDate) EodCapitalAtRisk
from (
select 
td.SessionDate, 
- min(RunningAllocatedCapital) MaxCapitalAtRisk,
- max(case h.rn when 1 then h.runningallocatedcapital end) EodCapitalAtRisk,
count(h.SessionDate) over(order by td.SessionDate) grp
from tradingdays td 
left join @history h on h.SessionDate = td.SessionDate 
where datename(dw, td.SessionDate) not in ('Saturday', 'Sunday')
group by td.SessionDate, h.SessionDate
) t
order by SessionDate

我将生成日期的公共表表达式更改为使用递归，因为我发现它更容易遵循 - 但这不会改变逻辑，如果您更喜欢它，您可以切换回原始 cte。如果您坚持使用我的 cte，并且您的日期分布在 100 多天内，那么您需要在查询的最后添加option(maxrecursion 0)。

DB小提琴上的演示：

会话日期 |最大风险资本 |EodCapitalAtRisk :---------------------- |:--------------- |:--------------- 2019-08-20 00：00：00.000 |170150.0000 |170150.0000     2019-08-21 00：00：00.000 |199466.4600 |199466.4600     2019-08-22 00：00：00.000 |207746.4600 |207746.4600     2019-08-23 00：00：00.000 |207746.4600 |207746.4600     2019-08-26 00：00：00.000 |207746.4600 |207746.4600     2019-08-27 00：00：00.000 |187052.4800 |187052.4800