DEV Community

AntDB
AntDB

Posted on

AntDB-Oracle Compatibility Developer’s Manual P3–52

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)