动态计算的 SQL 百分比

Question

如何在SQL中动态计算百分比？

假设您有一个名为 Classes 的下表:

ClassSession       StudentName
---------------------------------
Evening            Ben
Morning            Chris
Afternoon          Roger
Evening            Ben
Afternoon          Ben
Morning            Roger
Morning            Ben
Afternoon          Chris

假设 Ben，我期待

Evening = 50 %
Afternoon = 25%
Morning = 25%

我期待克里斯

Morning = 50%
Afternoon = 50%
Evening = 0 %

所以ClassSession(三个session)应该是常量，以便比较

到目前为止，我已经尝试了以下 SQL 语句:

Select 
    ClassSession,
    (Count(ClassSession) * 100 / (Select Count(*) From Classes)) as Percentage
From 
    Classes
Where 
    StudentName = 'Chris'
Group By 
    ClassSession

Answer 1

困难的部分是让零出现在给定类(class)中没有任何类(class)的学生身上。

这是一个PARTITION外连接的作业。

select c.studentname, 
       s.classsession, 
       round(ratio_to_report(count(c.classsession)) 
            over ( partition by c.studentname),2) pct
from c partition by ( studentname ) 
   right outer join ( SELECT distinct classsession from c ) s 
          on s.classsession = c.classsession
group by c.studentname, s.classsession
order by c.studentname, s.classsession;

注意连接中的PARTITION 关键字。这告诉 Oracle 为每个分区执行外部连接。因此，如果给定的 studentname 没有 classsession，请为该学生添加它。

此外，ratio_to_report 是计算百分比的好函数。

这是一个完整的例子，有数据:

with c (ClassSession, StudentName) AS ( 
SELECT 'Evening',            'Ben' FROM DUAL UNION ALL
SELECT 'Morning',            'Chris' FROM DUAL UNION ALL
SELECT 'Afternoon',          'Roger' FROM DUAL UNION ALL
SELECT 'Evening',            'Ben' FROM DUAL UNION ALL
SELECT 'Afternoon',          'Ben' FROM DUAL UNION ALL
SELECT 'Morning',            'Roger' FROM DUAL UNION ALL
SELECT 'Morning',            'Ben' FROM DUAL UNION ALL
SELECT 'Afternoon',          'Chris' FROM DUAL)
select c.studentname, 
       s.classsession, 
       round(ratio_to_report(count(c.classsession)) 
            over ( partition by c.studentname),2) pct
from c partition by ( studentname ) 
   right outer join ( SELECT distinct classsession from c ) s on s.classsession = c.classsession
group by c.studentname, s.classsession
order by c.studentname, s.classsession;


╔══════════════════════════════════════════════════════════════════╗
║ STUDENTNAME CLASSSESSION PCT                                     ║
╠══════════════════════════════════════════════════════════════════╣
║ ----------- ------------ --------------------------------------  ║
║ Ben         Afternoon                                      0.25  ║
║ Ben         Evening                                         0.5  ║
║ Ben         Morning                                        0.25  ║
║ Chris       Afternoon                                       0.5  ║
║ Chris       Evening                                           0  ║
║ Chris       Morning                                         0.5  ║
║ Roger       Afternoon                                       0.5  ║
║ Roger       Evening                                           0  ║
║ Roger       Morning                                         0.5  ║
╚══════════════════════════════════════════════════════════════════╝