Skip to main content

Oracle Analytic Functions

AVG(expression) : Returns average value of expression

CORR(expression1, expression2) : Returns the coefficient of correlation of a set of number pairs

COVAR_POP(expression1, expression2) : Returns the population covariance of a set of number pairs

COVAR_SAMP(expression1, expression2) : Returns the sample covariance of a set of number pairs

COUNT(expression) : Returns the number of rows returned by the query

CUME_DIST(expression1, ...) : Calculates the cumulative distribution of a value in a group of values

DENSE_RANK() : Computes the rank of a row in an ordered group of rows and returns the rank as a number

FIRST_VALUE(expression) : Returns the first value in an ordered set of values

LAG(expression, offset, ...) : Provides access to a row at a given physical offset prior to cursor position

LAST_VALUE(expression) : Returns the last value in an ordered set of values

LEAD(expression, offset, ...) : Provides access to a row at a given physical offset beyond cursor position

MAX(expression) : Returns maximum value of expression

MIN(expression) : Returns minimum value of expression

NTILE(expression) : Divides an ordered data set into a number of buckets indicated by expression and assigns the appropriate bucket number to each row

PERCENT_RANK(expression1, ...) : For a row r, PERCENT_RANK calculates the rank of r minus 1, divided by 1 less than the number of rows being evaluated

PERCENTILE_CONT(expression) : Takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value

PERCENTILE_DISC(expression) : Takes a percentile value and a sort specification, and returns an element from the set

RANK(expression1, ...) : Computes the rank of each row returned from a query with respect to the other rows returned by the query

RATIO_TO_REPORT(expression) : Computes the ratio of a value to the sum of a set of values

REGR_SLOPE(expression1, expression2) : Returns the slope of the line

REGR_INTERCEPT(expression1, expression2) : Returns the y-intercept of the regression line

REGR_COUNT(expression1, expression2) : Returns an integer that is the number of non-null number pairs used to fit the regression line

REGR_R2(expression1, expression2) : Returns the coefficient of determination (also called R-squared or goodness of fit) for the regression

REGR_AVGX(expression1, expression2) : Evaluates the average of the independent variable (expression2) of the regression line

REGR_AVGY(expression1, expression2) : Evaluates the average of the independent variable (expression1) of the regression line

ROW_NUMBER() : Assigns a unique number to each row to which it is applied

STDDEV(expression) : Returns the sample standart deviation of expression, a set of numbers

STDDEV_POP(expression) : Computes the population standart deviation and returns the square root of the population variance

STDDEV_SAMP(expression) : Computes the cumulative sample standart deviation and returns the square root of the sample variance

SUM(expression) : Returns the sum of values of expression

VAR_POP(expression) : Returns the population variance of a set of numbers

VAR_SAMP(expression) : Returns the sample variance of a set of numbers

Comments

Popular posts from this blog

Creating Multiple VLANs over Bonding Interfaces with Proper Routing on a Centos Linux Host

In this post, I am going to explain configuring multiple VLANs on a bond interface. First and foremost, I would like to describe the environment and give details of the infrastructure. The server has 4 Ethernet links to a layer 3 switch with names: enp3s0f0, enp3s0f1, enp4s0f0, enp4s0f1 There are two bond interfaces both configured as active-backup bond0, bond1 enp4s0f0 and enp4s0f1 interfaces are bonded as bond0. Bond0 is for making ssh connections and management only so corresponding switch ports are not configured in trunk mode. enp3s0f0 and enp3s0f1 interfaces are bonded as bond1. Bond1 is for data and corresponding switch ports are configured in trunk mode. Bond0 is the default gateway for the server and has IP address 10.1.10.11 Bond1 has three subinterfaces with VLAN 4, 36, 41. IP addresses are 10.1.3.11, 10.1.35.11, 10.1.40.11 respectively. Proper communication with other servers on the network we should use routing tables. There are three

Listing Zimbra Accounts with Some Details

Recently I have to export the user list for a particular domain. Luckily Zimbra has Admin GUI with a search feature. When you search accounts, you can download search results as a comma-separated csv file. So I did a search and download the result file, but the result did not have all the columns I need and also there is no option for customizing columns for search results. So I had to write a bash script to get the desired list. Here is the bash script ( It can be customized by adding or removing field names. Run it under zimbra user like ./zimbra_account_list.sh <domain_name_here> ):