/
PAL 54 Clustering - Kmeans Best K.sql
62 lines (53 loc) · 2.51 KB
/
PAL 54 Clustering - Kmeans Best K.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
SET SCHEMA PAL;
-- cleanup
DROP TYPE PAL_T_KM_DATA;
DROP TYPE PAL_T_KM_PARAMS;
DROP TYPE PAL_T_KM_RESULTS;
DROP TYPE PAL_T_KM_CENTERS;
DROP TABLE PAL_KM_SIGNATURE;
CALL SYSTEM.AFL_WRAPPER_ERASER ('PAL_KM');
DROP VIEW V_KM_DATA;
DROP TABLE KM_RESULTS;
DROP TABLE KM_CENTERS;
DROP VIEW V_KM_RESULTS;
-- PAL setup
CREATE TYPE PAL_T_KM_DATA AS TABLE (ID INTEGER, LIFESPEND DOUBLE, NEWSPEND DOUBLE, INCOME DOUBLE, LOYALTY DOUBLE);
CREATE TYPE PAL_T_KM_PARAMS AS TABLE (NAME VARCHAR(60), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR(100));
CREATE TYPE PAL_T_KM_RESULTS AS TABLE (ID INTEGER, CENTER_ID INTEGER, DISTANCE DOUBLE);
CREATE TYPE PAL_T_KM_CENTERS AS TABLE (CENTER_ID INTEGER, LIFESPEND DOUBLE, NEWSPEND DOUBLE, INCOME DOUBLE, LOYALTY DOUBLE);
CREATE COLUMN TABLE PAL_KM_SIGNATURE (ID INTEGER, TYPENAME VARCHAR(100), DIRECTION VARCHAR(100));
INSERT INTO PAL_KM_SIGNATURE VALUES (1, 'PAL.PAL_T_KM_DATA', 'in');
INSERT INTO PAL_KM_SIGNATURE VALUES (2, 'PAL.PAL_T_KM_PARAMS', 'in');
INSERT INTO PAL_KM_SIGNATURE VALUES (3, 'PAL.PAL_T_KM_RESULTS', 'out');
INSERT INTO PAL_KM_SIGNATURE VALUES (4, 'PAL.PAL_T_KM_CENTERS', 'out');
GRANT SELECT ON PAL_KM_SIGNATURE TO SYSTEM;
CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_KM', 'AFLPAL', 'KMEANS', PAL_KM_SIGNATURE);
-- app setup
CREATE VIEW V_KM_DATA AS
SELECT ID, LIFESPEND, NEWSPEND, INCOME, LOYALTY
FROM CUSTOMERS
;
CREATE COLUMN TABLE KM_RESULTS LIKE PAL_T_KM_RESULTS;
CREATE COLUMN TABLE KM_CENTERS LIKE PAL_T_KM_CENTERS;
CREATE VIEW V_KM_RESULTS AS
SELECT a.ID, b.CUSTOMER, b.LIFESPEND, b.NEWSPEND, b.INCOME, b.LOYALTY, a.CENTER_ID + 1 AS CLUSTER_NUMBER
FROM KM_RESULTS a, CUSTOMERS b
WHERE a.ID = b.ID
;
-- app runtime
DROP TABLE #KM_PARAMS;
CREATE LOCAL TEMPORARY COLUMN TABLE #KM_PARAMS LIKE PAL_T_KM_PARAMS;
INSERT INTO #KM_PARAMS VALUES ('THREAD_NUMBER', 2, null, null);
INSERT INTO #KM_PARAMS VALUES ('GROUP_NUMBER', 3, null, null);
--INSERT INTO #KM_PARAMS VALUES ('GROUP_NUMBER_MIN', 2, null, null);
--INSERT INTO #KM_PARAMS VALUES ('GROUP_NUMBER_MAX', 10, null, null);
INSERT INTO #KM_PARAMS VALUES ('INIT_TYPE', 1, null, null);
INSERT INTO #KM_PARAMS VALUES ('DISTANCE_LEVEL', 2, null, null);
INSERT INTO #KM_PARAMS VALUES ('MAX_ITERATION', 100, null, null);
INSERT INTO #KM_PARAMS VALUES ('NORMALIZATION', 0, null, null);
INSERT INTO #KM_PARAMS VALUES ('EXIT_THRESHOLD', null, 0.0001, null);
TRUNCATE TABLE KM_RESULTS;
TRUNCATE TABLE KM_CENTERS;
CALL _SYS_AFL.PAL_KM (V_KM_DATA, #KM_PARAMS, KM_RESULTS, KM_CENTERS) WITH OVERVIEW;
SELECT * FROM KM_RESULTS;
SELECT * FROM KM_CENTERS;