sql server计算全省日均、年均

用到的知识点：
1.按年、日分组convert函数
2.按日期循环自动补录区间数据
3.自动补录如果已经补录过再次运行不会重复补录
分手动补录和自动补录sql语句：

手动补录sql语句：

use [GWAMDB]


DECLARE @s_time datetime
DECLARE @e_time datetime
declare  @ii INT 
declare  @jj INT 

declare @i datetime
declare @j datetime
declare @h datetime
declare @m datetime
declare @a datetime

set @i='2022-01-01'   --全年初始时间,全年，一般不用变

set @s_time = '2022-01-01'  --全年、当月开始补录时间，可以自定义
set @e_time = '2022-01-14'  --全年、当月补录结束时间，可以自定义
set @ii = DATEDIFF(DAY,@s_time,@e_time)
set @jj = 0

while @jj <= @ii   --日期循环  ,手动补录的话要取消这行注释


BEGIN
	set @a = DATEADD(DAY, @jj, @s_time)
    --年均
 	INSERT INTO[tt1](datetime, type, PM25)
   SELECT @a,'全年累积平均浓度', round(AVG(pm25),0) as PM25 FROM [dbo].[ZHZSJS] where datetime BETWEEN @i and @a and  LEFT(citycode,2) in ('13') 
   group BY CONVERT(VARCHAR(4),datetime,120)
   ORDER BY CONVERT(VARCHAR(4),datetime,120);
	set @jj = @jj +1
END

     --当日
     INSERT INTO[tt1](datetime, type, PM25)
     SELECT DISTINCT CONVERT(VARCHAR(10),datetime,120), '当日平均值浓度', round(AVG(pm25),0) as PM25 FROM [dbo].[ZHZSJS] where datetime BETWEEN @s_time and  @e_time and  LEFT(citycode,2) in ('13') 
     group BY CONVERT(VARCHAR(10),datetime,120)
     ORDER BY CONVERT(VARCHAR(10),datetime,120);

自动补录昨日日均数据：

-- DROP TABLE tt1;

use [GWAMDB]

-- create table tt1
--  (datetime datetime,
--   type varchar(50),
--   PM25 float);

declare @i datetime
declare @j datetime
declare @h datetime
declare @m datetime
set @i='2022-01-01'   --全年
-- set @m='2022-02-01'   --当月
-- set @j='2022-02-01'   --自定义全部补录日期
set @j=(select max(datetime) FROM [ZHZSJS])
set @h=(select max(datetime) FROM [tt1])

  if @j > @h
     BEGIN
--        当日平均浓度不需要一天一天补录，它按datetime每天分组填入
           INSERT INTO[tt1](datetime, type, PM25)
           SELECT DISTINCT CONVERT(VARCHAR(10),datetime,120), '当日平均值浓度', round(AVG(pm25),0) as PM25 FROM [dbo].[ZHZSJS] where datetime =  @j and  LEFT(citycode,2) in ('13') 
           group BY CONVERT(VARCHAR(10),datetime,120)
           ORDER BY CONVERT(VARCHAR(10),datetime,120);
 
-- 全年累积平均，需要设置@j，@i默认为1月1日，一天一天补录
          INSERT INTO[tt1](datetime, type, PM25)
          SELECT @j as aa,'全年累积平均浓度', round(AVG(pm25),0) as PM25 FROM [dbo].[ZHZSJS] where datetime BETWEEN @i and @j and  LEFT(citycode,2) in ('13') 
          group BY CONVERT(VARCHAR(4),datetime,120)
          ORDER BY CONVERT(VARCHAR(4),datetime,120);
    end


-- select DISTINCT datetime, type as '全省当日/当月累积', PM25 from tt1    ORDER BY datetime ;