Schweizer Sturmforum

Code: Alles auswählen

WITH binned_data AS (
  -- Group data into 10-minute buckets
  SELECT 
    time_bucket('10 minutes', w.time) as bucket,
    w.value, 
    s.station_name, 
    s.altitude 
  FROM weather_data w 
  JOIN station_metadata s ON w.station = s.station_id 
  WHERE w.parameter = 'tre200s0' 
    AND $__timeFilter(w.time)
),
regression_stats AS (
  -- Calculate the height-dependent temperature line
  SELECT 
    bucket, 
    regr_slope(value, altitude) as sl, 
    regr_intercept(value, altitude) as ic 
  FROM binned_data 
  GROUP BY bucket
),
residuals AS (
  -- Calculate deviation and the expected value
  SELECT 
    b.bucket, b.station_name, b.value, b.altitude,
    (b.value - (b.altitude * rs.sl + rs.ic)) as res,
    (b.altitude * rs.sl + rs.ic) as expected
  FROM binned_data b
  JOIN regression_stats rs ON b.bucket = rs.bucket
),
variance_model AS (
  -- Model the cone
  SELECT 
    bucket,
    regr_slope(abs(res), altitude) as var_sl, 
    regr_intercept(abs(res), altitude) as var_ic
  FROM residuals
  GROUP BY bucket
)
SELECT 
  r.bucket as time, 
  r.station_name as "Station", 
  round(r.value::numeric, 1) as "Temp [°C]",
  round(r.expected::numeric, 1) as "Expected [°C]",
  round(
    (abs(r.res) / NULLIF((r.altitude * vm.var_sl + vm.var_ic), 0))::numeric, 
    2
  ) as "Sigma Score"
FROM residuals r
JOIN variance_model vm ON r.bucket = vm.bucket
WHERE (r.altitude * vm.var_sl + vm.var_ic) > 0.01
ORDER BY "Sigma Score" DESC 
LIMIT 100;

Code: Alles auswählen

CREATE FUNCTION [dbo].[CheckMesswertLI]
(
	@ANETZ_SLI_NR int
)
RETURNS int
AS
Begin

	--	02.01.2018/BO - Weitere Stationen (TIT, ATT, GOR, COV, MRP, PMA, WFJ) zur Prüfung mit grösserer Differenz hinzugefügt.
	--	01.01.2018/BO - Station "JUN - Jungfraujoch" wieder prüfen, allerdings mit grösserer Differenz. 01.01.2018 14:40 UTC hatte eine Fehlmessung bei JUN drin!
	--	19.12.2017/BO - Station "JUN - Jungfraujoch" von Prüfung ausgeschlossen.
	--	14.12.2017/BO - Differenz von +-3.0°C auf +-4.0°C geändert.
	--	13.12.2017/BO - Obere Grenze (98.0%) entfernt, da bei Problemen mit der Lüftung an Stationen es nur zu tiefe LI geben kann.
	--	12.12.2017/BO - Differenz von +-4.0°C auf +-3.0°C geändert.
	--	12.12.2017/BO - Differenz von +-5.0°C auf +-4.0°C geändert.
	--	27.11.2017/BO - Erstellt

	Declare 
		@LI_STATION_OK int,
		@LI_STATION float,
		@DATUM_ZEIT datetime,
		@ANZ_MESSWERTE int,
		@Quartil2 float,
		--@Quartil98 float,
		@STATION_NR int,
		@STATION_ABK varchar(10)
	
	set @LI_STATION_OK = 1
	
	if (@ANETZ_SLI_NR is not NULL)
	begin
		set @DATUM_ZEIT = NULL
		set @LI_STATION = NULL
		set @STATION_NR = NULL

		select top 1
			@STATION_NR = STATION_NR,
			@DATUM_ZEIT = DATUM_ZEIT,
			@LI_STATION = LI_STATION
		from dbo.ANETZ_SCHWEIZ WITH (NOLOCK)
		where (ANETZ_SLI_NR = @ANETZ_SLI_NR)

		if (@DATUM_ZEIT is not NULL)
			and (@LI_STATION is not NULL)
			and (@STATION_NR is not NULL)
		begin
			set @STATION_ABK = NULL

			select top 1
				@STATION_ABK = STATION_ABK
			from dbo.ANETZ_STATIONEN WITH (NOLOCK)
			where (STATION_NR = @STATION_NR)
				and (isnull(ACTIVE,0) = 1)

			set @ANZ_MESSWERTE = NULL

			select
				@ANZ_MESSWERTE = count(distinct ANETZ_SLI_NR)
			from dbo.ANETZ_SCHWEIZ WITH (NOLOCK)
			where (DATUM_ZEIT = @DATUM_ZEIT)
				and (LI_STATION is not NULL)

			if (isnull(@ANZ_MESSWERTE,0) >= 10)
			begin
				set @Quartil2 = NULL
				--set @Quartil98 = NULL

				-- 2.0% und 98.0% Grenze berechnen
				set @Quartil2 = (select distinct
					PERCENTILE_CONT(0.02) WITHIN GROUP (ORDER BY sd.LI_STATION) OVER(PARTITION BY sd.DATUM_ZEIT) as Quartil2
				from dbo.ANETZ_SCHWEIZ sd WITH (NOLOCK)
				where (sd.DATUM_ZEIT = @DATUM_ZEIT)
					and (sd.LI_STATION is not NULL))

				--set @Quartil98 = (select distinct
				--	PERCENTILE_CONT(0.98) WITHIN GROUP (ORDER BY sd.LI_STATION) OVER(PARTITION BY sd.DATUM_ZEIT) as Quartil98
				--from dbo.ANETZ_SCHWEIZ sd WITH (NOLOCK)
				--where (sd.DATUM_ZEIT = @DATUM_ZEIT)
				--	and (sd.LI_STATION is not NULL))

				if (isnull(@STATION_ABK,'') = 'JUN')		-- JUN = Jungfraujoch
					or (isnull(@STATION_ABK,'') = 'TIT')	-- TIT = Titlis
					or (isnull(@STATION_ABK,'') = 'ATT')	-- ATT = Les Attelas
					or (isnull(@STATION_ABK,'') = 'GOR')	-- GOR = Gornergrat
					or (isnull(@STATION_ABK,'') = 'COV')	-- COV = Corvatsch
					or (isnull(@STATION_ABK,'') = 'MRP')	-- MRP = Monte Rosa-Plattje
					or (isnull(@STATION_ABK,'') = 'PMA')	-- PMA = Piz Martegnas
					or (isnull(@STATION_ABK,'') = 'WFJ')	-- WFJ = Weissfluhjoch
				begin
					if (@Quartil2 is not NULL)
					begin
						if (@LI_STATION < (@Quartil2 - 6.0))
						begin
							set @LI_STATION_OK = 0
						end
					end
				end
				else
				begin
					if (@Quartil2 is not NULL)
					begin
						if (@LI_STATION < (@Quartil2 - 4.0))
						begin
							set @LI_STATION_OK = 0
						end
					end
				end

				--if (@Quartil98 is not NULL)
				--begin
				--	if (@LI_STATION > (@Quartil98 + 4.0))
				--	begin
				--		set @LI_STATION_OK = 0
				--	end
				--end
			end
		end
	end

	RETURN @LI_STATION_OK
