Shulou(Shulou.com)06/01 Report--

There are always some things that need to be persisted.

Today, let's review the statistics of the same data we used before.

First of all, create a simple test table, here the process will be skipped, directly on the table (really simple, …)

The first way to write row_number ():

SELECT val,COUNT (*) FROM (SELECT ID,val,row_number () OVER (ORDER BY ID)-row_number () OVER (PARTITION BY val ORDER BY ID) xFROM LCY) GROUP BY val,x ORDER BY MIN (ID)

The second rank ():

SELECT val,COUNT (*) FROM (SELECT ID,val,rank () OVER (ORDER BY ID)-rank () OVER (PARTITION BY val ORDER BY ID) xFROM LCY) GROUP BY val,x ORDER BY MIN (ID)

The third dense_rank ():

SELECT val,COUNT (*) FROM (SELECT ID,val,dense_rank () OVER (ORDER BY ID)-dense_rank () OVER (PARTITION BY val ORDER BY ID) xFROM LCY) GROUP BY val,x ORDER BY MIN (ID)

Results:

The usage of the three here is just the same, but there are differences in other needs, such as performance rankings.

The knowledge points involved in this chapter are analysis function, aggregate function, as well as data analysis function and statistical summation function.

A brief introduction:

Aggregate function

SUM: this function calculates the cumulative sum of expressions in a group MIN: find the minimum value of an expression in the data window in a group MAX: find the maximum value of the expression in the data window in a group AVG: used to calculate the average of expressions in a group and in the data window. COUNT: a cumulative count of what happens in a group

Analysis function

RANK: calculates their relative position to other rows from each row returned by the query based on the value of the expression in the ORDER BY clause DENSE_RANK: each row returned from the query based on the value of the expression in the ORDER BY clause Calculate their relative position to other rows FIRST: fetch the row of the first value from the collection returned by DENSE_RANK LAST: fetch the row of the last value from the collection returned by DENSE_RANK first _ VALUE: return the first value of the data window in the group LAST_VALUE: return the last value of the data window in the group. LAG: other rows in the result set can be accessed without self-joining LEAD: LEAD, in contrast to LAG, LEAD can access the row after the current row in the group row _ NUMBER: returns the offset of one row in the ordered group, which can be used for row numbers sorted by specific criteria

Data analysis function

STDDEV: calculates the standard deviation of the current line with respect to the group STDDEV_POP: this function calculates the population standard deviation and returns the square root of the population variable VARIANCE _ SAMP: this function calculates the cumulative sample standard deviation and returns the square root of the population variable VAR_SAMP _ POP: this function returns the population variable of the non-empty set (ignoring null) VARIANCE: if the number of rows in the expression is 1 Returns 0 if the number of lines in the expression is greater than 1 Returns VAR_SAMPCOVAR_POP: returns the total covariance of a pair of expressions COVAR_SAMP: returns the sample covariance of a pair of expressions CORR: returns the correlation coefficient of a pair of expressions CUME_DIST: calculates the relative position of a row in a group NTILE: a hash representation of an expression PERCENT_RANK: and CUME_DIST (cumulative allocation) function similar to PERCENTILE_DISC: returns a distribution hundred with input PERCENTILE_CONT: returns a data value corresponding to the input distribution percentage value RATIO_TO_REPORT: this function calculates the value of expression/ (sum (expression)) It gives the percentage of REGR _ (Linear Regression) Functions relative to the total: these linear regression functions are suitable for the least square regression line, and there are 9 different regression functions available.

Statistical summation function

CUBE: data statistics are carried out according to the CUBE method of OLAP, that is, statistics are required for all dimensions.

The next article describes the differences between the three, as well as other commonly used analysis functions and aggregate functions.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.