KEEP function description
keep is a special analysis function, his use is different from the analysis function specified by the over keyword, can be used for such an occasion: take the same group to a field sorted, the specified field to take the smallest or largest value of that.
Syntax:
min | max(column1) keep (dense_rank first | last order by column2)
[ over (partion by column3) ]
Example:
SELECT deptno, MIN(t.mgr) KEEP (DENSE_RANK FIRST ORDER BY t.sal) a
from emp t group by deptno;
Meaning: grouped by deptno, sorted by sal within the group, DENSE_RANK FIRST means to keep the sal ranked in front of a group of data (when the sal ranked in front has duplicate values, save multiple data). Then in each group of records, execute the previous aggregation function, here is min(t.mgr).
Execution results:
DEPTNO | A
--------+------
10 | 7782
30 | 7698
20 | 7902
(3 rows)
Aggregation functions with statistical use
| Name | Number of arguments | Function | For example: |
|---|---|---|---|
| COVAR_POP | 2 | COVAR_POP(expr1, expr2) returns the overall covariance of a pair of expressions | |
| COVAR_SAMP | 2 | COVAR_SAMP(expr1, expr2) returns the sample covariance of a pair of expressions | |
| CUME_DIST | 0~n | Calculate the cumulative distribution of a value in a set of values | CUME_DIST(15500, .05) WITHIN GROUP (ORDER BY salary, commission_pct) |
| RANK | 0~n | Determines the ranking of a set of values based on the ORDER BY expression in the OVER clause. When there are the same sorted values, there will be the same ranking and the number of rows with the same values will be recorded in the next ranking. | RANK(15500) WITHIN GROUP (ORDER BY salary DESC) |
| DENSE_RANK | 0~n | Calculates the sorted values in a set of values, sorted consecutively, with two second places still followed by the third place | DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary) |
| PERCENT_RANK | 0~n | Percentage ranking | PERCENT_RANK(15000, .05) WITHIN GROUP (ORDER BY salary |
| REGR_SLOPE | 2 | REGR_SLOPE: return slope, equal to COVAR_POP(expr1, expr2) / VAR_POP(expr2) | REGR_SLOPE(expr1, expr2) |
| REGR_INTERCEPT | 2 | REGR_INTERCEPT: returns the y-intercept of the regression line, equal to | REGR_INTERCEPT(expr1, expr2) |
| REGR_COUNT | 2 | Returns the number of non-empty pairs of numbers used to fill the regression line | REGR_COUNT(expr1, expr2) |
| REGR_R2 | 2 | Return the coefficient of determination of the regression line, calculated as If VAR_POP(expr2) = 0 then return NULL If VAR_POP(expr1) = 0 and VAR_POP(expr2) != 0 then return 1 If VAR_POP(expr1) > 0 and VAR_POP(expr2 != 0 then return POWER(CORR(expr1,expr),2) | REGR_R2(expr1, expr2) |
| REGR_AVGX | 2 | Calculating the mean of the independent variable (expr2) of the regression line, after removing the null pair (expr1, expr2), is equal to AVG(expr2) | REGR_AVGX(expr1, expr2) |
| REGR_AVGY | 2 | Calculate the mean of the strain variable (expr1) of the regression line, after removing the null pair (expr1, expr2), which is equal to AVG(expr1) | REGR_AVGY(expr1, expr2) |
| REGR_SXX | 2 | The return value is equal to REGR_COUNT(expr1, expr2) * VAR_POP(expr2) | REGR_SXX(expr1, expr2) |
| REGR_SYY | 2 | The return value is equal to REGR_COUNT(expr1, expr2) * VAR_POP(expr1) | REGR_SYY(expr1, expr2) |
| REGR_SXY | 2 | The return value is equal to REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) | REGR_SXY(expr1, expr2) |
| STDDEV | 1 | Calculate the standard deviation of the current row about the group | stddev(col) stddev(distinct col) stddev(all col) |
| STDDEV_POP | 1 | This function calculates the overall standard deviation and returns the square root of the overall variable | STDDEV_POP(salary) |
| STDDEV_SAMP | 1 | This function calculates the sample standard deviation and returns the square root of the overall variable | STDDEV_SAMP(salary) |
| VAR_POP | 1 | This function returns the overall variable of the non-empty set (ignoring null) | VAR_POP(salary) |
| VAR_SAMP | 1 | This function returns the sample variable of the non-empty set (ignoring null) | VAR_SAMP(salary) |
| VARIANCE | 1 | Returns 0 if the number of rows in the expression is 1, or VAR_SAMP if the number of rows in the expression is greater than 1 | variance(col) |
| CORR | 2 | Returns the correlation coefficient of a pair of expressions | CORR(list_price, min_price) |
Top comments (0)