如何聚合非工作日期的数据?

Question

我有两个表。table1包含工作日期，table2包含数字Value用于日历日期。
我需要连接这些表，并从table2输出Value和table1的日期。

棘手的是，在非工作日期期间，Value应该汇总并添加到下一个工作日期的Value。

下面是一个简单的脚本来创建这个场景的样例数据。

CREATE TABLE #table1
(
[Date] DATE PRIMARY KEY
)
INSERT INTO #table1 VALUES 
('2021-02-12'),
('2021-02-15'),
('2021-02-16'),
('2021-02-17'),
('2021-02-18'),
('2021-02-19'),
('2021-02-22'),
('2021-02-23'),
('2021-02-24')
CREATE TABLE #table2
(
[Date] DATE PRIMARY KEY,
[Value] INT NOT NULL
)
INSERT INTO #table2 VALUES 
('2021-02-12', 1),
('2021-02-13', 1),
('2021-02-14', 2),
('2021-02-15', 3),
('2021-02-16', 5),
('2021-02-17', 8),
('2021-02-18', 13),
('2021-02-19', 21),
('2021-02-20', 34),
('2021-02-21', 55),
('2021-02-22', 89),
('2021-02-23', 144),
('2021-02-24', 233)
GO

输出应该是这样的:

---------------------
| Date       |Value |
| ------------------|
| 2021-02-12 | 1    |
| 2021-02-15 | 6    |
| 2021-02-16 | 5    |
| 2021-02-17 | 8    |
| 2021-02-18 | 13   |
| 2021-02-19 | 21   |
| 2021-02-22 | 178  |
| 2021-02-23 | 144  |
| 2021-02-24 | 233  |
---------------------

我试过如下:

WITH x AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY [Date]) AS RowID
,[Date]
FROM #table1
)
SELECT 
t1.[Date]
,(
SELECT SUM([Value])
FROM #table2
WHERE [Date] > ISNULL(t1p.[Date], '00010101') AND [Date] <= t1.[Date]
) AS [Value]
FROM x t1
LEFT OUTER JOIN x t1p
ON t1.RowID = t1p.RowID + 1
ORDER BY
t1.[Date]
GO

这是工作和生产什么我需要，但它是痛苦的缓慢(不是在这个样本，但在实际数据)。

如果我知道如何通过分组或其他技术来优化这一点，使其更快，我将不胜感激。

Answer 1

您可以尝试使用SUM窗口函数与您的条件一起创建组号，然后聚合非工作日期的数据。

;WITH CTE AS (
SELECT t2.[Date],
t2.[Value],
SUM(CASE WHEN t1.[Date] IS NOT NULL Then 1 END) OVER(ORDER BY t2.[Date] DESC) grp
FROM #table2 t2 
LEFT JOIN #table1 t1
ON t1.[Date] = t2.[Date]
)
SELECT MAX([Date]) as 'Date',
SUM([Value]) as 'Value'
FROM CTE 
GROUP BY grp
ORDER BY [Date]

sqlfiddle