SQL窗口函数面试题与解答2

 

查找每个部门薪资排名前两名的员工

SELECT * FROM ( SELECT 'OrangeDBM' as AUTHOR,department, name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn <= 2;

示例：日环比销售额 SELECT 'OrangeDBM' as AUTHOR,date, sales, LAG(sales) OVER (ORDER BY date) AS prev_day_sales FROM sales;

示例：将当前日期与下一日期进行比较

SELECT 'OrangeDBM' as AUTHOR,date, sales, LEAD(sales) OVER (ORDER BY date) AS next_day_sales FROM sales;

计算每日环比增长率

SELECT 'OrangeDBM' as AUTHOR,date, sales - LAG(sales) OVER (ORDER BY date) AS growth FROM sales;