| m4_include(`SQLCommon.m4') |
| |
| /* ----------------------------------------------------------------------------- |
| * Test Linear Regression. |
| * -------------------------------------------------------------------------- */ |
| |
| /* |
| * The following example is taken from: |
| * http://www.weibull.com/DOEWeb/estimating_regression_models_using_least_squares.htm#Example%205.1 |
| * http://www.weibull.com/DOEWeb/hypothesis_tests_in_multiple_linear_regression.htm#Example%205.3 |
| */ |
| DROP TABLE IF EXISTS weibull; |
| CREATE TABLE weibull ( |
| id INTEGER NOT NULL, |
| x1 DOUBLE PRECISION, |
| x2 DOUBLE PRECISION, |
| y DOUBLE PRECISION |
| ) m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (id)'); |
| |
| /* |
| * We materialize the table here because on MPP systems we want to distribute |
| * the data to the segments (and not do the calculations in memory). |
| */ |
| INSERT INTO weibull(id, x1, x2, y) VALUES |
| ( 1, 41.9, 29.1, 251.3), |
| ( 2, 43.4, 29.3, 251.3), |
| ( 3, 43.9, 29.5, 248.3), |
| ( 4, 44.5, 29.7, 267.5), |
| ( 5, 47.3, 29.9, 273.0), |
| ( 6, 47.5, 30.3, 276.5), |
| ( 7, 47.9, 30.5, 270.3), |
| ( 8, 50.2, 30.7, 274.9), |
| ( 9, 52.8, 30.8, 285.0), |
| (10, 53.2, 30.9, 290.0), |
| (11, 56.7, 31.5, 297.0), |
| (12, 57.0, 31.7, 302.5), |
| (13, 63.5, 31.9, 304.5), |
| (14, 65.3, 32.0, 309.3), |
| (15, 71.1, 32.1, 321.7), |
| (16, 77.0, 32.5, 330.7), |
| (17, 77.8, 32.9, 349.0), |
| -- Add NULL values |
| (18, NULL, 32.1, 321.7), |
| (19, 77.0, NULL, 330.7), |
| (20, 77.8, 32.9, NULL); |
| |
| SELECT assert( |
| relative_error(coef, ARRAY[-153.51, 1.24, 12.08]) < 1e-4 AND |
| relative_error(t_stats[2], 3.1393) < 1e-4 AND |
| relative_error(t_stats[3], 3.0726) < 1e-4 AND |
| relative_error(p_values[3], 0.0083) < 1e-2, |
| 'Linear regression (weibull.com test): Wrong results' |
| ) FROM ( |
| SELECT (linregr(y, ARRAY[1, x1, x2])).* |
| FROM weibull |
| ) q; |
| |
| |
| |
| /* |
| * The following example is taken from: |
| * http://biocomp.health.unm.edu/biomed505/Course/Cheminformatics/advanced/data_classification_qsar/linear_multilinear_regression.pdf |
| */ |
| DROP TABLE IF EXISTS unm; |
| CREATE TABLE unm ( |
| id INTEGER NOT NULL, |
| x1 DOUBLE PRECISION, |
| x2 DOUBLE PRECISION, |
| y DOUBLE PRECISION |
| ); |
| |
| INSERT INTO unm(id, x1, x2, y) VALUES |
| (1, 0, 0.30, 10.14), |
| (2, 0.69, 0.60, 11.93), |
| (3, 1.10, 0.90, 13.57), |
| (4, 1.39, 1.20, 14.17), |
| (5, 1.61, 1.50, 15.25), |
| (6, 1.79, 1.80, 16.15), |
| -- Add NULL values |
| (7, NULL, 1.20, 14.17), |
| (8, 1.61, NULL, 15.25), |
| (9, 1.79, 1.80, NULL); |
| |
| SELECT assert( |
| relative_error(coef, ARRAY[9.69, 2.09, 1.50]) < 1e-2, |
| 'Linear regression (unm): Wrong coefficients' |
| ) FROM ( |
| SELECT (linregr(y, ARRAY[1, x1, x2])).* |
| FROM unm |
| ) q; |
| |
| |
| /* |
| * The following example is taken from: |
| * http://www.stat.columbia.edu/~martin/W2110/SAS_7.pdf |
| */ |
| |
| DROP TABLE IF EXISTS houses; |
| CREATE TABLE houses ( |
| id SERIAL NOT NULL, |
| tax INTEGER, |
| bedroom REAL, |
| bath REAL, |
| price INTEGER, |
| size INTEGER, |
| lot INTEGER |
| ); |
| |
| INSERT INTO houses(tax, bedroom, bath, price, size, lot) VALUES |
| ( 590, 2, 1, 50000, 770, 22100), |
| (1050, 3, 2, 85000, 1410, 12000), |
| ( 20, 3, 1, 22500, 1060, 3500 ), |
| ( 870, 2, 2, 90000, 1300, 17500), |
| (1320, 3, 2, 133000, 1500, 30000), |
| (1350, 2, 1, 90500, 820, 25700), |
| (2790, 3, 2.5, 260000, 2130, 25000), |
| ( 680, 2, 1, 142500, 1170, 22000), |
| (1840, 3, 2, 160000, 1500, 19000), |
| (3680, 4, 2, 240000, 2790, 20000), |
| (1660, 3, 1, 87000, 1030, 17500), |
| (1620, 3, 2, 118600, 1250, 20000), |
| (3100, 3, 2, 140000, 1760, 38000), |
| (2070, 2, 3, 148000, 1550, 14000), |
| ( 650, 3, 1.5, 65000, 1450, 12000); |
| |
| -- Values computed with MADlib |
| SELECT assert( |
| relative_error(coef, ARRAY[27923.43, -35524.78, 2269.34, 130.79]) < 1e-4 AND |
| relative_error(r2, 0.74537) < 1e-3 AND |
| relative_error(std_err, ARRAY[56306, 25037, 22208, 36.209]) < 1e-4 AND |
| relative_error(t_stats, ARRAY[0.49592, -1.4189, 0.10218, 3.6122]) < 1e-4 AND |
| relative_error(p_values, ARRAY[0.62971, 0.18363, 0.92045, 0.0040816]) < 1e-4 AND |
| relative_error(condition_no, 9783.0184) < 1e-4, |
| 'Linear regression (houses): Wrong results' |
| ) FROM ( |
| SELECT (linregr(price, array[1, bedroom, bath, size])).* |
| FROM houses |
| ) q; |
| |
| -- Merge functions must prodice valid results when being called with the |
| -- aggregate's initial state |
| SELECT assert( |
| (linregr).r2 = 1, |
| 'Linear regression failed on singleton data set.' |
| ) FROM ( |
| SELECT linregr_final( |
| linregr_merge_states( |
| linregr_transition(CAST('' AS bytea8), 3, ARRAY[5,2]), |
| CAST('' AS bytea8) |
| ) |
| ) AS linregr |
| ) ignored; |
| |
| SELECT assert( |
| (linregr).r2 = 1, |
| 'Linear regression failed on singleton data set.' |
| ) FROM ( |
| SELECT linregr_final( |
| linregr_merge_states( |
| CAST('' AS bytea8), |
| linregr_transition(CAST('' AS bytea8), 3, ARRAY[5,2]) |
| ) |
| ) AS linregr |
| ) ignored; |
| |
| ------------------------------------------------------------------------ |
| |
| drop table if exists result_lin_houses; |
| drop table if exists result_lin_houses_summary; |
| select linregr_train('houses', 'result_lin_houses', 'price', |
| 'array[1, tax, bath, size]', |
| 'bedroom', True); |
| |
| select assert( |
| relative_error(coef, array[-84242.0345, 55.4430, -78966.9754, 225.6119]) < 1e-2 and |
| relative_error(r2, 0.9688) < 1e-2 and |
| relative_error(std_err, array[35019.00, 19.57, 23036.81, 49.04]) < 1e-2 and |
| relative_error(t_stats, array[-2.406, 2.833, -3.428, 4.600]) < 1e-2 and |
| relative_error(p_values, array[0.251, 0.216, 0.181, 0.136]) < 1e-2 and |
| relative_error(condition_no, 10086.1) < 1e-2 and |
| relative_error(bp_stats, 2.5451) < 1e-2 and |
| relative_error(bp_p_value, 0.467192) < 1e-2, |
| 'Linear regression with heteroskedasticity & grouping (houses): Wrong results' |
| ) |
| from result_lin_houses |
| where bedroom = 2; |
| |
| select assert( |
| relative_error(coef, array[-88155.8292502747,27.1966436293179,41404.0293389239,62.6375210724027]) < 1e-2 and |
| relative_error(r2, 0.841699901312963) < 1e-2 and |
| relative_error(std_err, array[57867.9999699512,17.82723091538,43643.1321521931,70.8506824870639]) < 1e-2 and |
| relative_error(t_stats, array[-1.52339512850022,1.52556747362568,0.948695185179172,0.884077878626493]) < 1e-2 and |
| relative_error(p_values, array[0.18816143289241,0.187636685729725,0.38634003235866,0.417132778730133]) < 1e-2 and |
| relative_error(condition_no, 11722.62) < 1e-2 and |
| relative_error(bp_stats, 6.753838) < 1e-2 and |
| relative_error(bp_p_value, 0.08017) < 1e-2, |
| 'Linear regression with heteroskedasticity & grouping (houses): Wrong results' |
| ) |
| from result_lin_houses |
| where bedroom = 3; |
| |
| SELECT id, |
| linregr_predict(coef, ARRAY[1, tax, bath, size]) as pred_value |
| FROM houses h, result_lin_houses m |
| ORDER BY id; |
| |
| drop table if exists result_lin_houses cascade; |
| |
| select linregr_train(); |
| select linregr_train('usage'); |
| select linregr_train('example'); |
| |
| select linregr_predict(); |
| select linregr_predict('usage'); |
| select linregr_predict('example'); |
| |
| |
| |
| |
| |
| ------------------------------------------------------------------------ |
| |
| |
| -- Test case for JSON Data Type and Special characters. |
| -- This function checks special characters for GPDB version = 4.x and JSON for 5.x and above |
| |
| create or replace function linereg_expr_test() RETURNS VOID AS |
| $$ |
| DECLARE col_type TEXT; |
| |
| begin |
| select typname into col_type from pg_type where typname = 'json' ; |
| if col_type = 'json' THEN |
| |
| DROP TABLE IF EXISTS houses_json; |
| CREATE TABLE houses_json ( |
| id SERIAL NOT NULL, |
| data json |
| ); |
| |
| |
| INSERT INTO houses_json VALUES |
| ( 2 ,'{ "ta,x": 590, "bedroom":2, "ba$th":1, "pr''ice": 50000, "size": 770, "lot":22100 }'), |
| ( 4 ,'{ "ta,x": 1050, "bedroom":3, "ba$th":2, "pr''ice": 85000, "size":1410, "lot":12000 }'), |
| ( 1 ,'{ "ta,x": 20, "bedroom":3, "ba$th":1, "pr''ice": 22500, "size":1060, "lot":3500 }'), |
| ( 6 ,'{ "ta,x": 870, "bedroom":2, "ba$th":2, "pr''ice": 90000, "size":1300, "lot":17500 }'), |
| ( 3 ,'{ "ta,x": 1320, "bedroom":3, "ba$th":2, "pr''ice": 133000, "size":1500, "lot":30000 }'), |
| ( 5 ,'{ "ta,x": 1350, "bedroom":2, "ba$th":1, "pr''ice": 90500, "size": 820, "lot":25700 }'), |
| ( 7 ,'{ "ta,x": 2790, "bedroom":3, "ba$th":2.5, "pr''ice": 260000, "size":2130, "lot":25000 }'), |
| ( 10 ,'{ "ta,x": 680, "bedroom":2, "ba$th":1, "pr''ice": 142500, "size":1170, "lot":22000 }'), |
| ( 9 ,'{ "ta,x": 1840, "bedroom":3, "ba$th":2, "pr''ice": 160000, "size":1500, "lot":19000 }'), |
| ( 8 ,'{ "ta,x": 3680, "bedroom":4, "ba$th":2, "pr''ice": 240000, "size":2790, "lot":20000 }'), |
| ( 11 ,'{ "ta,x": 1660, "bedroom":3, "ba$th":1, "pr''ice": 87000, "size":1030, "lot":17500 }'), |
| ( 12 ,'{ "ta,x": 1620, "bedroom":3, "ba$th":2, "pr''ice": 118600, "size":1250, "lot":20000 }'), |
| ( 13 ,'{ "ta,x": 3100, "bedroom":3, "ba$th":2, "pr''ice": 140000, "size":1760, "lot":38000 }'), |
| ( 14 ,'{ "ta,x": 2070, "bedroom":2, "ba$th":3, "pr''ice": 148000, "size":1550, "lot":14000 }'), |
| ( 15 ,'{ "ta,x": 650, "bedroom":3, "ba$th":1.5, "pr''ice": 65000, "size":1450, "lot":12000 }'); |
| |
| drop table if exists result_lin_houses_json; |
| drop table if exists result_lin_houses_json_summary; |
| PERFORM linregr_train('houses_json', 'result_lin_houses_json', '(data->>''pr''''ice'')::integer', |
| 'array[1, (data->>''ta,x'')::integer, (data->>''ba$th'')::double precision, (data->>''size'')::integer]', |
| 'data->>''bedroom''', True); |
| PERFORM assert( |
| relative_error(coef, array[-84242.0345, 55.4430, -78966.9754, 225.6119]) < 1e-2 and |
| relative_error(r2, 0.9688) < 1e-2 and |
| relative_error(std_err, array[35019.00, 19.57, 23036.81, 49.04]) < 1e-2 and |
| relative_error(t_stats, array[-2.406, 2.833, -3.428, 4.600]) < 1e-2 and |
| relative_error(p_values, array[0.251, 0.216, 0.181, 0.136]) < 1e-2 and |
| relative_error(condition_no, 10086.1) < 1e-2 and |
| relative_error(bp_stats, 2.5451) < 1e-2 and |
| relative_error(bp_p_value, 0.467192) < 1e-2, |
| 'Linear regression with heteroskedasticity & grouping (houses): Wrong results' |
| ) |
| from result_lin_houses_json |
| where "data->>'bedroom'"::integer = 2; |
| |
| else |
| |
| |
| |
| DROP TABLE IF EXISTS houses_spcl; |
| CREATE TABLE houses_spcl ( |
| id SERIAL NOT NULL, |
| "ta,x" INTEGER, |
| "bed,room" REAL, |
| "ba$th" REAL, |
| "pr'ice" INTEGER, |
| size INTEGER, |
| lot INTEGER |
| ); |
| |
| INSERT INTO houses_spcl("ta,x", "bed,room", "ba$th", "pr'ice", size, lot) VALUES |
| ( 590, 2, 1, 50000, 770, 22100), |
| (1050, 3, 2, 85000, 1410, 12000), |
| ( 20, 3, 1, 22500, 1060, 3500 ), |
| ( 870, 2, 2, 90000, 1300, 17500), |
| (1320, 3, 2, 133000, 1500, 30000), |
| (1350, 2, 1, 90500, 820, 25700), |
| (2790, 3, 2.5, 260000, 2130, 25000), |
| ( 680, 2, 1, 142500, 1170, 22000), |
| (1840, 3, 2, 160000, 1500, 19000), |
| (3680, 4, 2, 240000, 2790, 20000), |
| (1660, 3, 1, 87000, 1030, 17500), |
| (1620, 3, 2, 118600, 1250, 20000), |
| (3100, 3, 2, 140000, 1760, 38000), |
| (2070, 2, 3, 148000, 1550, 14000), |
| ( 650, 3, 1.5, 65000, 1450, 12000); |
| |
| drop table if exists result_lin_houses_spcl; |
| drop table if exists result_lin_houses_spcl_summary; |
| PERFORM linregr_train('houses_spcl', 'result_lin_houses_spcl', '"pr''ice"', |
| 'array[1, "ta,x", "ba$th", size]', |
| '"bed,room"', True); |
| PERFORM assert( |
| relative_error(coef, array[-84242.0345, 55.4430, -78966.9754, 225.6119]) < 1e-2 and |
| relative_error(r2, 0.9688) < 1e-2 and |
| relative_error(std_err, array[35019.00, 19.57, 23036.81, 49.04]) < 1e-2 and |
| relative_error(t_stats, array[-2.406, 2.833, -3.428, 4.600]) < 1e-2 and |
| relative_error(p_values, array[0.251, 0.216, 0.181, 0.136]) < 1e-2 and |
| relative_error(condition_no, 10086.1) < 1e-2 and |
| relative_error(bp_stats, 2.5451) < 1e-2 and |
| relative_error(bp_p_value, 0.467192) < 1e-2, |
| 'Linear regression with heteroskedasticity & grouping (houses): Wrong results' |
| ) |
| from result_lin_houses_spcl |
| where result_lin_houses_spcl."bed,room" = 2; |
| |
| |
| end IF; |
| |
| end; |
| $$ LANGUAGE plpgsql; |
| |
| |
| select linereg_expr_test(); |
| |
| |
| |
| ------------------------------------------------------------------------ |
| |
| -- Test Case for : when grouping_cols is NULL and the heteroskedasticity_option is True |
| |
| |
| |
| |
| drop table if exists result_lin_houses; |
| drop table if exists result_lin_houses_summary; |
| select linregr_train('houses', 'result_lin_houses', 'price', |
| 'array[1, tax, bath, size]', |
| NULL, True); |
| |
| select assert( |
| relative_error(coef, array[-12849.4168959872,28.9613922651765,10181.6290712648,50.516894915354]) < 1e-2 and |
| relative_error(r2, 0.768577580597443) < 1e-2 and |
| relative_error(std_err, array[33453.0344331391,15.8992104963997,19437.7710925923,32.928023174087]) < 1e-2 and |
| relative_error(t_stats, array[-0.38410317968819,1.82156166004184,0.523806408809133,1.53416118083605]) < 1e-2 and |
| relative_error(p_values, array[0.708223134615422,0.0958005827189772,0.610804093526536,0.153235085548186]) < 1e-2 and |
| relative_error(condition_no, 9002.50) < 1e-2 and |
| relative_error(bp_stats, 1.22605243985138) < 1e-2 and |
| relative_error(bp_p_value, 0.746762880478034) < 1e-2, |
| 'Linear regression with heteroskedasticity with no grouping: Wrong results' |
| ) |
| from result_lin_houses; |
| |
