Skip to Content
Author's profile photo Former Member

Predictive analysis function Time series and Anomaly detection

HI All,

Please find the below code for predictive analysis function Time series and Anomaly detection.

I hope its going help who is working on predictive analysis.

Time Series Single smooth and anomaly detection

SET SCHEMA DM_PAL;

DROP TYPE PAL_SINGLESMOOTH_DATA_T;

CREATE TYPE PAL_SINGLESMOOTH_DATA_T AS TABLE(“ID” INT, “RAWDATA” DOUBLE);

DROP TYPE PAL_SINGLESMOOTH_RESULT_T;

CREATE TYPE PAL_SINGLESMOOTH_RESULT_T AS TABLE(“TIME” INT, “OUTPUT” DOUBLE);

DROP TYPE PAL_CONTROL_T;

CREATE TYPE PAL_CONTROL_T AS TABLE(“Name” VARCHAR(100), “intArgs” INT, “doubleArgs” DOUBLE, “strArgs” VARCHAR(100));

DROP table PAL_SINGLESMOOTH_PDATA_TBL;

CREATE column table PAL_SINGLESMOOTH_PDATA_TBL(“ID” INT,“TYPENAME” VARCHAR(100),“DIRECTION” VARCHAR(100));

insert into PAL_SINGLESMOOTH_PDATA_TBL values

(1,‘DM_PAL.PAL_SINGLESMOOTH_DATA_T’,‘in’);

insert into PAL_SINGLESMOOTH_PDATA_TBL values (2,‘DM_PAL.PAL_CONTROL_T’,‘in’);

insert into PAL_SINGLESMOOTH_PDATA_TBL values (3,‘DM_PAL.PAL_SINGLESMOOTH_RESULT_T’,‘out’);

GRANT SELECT ON DM_PAL.PAL_SINGLESMOOTH_PDATA_TBL to SYSTEM;

CALL SYSTEM.afl_wrapper_generator(‘SINGLESMOOTH_TEST’,‘AFLPAL’,‘SINGLESMOOTH’,PAL_SINGLESMOOTH_PDATA_TBL);

DROP TABLE  #PAL_CONTROL_TBL;

CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL (“Name” VARCHAR(100),

“intArgs” INT, “doubleArgs” DOUBLE, “strArgs” VARCHAR(100));

INSERT INTO #PAL_CONTROL_TBL VALUES (‘RAW_DATA_COL’,1,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES (‘ALPHA’,null,0.1,null);

INSERT INTO #PAL_CONTROL_TBL VALUES (‘FORECAST_NUM’,1,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES (‘STARTTIME’,2000,null,null);

DROP TABLE PAL_SINGLESMOOTH_DATA_TBL;

