Vertica的剖析函数介绍(国外英文资料).doc

Vertica的分析函数介绍(国外英文资料)Introduction to the analysis function of VerticaThe analysis function, as an extension of SQL speech, has been incorporated into the SQL specification of the National Committee for standardization of the national organization for Standardization (SQL). So, the syntax and function names of the analysis functions supported by different DB vendors are basically the same. Save space, here only introduces vertica analysis function, grammar and function function, function application example.1. Analytic function syntaxANALYTIC_FUNCTION (argument-1,..., argument-n)OVER [[window_partition_clause][window_order_clause][window_frame_clause]The following is a short description of the functions use:SUMThis function computes the sum of the expressions in a groupMINFinding the minimum expression in the data window of a groupMAXFinding the maximum value of an expression in the data window of a groupAVGUsed to calculate the average value of an expression in a group and data window.COUNTA cumulative count of what happens within a setRANKAccording to the value of the expression in the ORDER BY clause, each row returned from the query calculates their relative positions to the other rowsDENSE_RANKAccording to the value of the expression in the ORDER BY clause, each row returned from the query calculates their relative positions to the other rowsFIRST_VALUEReturns the first value of the data window in the groupLAST_VALUEReturns the last value of the data window in the group.LAGYou can access other rows in the result set without self - connectionLEADLEAD, contrary to LAG, LEAD can access rows after the current row in the groupROW_NUMBERReturns the offset of a row in an ordered group that can be used for line numbers sorted by a specific standardSTDDEVCalculates the standard deviation of the current row about the groupSTDDEV_POPThe function calculates the overall standard deviation and returns the square root of the overall variableSTDDEV_SAMPThe function calculates