sql命令查找过去6个月内用户访问网站的平均次数

Question

我有一个表，有两列，日期和访问次数。

我需要计算过去6个月每月访问次数的平均差异

Date       Number_of_Visits
2018-04-06   5
2018-02-06   6
2017-04-10   3
2017-02-10   9

SQL应输出

Avg_count difference visits past 6 months
5-3=2
6-9=-3
-3+2/2=-0.5

sql查询输出应为-0.5

创建sql如下

With cte as (
SELECT Year(v1.date) as Year, Month(v1.date) as Month, sum(v1.visits) as SumCount 
FROM visits_table v1 
group by Year(v1.date), Month(v1.date)
)

Answer 1

你想要多年来同月差异的平均值吗？同比比较？

这将给您想要的-0.5的结果

; With 
cte as 
(
SELECT Year(v1.date) as Year, Month(v1.date) as Month, sum(v1.visits) as SumCount 
FROM   visits_table v1 
WHERE  v1.date >= DATEADD(MONTH, -6, GETDATE()) -- Add here
group by Year(v1.date), Month(v1.date)
)
SELECT  AVG (diff * 1.0)
FROM
(
SELECT  *, diff = SumCount 
- LAG (SumCount) OVER (PARTITION BY Month 
ORDER BY Year)
FROM    cte
) d