数据库窗口分析函数~CDA打卡

数据库窗口分析函数详细知识点

窗口分析函数是SQL中用于对结果集进行分析和处理的一类函数，它们可以在查询语句中进行数据分区、排序和排名等操作。以下是常见的窗口分析函数及其详细知识点：

1. LAG函数

   • 语法：LAG(expression, offset, default)
   • 作用：用于获取当前行之前的行的数据。
   • 参数：
     • expression：要获取值的列或表达式。
     • offset：要向前偏移的行数，默认为1。
     • default：可选参数，如果没有找到行，则返回的默认值。
   • 示例代码：

     SELECT name, salary, LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary
     FROM employees;
     

2. LEAD函数

   • 语法：LEAD(expression, offset, default)
   • 作用：用于获取当前行之后的行的数据。
   • 参数与示例同LAG函数。

3. FIRST_VALUE函数

   • 语法：FIRST_VALUE(expression) OVER (partition_clause order_clause)
   • 作用：返回分区内第一个行的指定列的值。
   • 示例代码：

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

4. LAST_VALUE函数

   • 语法：LAST_VALUE(expression) OVER (partition_clause order_clause)
   • 作用：返回分区内最后一个行的指定列的值。
   • 示例代码：

     SELECT department, salary, LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS max_salary
     FROM employees;
     

5. ROW_NUMBER函数

   • 语法：ROW_NUMBER() OVER (partition_clause order_clause)
   • 作用：为结果集中的每一行分配一个唯一的整数值，按指定顺序排列。
   • 示例代码：

     SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
     FROM employees;
     

6. RANK函数

   • 语法：RANK() OVER (partition_clause order_clause)
   • 作用：为结果集中的每一行分配一个排名值，相同值的行将具有相同的排名，并且会出现间断。
   • 示例代码同ROW_NUMBER函数。

7. DENSE_RANK函数

   • 语法：DENSE_RANK() OVER (partition_clause order_clause)
   • 作用：为结果集中的每一行分配一个密集排名值，相同值的行将具有相同的排名，但不会出现间断。
   • 示例代码同ROW_NUMBER函数。

8. CUME_DIST函数

   • 语法：CUME_DIST() OVER (partition_clause order_clause)
   • 作用：计算分区内当前行在排序后的结果集中所占的百分比。
   • 示例代码：

     SELECT name, salary, CUME_DIST() OVER (ORDER BY salary) AS percentile
     FROM employees;
     

9. PERCENT_RANK函数

   • 语法：PERCENT_RANK() OVER (partition_clause order_clause)
   • 作用：计算分区内当前行在排序后的结果集中的相对排名百分比。
   • 示例代码同CUME_DIST函数。

10. NTILE函数

    • 语法：NTILE(number) OVER (partition_clause order_clause)
    • 作用：将结果集划分为指定数量的桶，并为每个桶分配一个编号。
    • 示例代码：

      SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile
      FROM employees;