Процент 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 (три сеанса) должны быть постоянными для сравнения

До сих пор я пробовал следующие операторы SQL:

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

Answer 1

Один метод использует условное агрегирование и оконные функции:

Select ClassSession,
       (sum(case when StudentName = 'Chris' then 100.0 else 0 end) /
        sum(sum(case when StudentName = 'Chris' then 100.0 else 0 end)) over ()
       ) as Percentage
From Classes
Group By ClassSession;

Это гарантирует, что событие обнаружит нули.

Answer 2

Сложнее всего заставить нули показываться для учеников, у которых нет занятий на данном занятии.

Это работа для 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  ║
╚══════════════════════════════════════════════════════════════════╝

Answer 3

Вот более или менее традиционный способ использования SQL Server 2008 и выше. (Могут быть более целесообразные способы написать это с использованием статистических оконных функций в более поздних версиях.)

Это вернет данные для всех учеников хотя бы в одном классе, во всех классах хотя бы с одним учеником. Если таблицы большие, раскомментируйте where пункт, чтобы получить данные для одного студента одновременно

Во-первых, данные тестирования:

CREATE TABLE #Test
 (
   ClassSession  varchar(20)  not null
  ,StudentName   varchar(20)  not null
 )

INSERT #Test values 
  ('Evening', 'Ben')
 ,('Morning', 'Chris')
 ,('Afternoon', 'Roger')
 ,('Evening', 'Ben')
 ,('Afternoon', 'Ben')
 ,('Morning', 'Roger')
 ,('Morning', 'Ben')
 ,('Afternoon', 'Chris')


SELECT *
 from #Test

И запрос:

WITH cteClasses
 as (--  First, get the list of classes
     SELECT distinct ClassSession
      from #Test
     )
 ,cteStudents
  as (--  Next, get a list of all students
      SELECT
         StudentName
        ,count(*) * 1.00  ClassCount
       from #Test
       --where StudenName = @StudentParameter
       group by StudentName
     )
 --  Mush them all together, and...
 SELECT
   st.StudentName
  ,cl.ClassSession
  ,count(te.StudentName) / st.ClassCount * 100  Percentage
 from cteStudents st
  cross join cteClasses cl
  left join #Test te
   on te.ClassSession = cl.ClassSession
    and te.StudentName = st.StudentName
 group by
   st.StudentName
  ,cl.ClassSession
  ,st.ClassCount
 order by 
   st.StudentName
  ,cl.ClassSession