优化执行时间过长的查询

Question

我使用以下查询来计算每个月以及随后的12个月的金额，并分别计算总和，但速度太慢。如何优化它？

我创建了所有需要的索引，但速度仍然很慢。

SELECT s.Activity, s.Sector, s.StartOfMonth, s.AmountMonth, SUM(salesLtd.Amount) [AmountLTD]
FROM (
SELECT Activity, Sector, SUM(Amount) [AmountMonth], StartOfMonth
FROM sales 
CROSS APPLY (SELECT DATEADD(month, DATEDIFF(month, 0, dateSale), 0) [StartOfMonth]) [forStart]
GROUP BY Activity, Sector, StartOfMonth
) s
INNER JOIN sales salesLtd ON salesLtd.dateSale <= EOMONTH(s.StartOfMonth) 
AND salesLtd.dateSale >= DATEADD(year, -1, s.StartOfMonth)
GROUP BY s.Activity, s.Sector, s.StartOfMonth, s.AmountMonth

Answer 1

假设。你有每个月的值，然后只使用一个窗口函数：

SELECT Activity, Sector, SUM(Amount) [AmountMonth], StartOfMonth,
SUM(SUM(Amount)) OVER (PARTITION BY Activity, Sector ORDER BY MIN(dateSale) ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) as amount_12monhth
FROM sales CROSS APPLY 
(VALUES (DATEFROMPARTS(YEAR(dateSale), MONTH(dateSale), 1)
) v([StartOfMonth])
GROUP BY Activity, Sector, StartOfMonth