Percentile_CONT函数返回多行

Question

我正在编写返回多行结果的Percentile_CONT函数。我假设PERCENTILE_CONT函数应该只返回一个value.This is my SQL

select PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY RegoToSrvc  DESC) 
           OVER (PARTITION BY Hospid) as MedianSeconds,
       'MedianWaitTime' as Flag
from A   
where A.RegistrationDate  between '20140701' and '20140731' and
      Hospid = 21

Answer 1

您使用的是窗口函数，因此集合中的行数不会减少。如果你想要中位数，只需要一行：

select TOP 1 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY RegoToSrvc  DESC) 
           OVER (PARTITION BY Hospid) as MedianSeconds,
       'MedianWaitTime' as Flag
from A   
where A.RegistrationDate  between '20140701' and '20140731' and
      Hospid = 21;