CREATE COLUMN TABLE PAL_SINGLESMOOTH_DATA_TBL (“ID” INT, “RAWDATA” DOUBLE);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (0,200.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (1,135.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (2,195.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (3,197.5);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (4,310.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (5,175.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (6,155.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (7,130.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (8,220.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (9,277.5);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (10,235.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (11,230.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (12,-13.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (13,198.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (14,-10.5);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (15,313.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (16,-3.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (17,13.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (18,130.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (19,60.0);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (20,2777.5);

INSERT INTO PAL_SINGLESMOOTH_DATA_TBL VALUES (21,2777.5);

DROP TABLE PAL_SINGLESMOOTH_RESULT_TBL;

CREATE COLUMN TABLE PAL_SINGLESMOOTH_RESULT_TBL (“TIME” INT, “OUTPUT” DOUBLE);

CALL _SYS_AFL.SINGLESMOOTH_TEST(PAL_SINGLESMOOTH_DATA_TBL, “#PAL_CONTROL_TBL”, PAL_SINGLESMOOTH_RESULT_TBL) with

overview;

SELECT * FROM PAL_SINGLESMOOTH_RESULT_TBL;

For Anomaly Detection

SET SCHEMA PAL;

DROP TYPE PAL_AD_RESULT_T;

CREATE TYPE PAL_AD_RESULT_T AS TABLE( “ID” INT, “V000” DOUBLE, “V001” DOUBLE);

DROP TYPE PAL_AD_DATA_T;

CREATE TYPE PAL_AD_DATA_T AS TABLE( “ID” INT, “V000” DOUBLE, “V001” DOUBLE);

DROP TYPE PAL_CONTROL_T;

CREATE TYPE PAL_CONTROL_T AS TABLE( “NAME” VARCHAR (50), “INTARGS” INTEGER, “DOUBLEARGS” DOUBLE, “STRINGARGS” VARCHAR

(100));

DROP TABLE PAL_AD_PDATA_TBL;

CREATE COLUMN TABLE PAL_AD_PDATA_TBL( “ID” INT, “TYPENAME” VARCHAR(100), “DIRECTION” VARCHAR(100) );

INSERT INTO PAL_AD_PDATA_TBL VALUES (1, ‘PAL.PAL_AD_DATA_T’, ‘in’);

INSERT INTO PAL_AD_PDATA_TBL VALUES (2, ‘PAL.PAL_CONTROL_T’, ‘in’);

INSERT INTO PAL_AD_PDATA_TBL VALUES (3, ‘PAL.PAL_AD_RESULT_T’, ‘out’);

GRANT SELECT ON PAL.PAL_AD_PDATA_TBL to SYSTEM;

CALL SYSTEM.afl_wrapper_generator(‘PAL_ANOMALY_DETECTION’, ‘AFLPAL’, ‘ANOMALYDETECTION’, PAL_AD_PDATA_TBL);

DROP TABLE PAL_AD_DATA_TBL;

CREATE COLUMN TABLE PAL_AD_DATA_TBL ( “ID” INT, “V000” DOUBLE, “V001” DOUBLE);

INSERT INTO PAL_AD_DATA_TBL VALUES (0 , 0.5, 0.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (1 , 1.5, 0.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (2 , 1.5, 1.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (3 , 0.5, 1.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (4 , 1.1, 1.2);

INSERT INTO PAL_AD_DATA_TBL VALUES (5 , 0.5, 15.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (6 , 1.5, 15.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (7 , 1.5, 16.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (8 , 0.5, 16.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (9 , 1.2, 16.1);

INSERT INTO PAL_AD_DATA_TBL VALUES (10, 15.5, 15.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (11, 16.5, 15.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (12, 16.5, 16.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (13, 15.5, 16.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (14, 15.6, 16.2);

INSERT INTO PAL_AD_DATA_TBL VALUES (15, 15.5, 0.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (16, 16.5, 0.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (17, 16.5, 1.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (18, 15.5, 1.5);

INSERT INTO PAL_AD_DATA_TBL VALUES (19, 15.7, 1.6);

INSERT INTO PAL_AD_DATA_TBL VALUES (20,-1.0, -1.0);

DROP TABLE #PAL_CONTROL_TBL;

CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_CONTROL_TBL ( “NAME” VARCHAR (50), “INTARGS” INTEGER, “DOUBLEARGS” DOUBLE,

“STRINGARGS” VARCHAR (100));

INSERT INTO #PAL_CONTROL_TBL VALUES (‘THREAD_NUMBER’,2,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES (‘GROUP_NUMBER’,4,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES (‘INIT_TYPE’,4,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES (‘DISTANCE_LEVEL’,2,null,null);

INSERT INTO #PAL_CONTROL_TBL VALUES (‘MAX_ITERATION’,100,null,null);

DROP TABLE PAL_AD_RESULT_TBL;

CREATE COLUMN TABLE PAL_AD_RESULT_TBL ( “ID” INT, “V000” DOUBLE, “V001” DOUBLE);

CALL _SYS_AFL.PAL_ANOMALY_DETECTION(PAL_AD_DATA_TBL, “#PAL_CONTROL_TBL”, PAL_AD_RESULT_TBL) with overview;

select * from PAL_AD_RESULT_TBL;

Assigned tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.