在Oracle数据库中,GROUPING
函数通常与GROUP BY
和ROLLUP
一起使用,以便对数据进行分组和汇总
首先,假设我们有一个名为employees
的表,其中包含员工的信息,如下所示:
CREATE TABLE employees ( id NUMBER PRIMARY KEY, name VARCHAR2(50), department_id NUMBER, salary NUMBER );
接下来,我们将插入一些示例数据:
INSERT INTO employees (id, name, department_id, salary) VALUES (1, 'Alice', 10, 5000); INSERT INTO employees (id, name, department_id, salary) VALUES (2, 'Bob', 10, 6000); INSERT INTO employees (id, name, department_id, salary) VALUES (3, 'Charlie', 20, 7000); INSERT INTO employees (id, name, department_id, salary) VALUES (4, 'David', 20, 8000);
现在,我们将使用GROUPING
函数对部门进行分组,并计算每个部门的平均工资。我们还将使用ROLLUP
对所有部门进行汇总。
SELECT department_id, AVG(salary) AS avg_salary, GROUPING(department_id) AS is_grouped FROM employees GROUP BY ROLLUP (department_id) ORDER BY department_id;
这将返回以下结果:
DEPARTMENT_ID AVG_SALARY IS_GROUPED ------------- ---------- ---------- 10 5500 0 20 7500 0 NULL 6500 1
在这个例子中,GROUPING
函数返回0表示该行是一个实际的部门分组,而返回1表示该行是一个汇总行(在这种情况下为所有部门的汇总)。
要对结果进行排名,可以使用RANK()
或DENSE_RANK()
窗口函数。以下是一个使用DENSE_RANK()
的例子:
WITH grouped_data AS ( SELECT department_id, AVG(salary) AS avg_salary, GROUPING(department_id) AS is_grouped FROM employees GROUP BY ROLLUP (department_id) ) SELECT department_id, avg_salary, DENSE_RANK() OVER (ORDER BY avg_salary DESC) AS rank FROM grouped_data WHERE is_grouped = 0 ORDER BY department_id;
这将返回以下结果:
DEPARTMENT_ID AVG_SALARY RANK ------------- ---------- ---- 20 7500 1 10 5500 2
在这个例子中,我们首先使用WITH
子句创建了一个名为grouped_data
的临时结果集,其中包含使用GROUPING
和ROLLUP
的查询结果。然后,我们从grouped_data
中选择部门ID、平均工资和排名,其中排名是根据平均工资降序排列的。最后,我们使用WHERE
子句过滤掉汇总行,只显示实际部门的排名。