Oracle开窗函数分类与统计应用

Oracle开窗函数分类统计

Oracle开窗函数(Window Functions)是SQL中强大的分析工具，允许在不减少行数的情况下进行计算和聚合。以下是Oracle开窗函数的主要分类和统计应用：

一、基本分类

1. 聚合类开窗函数

   • SUM() OVER()
   • AVG() OVER()
   • COUNT() OVER()
   • MAX() OVER()
   • MIN() OVER()

2. 排名类开窗函数

   • ROW_NUMBER() OVER()
   • RANK() OVER()
   • DENSE_RANK() OVER()
   • NTILE(n) OVER()

3. 分析类开窗函数

   • FIRST_VALUE() OVER()
   • LAST_VALUE() OVER()
   • LAG() OVER()
   • LEAD() OVER()

二、常用统计场景

1. 累计统计

-- 计算销售额累计和
SELECT 
    order_date,
    sales_amount,
    SUM(sales_amount) OVER(ORDER BY order_date) AS running_total
FROM sales;

2. 分组排名

-- 按部门分组计算员工薪水排名
SELECT 
    employee_id,
    department_id,
    salary,
    RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

3. 移动平均

-- 计算3个月的移动平均销售额
SELECT 
    month,
    sales,
    AVG(sales) OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM monthly_sales;

4. 前后值比较

-- 比较当前行与前一行的值
SELECT 
    date,
    temperature,
    LAG(temperature, 1) OVER(ORDER BY date) AS prev_day_temp,
    temperature - LAG(temperature, 1) OVER(ORDER BY date) AS temp_diff
FROM weather_data;

三、高级用法

1. 多级分区

SELECT 
    product_id,
    region,
    quarter,
    sales,
    SUM(sales) OVER(PARTITION BY region, quarter) AS region_quarter_sales
FROM product_sales;

2. 百分比计算

SELECT 
    student_id,
    test_score,
    test_score / SUM(test_score) OVER() * 100 AS percent_of_total
FROM test_results;

3. 首尾值分析

SELECT 
    department_id,
    employee_id,
    hire_date,
    FIRST_VALUE(employee_id) OVER(PARTITION BY department_id ORDER BY hire_date) AS first_hire
FROM employees;

四、性能考虑

1. 开窗函数通常在WHERE和GROUP BY之后执行
2. 合理使用PARTITION BY可以减少计算量
3. 对于大数据集，考虑使用RANGE而非ROWS可能更高效

Oracle开窗函数提供了强大的数据分析能力，能够在不改变结果集行数的情况下实现复杂的统计计算。