그라파나 편차작업

하는이유- scr과 비슷한그래프찾기 위해서, 완만성 나타내기

 

[보고있는 데이터]

FARM_3797_CHOI

panel title : Parksagol_234Dev_4010_이동_R20_T10_A144_편차

 

[비슷한것]

	WITH tmpMovingAVG AS (
		SELECT sum(avgRate) wall, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd='234'
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
		avg(wall+100) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) - avg(wall) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 71 PRECEDING AND CURRENT ROW)
		  AS AVG_1_3_편차_wallPlus100
		FROM tmpMovingAVG
	) K order by [time]

 

	WITH tmpMovingAVG AS (
		SELECT sum(walkAll) wall, sum(AVGRATE) avr, sum(avgVal) avVal, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd='234'
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
		avg(avr) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) 
		+ avg(avVal) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW)
		- avg(avr) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 167 PRECEDING AND CURRENT ROW) 
		  AS avgVal추가
		FROM tmpMovingAVG
	) K order by [time]

	WITH tmpMovingAVG AS (
		SELECT sum(AVGRATE) wall, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd='234'
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
		avg(wall+100) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN $AVG_Time PRECEDING AND CURRENT ROW) 
		AS AVGRATE24_PLUS100
		FROM tmpMovingAVG
	) K order by [time]

 

 

 

 

 

419

	WITH tmpMovingAVG AS (
		SELECT sum(AVGRATE) wall, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd= $devCd
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
		  avg(wall+100) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) - avg(wall) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 71 PRECEDING AND CURRENT ROW) 
		  AS 삼일_평균편차
		FROM tmpMovingAVG
	) K order by [time]

	WITH tmpMovingAVG AS (
		SELECT sum(AVGRATE) avr, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd= 234
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
		avg(avr+100) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) 
		AS Day_24시간평균
		FROM tmpMovingAVG
	) K order by [time]

	WITH tmpMovingAVG AS (
		SELECT sum(AVGRATE) avr, sum(WALKALL) walkall, sum(walk1+walk2+walk3+walk4+walk5+walk6+walk7+walk9+walk10+walk11+YL-avgVal+AVGRATE) walknum, sum(YL-avgVal+avgrate) ylVal, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd= 234
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
		avg(ylVal+100) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) - avg(ylVal) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 71 PRECEDING AND CURRENT ROW)
		AS Day_24시간평균2
		FROM tmpMovingAVG
	) K order by [time]

 

 

 

418

 

 

 

	WITH tmpMovingAVG AS (
		SELECT sum(avgRate) avr, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd='234'
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
avg(avr) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 20 PRECEDING AND CURRENT ROW)
		  AS AVG_20_평균
		FROM tmpMovingAVG
	) K order by [time]

415

4월 14일 캡처

4월 12일

4월 8일

[실험] avgVal추가avgRATE-avgRate between167

	WITH tmpMovingAVG AS (
		SELECT sum(walkAll) wall, sum(AVGRATE) avr, sum(avgVal) avVal, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd='234'
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
		avg(avr) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) 
		+ avg(avVal) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW)
		- avg(avr) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 167 PRECEDING AND CURRENT ROW) 
		  AS 실험
		FROM tmpMovingAVG
	) K order by [time]

 

 

 

[4일 편차]

	WITH tmpMovingAVG AS (
		SELECT sum(walkAll) wall, sum(AVGRATE) avr, sum(avgVal) avVal, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd='234'
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
		avg(avr+100) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) - avg(avr) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 95 PRECEDING AND CURRENT ROW) 
		  AS 실험
		FROM tmpMovingAVG
	) K order by [time]

[5일편차]

[6일편차]

[7일편차]

4월 7일

4010번호, 222 기기번호

WALKALL

AVERAGE24+100

AVERAGE 하루-3일 편차 +100

WALKALL 2일-3일 편차 +100

 

4월 6일

	WITH tmpMovingAVG AS (
		SELECT sum(walkAll) wall, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd= $devCd
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
		  avg(wall+100) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) - avg(wall) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 71 PRECEDING AND CURRENT ROW) 
		  AS 삼일_평균편차
		FROM tmpMovingAVG
	) K order by [time]

4010번호

 

 

 

 

 

 

편차: 24시간 평균값- 3일평균값

	WITH tmpMovingAVG AS (
		SELECT sum(walkAll) wall, heatDt, heatTm, devCd from KITETERA.dbo.tblHeatNeo 
		where compCd='3797' AND devCd= $devCd
		group by heatDt, heatTm, devCd
	) 
	select * FROM (
		SELECT CAST(heatDt+' '+heatTm+':00' as DATETIME) as [time], 
		  avg(wall) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) - avg(wall) OVER (ORDER BY heatDt, heatTm ROWS BETWEEN 47 PRECEDING AND CURRENT ROW) 
		  AS avg23_avg47
		FROM tmpMovingAVG
	) K order by [time]

비교 208 - 3827

 

비교502