Oracle 修改脚本。

你好-

我有一个脚本如下:

SELECT  
            s.schoolid sid, 
            s.lastfirst lf,
            s.grade_level grl,
            sum(ada.membershipvalue)-sum(ada.attendancevalue) absences,
            sum(ada.membershipvalue) membership,
            round((sum(ada.membershipvalue)-sum(ada.attendancevalue))/sum(ada.membershipvalue)*100,2) absenceRate, 
            round(sum(ada.attendancevalue)/sum(ada.membershipvalue)*100,2)  PresentRate, 
            case when round(sum(ada.attendancevalue)/sum(ada.membershipvalue)*100,2) <= 89.99 then 'Red' else 'Green' end attctg 
FROM students s
            JOIN schools schl ON schl.school_number = s.schoolid
            JOIN ps_adaadm_defaults_all ada ON ada.studentid = s.id
WHERE
                s.schoolid = 1 
            and s.enroll_status = 0
            AND to_date(ada.calendardate) >= to_date('09/01/2018','MM/DD/YYYY') -- param start dt of sch 
            AND to_date(ada.calendardate) <= to_date(current_date)
            AND ada.membershipvalue > 0          
GROUP BY s.schoolid, 
         s.lastfirst, 
         s.grade_level 
复制

如何修改此脚本以显示按年级级别划分的相应类别 (红色和绿色) 的学生百分比？

谢谢

文卡特

好吧... 没有任何真实的上下文，我强烈建议你测试答案的有效性，但这样的事情应该这样做:

with grouped_data as
(
SELECT  
            s.schoolid sid, 
            s.lastfirst lf,
            s.grade_level grl,
            sum(ada.membershipvalue)-sum(ada.attendancevalue) absences,
            sum(ada.membershipvalue) membership,
            round((sum(ada.membershipvalue)-sum(ada.attendancevalue))/sum(ada.membershipvalue)*100,2) absenceRate, 
            round(sum(ada.attendancevalue)/sum(ada.membershipvalue)*100,2)  PresentRate, 
            case when round(sum(ada.attendancevalue)/sum(ada.membershipvalue)*100,2) <= 89.99 then 'Red' else 'Green' end attctg,
            count(*) cnt_per_color
FROM students s
JOIN schools schl ON schl.school_number = s.schoolid
JOIN ps_adaadm_defaults_all ada ON ada.studentid = s.id
WHERE
                s.schoolid = 1 
            and s.enroll_status = 0
            AND to_date(ada.calendardate) >= to_date('09/01/2018','MM/DD/YYYY') -- param start dt of sch 
            AND to_date(ada.calendardate) <= to_date(current_date)
            AND ada.membershipvalue > 0          
GROUP BY s.schoolid, 
         s.lastfirst, 
         s.grade_level 
)         
select 
  sid, 
  lf,
  grl,
  absences,
  membership,
  absenceRate, 
  PresentRate, 
  attctg,
  100 * cnt_per_color / sum(cnt_per_color) over ( partition by sid,lf,grl,absenceRate,PresentRate) as color_pct
from grouped_data


复制

我添加了 “count_per_color”，然后使用分析函数对每个计数除以红色和绿色的计数之和。