greenplum 数组操作

参考：http://gpdb.docs.pivotal.io/4390/admin_guide/query/topics/functions-operators.html

Table 4. Advanced Analytic Functions

Function	Return Type	Full Syntax	Description
matrix_add(array[], array[])	smallint[], int[], bigint[], float[]	matrix_add( array[[1,1],[2,2]], array[[3,4],[5,6]])	Adds two two-dimensional matrices. The matrices must be conformable.
matrix_multiply( array[], array[])	smallint[]int[], bigint[], float[]	matrix_multiply( array[[2,0,0],[0,2,0],[0,0,2]], array[[3,0,3],[0,3,0],[0,0,3]] )	Multiplies two, three- dimensional arrays. The matrices must be conformable.
matrix_multiply( array[], expr)	int[], float[]	matrix_multiply( array[[1,1,1], [2,2,2], [3,3,3]], 2)	Multiplies a two-dimensional array and a scalar numeric value.
matrix_transpose( array[])	Same as input arraytype.	matrix_transpose( array [[1,1,1],[2,2,2]])	Transposes a two-dimensional array.
pinv(array [])	smallint[]int[], bigint[], float[]	pinv(array[[2.5,0,0],[0,1,0],[0,0,.5]])	Calculates the Moore-Penrose pseudoinverse of a matrix.
unnest (array[])	set of anyelement	unnest( array['one', 'row', 'per', 'item'])	Transforms a one dimensional array into rows. Returns a set ofanyelement, a polymorphic pseudotype in PostgreSQL.

Table 5. Advanced Aggregate Functions

Function	Return Type	Full Syntax	Description
MEDIAN (expr)	timestamp, timestampz, interval, float	MEDIAN (expression) Example: SELECT department_id, MEDIAN(salary) FROM employees GROUP BY department_id;	Can take a two-dimensional array as input. Treats such arrays as matrices.
PERCENTILE_CONT (expr) WITHIN GROUP (ORDER BYexpr [DESC/ASC])	timestamp, timestampz, interval, float	PERCENTILE_CONT(percentage) WITHIN GROUP (ORDER BY expression) Example: SELECT department_id, PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY salary DESC) "Median_cont"; FROM employees GROUP BY department_id;	Performs an inverse function that assumes a continuous distribution model. It takes a percentile value and a sort specification and returns the same datatype as the numeric datatype of the argument. This returned value is a computed result after performing linear interpolation. Null are ignored in this calculation.
PERCENTILE_DISC (expr) WITHIN GROUP (ORDER BYexpr [DESC/ASC])	timestamp, timestampz, interval, float	PERCENTILE_DISC(percentage) WITHIN GROUP (ORDER BY expression) Example: SELECT department_id, PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY salary DESC) "Median_desc"; FROM employees GROUP BY department_id;	Performs an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification. This returned value is an element from the set. Null are ignored in this calculation.
sum(array[])	smallint[]int[], bigint[], float[]	sum(array[[1,2],[3,4]]) Example: CREATE TABLE mymatrix (myvalue int[]); INSERT INTO mymatrix VALUES (array[[1,2],[3,4]]); INSERT INTO mymatrix VALUES (array[[0,1],[1,0]]); SELECT sum(myvalue) FROM mymatrix; sum --------------- {{1,3},{4,4}}	Performs matrix summation. Can take as input a two-dimensional array that is treated as a matrix.
pivot_sum (label[], label, expr)	int[], bigint[], float[]	pivot_sum( array['A1','A2'], attr, value)	A pivot aggregation using sum to resolve duplicate entries.
mregr_coef(expr, array[])	float[]	mregr_coef(y, array[1, x1, x2])	The four mregr_*aggregates perform linear regressions using the ordinary-least-squares method. mregr_coefcalculates the regression coefficients. The size of the return array formregr_coef is the same as the size of the input array of independent variables, since the return array contains the coefficient for each independent variable.
mregr_r2 (expr, array[])	float	mregr_r2(y, array[1, x1, x2])	The four mregr_*aggregates perform linear regressions using the ordinary-least-squares method. mregr_r2calculates the r-squared error value for the regression.
mregr_pvalues(expr, array[])	float[]	mregr_pvalues(y, array[1, x1, x2])	The four mregr_*aggregates perform linear regressions using the ordinary-least-squares method. mregr_pvaluescalculates the p-values for the regression.
mregr_tstats(expr, array[])	float[]	mregr_tstats(y, array[1, x1, x2])	The four mregr_*aggregates perform linear regressions using the ordinary-least-squares method. mregr_tstatscalculates the t-statistics for the regression.
nb_classify(text[], bigint, bigint[], bigint[])	text	nb_classify(classes, attr_count, class_count, class_total)	Classify rows using a Naive Bayes Classifier. This aggregate uses a baseline of training data to predict the classification of new rows and returns the class with the largest likelihood of appearing in the new rows.
nb_probabilities(text[], bigint, bigint[], bigint[])	text	nb_probabilities(classes, attr_count, class_count, class_total)	Determine probability for each class using a Naive Bayes Classifier. This aggregate uses a baseline of training data to predict the classification of new rows and returns the probabilities that each class will appear in new rows.

 

转载于:https://www.cnblogs.com/lvlin241/p/9378583.html