SQL 开窗常用函数在分析中的运用

开窗函数（Window Function）在 SQL 查询中非常有用，它们允许我们在结果集的一组行上执行计算，而不是整个结果集

1. SUM(col) OVER()

功能：对 col 列进行累计求和。

使用案例： 假设我们有一个名为 sales 的表，记录了每日的销售金额。我们想知道每个月的累计销售额。

SELECT 
    date, 
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY MONTH(date)) AS cumulative_sales
FROM sales;

2. COUNT(col) OVER()

功能：对 col 列进行计数。

使用案例： 统计每个部门的员工数量。

SELECT 
    department, 
    employee_id,
    COUNT(employee_id) OVER (PARTITION BY department) AS num_employees
FROM employees;

3. MIN(col) OVER() 和 MAX(col) OVER()

功能：分别求 col 列的最小值和最大值。

使用案例： 找出每个部门的最低工资和最高工资。

SELECT 
    department, 
    salary,
    MIN(salary) OVER (PARTITION BY department) AS min_salary,
    MAX(salary) OVER (PARTITION BY department) AS max_salary
FROM employees;

4. AVG(col) OVER()

功能：求 col 列的平均值。

使用案例： 计算每个部门的平均工资。

SELECT 
    department, 
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

5. FIRST_VALUE(col) OVER() 和 LAST_VALUE(col) OVER()

功能：分别返回某分区排序后的第一个和最后一个 col 值。

使用案例： 找出每个部门工资最高的员工的工资（使用 FIRST_VALUE 与排序结合）。

SELECT 
    department, 
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_salary
FROM employees;

6. LAG(col, n, DEFAULT) 和 LEAD(col, n, DEFAULT)

功能：分别返回往前 n 行和往后 n 行的 col 值。

使用案例： 计算每个员工的工资与前一月的工资差异。

SELECT 
    date, 
    employee_id, 
    salary,
    salary - LAG(salary, 1) OVER (PARTITION BY employee_id ORDER BY date) AS salary_diff
FROM salaries;

7. NTILE(n)

功能：将分组数据按照顺序切分成 n 片，并返回当前切片值。

使用案例： 将员工按工资从高到低分为三个等级。

SELECT 
    employee_id, 
    salary,
    NTILE(3) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;