통계용 함수를 MySQL의 Plugin 형식으로 개발
1. MySQL Plugin용 Math UDF -- -- 01. Install -- ( use root user ) git clone http://github.com/megastep/mysql-udf.git if os = ubuntu then apt install gcc gcc-c++ make libmariadbclient-dev else if os = centos yum install gcc gcc-c++ make MariaDB-devel-xx.xx end if vi Makefile /* MySQL Header File 위치 수정 */ INCL = -l/usr/include/mysql /* Build */ make cp udf_math.so /usr/lib64/mysql/plugin /* function import */ mysql -uroot -p -A mysql -e "source import.sql" -- -- 02. Check -- select * from mysql.func; name ret dl type ----------------- ------ ----------- ----------- colwidth 2 udf_math.so aggregate confidence_higher 1 udf_math.so function confidence_lower 1 udf_math.so function correlation 1 udf_math.so aggregate faculty 1 udf_math.so function geomean 1 udf_math.so aggregate intercept 1 udf_math.so aggregate kurtosis 1 udf_math.so aggregate longest 0 udf_math.so aggregate median 1 udf_math.so aggregate noverm 1 udf_math.so function skewness 1 udf_math.so aggregate slope 1 udf_math.so aggregate stdnorm_density 1 udf_math.so function stdnorm_dist 1 udf_math.so function weightedavg 1 udf_math.so aggregate steyx 1 udf_math.so aggregate
Function Spec
UDF MEDIAN()
Function name median
Purpose calculate the median of a column
Aggregate function Yes
Input parameter(s) 1 (column: REAL)
Conditions -
Output value(s) 1 per group (REAL)
Examples SELECT MEDIAN(age) FROM bar
SELECT gender, MEDIAN(age),AVG(age) FROM bar GROUP BY gender
Registration CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';
UDF CORRELATION()
Function name correlation
Purpose calculate the correlation coefficient of the linear regression of two sets of variables
Aggregate function Yes
Input parameter(s) 1 (dependent variable: REAL,
independent variable: REAL)
Conditions -
Output value(s) 1 per group (REAL)
Examples SELECT correlation(income,age) FROM customers
Registration CREATE AGGREGATE FUNCTION correlation RETURNS REAL SONAME 'udf_correlation.so';
UDF INTERCEPT()
Function name intercept
Purpose calculate the intercept of the linear regression of two sets of variables
Aggregate function Yes
Input parameter(s) 2 (dependent variable: REAL,
independent variable: REAL)
Conditions -
Output value(s) 1 per group (REAL)
Examples SELECT intercept(income,age) FROM customers
Registration CREATE AGGREGATE FUNCTION intercept RETURNS REAL SONAME 'udf_intercept.so';
UDF SLOPE()
Function name slope
Purpose calculate the slope of the linear regression of two sets of variables
Aggregate function Yes
Input parameter(s) 2 (dependent variable: REAL,
independent variable: REAL)
Conditions -
Output value(s) 1 per group (REAL)
Examples SELECT slope(income,age) FROM customers
Registration CREATE AGGREGATE FUNCTION slope RETURNS REAL SONAME 'udf_slope.so';
UDF SKEWNESS()
Function name skewness
Purpose calculate the skewness of a distribution of values
Aggregate function Yes
Input parameter(s) 1 (data: REAL)
Conditions -
Output value(s) 1 per group (REAL)
Examples SELECT skewness(age) FROM customers
Registration CREATE AGGREGATE FUNCTION skewness RETURNS REAL SONAME 'udf_skewness.so';
UDF KURTOSIS()
Function name kurtosis
Purpose calculate the kurtosis of a distribution of values
Aggregate function Yes
Input parameter(s) 1 (data: REAL)
Conditions -
Output value(s) 1 per group (REAL)
Examples SELECT kurtosis(age) FROM customers
Registration CREATE AGGREGATE FUNCTION kurtosis RETURNS REAL SONAME 'udf_kurtosis.so';
UDF CONFIDENCE_HIGHER()
Function name confidence_higher
Purpose calculate the upper bound of the confidence interval for a given standard deviation, sample size, mean and confidence probabilty
Aggregate function No
Input parameter(s) 4 or 5 (confidence probability p: INT or REAL,
sample size n: INT,
arithmetic mean my: INT or REAL,
standard deviation s: INT or REAL,
number of decimals: INT, optional)
Conditions -
Output value(s) 1 per row (REAL)
Examples SELECT confidence_higher(0.95,2000,550,60,4)
Registration CREATE FUNCTION confidence_higher RETURNS REAL SONAME 'udf_confidence_higher.so';
UDF CONFIDENCE_LOWER()
Function name confidence_lower
Purpose calculate the lower bound of the confidence interval for a given standard deviation, sample size, mean and confidence probabilty
Aggregate function No
Input parameter(s) 4 or 5 (confidence probability p: INT or REAL,
sample size n: INT,
arithmetic mean my: INT or REAL,
standard deviation s: INT or REAL,
number of decimals: INT, optional)
Conditions -
Output value(s) 1 per row (REAL)
Examples SELECT confidence_lower(0.95,2000,550,60,4)
Registration CREATE FUNCTION confidence_lower RETURNS REAL SONAME 'udf_confidence_lower.so';
UDF STDNORM_DENSITY()
Function name stdnorm_density
Purpose calculate the value of the density function of the standard normal distribution for a given point
Aggregate function No
Input parameter(s) 1 or 2 ( point p: INT or REAL,
number of decimals: INT, optional)
Conditions -
Output value(s) 1 per row (REAL)
Examples SELECT stdnorm_density(0,4)
Registration CREATE FUNCTION stdnorm_density RETURNS REAL SONAME 'udf_stdnorm_density.so';
UDF STDNORM_DIST()
Function name stdnorm_dist
Purpose calculate the value of the distribution function of the standard normal distribution for a given point
Aggregate function No
Input parameter(s) 1 or 2 ( point p: INT or REAL,
number of decimals: INT, optional)
Conditions -
Output value(s) 1 per row (REAL)
Examples SELECT stdnorm_dist(0,4)
Registration CREATE FUNCTION stdnorm_dist RETURNS REAL SONAME 'udf_stdnorm_dist.so';
UDF GEOMEAN()
Function name geomean
Purpose calculate the geometric mean of a column
Aggregate function Yes
Input parameter(s) 1 (data: REAL)
Conditions -
Output value(s) 1 per group (REAL)
Examples SELECT geomean(price) FROM products
Registration CREATE AGGREGATE FUNCTION geomean RETURNS REAL SONAME 'udf_geomean.so';
UDF WEIGHTEDAVG()
Function name weightedavg
Purpose calculate the weighted average of a values
Aggregate function Yes
Input parameter(s) 2 (data: REAL)
(weight: REAL)
Conditions -
Output value(s) 1 per group (REAL)
Examples SELECT weightedavg(voting,categoryweight) FROM votes
Registration CREATE AGGREGATE FUNCTION weightedavg RETURNS REAL SONAME 'udf_weightedavg.so';
UDF NOVERM()
Function name noverm
Purpose calculate the number of possible combinations of size m from a set of n total values
(without regarding the order of values in combinations)
Aggregate function No
Input parameter(s) 2 (n: INTEGER,
m: INTEGER)
Conditions n must be greater than 1, m must be between 1 and m
Output value(s) 1 (REAL)
Examples SELECT noverm(10,2)
SELECT noverm(foo,bar) FROM baz
Registration CREATE FUNCTION noverm RETURNS INTEGER SONAME 'udf_noverm.so';
UDF FACULTY()
Function name faculty
Purpose calculate the faculty (n!) of a number (1*2*3*4*5*6...*n)
Aggregate function No
Input parameter(s) 1 (n: INTEGER)
Conditions n must be greater than 0
Output value(s) 1 (REAL)
Examples SELECT faculty(20)
SELECT faculty(foo) FROM bar
Registration CREATE FUNCTION faculty RETURNS REAL SONAME 'udf_faculty.so';
UDF COLWIDTH()
Function name colwidth
Purpose calculate the length of the longest value in a STRING column
Aggregate function Yes
Input parameter(s) 1 (column: STRING)
Conditions -
Output value(s) 1 per group (INTEGER)
Examples SELECT COLWIDTH(request_vars) FROM request_log
SELECT session.id,COLWIDTH(session_vars.value) FROM session,session_vars
WHERE session.id=session_vars.session_id
GROUP BY session_id
Registration CREATE AGGREGATE FUNCTION COLWIDTH RETURNS INTEGER SONAME 'udf_colwidth.so';
UDF LONGEST()
Function name longest
Purpose get the longest value in a STRING column
Aggregate function Yes
Input parameter(s) 1 (column: STRING)
Conditions -
Output value(s) 1 per group (STRING)
Examples SELECT LONGEST(name) FROM users
SELECT LONGEST(request_vars) FROM request_log GROUP BY script_name
Registration CREATE AGGREGATE FUNCTION LONGEST RETURNS STRING SONAME 'udf_longest.so';
댓글 없음:
댓글 쓰기