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

Question

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

我需要计算过去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