sql中的NTH_VALUE（）函数

NTH_VALUE()函数是SQL中的窗口函数之一，它用于获取分组内指定位置的值。它的语法如下：

NTH_VALUE(expression, n) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

其中，expression表示要获取的值的列或表达式，n表示要获取的位置。PARTITION BY子句指定分组依据，ORDER BY子句指定排序方式，ROWS BETWEEN子句指定窗口大小。

例如，以下语句将在每个部门内按照工资从高到低排序，然后获取每个部门内工资排名第二的员工的姓名和工资：

SELECT department, name, salary, 
  NTH_VALUE(name, 2) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_paid_employee,
  NTH_VALUE(salary, 2) OVER (
    PARTITION BY department 
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_salary
FROM employees