Christina,valenwon翻译 2004-11-28
Analytic Functions in Oracle 8i and 9i Oracle 8i and 9i分析函数
Contents目录
Overview and Introduction概述与简介 How Analytic Functions Work分析函数原理 The Syntax句法
Calculate a running Total累计计算 Top-N Queries前N条查询 Example 1例1 Example 2例2 Windows窗口
Range Windows范围窗口
Compute average salary for defined range计算定义范围的平均工资 Row Windows行窗口
Accessing Rows Around Your Current Row访问当前行前后的行 LAG LEAD Determine the First Value / Last Value of a Group确定组的首值和末值 Crosstab or Pivot Queries交叉表或Pivot查询
Christina,valenwon翻译 2004-11-28
Conclusion结论
Links and Documents链接和文档
Overview概述：
Analytic Functions, which have been available since Oracle 8.1.6, are designed to address such problems as \percentages within a group\standard PL/SQL, however the performance is often not what it should be. Analytic Functions add extensions to the SQL language that not only make these operations easier to code; they make them faster than could be achieved with pure SQL or PL/SQL. These extensions are currently under review by the ANSI SQL committee for inclusion in the SQL specification.
分析函数，最早是从ORACLE8.1.6开始出现的，它的设计目的是为了解决诸如“累计计算”，“找出分组内百分比”，“前-N条查询”，“移动平均数计算”\等问题。其实大部分的问题都可以用PL/SQL解决，但是它的性能并不能达到你所期望的效果。分析函数是SQL言语的一种扩充，它并不是仅仅试代码变得更简单而已，它的速度比纯粹的SQL或者PL/SQL更快。现在这些扩展已经被纳入了美国国家标准化组织SQL委员会的SQL规范说明书中。
How Analytic Functions Work ? 分析函数的原理
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a \The window determines the range of rows used to perform the calculations for the \physical number of rows or a logical interval such as time. 分析函数是在一个记录行分组的基础上计算它们的总值。与集合函数不同，他们返回
Christina,valenwon翻译 2004-11-28
各分组的多行记录。行的分组被称窗口，并通过分析语句定义。对于每记录行，定义了一个“滑动”窗口。该窗口确定“当前行”计算的范围。窗口的大小可由各行的实际编号或由时间等逻辑间隔确定。
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause. 除了ORDER BY（按?排序）语句外，分析函数是一条查询被执行的操作。所有合并、WHERE、GROUP BY、HAVING语句都是分析函数处理之前完成的。因此，分析函数只出现在选择目录或ORDER BY（按?排序）语句中。
The Syntax句法
The Syntax of analytic functions is rather straightforward in appearance分析函数的句法非常简单。
Analytic-Function(
o Analytic-Function分析函数的种类
Christina,valenwon翻译 2004-11-28
Specify the name of an analytic function, Oracle actually provides many analytic functions such as AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.
分析函数的名称，ORACLE通常多个分析函数，包括：AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.
o Arguments参数
Analytic functions take 0 to 3 arguments. 分析函数通常有0到3个参数
o Query-Partition-Clause查询划分语句
The PARTITION BY clause logically breaks a single result set into N groups, according to the criteria set by the partition expressions. The words \independently, they are reset for each group. 根据划分表达式设置的规则，PARTITION BY（按?划分）将一个结果逻辑分成N个分组划分表达式。在此“划分”和“分组”用作同义词。分析函数独立应用于各个分组，并在应用时重置。
o Order-By-Clause排序语句
