337
edits
Changes
Jump to navigation
Jump to search
→Sample call of an aggregate function
== Perhaps a Simpler Alternative ==
Aggregate functions in PostgreSQL:
https://www.postgresql.org/docs/9.0/static/functions-aggregate.html
=== Aggregate Functions ===
Table 9-44. Aggregate Functions for Statistics
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
covar_samp(Y, X) | double precision | double precision | sample covariance
regr_avgx(Y, X) | double precision | double precision | average of the independent variable (sum(X)/N)
regr_avgy(Y, X) | double precision | double precision | average of the dependent variable (sum(Y)/N)
regr_count(Y, X) | double precision | bigint | number of input rows in which both expressions are nonnull
regr_intercept(Y, X) | double precision | double precision | y-intercept 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_slope(Y, X) | double precision | double precision | slope of the least-squares-fit linear equation determined by the (X, Y) pairs
regr_sxx(Y, X) | double precision | double precision | sum(X^2) - sum(X)^2/N ("sum of squares" of the independent 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)
regr_syy(Y, X) | double precision | double precision |sum(Y^2) - sum(Y)^2/N ("sum of squares" of the 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
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_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
variance(expression) | smallint, int, bigint, real, double precision, or numeric| double precision for floating-point arguments, otherwise numeric| historical alias for var_samp
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)
=== Sample call of an aggregate function ===
SELECT regr_slope(tothullcount, avghullcount) FROM hcllayerwinv;
==== Output ====
regr_slope
------------------
2.31850202053387
(1 row)
==Joe Conway's Documentation on pl/r==
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 ===
==== Creating the Sample Function ====
Inside a database (ex) tigertest):
return(arg2)
’ LANGUAGE ’plr’ STRICT;
==== Calling the Sample Function ====
SELECT * FROM r_max(2,3);
returns
r_max
-------
3
(1 row)
=== 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 r_reg(tothullcount, avghullcount) from hcllayerwinv;
Results:
r_reg
-------
0
0
3
0
5
4
0
0
5
4
0
0
6
4
4
0
0
4
0
0
4
0
0
4
0
0
3
0
5
3
0
0
7
5
3
3
0
0
6
4
0
0
0
5
4
0
0
7
== hcllayerwinv table from tigertest database ==
Table "public.hcllayerwinv"
Column | Type | Collation | Nullable | Default
------------------+------------------------+-----------+----------+---------
place | character varying(100) | | |
statecode | character varying(2) | | |
year | integer | | |
layer | integer | | |
lcount | numeric | | |
nosingleton | bigint | | |
nomultiton | bigint | | |
nopair | bigint | | |
nohull | bigint | | |
totmultitoncount | numeric | | |
avgmultitoncount | numeric | | |
totpaircount | numeric | | |
avgpaircount | numeric | | |
totpairlength | double precision | | |
avgpairlength | double precision | | |
minpairlength | double precision | | |
maxpairlength | double precision | | |
tothullcount | numeric | | |
avghullcount | numeric | | |
tothullarea | double precision | | |
avghullarea | double precision | | |
minhullarea | double precision | | |
maxhullarea | double precision | | |
tothulldensity | double precision | | |
avghulldensity | double precision | | |
seedearlyinvf | double precision | | |
inflator | numeric | | |
seedearlyinvl16f | double precision | | |
=== Regress by===
--By: place, statecode, layer,
--Regress: seedearlyinvl16f ON nosingleton, totmultitoncount, totpaircount, tothullcount, avgpairlength, avghullarea, avghulldensity
==Installing PL/R on the dbase server==
