SQL Server中查询累计和与滑动平均值

痛定思痛。 2021-12-05 18:17 280阅读 0赞

说明：本文测试时使用的是在线测试环境[http://www.sqlfiddle.com/][http_www.sqlfiddle.com]，SQL Server的版本为SQL Server 2017

构建测试所用产品销售信息表sales\_info：

-- 创建产品销售信息表
    create table sales_info(
    pro_type varchar(5),   			  
    pro_name char(5),                 
    pro_sale decimal(9,2),            
    pro_date date)                    
    
    -- sales_info的字段中pro_type表示产品类型，pro_name表示产品名称，
    -- pro_sale表示产品销售额，pro_date表示产品销售日期
    
    -- 向产品销售信息表sales_info中插入数据
    insert into sales_info values('p1','AAA',391050.20,'2019-06-01')
    insert into sales_info values('p1','AAA',323080.20,'2019-06-02')
    insert into sales_info values('p1','ABA',365100.50,'2019-06-03')
    insert into sales_info values('p1','BAA',398040.30,'2019-06-04')
    insert into sales_info values('p1','AAC',341050.80,'2019-06-05')
    insert into sales_info values('p1','CDA',691070.40,'2019-06-06')
    insert into sales_info values('p1','ABC',891060.60,'2019-06-07')
    insert into sales_info values('p1','AAA',334200.30,'2019-06-08')
    insert into sales_info values('p1','BCA',344200.30,'2019-06-09')
    insert into sales_info values('p1','BBA',358252.80,'2019-06-10')
    insert into sales_info values('p2','DAA',691050.20,'2019-06-11')
    insert into sales_info values('p2','CAA',623080.20,'2019-06-12')
    insert into sales_info values('p2','EBA',665100.50,'2019-06-13')
    insert into sales_info values('p2','BBA',698040.30,'2019-06-14')
    insert into sales_info values('p2','CAC',641050.80,'2019-06-15')
    insert into sales_info values('p2','CBA',991070.40,'2019-06-16')
    insert into sales_info values('p2','ABC',1191060.60,'2019-06-17')
    insert into sales_info values('p2','DAA',634200.30,'2019-06-18')
    insert into sales_info values('p2','BDA',644200.30,'2019-06-19')
    insert into sales_info values('p2','BBD',658252.80,'2019-06-20')

查看产品销售信息表的表结构：

select * from sales_info

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70][]

查询不同类型产品按时间升序排序后的销售额的累计和，以及销售额的周滑动平均值(周指除当前日期外，还包括前三天与后三天) ：

select pro_date,pro_type,pro_name,pro_sale,
    sum(pro_sale) over(partition by pro_type order by pro_date rows between unbounded
    preceding and current row) 
    as running_total,
    round(avg(pro_sale) over (partition by pro_type order by pro_date rows between 3
    preceding and 3 following),2) as running_average
    from sales_info

查询结果如下图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 1][]

实际上上述代码可以简化如下(省略rows between unbounded preceding and current row)：

select pro_date,pro_type,pro_name,pro_sale,
    sum(pro_sale) over(partition by pro_type order by pro_date) 
    as running_total,
    round(avg(pro_sale) over (partition by pro_type order by pro_date rows between 3
    preceding and 3 following),2) as running_average
    from sales_info

\--------------------------------------------------------分割线---------------------------------------------------------

由于电脑上装的SQL Server版本是2008 r2，所以查询的时候一直报错。我以为是代码有问题，其实是SQL Server的版本太低的原因，在SQL Server 2008 r2版本中，聚合开窗函数只能使用partition by子句或者什么子句都不带。最后只能找在线测试环境进行测试，简单试了几个，觉得还是SQL Fiddle最好用，所以这里推荐一下SQL在线测试环境SQL Fiddle，地址是[http://www.sqlfiddle.com/][http_www.sqlfiddle.com]。这是一款非常好用的SQL在线测试环境，虽然是英文的的操作界面，但是操作简单，支持MySQL、SQL Server、Oracle等多种数据库。我们可以看一下它的操作页面：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 2][]

左边的白板用来创建测试表，向测试表中插入数据，右边的白板用来写查询语句。鼠标多点击几下，很快就可以学会使用。

参考：

[https://blog.csdn.net/qq\_41080850/article/details/86416248][https_blog.csdn.net_qq_41080850_article_details_86416248]

[https://blog.csdn.net/liu\_1983/article/details/7269312][https_blog.csdn.net_liu_1983_article_details_7269312]

[http_www.sqlfiddle.com]: http://www.sqlfiddle.com/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70]: /images/20211205/397956b883ab4273b660653998ef2ba0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 1]: /images/20211205/d02bddb877d7466fb8919cc0f727c7de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMDgwODUw_size_16_color_FFFFFF_t_70 2]: /images/20211205/552820ba4c9248f5b00373a2caf5bbcd.png
[https_blog.csdn.net_qq_41080850_article_details_86416248]: https://blog.csdn.net/qq_41080850/article/details/86416248
[https_blog.csdn.net_liu_1983_article_details_7269312]: https://blog.csdn.net/liu_1983/article/details/7269312