window function: avg over(partition by )

[woneuy01]

SELECT first_name, last_name, department_id, ROUND(AVG(salary) OVER ( PARTITION BY department_id ##group by 와 비슷하나 row가 줄지는 않음 )) avg_department_salary FROM employees;

[woneuy01]

SQL LAG() is a window function that provides access to a row at a specified physical offset which comes before the current row.

SELECT employee_id, fiscal_year, salary, LAG(salary) OVER ( PARTITION BY employee_id ORDER BY fiscal_year) previous_salary FROM basic_pays;

[woneuy01]

SELECT employee_id, fiscal_year, salary, previous_salary, CONCAT(ROUND(( salary - previous_salary ) * 100 /previous_salary,0),'%') YoY FROM ( SELECT employee_id, fiscal_year, salary, LAG(salary,1,0) OVER ( PARTITION BY employee_id ORDER BY fiscal_year) previous_salary FROM basic_pays ) t;

[woneuy01]

RANK() function

SELECT first_name, last_name, salary, RANK() OVER (ORDER BY salary) salary_rank FROM employees;