四种排序和其他常见开窗（分析）函数

Oracle排序与分析函数

基础排序

NULL值处理

-- NULL值放最后
SELECT * FROM tableA ORDER BY columnT DESC NULLS LAST;

取前N条记录

传统方法（三层嵌套）：

SELECT * FROM (
    SELECT ROWNUM rn, a.* FROM (
        SELECT * FROM tableA ORDER BY columnT DESC NULLS LAST
    ) a
) WHERE rn <= 10;

优化方法（开窗函数）：

SELECT * FROM (
    SELECT a.*, ROW_NUMBER() OVER(ORDER BY columnT DESC NULLS LAST) AS rn
    FROM tableA a
) WHERE rn <= 10;

注意事项：

• 可使用WITH AS子句避免过多嵌套
• 字符型数字需要TO_NUMBER()转换

排序函数对比

函数	特点	排序结果示例
ROW_NUMBER()	连续唯一	1,2,3,4,5,6
RANK()	不连续，有并列	1,2,2,4,5,6
DENSE_RANK()	连续，有并列	1,2,2,3,4,5
NTILE(n)	分组排序	1,1,1,2,2,2

基本语法

-- 全局排序
ROW_NUMBER() OVER(ORDER BY columnT DESC)

-- 分组内排序
ROW_NUMBER() OVER(PARTITION BY groupCol ORDER BY columnT DESC)

常用分析函数

位置函数

FIRST_VALUE(columnT) OVER(ORDER BY columnT)  -- 取第一个值
LAST_VALUE(columnT) OVER(ORDER BY columnT)   -- 取最后一个值

偏移函数

LAG(columnT, 1) OVER(ORDER BY dateCol)       -- 取前一行
LEAD(columnT, 1) OVER(ORDER BY dateCol)      -- 取后一行

聚合函数

SUM(amount) OVER(ORDER BY dateCol)           -- 累计求和
AVG(amount) OVER(ORDER BY dateCol ROWS 2 PRECEDING) -- 移动平均

分组聚合

-- 分层聚合
GROUP BY ROLLUP(col1, col2)    -- 层次汇总
GROUP BY CUBE(col1, col2)      -- 交叉汇总

实用示例

同比环比计算

SELECT 
    month_col,
    sales,
    LAG(sales, 1) OVER(ORDER BY month_col) AS last_month,
    LAG(sales, 12) OVER(ORDER BY month_col) AS last_year
FROM sales_table;

占比计算

SELECT 
    product,
    sales,
    sales / SUM(sales) OVER() AS ratio
FROM product_sales;

分组排名

SELECT 
    department,
    employee,
    salary,
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employee;

总结

Oracle分析函数提供了强大的数据处理能力，合理使用可以简化复杂的SQL逻辑，提高查询效率。关键是理解各函数的特点和适用场景。