📄 cunchu.sql

📁 使用SQLserver采用贝塞尔函数低通滤波对读入的数据进行谐波分析
💻 SQL
📖 第 1 页 / 共 2 页
字号:
12 下一页
CREATE proc dbo.switch
	@U1	FLOAT,
	@U2	FLOAT,
	@U3	FLOAT,
	@I1	FLOAT,
	@I2	FLOAT,
	@I3	FLOAT,
	@t FLOAT,
	@SampleNumber Int
as
--①初始化
DECLARE @w float;			-- ω
DECLARE @k INT
DECLARE @SQRT2of3 float
DECLARE @offtime float
DECLARE @dotnumber int

--SET @w = 2 * pi() / @t
SET @w = 317.30085801256911708472698171123
SET @SQRT2of3 = 0.81649658092772603273242802490196
SET @offtime = 0.0003125
select @dotnumber = dotnumber from TempData;

--INSERT INTO TempData (time_now,HV1,HV2 ,HV3 ,HV4 ,HV5 ,HV6 ,HV7 ,HI1 ,HI2 ,HI3 ,HI4 ,HI5 ,HI6 ,HI7,dotnumber) VALUES ( getdate(),0,0,0,0,0,0,0,0,0,0,0,0,0,0,1 );		-- 要写入表HarmData的内容

	--读入常数
	DECLARE @a10 float(25)
	DECLARE @a11 float(25)
	DECLARE @b11 float(25)
	DECLARE @a20 float(25)
	DECLARE @a21 float(25)
	DECLARE @a22 float(25)
	DECLARE @b21 float(25)
	DECLARE @b22 float(25)
	DECLARE @a30 float(25)
	DECLARE @a31 float(25)
	DECLARE @a32 float(25)
	DECLARE @a33 float(25)
	DECLARE @b31 float(25)
	DECLARE @b32 float(25)
	DECLARE @b33 float(25)
	DECLARE @a40 float(25)
	DECLARE @a41 float(25)
	DECLARE @a42 float(25)
	DECLARE @a43 float(25)
	DECLARE @a44 float(25)
	DECLARE @b41 float(25)
	DECLARE @b42 float(25)
	DECLARE @b43 float(25)
	DECLARE @b44 float(25)
	SELECT @a10 = 4.8847995518126915E-3;
	SELECT @a11 = 4.8847995518126915E-3;
	SELECT @b11 = -0.9902304008963746;
	SELECT @a20 = 7.1234135772961792E-5;
	SELECT @a21 = 1.4246827154592358E-4;
	SELECT @a22 = 7.1234135772961792E-5;
	SELECT @b21 = -1.9706918986180728;
	SELECT @b22 = 0.97097683516116473;
	SELECT @a30 = 1.7228673229720418E-6;
	SELECT @a31 = 5.1686019689161257E-6;
	SELECT @a32 = 5.1686019689161257E-6;
	SELECT @a33 = 1.7228673229720418E-6;
	SELECT @b31 = -2.9413858847763392;
	SELECT @b32 = 2.884196351280198;
	SELECT @b33 = -0.9427966835652748;
	SELECT @a40 = 5.8052002824876408E-8;
	SELECT @a41 = 2.3220801129950563E-7;
	SELECT @a42 = 3.4831201694925848E-7;
	SELECT @a43 = 2.3220801129950563E-7;
	SELECT @a44 = 5.8052002824876408E-8;
	SELECT @b41 = -3.9023144461716144;
	SELECT @b42 = 5.711216679828822;
	SELECT @b43 = -3.715393554183382;
	SELECT @b44 = 0.9064922493582197;

DECLARE @cAbcDq TABLE			-- 转换矩阵 2*3
	(rowNO INT NOT NULL,
	colNO INT NOT NULL,
	data float,
	PRIMARY KEY NONCLUSTERED (rowNO,colNO) )

DECLARE @cDqAbc TABLE			-- 转换矩阵 3*2
	(rowNO INT NOT NULL,
	colNO INT NOT NULL,
	data float,
	PRIMARY KEY NONCLUSTERED (rowNO,colNO) )

DECLARE @Vdqk TABLE			--double VDqk[2] = {0,0}
	(RowNo INT NOT NULL PRIMARY KEY,
	valu float)
