在SQL中,使用PARTITION BY
和GROUP BY
可以将数据按照分区进行分组。下面是一个示例代码:
假设有一个名为employees
的表,包含以下列:id, name, department, salary
。
要按照部门分区,并按照部门和工资分组,并计算每个组的平均工资和总工资,可以使用以下SQL查询:
SELECT department,
AVG(salary) OVER (PARTITION BY department) AS avg_salary,
SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees
GROUP BY department, salary;
这个查询使用了PARTITION BY department
将数据按照部门分区,然后使用GROUP BY department, salary
对每个分区内的数据进行分组。
AVG(salary) OVER (PARTITION BY department)
计算了每个分区内工资的平均值,SUM(salary) OVER (PARTITION BY department)
计算了每个分区内工资的总和。
注意,这里使用了窗口函数OVER (PARTITION BY department)
来计算每个分区内的统计值。