2014年分析函数-oracle9iOLAP.docVIP

?? 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例2Windows窗口??? Range Windows范围窗口??? Compute average salary for defined range计算定义范围的平均工资??? Row Windows行窗口??? Accessing Rows Around Your Current Row访问当前行前后的行LAGLEADDetermine the First Value / Last Value of a Group确定组的首值和末值Crosstab or Pivot Queries交叉表或Pivot查询Conclusion结论Links and Documents链接和文档 Overview概述： Analytic Functions, which have been available since Oracle 8.1.6, are designed to address such problems as Calculate a running total, Find percentages within a group, Top-N queries, Compute a moving average and many more. Most of these problems can be solved using 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 sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time. 分析函数是在一个记录行分组的基础上计算它们的总值。与集合函数不同，他们返回各分组的多行记录。行的分组被称窗口，并通过分析语句定义。对于每记录行，定义了一个“滑动”窗口。该窗口确定“当前行”计算的范围