DECLARE @Idqk TABLE			--double IDqk[2] = {0,0}
	(RowNo INT NOT NULL PRIMARY KEY,
	valu float)
INSERT into @Vdqk(RowNo,valu) values ( 0,0 )
INSERT into @Vdqk(RowNo,valu) values ( 1,0 )
INSERT into @Idqk(RowNo,valu) values ( 0,0 )
INSERT into @Idqk(RowNo,valu) values ( 1,0 )

--②abc到dq_k的转换矩阵
IF (@dotnumber = 64)
BEGIN
	UPDATE TempData set HV1 = sqrt(HV1/0.02),HV2 = sqrt(HV2/0.02),HV3 = sqrt(HV3/0.02),HV4 = sqrt(HV4/0.02),HV5 = sqrt(HV5/0.02),HV6 = sqrt(HV6/0.02),HV7 = sqrt(HV7/0.02),HI1 = sqrt(HI1/0.02),HI2 = sqrt(HI2/0.02),HI3 = sqrt(HI3/0.02),HI4 = sqrt(HI4/0.02),HI5 = sqrt(HI5/0.02),HI6 = sqrt(HI6/0.02),HI7 = sqrt(HI7/0.02),dotnumber = 1;
	INSERT INTO HarmData( time_now ,HV1,HV2 ,HV3 ,HV4 ,HV5 ,HV6 ,HV7 ,HI1 ,HI2 ,HI3 ,HI4 ,HI5 ,HI6 ,HI7 ) SELECT getdate(),HV1,HV2 ,HV3 ,HV4 ,HV5 ,HV6 ,HV7 ,HI1 ,HI2 ,HI3 ,HI4 ,HI5 ,HI6 ,HI7 FROM TempData;
	UPDATE TempData set HV1 = 0,HV2 = 0,HV3 = 0,HV4 = 0,HV5 = 0,HV6 = 0,HV7 = 0,HI1 = 0,HI2 = 0,HI3 = 0,HI4 = 0,HI5 = 0,HI6 = 0,HI7 = 0,dotnumber = 1;
