In this article, we will explore how to perform aggregations within a partition or group using Windowing/Analytics Functions.
Key Concept 1
In cases where we need to calculate aggregated results based on a grouping key, we can use the GROUP BY
clause.
SELECT employee_id, department_id, salary
FROM employees
GROUP BY department_id;
Key Concept 2
To obtain raw data alongside aggregated results, using the GROUP BY
clause might be complex. Using aggregate functions with OVER
clause simplifies the query writing process and improves performance.
SELECT e.employee_id, e.department_id, e.salary,
ae.department_salary_expense,
ae.avg_salary_expense
FROM employees e JOIN (
SELECT department_id,
sum(salary) AS department_salary_expense,
round(avg(salary)::numeric, 2) AS avg_salary_expense
FROM employees
GROUP BY department_id
) ae
ON e.department_id = ae.department_id
Hands-On Tasks
List of tasks to apply concepts from the article:
- Write a SQL query using the
OVER
clause for aggregating results within a partition. - Calculate the percentage of employee salary compared to department salary expense.
Conclusion
In conclusion, we have delved into performing aggregations using Analytic Functions and how they simplify and streamline the process in SQL queries. By using OVER
clauses, we can efficiently accomplish complex aggregations and calculations within windows or groups.
Click here to watch the video tutorial to supplement the text.