END

Code: Alles auswählen

CREATE PROCEDURE [dbo].[MesswerteCheckMail]
AS

	--	02.01.2018/BO - Erstellt

	--	Test: EXEC dbo.MesswerteCheckMail

BEGIN

	declare
		@EMailSubject nvarchar(255),
		@BodyHTML nvarchar(max),
		@loop_counter int,
	    @item_category_counter int,
		@CRLF nvarchar(10),
		@DATUM_ZEIT_CHECK_VON datetime,
		@DATUM_ZEIT_CHECK_BIS datetime,
		@ANETZ_SLI_NR int,
		@STATION_NR int,
		@STATION_CODE varchar(255),
		@DATUM_ZEIT varchar(255),
		@T_STATION float,
		@TD_STATION float,
		@LI_STATION float

	set @DATUM_ZEIT_CHECK_VON = dateadd(day, -1, convert(date,getdate()))
	set @DATUM_ZEIT_CHECK_BIS = dateadd(minute, 59, dateadd(hour, 23, @DATUM_ZEIT_CHECK_VON) )

	set @CRLF = char(10) + char(13)
	set @BodyHTML = ''

	-- Memory Tabelle
	DECLARE @MESSWERTE_CHECK TABLE 
	(
		PK INT IDENTITY(1,1) NOT NULL,
		ANETZ_SLI_NR int,
		STATION_NR int,
		STATION_CODE varchar(255),
		DATUM_ZEIT varchar(255),
		T_STATION float,
		TD_STATION float,
		LI_STATION float
	)

	insert into @MESSWERTE_CHECK
	(
		ANETZ_SLI_NR,
		STATION_NR,
		STATION_CODE,
		DATUM_ZEIT,
		T_STATION,
		TD_STATION,
		LI_STATION
	)
	select
		a.ANETZ_SLI_NR,
		s.STATION_NR, 
		(s.STATION_ABK + ' ' + s.STATION_NAME + ' ' + convert(varchar(20),s.ALTITUDE) + 'm') as STATION_CODE,
		(convert(varchar(20), a.DATUM_ZEIT, 104) + ' ' + convert(varchar(20), a.DATUM_ZEIT, 108)) as DATUM_ZEIT,
		a.T_STATION,
		a.TD_STATION,
		a.LI_STATION
	from dbo.ANETZ_SCHWEIZ a with (nolock)
	inner join dbo.ANETZ_STATIONEN s with (nolock) on a.STATION_NR = s.STATION_NR
	where (a.DATUM_ZEIT >= @DATUM_ZEIT_CHECK_VON)
		and (a.DATUM_ZEIT <= @DATUM_ZEIT_CHECK_BIS)
		and (a.LI_STATION is not NULL)
		and not(isnull(a.CheckMesswertLI,1) = 1)
	order by s.STATION_NR, a.DATUM_ZEIT

	-- Loop Counter
	set @loop_counter = ISNULL((SELECT COUNT(*) FROM @MESSWERTE_CHECK),0)
	set @item_category_counter = 1

	if (@loop_counter > 0)
	begin
		set @BodyHTML ='<html><head>' + @CRLF
			+ '<style type="text/css">' + @CRLF
			+ '<!--' + @CRLF
			+ 'TD { FONT-SIZE: 8pt; COLOR: #000000; FONT-FAMILY: Verdana }' + @CRLF
			+ '//-->' + @CRLF
			+ '</style>' + @CRLF
			+ '</head><body><font face="Verdana" size="1">' + @CRLF
			+ '<H3>SwissMetNet ungültige Messwerte gemäss Lifted-Index Vergleich:</H3>' + @CRLF
			+ '<table border="1" style="border-collapse:collapse"><tr><td><b>ID</b></td><td><b>Datum/Zeit (UTC)</b></td><td><b>Station</b></td><td><b>Temperatur</b></td><td><b>Taupunkt</b></td><td><b>Lifted-Index</b></td></tr>' + @CRLF

		-- Loop
		while @loop_counter > 0 and @item_category_counter <= @loop_counter
		begin
			-- Reset Variablen
			set @ANETZ_SLI_NR = NULL
			set @STATION_NR = NULL
			set @STATION_CODE = NULL
			set @DATUM_ZEIT = NULL
			set @T_STATION = NULL
			set @TD_STATION = NULL
			set @LI_STATION = NULL

			-- Laden Variablen des zu verarbeitenden Falles
			select 
				@ANETZ_SLI_NR = ANETZ_SLI_NR,
				@STATION_NR = STATION_NR,
				@STATION_CODE = STATION_CODE,
				@DATUM_ZEIT = DATUM_ZEIT,
				@T_STATION = T_STATION,
				@TD_STATION = TD_STATION,
				@LI_STATION = LI_STATION
			from @MESSWERTE_CHECK
			where (PK = @item_category_counter)

			-- Eintrag für jeden Fehler
			set @BodyHTML = @BodyHTML 
				+ '<tr><td>' + isnull(convert(varchar(20),@ANETZ_SLI_NR),'') 
				+ '</td><td>' + isnull(convert(varchar(20),@DATUM_ZEIT),'') 
				+ '</td><td>' + isnull(convert(varchar(255),@STATION_NR),'') + '&nbsp;-&nbsp;' + isnull(@STATION_CODE,'') 
				+ '</td><td>' + isnull(convert(varchar(255),@T_STATION),'') 
				+ '</td><td>' + isnull(convert(varchar(255),@TD_STATION),'') 
				+ '</td><td>' + isnull(convert(varchar(255),@LI_STATION),'') 
				+ '</td></tr>' + @CRLF

			-- Counter für Loop erhöhen 
			set @item_category_counter = @item_category_counter + 1
		end

		set @BodyHTML = @BodyHTML +
				+ '</table>' + @CRLF
	end
	else
	begin
		set @BodyHTML ='<html><head>' + @CRLF
			+ '</head><body><font face="Verdana" size="3">' + @CRLF
			+ '<H3>SwissMetNet Messwerte Check:</H3>' + @CRLF
			+ 'Es wurden keine ungültigen Messwerte gefunden am: '
			+ convert(varchar(20), @DATUM_ZEIT_CHECK_VON, 104) + @CRLF
	end

	-- Wenn es Fehler gibt, dann eine E-Mail verschicken.
	if not(@BodyHTML = '')
	begin
		set @BodyHTML = @BodyHTML +'</font></body></html>'

		set @EMailSubject = 'i9: SwissMetNet Messwerte Check vom: ' + convert(varchar(20), @DATUM_ZEIT_CHECK_VON, 104)

		EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'PCBOKERMail',
		@body = @BodyHTML,
		@body_format ='HTML',
		@recipients = 'oker.b@bluewin.ch', 
		@subject = @EMailSubject;
	end
END