END
ELSE
BEGIN
	set @k = 1
	WHILE (@k <= 7)
	BEGIN

		INSERT into @cAbcDq(rowNO,colNO,data) values ( 0,0,cos(@k*@W*@t)*@SQRT2of3 )
		INSERT into @cAbcDq(rowNO,colNO,data) values ( 0,1,cos(@k*@W*@t-2*@k*pi()/3)*@SQRT2of3 )
		INSERT into @cAbcDq(rowNO,colNO,data) values ( 0,2,cos(@k*@W*@t+2*@k*pi()/3)*@SQRT2of3 )
		INSERT into @cAbcDq(rowNO,colNO,data) values ( 1,0,-sin(@k*@W*@t)*@SQRT2of3 )
		INSERT into @cAbcDq(rowNO,colNO,data) values ( 1,1,-sin(@k*@W*@t-2*@k*pi()/3)*@SQRT2of3 )
		INSERT into @cAbcDq(rowNO,colNO,data) values ( 1,2,-sin(@k*@W*@t+2*@k*pi()/3)*@SQRT2of3 )

		--dq_k到abc的转换矩阵
		INSERT into @cDqAbc(rowNO,colNO,data) values ( 0,0,cos(@k*@W*@t)*@SQRT2of3 )
		INSERT into @cDqAbc(rowNO,colNO,data) values ( 0,1,-sin(@k*@W*@t)*@SQRT2of3 )
		INSERT into @cDqAbc(rowNO,colNO,data) values ( 1,0,cos(@k*@W*@t-2*@k*pi()/3)*@SQRT2of3 )
		INSERT into @cDqAbc(rowNO,colNO,data) values ( 1,1,-sin(@k*@W*@t-2*@k*pi()/3)*@SQRT2of3 )
		INSERT into @cDqAbc(rowNO,colNO,data) values ( 2,0,cos(@k*@W*@t+2*@k*pi()/3)*@SQRT2of3 )
		INSERT into @cDqAbc(rowNO,colNO,data) values ( 2,1,-sin(@k*@W*@t+2*@k*pi()/3)*@SQRT2of3 )

		--③
		DECLARE @i_loop_m INT
		DECLARE @i_loop_n INT
		DECLARE @vDq float
		DECLARE @iDq float	--在每一个m的循环中,vdq[]只出现vdq[m]，所以可不使用数组
		DECLARE @temp_U float
		DECLARE @temp_I float	--u[n]和i[n]均只有2个元素，设计此临时变量以减少系统压力

		SET @i_loop_m = 0
		WHILE @i_loop_m < 2
		BEGIN
			SET @i_loop_n = 0
			-- 01
			SET @vDq = 0
			SET @iDq = 0
			
			-- 02
			WHILE @i_loop_n < 3
			BEGIN
				--实现u[n]
				IF @i_loop_n = 0
					SET @temp_U = @U1
				ELSE
					IF @i_loop_n = 1
						SET @temp_U = @U2
					ELSE
						SET @temp_U = @U3
				--实现i[n]
				IF @i_loop_n = 0
					SET @temp_I = @I1
				ELSE
					IF @i_loop_n = 1
						SET @temp_I = @I2
					ELSE
						SET @temp_I = @I3


				SELECT @vDq = data * @temp_U + @vDq FROM @cAbcDq WHERE rowNO = @i_loop_m AND colNO = @i_loop_n ;
				SELECT @iDq = data * @temp_I + @iDq FROM @cAbcDq WHERE rowNO = @i_loop_m AND colNO = @i_loop_n ;
				
				SET @i_loop_n = @i_loop_n + 1
			END
			
			--采用贝塞尔函数低通滤波得到k次谐波直流分量VDqK,IDqK
			-- 03
			IF @SampleNumber = 1
			BEGIN
				--初始化
				UPDATE Uv SET col1 = 0,col2 = 0,col3 = 0,col4 = 0,col5 = 0 where k = @k;
				UPDATE Ui SET col1 = 0,col2 = 0,col3 = 0,col4 = 0,col5 = 0 where k = @k;
				UPDATE Yv SET col1 = 0,col2 = 0,col3 = 0,col4 = 0,col5 = 0 where k = @k;
				UPDATE Yv SET col1 = 0,col2 = 0,col3 = 0,col4 = 0,col5 = 0 where k = @k;
				--Uv[m][0] = vDq[m]
				UPDATE Uv SET col1 = @vDq WHERE RowNo = @i_loop_m and k = @k;	--col1对应Uv的第0列，i_loop_m对应第m行
				--Ui[m][0] = iDq[m]
				UPDATE Ui SET col1 = @iDq WHERE RowNo = @i_loop_m and k = @k;
				--得到电压直流分量 VDqk[m]=Yv[m][0]
				UPDATE @VDqk SET valu =	(SELECT col1 from Yv where RowNo = @i_loop_m and k = @k) where RowNo = @i_loop_m;
				--得到电流直流分量 IDqk[m]=Yi[m][0]
				UPDATE @IDqk SET valu =	(SELECT col1 from Yi where RowNo = @i_loop_m and k = @k) where RowNo = @i_loop_m;
			END
			
			IF @SampleNumber = 2
			BEGIN
				--Uv[m][1] = vDq[m]
				UPDATE Uv SET col2 = @vDq WHERE RowNo = @i_loop_m and k = @k;	--col2对应Uv的第1列，i_loop_m对应第m行
				--Ui[m][1] = iDq[m]
				UPDATE Ui SET col2 = @iDq WHERE RowNo = @i_loop_m and k = @k;
				--Yv[m][1] = a10*Uv[m][1]+a11*Uv[m][0]-b11*Yv[m][0];
				UPDATE Yv SET col2 = @a10*@vDq+@a11*(select col1 from Uv where RowNo = @i_loop_m and k = @k) - @b11*(select col1 from Yv where RowNo = @i_loop_m  and k = @k)
					WHERE RowNo = @i_loop_m and k = @k;
				--Yi[m][1] = a10*Ui[m][1]+a11*Ui[m][0]-b11*Yi[m][0];
				UPDATE Yi SET col2 = @a10*@iDq+@a11*(select col1 from Ui where RowNo = @i_loop_m and k = @k) - @b11*(select col1 from Yi where RowNo = @i_loop_m and k = @k)
12 下一页
💿 文件大小 4 K
👤 上传用户 siclj
📂 所属分类 SQL Server
🏷️ 相关标签

#SQLserver #贝塞尔函数 #低通滤波 #数据
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -