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;

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply