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;