2021-06-16

group by cube() 计算百分比被稀释： ratio_to_report()over()

CREATE TABLE teacher_student(study_id NUMBER(6) PRIMARY KEY,
                             sex VARCHAR(6),
                             ROLE VARCHAR(6),
                             grade VARCHAR(9));

SELECT CASE
         WHEN st.sex IS NULL AND st.grade IS NULL AND st.role IS NULL THEN
          '总计'
         WHEN st.sex IS NULL THEN
          st.grade || st.role || '小计'
         ELSE
          st.sex
       END sex,
       CASE
         WHEN st.sex IS NULL AND st.grade IS NULL AND st.role IS NULL THEN
          '总计'
         WHEN st.role IS NULL THEN
          st.grade || st.sex || '小计'
         ELSE
          st.role
       END ROLE,
       CASE
         WHEN st.sex IS NULL AND st.grade IS NULL AND st.role IS NULL THEN
          '总计'
         WHEN st.grade IS NULL THEN
          st.role || st.sex || '小计'
         ELSE
          st.grade
       END grade,
       ratio_to_report(COUNT(st.study_id)) OVER() * 8 AS "占比"--所有占比被稀释了2**n，cube(1,2,3,n)
  FROM teacher_student st
 GROUP BY CUBE(st.sex, st.role, st.grade);