我有一个表格,里面有我每天的销售数据.
Date | Sales |
---|---|
3/1/2023 | 800 |
3/2/2023 | 300 |
3/3/2023 | 500 |
4/1/2023 | 300 |
4/2/2023 | 500 |
4/3/2023 | 200 |
5/1/2023 | 100 |
5/2/2023 | 400 |
5/3/2023 | 400 |
预期输出:
Date | Sales | Diff |
---|---|---|
5/1/2023 | 900 | 100 |
4/1/2023 | 1000 | 600 |
3/1/2023 | 1600 | 0 |
我创建了一个查询来获取每个月的销售额,并且我需要计算上个月和下个月之间的差额:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, Salesdate), 0) AS TrendStartDate,
SUM(Sales) AS TotalSales ,
LEAD(SUM(Sales), 1) OVER (PARTITION BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Salesdate), 0)
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Salesdate), 0) DESC) AS Sales_diff
FROM salestable
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Salesdate), 0)
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Salesdate), 0) ASC
有谁能帮个忙吗?