Oracle分析函数详解：从rollup到last_value

"Oracle的分析函数汇总" Oracle的分析函数是一种强大的工具，用于处理数据库中的大量数据，它在Oracle 8i版本中被引入，极大地简化了数据聚合和分析的复杂性。分析函数允许用户在单个SQL语句中对一组行进行计算，而无需依赖子查询或自连接。以下是对描述中提到的一些主要分析函数的详细说明： 1. **自动汇总函数（ROLLUP, CUBE）** - **ROLLUP** 函数用于生成多级的汇总数据，它会创建一个结果集，包含从最细粒度到最粗粒度的所有组合。例如，如果你对`BILL_MONTH`和`AREA_CODE`分组，它会返回按月、按区、按月+区和总体的总计。 - **CUBE** 函数与ROLLUP类似，但会生成所有可能的子集，包括单列、两列的组合以及所有列的总计。 2. **排名函数（RANK, DENSE_RANK, ROW_NUMBER）** - **RANK()** 为每一行分配一个唯一的排名，如果两个或多个行有相同的值，则它们将获得相同的排名，并且下一行的排名会跳过相应数量。 - **DENSE_RANK()** 类似于RANK，但不跳过排名，当有相同值时，连续的排名会被分配。 - **ROW_NUMBER()** 为每一行分配一个唯一的数字，无论值是否重复，每次都会递增。 3. **LAG, LEAD函数** - **LAG()** 允许你访问当前行之前的数据，可以指定偏移量和默认值。比如，你可以查看当前行的前一行的`LOCAL_FARE`。 - **LEAD()** 则相反，它提供当前行之后的数据。这对于预测或分析序列中的下一个值非常有用。 4. **移动计算函数（SUM, AVG的移动增加, 移动平均数）** - **移动SUM** 是在指定窗口内计算累计总和，移动AVG则是计算滑动窗口内的平均值。这些函数允许你在数据流中动态跟踪累计值或平均值，而不必一次性计算整个集合。 5. **ratio_to_report报表处理函数** - **ratio_to_report()** 返回当前行值占整个分组总和的比例。这对于计算百分比分布非常有用。 6. **first, last取基数的分析函数** - **FIRST_VALUE()** 和 **LAST_VALUE()** 分别返回指定列在当前分组中的第一行和最后一行的值，这些函数在处理时间序列数据或需要特定位置的值时很有用。 以提供的基础数据为例，如果我们想计算每个`BILL_MONTH`和`AREA_CODE`的`LOCAL_FARE`总和，然后再按月汇总，我们可以使用ROLLUP函数： ```sql SELECT BILL_MONTH, AREA_CODE, SUM(LOCAL_FARE) OVER (PARTITION BY BILL_MONTH, AREA_CODE) AS SUM_LOCAL_FARE, SUM(LOCAL_FARE) OVER (PARTITION BY BILL_MONTH) AS MONTHLY_TOTAL FROM T GROUP BY ROLLUP(BILL_MONTH, AREA_CODE); ``` 这样，我们不仅得到了每个区的月度费用，还得到了每月的总体费用。 了解并熟练使用Oracle的分析函数对于数据分析和报表生成至关重要，它们能够有效提升SQL查询的效率和数据处理的灵活性。通过合理运用这些函数，你可以更高效地处理大量数据，为业务决策提供更准确的信息支持。