英文:
How to get sales diff between previous and next month
问题
我有一个表格，其中包含每天的销售数据。
| 日期 | 销售 |
|---|---|
| 2023年3月1日 | 800 |
| 2023年3月2日 | 300 |
| 2023年3月3日 | 500 |
| 2023年4月1日 | 300 |
| 2023年4月2日 | 500 |
| 2023年4月3日 | 200 |
| 2023年5月1日 | 100 |
| 2023年5月2日 | 400 |
| 2023年5月3日 | 400 |
期望输出：
| 日期 | 销售 | 差异 |
|---|---|---|
| 2023年5月1日 | 900 | 100 |
| 2023年4月1日 | 1000 | 600 |
| 2023年3月1日 | 1600 | 0 |
我创建了一个查询，以获取每个月的销售总额，我需要计算上个月和下个月之间的差异。
英文:
I have table where I am having sales data for each day.
| 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 |
Expected Output:
| Date | Sales | Diff |
|---|---|---|
| 5/1/2023 | 900 | 100 |
| 4/1/2023 | 1000 | 600 |
| 3/1/2023 | 1600 | 0 |
I created a query to get sales sum for each month and I need to calculate difference between previous month and next month:
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
Could any one please help on this?
答案1
得分: 1
你可以首先应用一个简单的聚合函数，聚合以下内容：
- 最小日期，
- 销售总和
同时根据以下条件分区： - 年份日期，
- 月份日期
然后，你可以使用 LAG 窗口函数来获取之前的销售总和。
SELECT MIN(日期) AS 日期,
SUM(销售额) AS 总销售额,
LAG(SUM(销售额), 1, SUM(销售额)) OVER(ORDER BY 年份(日期), 月份(日期)) - SUM(销售额) AS 差值
FROM 表格
GROUP BY 年份(日期), 月份(日期)
ORDER BY 日期 DESC
最后的 ORDER BY 句不是必需的，只是为了可视化效果。
输出结果：
| 日期 | 总销售额 | 差值 |
|---|---|---|
| 2023-05-01 | 900 | 100 |
| 2023-04-01 | 1000 | 600 |
| 2023-03-01 | 1600 | 0 |
在此处查看演示链接。
英文:
You can apply a simple aggregation first, and aggregate:
- minimum date,
- sum of sales
yet partitioning on: - year date,
- month date
Then you can use the LAG window function to gather your previous sum of sales.
SELECT MIN(Date) AS Date,
SUM(Sales) AS TotalSales,
LAG(SUM(Sales), 1, SUM(Sales)) OVER(ORDER BY YEAR(Date), MONTH(Date)) - SUM(Sales) AS Diff
FROM tab
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY Date DESC
The last ORDER BY is not needed, it's just for visualization purposes.
Output:
| Date | TotalSales | Diff |
|---|---|---|
| 2023-05-01 | 900 | 100 |
| 2023-04-01 | 1000 | 600 |
| 2023-03-01 | 1600 | 0 |
Check the demo here.
答案2
得分: 0
你可以通过将 LAG 函数应用于仅包含总计的行来完成如下：
首先，我们需要使用以下查询按月份获取总计：
select MIN(Date) as date, sum(Sales) as TotalSales
from salestable
group by YEAR(Date), MONTH(Date)
然后将分析函数 LAG() 应用于此数据集以获取差异：
select *, LAG(TotalSales, 1, TotalSales) OVER(ORDER BY date) - TotalSales AS Diff
from (
select MIN(Date) as date, sum(Sales) as TotalSales
from salestable
group by YEAR(Date), MONTH(Date)
) as s
order by date desc
英文:
You can do it by applying the LAG function to only the rows with totals as follows :
First we need to get totals per month using :
select MIN(Date) as date, sum(Sales) as TotalSales
from salestable
group by YEAR(Date), MONTH(Date)
Then apply the analytic function LAG() to this dataset to get difference:
select *, LAG(TotalSales, 1, TotalSales) OVER(ORDER BY date) - TotalSales AS Diff
from (
select MIN(Date) as date, sum(Sales) as TotalSales
from salestable
group by YEAR(Date), MONTH(Date)
) as s
order by date desc
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论