达梦cube和rollup函数使用

在使用前需要安装达梦数据库，并创建示例库DMHR

● ROLLUP ●

比如获取公司各城市每个部门员工人数分布和薪资分布情况，使用group by rollup的sql样例参考如下：

从结果集中可以看出group by rollup聚合了城市和部门组合，城市组合，所有组合三种情况。输出结果展示如下：

SELECT
        C.CITY_NAME                                                 ,
        DEPT.DEPARTMENT_NAME                                        ,
        COUNT(                       *) TOAL_PERSONNUM              ,
        ROUND((RATIO_TO_REPORT(COUNT(*))OVER())*100, 2) PERSON_RATIO,
        SUM(EMP.SALARY) TOTAL_SALARY                                ,
        ROUND((RATIO_TO_REPORT(SUM(EMP.SALARY))OVER())*100, 2) SAL_RATIO
FROM
        DMHR.EMPLOYEE EMP   ,
        DMHR.DEPARTMENT DEPT,
        DMHR.LOCATION L     ,
        DMHR.CITY C
WHERE
        EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID(+)
    AND DEPT.LOCATION_ID  = L.LOCATION_ID(+)
    AND L.CITY_ID         = C.CITY_ID(+)
GROUP BY
        ROLLUP (C.CITY_NAME, DEPT.DEPARTMENT_NAME);

结果集如下

从结果集中可以看出group by rollup聚合了城市和部门组合，城市组合，所有组合三种情况。输出结果展示如下：

● CUBE ●

上例中的sql将rollup换成cube，sql样例参考如下：

select
        c.city_name                                                 ,
        dept.department_name                                        ,
        count(                       *) toal_personnum              ,
        round((ratio_to_report(count(*))over())*100, 2) person_ratio,
        sum(emp.salary) total_salary                                ,
        round((ratio_to_report(sum(emp.salary))over())*100, 2) sal_ratio
from
        DMHR.employee emp   ,
        DMHR.department dept,
        DMHR.location l     ,
       DMHR.city c
where
        emp.department_id = dept.department_id(+)
    and dept.location_id  = l.location_id(+)
    and l.city_id         = c.city_id(+)
group by
        cube (c.city_name, dept.department_name);

结果集如下：

 从结果集中可以看出group by cube聚合了城市和部门组合，城市组合，部门组合，所有组合四种情况。结果集展示如下：