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 `array`type.	`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 of`anyelement`, 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_coef`calculates the regression coefficients. The size of the return array for`mregr_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_r2`calculates 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_pvalues`calculates 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_tstats`calculates 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.