Linear regression | Documentation | MentDB Weak
Goal
  • Simple linear regression is useful for finding relationship between two continuous variables.
  • We are going to give to a linear regression algorithm data from a SQL database,
    and then we are going to make predictions.
  • Reminder, to execute an order, you can click on or or [CTRL]+E or [Apple]+E on OSX.

Let's start by creating a table and adding data to it.
  • Here some data:
    json load "data" "[]";
    json load "row" "[]";json iarray "row" / 1 NUM;json iarray "row" / 1 NUM;json iarray "data" / (json doc "row") ARRAY;
    json load "row" "[]";json iarray "row" / 3 NUM;json iarray "row" / 2 NUM;json iarray "data" / (json doc "row") ARRAY;
    json load "row" "[]";json iarray "row" / 5 NUM;json iarray "row" / 3 NUM;json iarray "data" / (json doc "row") ARRAY;
    json load "row" "[]";json iarray "row" / 7 NUM;json iarray "row" / 4 NUM;json iarray "data" / (json doc "row") ARRAY;
    json load "row" "[]";json iarray "row" / 9 NUM;json iarray "row" / 7 NUM;json iarray "data" / (json doc "row") ARRAY;
    json load "row" "[]";json iarray "row" / 11 NUM;json iarray "row" / 8 NUM;json iarray "data" / (json doc "row") ARRAY;
    json load "row" "[]";json iarray "row" / 13 NUM;json iarray "row" / 9 NUM;json iarray "data" / (json doc "row") ARRAY;
    json load "row" "[]";json iarray "row" / 15 NUM;json iarray "row" / 10 NUM;json iarray "data" / (json doc "row") ARRAY;
    json doc "data";
    
  • Result:
    "[
      [
        1.0,
        1.0
      ],
      [
        3.0,
        2.0
      ],
      [
        5.0,
        3.0
      ],
      [
        7.0,
        4.0
      ],
      [
        9.0,
        7.0
      ],
      [
        11.0,
        8.0
      ],
      [
        13.0,
        9.0
      ],
      [
        15.0,
        10.0
      ]
    ]"
    ;
  • Create the table 'test_country':
    sql connect "session1" {cm get "MENTDB";};
    sql dml "session1" (concat "CREATE TABLE `test_country` (
      `id` BIGINT(11) NOT NULL AUTO_INCREMENT,
      `x` FLOAT NOT NULL,
      `y` FLOAT NOT NULL,
      PRIMARY KEY (`id`));
    ");
    sql disconnect "session1";
    
  • The table has been created.
  • Adding data into this table:
  • Go to the 'Connect' section
  • Double click on 'MENTDB' sql connection...
  • All tables are displayed.
  • Click right on 'test_country' and select 'GEN WS > INSERT'.
  • This MQL source code are generated:
    script create post "MENTDB.test_country.insert" false 1
      (param
      	(var "[id]" {true} "description ..." is_null:false is_empty:false "example ...")
      	(var "[x]" {true} "description ..." is_null:false is_empty:false "example ...")
      	(var "[y]" {true} "description ..." is_null:false is_empty:false "example ...")
      )
      "Insert a new element into the table 'test_country'."
    {
    	
    	try {
    		
    		#Connection ...;
    		sql connect "session1" {cm get "MENTDB"};
    		
    		-> "[result]" (sql dml "session1" (concat 
    			"INSERT INTO public.test_country (
    				id,
    				x,
    				y
    			) VALUES (
    				" (sql encode [id]) " ,
    				" (sql encode [x]) " ,
    				" (sql encode [y]) "
    			);"
    		));
    		
    		#Disconnection ...;
    		sql disconnect "session1";
    		
    		# Return the json;
    		[result]
    		
    	} {
    
    		#Close the connection;
    		try {sql disconnect "session1"} {} "[sub_err]";
    
    		#Generate an error;
    		exception (1) ([err]);
    		
    	} "[err]";
    	
    } "Return the number of impacted lines.";
    
  • Copy this code...:
    #Connection ...;
    sql connect "session1" {cm get "MENTDB"};
    
    -> "[result]" (sql dml "session1" (concat 
    	"INSERT INTO public.test_country (
    		id,
    		x,
    		y
    	) VALUES (
    		" (sql encode [id]) " ,
    		" (sql encode [x]) " ,
    		" (sql encode [y]) "
    	);"
    ));
    
    #Disconnection ...;
    sql disconnect "session1";
    
  • And update it like this :
    #Connection ...;
    sql connect "session1" {cm get "MENTDB"};
    
    json parse_array "data" "/" "row" {
    
    	sql dml "session1" (concat 
    		"INSERT INTO public.test_country (
    			x,
    			y
    		) VALUES (
    			" (sql encode (json select "row" "/[0]")) " ,
    			" (sql encode (json select "row" "/[1]")) "
    		);"
    	);
    
    };
    
    #Disconnection ...;
    sql disconnect "session1";
    
  • Execute the MQL script. Data are inserted into the table.
  • Double click on the table 'test_country'
  • All data into the table 'test_country' are displayed.

Loading data into the algorithm.
  • Loading data into the algorithm
    pa rl load "reg1" "MENTDB" "x" "y" "select x, y from test_country";
    
  • Data are loaded.

Make predictions.
  • To see how much data is saved:
    pa rl count "reg1";
    
  • Result:
    8;
    
  • To get R:
    pa rl r "reg1";
    
  • Result:
    0.9883173560569456;
    
  • Make prediction with data from 1 to 15 (increment 0.1):
    json load "new_data" "[]";
    for (-> "[x]" 1) (< [x] 15) (-> "[x]" (+ [x] 0.1)) {
    
    	json load "new_row" "[]";
    	json iarray "new_row" / [x] NUM;
    	json iarray "new_row" / (pa rl predict "reg1" [x]) NUM;
    	json iarray "new_data" / (json doc "new_row") ARRAY;
    
    };
    pa xy_scatter (json doc "new_data") "X, Y";
    
  • Here the line:
  • Make simple prediction:
    pa rl predict "reg1" 25;
    
  • Result:
    17.23809523809524;
    
  • Now you can do linear regression by select from your SQL database.


MentDB © 2012 - 2020 - Legal Notice