
-- model evaluation & parameter search parameters
INSERT INTO "#Params" VALUES ('PROGRESS_INDICATOR_ID', null, null, 'Train Logistic Regression');
INSERT INTO "#Params" VALUES ('RESAMPLING_METHOD', null, null, 'stratified_cv'); -- cv, stratified_cv, bootstrap, stratified_bootstrap
INSERT INTO "#Params" VALUES ('FOLD_NUM', 5, null, null);
INSERT INTO "#Params" VALUES ('EVALUATION_METRIC', null, null, 'AUC'); -- RMSE, MAE, ERROR_RATE, NLL, AUC
INSERT INTO "#Params" VALUES ('REPEAT_TIMES', 2, null, null);
INSERT INTO "#Params" VALUES ('PARAM_SEARCH_STRATEGY', null, null, 'grid'); -- grid, random
--INSERT INTO "#Params" VALUES ('RANDOM_SEARCH_TIMES', 2, null, null); -- use when search strategy = random
-- algorithm specific parameter values
INSERT INTO "#Params" VALUES ('ENET_LAMBDA_VALUES', null, null, '{0.01,0.02,0.005,0.001}'); -- discrete values
INSERT INTO "#Params" VALUES ('ENET_ALPHA_RANGE', null, null, '[0.005,0.001,0.01]'); -- range: start, increment, end
-- check progress
SELECT * FROM "_SYS_AFL"."FUNCTION_PROGRESS_IN_AFLPAL" WHERE "EXECUTION_ID" = 'Train Logistic Regression';
-- multiple groups, group-specific parameters
CREATE TABLE "StockPrices" ("stockId" VARCHAR(3), "timeId" INTEGER, "price" DOUBLE);
CREATE LOCAL TEMPORARY COLUMN TABLE "#Params" ("stockId" VARCHAR(3), "name" VARCHAR(60), "intArgs" INTEGER, "doubleArgs" DOUBLE, "stringArgs" VARCHAR(100));
CREATE COLUMN TABLE "Forecast" ("stockId" VARCHAR(3), "timeId" INTEGER, "predictedPrice" DOUBLE, "pi1Lower" DOUBLE, "pi1Upper" DOUBLE, "pi2Lower" DOUBLE, "pi2Upper" DOUBLE);
CREATE COLUMN TABLE "Statistics" ("stockId" VARCHAR(3), "name" NVARCHAR(100), "value" NVARCHAR(100));
INSERT INTO "StockPrices" VALUES ('ABC', 1, 362);
INSERT INTO "StockPrices" VALUES ('ABC', 2, 385);
INSERT INTO "StockPrices" VALUES ('ABC', 3, 432);
INSERT INTO "StockPrices" VALUES ('ABC', 4, 341);
INSERT INTO "StockPrices" VALUES ('ABC', 5, 382);
INSERT INTO "StockPrices" VALUES ('ABC', 6, 409);
INSERT INTO "StockPrices" VALUES ('ABC', 7, 498);
INSERT INTO "StockPrices" VALUES ('ABC', 8, 387);
INSERT INTO "StockPrices" VALUES ('ABC', 9, 473);
INSERT INTO "StockPrices" VALUES ('ABC', 10, 513);
INSERT INTO "StockPrices" VALUES ('ABC', 11, 582);
INSERT INTO "StockPrices" VALUES ('ABC', 12, 474);
INSERT INTO "StockPrices" VALUES ('XYZ', 1, 544);
INSERT INTO "StockPrices" VALUES ('XYZ', 2, 582);
INSERT INTO "StockPrices" VALUES ('XYZ', 3, 681);
INSERT INTO "StockPrices" VALUES ('XYZ', 4, 557);
INSERT INTO "StockPrices" VALUES ('XYZ', 5, 626);
INSERT INTO "StockPrices" VALUES ('XYZ', 6, 654);
INSERT INTO "StockPrices" VALUES ('XYZ', 7, 691);
INSERT INTO "StockPrices" VALUES ('XYZ', 8, 712);
INSERT INTO "StockPrices" VALUES ('XYZ', 9, 674);
INSERT INTO "StockPrices" VALUES ('XYZ', 10, 732);
INSERT INTO "StockPrices" VALUES ('XYZ', 11, 745);
INSERT INTO "StockPrices" VALUES ('XYZ', 12, 775);
INSERT INTO "#Params" VALUES ('ABC', 'MODELSELECTION', 1, null, null);
INSERT INTO "#Params" VALUES ('ABC', 'FORECAST_NUM', 3, null, null);
INSERT INTO "#Params" VALUES ('ABC', 'INITIAL_METHOD', 0, null, null);
INSERT INTO "#Params" VALUES ('XYZ', 'MODELSELECTION', 1, null, null);
INSERT INTO "#Params" VALUES ('XYZ', 'FORECAST_NUM', 3, null, null);
INSERT INTO "#Params" VALUES ('XYZ', 'INITIAL_METHOD', 1, null, null);
INSERT INTO "#Params" VALUES ('XYZ', 'PREDICTION_CONFIDENCE_1', null, 0.75, null);
INSERT INTO "#Params" VALUES ('XYZ', 'PREDICTION_CONFIDENCE_2', null, 0.90, null);
CALL "_SYS_AFL"."PAL_AUTO_EXPSMOOTH" ("StockPrices", "#Params", "Forecast", "Statistics") WITH OVERVIEW WITH HINT (PARALLEL_BY_PARAMETER_VALUES(p1."stockId", p2."stockId"));
-- page rank
CREATE COLUMN TABLE "Data" ("pageFrom" NVARCHAR(100), "pageTo" NVARCHAR(100));
CREATE LOCAL TEMPORARY COLUMN TABLE "#Params" ("name" VARCHAR(60), "intArgs" INTEGER, "doubleArgs" DOUBLE, "stringArgs" VARCHAR(100));
CREATE COLUMN TABLE "Results" ("page" NVARCHAR(100), "rank" DOUBLE);
INSERT INTO "Data" VALUES ('Home', 'About');
INSERT INTO "Data" VALUES ('Home', 'Product');
INSERT INTO "Data" VALUES ('Home', 'Links');
INSERT INTO "Data" VALUES ('About', 'Home');
INSERT INTO "Data" VALUES ('Product', 'Home');
INSERT INTO "Data" VALUES ('Links', 'Home');
INSERT INTO "Data" VALUES ('Links', 'External Site A');
INSERT INTO "Data" VALUES ('Links', 'External Site B');
INSERT INTO "Data" VALUES ('Links', 'External Site C');
INSERT INTO "Data" VALUES ('Links', 'External Site D');
INSERT INTO "Data" VALUES ('External Site A', 'Home');
INSERT INTO "Data" VALUES ('External Site A', 'Product');
INSERT INTO "Data" VALUES ('External Site B', 'Home');
INSERT INTO "Data" VALUES ('External Site C', 'Home');
INSERT INTO "Data" VALUES ('External Site D', 'Home');
INSERT INTO "Data" VALUES ('External Site D', 'Product');
INSERT INTO "#Params" VALUES ('DAMPING', null, 0.85, null); -- default: 0.85
CALL "_SYS_AFL"."PAL_PAGERANK" ("Data", "#Params", "Results") WITH OVERVIEW;
-- create state
CREATE LOCAL TEMPORARY COLUMN TABLE "#Empty" ("id" INTEGER);
CREATE LOCAL TEMPORARY COLUMN TABLE "#Params" ("name" VARCHAR(60), "intArgs" INTEGER, "doubleArgs" DOUBLE, "stringArgs" VARCHAR(100));
CREATE COLUMN TABLE "State" ("name" VARCHAR(50), "value" VARCHAR(100));
INSERT INTO "#Params" VALUES ('ALGORITHM', 24, null, null); -- 1: SVM, 2: Random DT, 3: Decision Tree, 4: Cluster Assignment, 5: LDA Inference, 6: Binning, 7: Naive Bayes, 8: PCA, 9: BPNN, 11: FRM, 16: Multiple Linear Regression, 20: Logistic Regression, 24: ALS
INSERT INTO "#Params" VALUES ('STATE_DESCRIPTION', null, null, 'My Trained ALS Model');
CALL "_SYS_AFL"."PAL_CREATE_MODEL_STATE" ("ModelMetadata", "ModelMap", "ModelFactors", "#Empty", "#Empty", "#Params", "State") WITH OVERVIEW;
SELECT * FROM "State";
SELECT * FROM "SYS"."M_AFL_STATES";
-- predict with state
TRUNCATE TABLE "#Params";
CREATE COLUMN TABLE "RatingsNew" ("id" INTEGER, "user" NVARCHAR(255), "album" NVARCHAR(255));
CREATE COLUMN TABLE "EmptyModelMetadata" LIKE "ModelMetadata";
CREATE COLUMN TABLE "EmptyModelMap" LIKE "ModelMap";
CREATE COLUMN TABLE "EmptyModelFactors" LIKE "ModelFactors";
INSERT INTO "#Params" ("name", "stringArgs") SELECT "name", "value" FROM "State";
TRUNCATE TABLE "RatingsNew";
INSERT INTO "RatingsNew" VALUES (1,'Julie','Led Zeppelin IV');
--INSERT INTO "RatingsNew" VALUES (1,'Jamie','Born in the U.S.A.');
CALL "_SYS_AFL"."PAL_ALS_PREDICT" ("RatingsNew", "EmptyModelMetadata", "EmptyModelMap", "EmptyModelFactors", "#Params", ?);
-- delete state
TRUNCATE TABLE "#Params";
CALL "_SYS_AFL"."PAL_DELETE_MODEL_STATE" ("State", "#Params", ?);
TRUNCATE TABLE "State";
SELECT * FROM "SYS"."M_AFL_STATES";
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
7 | |
7 | |
7 | |
7 | |
6 | |
5 | |
5 | |
5 | |
5 | |
5 |