SQL存储过程实现平均DP值计算-优快云博客

本文介绍了一个SQL存储过程，用于计算指定时间段内的平均DP值。该过程通过组合不同周期的数据点来生成加权平均值，并更新或插入到目标表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/******************Proc nm_StatAVGDp Begin*******************/
if exists (select 1 from sysobjects
            where name = 'nm_StatAVGDp' and type = 'P')
    drop proc nm_StatAVGDp
go
CREATE proc nm_StatAVGDp(@beginTime datetime,
                          @pkg_id int)

as
declare @dateDiff integer
declare @mPara float
declare @wPara float
declare @dPara float
set @mPara = 0.5 /*月参数*/
set @wPara = 0.3 /*周参数*/
set @dPara = 0.2 /*日参数*/

declare @mCnt int
declare @wCnt int
declare @dCnt int
set @mCnt = -30
set @wCnt = -7
set @dCnt = -1

declare @mDp int
declare @wDp int
declare @dDp int

declare @dp int
if(@beginTime is null)
begin
set @beginTime = getdate()
end
/***得30天以前的dp值 ***/
select @mDp = isnull(avg(global_dp),0) from TB_NM_GLOBALDP_PERDAY
where pkg_id = @pkg_id and datetime>DATEADD([day], @mCnt, @beginTime ) and datetime<=@beginTime

/***得7天以前的dp值 ***/
select @wDp = isnull(avg(global_dp),0) from TB_NM_GLOBALDP_PERDAY
where pkg_id = @pkg_id and datetime>DATEADD([day], @wCnt, @beginTime ) and datetime<=@beginTime

/***得当天的dp值 ***/
select @dDp = isnull(avg(global_dp),0) from TB_NM_GLOBALDP_PERDAY
where pkg_id = @pkg_id and datetime>DATEADD([day], @dCnt, @beginTime ) and datetime<=@beginTime

set @dp = @mDp * @mPara + @wDp * @wPara + @dDp * @dPara
update TB_NM_AVGGLOBALDP_PERDAY set GLOBAL_DP = @dp where pkg_id = @pkg_id and [datetime]=left(convert

(varchar(32),@beginTime,21),10)
if(@@ROWCOUNT=0)
insert into TB_NM_AVGGLOBALDP_PERDAY([PKG_ID],[GLOBAL_DP],[DATETIME]) values(@pkg_id,@dp,left

(convert(varchar(32),@beginTime,21),10))

/******************Proc nm_StatAVGDp End*******************/