0
sql中的分析函数都有哪些，怎么用的
                           
天天向上
发布： 2024-12-08 18:20:08
原创
984 人浏览过

SQL中的分析函数（窗口函数）是用于在结果集中执行复杂的计算和分析操作，而无需改变查询的结果集结构。分析函数在SQL中非常强大，常用于排名、分组聚合、滑动窗口计算、累计等场景。

以下是SQL中常见的分析函数，以及它们的使用方法：

1. 排名函数

排名函数用于为查询结果中的行分配一个排名。

1.1 ROW_NUMBER()

ROW_NUMBER() 函数为结果集中的每一行分配一个唯一的行号。

用法：

SELECT 
    employee_id, 
    salary, 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;
  • ORDER BY salary DESC：根据薪资降序排列，为每个员工分配一个唯一的行号。

1.2 RANK()

RANK() 函数为结果集中的每一行分配一个排名，相同值的行会共享相同的排名，后续的排名会跳过。

用法：

SELECT 
    employee_id, 
    salary, 
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
  • RANK() 计算薪资排名，薪资相同的员工会共享相同的排名，后续排名会跳过。

1.3 DENSE_RANK()

DENSE_RANK() 函数类似于 RANK()，但不会跳过排名，相同的值会分配相同的排名，但后续的排名是连续的。

用法：

SELECT 
    employee_id, 
    salary, 
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
  • DENSE_RANK() 计算薪资排名，薪资相同的员工会共享排名，后续排名不会跳过。

2. 聚合函数

聚合函数可以与分析函数一起使用，对分组的数据进行聚合计算。

2.1 SUM()

SUM() 函数用于计算某个字段的总和。

用法：

SELECT 
    department_id, 
    employee_id, 
    salary, 
    SUM(salary) OVER (PARTITION BY department_id) AS department_salary
FROM employees;
  • PARTITION BY department_id：按部门分组计算每个部门的总薪资。

2.2 AVG()

AVG() 函数用于计算某个字段的平均值。

用法：

SELECT 
    department_id, 
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;
  • 计算每个部门的平均薪资。

2.3 COUNT()

COUNT() 函数用于计算某个字段的数量。

用法：

SELECT 
    department_id, 
    COUNT(employee_id) OVER (PARTITION BY department_id) AS department_count
FROM employees;
  • 计算每个部门的员工数量。

3. 滑动窗口函数

滑动窗口函数用于在查询结果中计算前后某行的数据，常用于计算累计值或滑动平均。

3.1 LEAD()

LEAD() 函数返回当前行之后某一行的值，常用于查看后续的行数据。

用法：

SELECT 
    employee_id, 
    salary, 
    LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;
  • LEAD(salary, 1)：获取当前行之后1行的薪资数据。

3.2 LAG()

LAG() 函数返回当前行之前某一行的值，常用于查看前面的行数据。

用法：

SELECT 
    employee_id, 
    salary, 
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary
FROM employees;
  • LAG(salary, 1)：获取当前行之前1行的薪资数据。

3.3 NTILE()

NTILE(n) 函数将数据分成n个桶，返回每一行所在的桶编号。

用法：

SELECT 
    employee_id, 
    salary, 
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
  • NTILE(4)：将结果集分为4个组，按薪资降序排列。

4. 窗口框架函数

窗口框架用于指定窗口的范围，控制参与分析的数据行。

4.1 ROWS BETWEEN

ROWS BETWEEN 用于定义分析窗口的前后范围。

用法：

SELECT 
    employee_id, 
    salary, 
    SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS window_sum
FROM employees;
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：包含当前行的前一行和后一行的数据。

4.2 RANGE BETWEEN

RANGE BETWEEN 用于基于排序列的范围计算。

用法：

SELECT 
    employee_id, 
    salary, 
    SUM(salary) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;
  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从第一行到当前行的所有行都参与计算。

5. 特殊分析函数

一些分析函数用于获取特定的行值，比如获取分组的第一个或最后一个值。

5.1 FIRST_VALUE()

FIRST_VALUE() 函数返回分组内排序后的第一行数据。

用法：

SELECT 
    department_id, 
    employee_id, 
    salary, 
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary
FROM employees;
  • 获取每个部门薪资最高的员工薪资。

5.2 LAST_VALUE()

LAST_VALUE() 函数返回分组内排序后的最后一行数据。

用法：

SELECT 
    department_id, 
    employee_id, 
    salary, 
    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary
FROM employees;
  • 获取每个部门薪资最低的员工薪资。

总结

SQL分析函数（窗口函数）提供了强大的数据分析能力，常用于：

  • 排名：ROW_NUMBER(), RANK(), DENSE_RANK()
  • 聚合：SUM(), AVG(), COUNT()
  • 滑动窗口：LEAD(), LAG(), NTILE()
  • 窗口框架：ROWS BETWEEN, RANGE BETWEEN
  • 特殊：FIRST_VALUE(), LAST_VALUE()

使用分析函数时，可以结合 PARTITION BY（分组）和 ORDER BY（排序）来精细控制分析的方式，进而获得强大的数据分析功能。

发表回复 0

Your email address will not be published. Required fields are marked *