Using R in PostgreSQL - Revision history

JeeminS: /* Sample call of an aggregate function */

2018-03-20T17:26:26Z

Sample call of an aggregate function

JeeminS: /* Perhaps a Simpler Alternative */

2018-03-20T17:24:44Z

Perhaps a Simpler Alternative

JeeminS: /* Regression */

2018-03-20T17:22:28Z

Regression

JeeminS: /* Attempts to call R built-in function from PSQL */

2018-03-20T17:18:09Z

Attempts to call R built-in function from PSQL

JeeminS: /* Joe Conway's Documentation on pl/r */

2018-03-20T17:14:29Z

Joe Conway's Documentation on pl/r

JeeminS: /* Aggregate Functions */

2018-03-06T19:25:48Z

Aggregate Functions

JeeminS: /* Perhaps a Simpler Alternative */

2018-03-06T19:15:01Z

Perhaps a Simpler Alternative

JeeminS at 18:25, 2 March 2018

2018-03-02T18:25:28Z

JeeminS: /* Joe Conway's Documentation on pl/r */

2018-03-01T20:01:13Z

Joe Conway's Documentation on pl/r

JeeminS: /* hcllayerwinv table from tigertest database */

2018-02-27T18:53:02Z

hcllayerwinv table from tigertest database

@@ Line 31: / Line 31: @@
   SELECT regr_slope(tothullcount, avghullcount) FROM hcllayerwinv;
      regr_slope
   ------------------

@@ Line 27: / Line 27: @@
   var_pop(expression)	| smallint, int, bigint, real, double precision, or numeric| double precision for floating-point arguments, otherwise numeric|	population variance of the input values (square of the population standard deviation)
   var_samp(expression)	| smallint, int, bigint, real, double precision, or numeric| double precision for floating-point arguments, otherwise numeric|	sample variance of the input values (square of the sample standard deviation)
 ==Joe Conway's Documentation on pl/r==

@@ Line 71: / Line 71: @@
 Calling Function:
-  SELECT *
+  SELECT r_reg(tothullcount, avghullcount) from hcllayerwinv;
 == hcllayerwinv table from tigertest database ==

← Older revision		Revision as of 17:18, 20 March 2018
Line 58:		Line 58:

	=== Attempts to call R built-in function from PSQL ===		=== Attempts to call R built-in function from PSQL ===
		+
		+	==== Regression ====
		+
		+	Creating Function:
		+
		+	CREATE OR REPLACE FUNCTION r_reg(y numeric[], x numeric[])
		+	RETURNS numeric AS $$
		+	res <- lm(y ~ x)
		+	return(coef(res)[1])
		+	$$ LANGUAGE 'plr' immutable;
		+
		+	Calling Function:
		+
		+	SELECT *

	== hcllayerwinv table from tigertest database ==		== hcllayerwinv table from tigertest database ==

← Older revision		Revision as of 17:14, 20 March 2018
Line 56:		Line 56:
	3		3
	(1 row)		(1 row)
		+
		+	=== Attempts to call R built-in function from PSQL ===

	== hcllayerwinv table from tigertest database ==		== hcllayerwinv table from tigertest database ==

@@ Line 6: / Line 6: @@
 Table 9-44. Aggregate Functions for Statistics
-−
-Function	Argument Type	Return Type	Description
+       Function	        |                    Argument Type	                   |                       Return Type	                             |                                        Description
-corr(Y, X)	double precision	double precision	correlation coefficient
+ =======================+==========================================================+=================================================================+=============================================================================================
-covar_pop(Y, X)	double precision	double precision	population covariance
+ corr(Y, X)	        | double precision                                         | double precision 	                                             |  correlation coefficient
-covar_samp(Y, X)	double precision	double precision	sample covariance
+ covar_pop(Y, X)        | double precision                                         | double precision 	                                             |  population covariance
-regr_avgx(Y, X)	double precision	double precision	average of the independent variable (sum(X)/N)
+ covar_samp(Y, X)       | double precision                                         | double precision		                                     |   sample covariance
-regr_avgy(Y, X)	double precision	double precision	average of the dependent variable (sum(Y)/N)
+ regr_avgx(Y, X)        | double precision                                         | double precision  	                                             | average of the independent variable (sum(X)/N)
-regr_count(Y, X)	double precision	bigint	number of input rows in which both expressions are nonnull
+ regr_avgy(Y, X)        | double precision                                         | double precision		                                     |  average of the dependent variable (sum(Y)/N)
-regr_intercept(Y, X)	double precision	double precision	y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs
+ regr_count(Y, X)       | double precision                                         | bigint		                                             | number of input rows in which both expressions are nonnull
-regr_r2(Y, X)	double precision	double precision	square of the correlation coefficient
+ regr_intercept(Y, X)   | double precision                                         | double precision	                                             | y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs
-regr_slope(Y, X)	double precision	double precision	slope of the least-squares-fit linear equation determined by the (X, Y) pairs
+ regr_r2(Y, X)	        | double precision                                         | double precision	                                             | square of the correlation coefficient
-regr_sxx(Y, X)	double precision	double precision	sum(X^2) - sum(X)^2/N ("sum of squares" of the independent variable)
+ regr_slope(Y, X)       | double precision                                         | double precision		                                     | slope of the least-squares-fit linear equation determined by the (X, Y) pairs
-regr_sxy(Y, X)	double precision	double precision	sum(X*Y) - sum(X) * sum(Y)/N ("sum of products" of independent times dependent variable)
+ regr_sxx(Y, X)	        | double precision                                         | double precision		                                     |	sum(X^2) - sum(X)^2/N ("sum of squares" of the independent variable)
-regr_syy(Y, X)	double precision	double precision	sum(Y^2) - sum(Y)^2/N ("sum of squares" of the dependent variable)
+ regr_sxy(Y, X)	        | double precision                                         | double precision		                                     |	sum(X*Y) - sum(X) * sum(Y)/N ("sum of products" of independent times dependent variable)
-stddev(expression)	smallint, int, bigint, real, double precision, or numeric	double precision for floating-point arguments, otherwise numeric	historical alias for stddev_samp
+ regr_syy(Y, X)	        | double precision                                         | double precision	                                             |sum(Y^2) - sum(Y)^2/N ("sum of squares" of the dependent variable)
-stddev_pop(expression)	smallint, int, bigint, real, double precision, or numeric	double precision for floating-point arguments, otherwise numeric	population standard deviation of the input values
+ stddev(expression)     | smallint, int, bigint, real, double precision, or numeric| double precision for floating-point arguments, otherwise numeric|	historical alias for stddev_samp
-stddev_samp(expression)	smallint, int, bigint, real, double precision, or numeric	double precision for floating-point arguments, otherwise numeric	sample standard deviation of the input values
+ stddev_pop(expression)	| smallint, int, bigint, real, double precision, or numeric| double precision for floating-point arguments, otherwise numeric|	population standard deviation of the input values
-variance(expression)	smallint, int, bigint, real, double precision, or numeric	double precision for floating-point arguments, otherwise numeric	historical alias for var_samp
+ stddev_samp(expression)| smallint, int, bigint, real, double precision, or numeric| double precision for floating-point arguments, otherwise numeric|	sample standard deviation of the input values
-var_pop(expression)	smallint, int, bigint, real, double precision, or numeric	double precision for floating-point arguments, otherwise numeric	population variance of the input values (square of the population standard deviation)
+ variance(expression)   | smallint, int, bigint, real, double precision, or numeric| double precision for floating-point arguments, otherwise numeric|	historical alias for var_samp
-var_samp(expression)	smallint, int, bigint, real, double precision, or numeric	double precision for floating-point arguments, otherwise numeric	sample variance of the input values (square of the sample standard deviation)
+ var_pop(expression)	| smallint, int, bigint, real, double precision, or numeric| double precision for floating-point arguments, otherwise numeric|	population variance of the input values (square of the population standard deviation)
 ==Joe Conway's Documentation on pl/r==

@@ Line 2: / Line 2: @@
   Aggregate functions in PostgreSQL:
   https://www.postgresql.org/docs/9.0/static/functions-aggregate.html
 ==Joe Conway's Documentation on pl/r==

← Older revision		Revision as of 18:25, 2 March 2018
Line 1:		Line 1:
		+	== Perhaps a Simpler Alternative ==
		+	Aggregate functions in PostgreSQL:
		+	https://www.postgresql.org/docs/9.0/static/functions-aggregate.html
		+
	==Joe Conway's Documentation on pl/r==		==Joe Conway's Documentation on pl/r==

← Older revision		Revision as of 20:01, 1 March 2018
Line 2:		Line 2:

	http://joeconway.com/doc/plr-US.pdf		http://joeconway.com/doc/plr-US.pdf
		+	http://www.joeconway.com/plr/doc/plr-funcs.html
		+	http://www.joeconway.com/plr/doc/plr-window-funcs.html

	=== Sample Function Code in PostgreSQL ===		=== Sample Function Code in PostgreSQL ===

@@ Line 58: / Line 58: @@
    seedearlyinvf    | double precision       |           |          |
    inflator         | numeric                |           |          |
    seedearlyinvl16f | double precision       |           |          |
 ==Installing PL/R on the dbase server==