时间戳

首页 > 解决方案 > 如何将平均值与总和相加

sql - 如何将平均值与总和相加

问题描述

我的查询是：

select
TIERING_2, ASP_NAME , 
sum(case when  SUBCASE_MEASURED = '1' AND FE_TYPE = 'ASP' AND CLOSE_MONTH = '3' and region = 'EEMEA' AND SCHEDULED_RESCHEDULED = 'Scheduled' then 1 else 0 end) DA_SCHEDULED,
sum(case when  SUBCASE_MEASURED = '1' AND FE_TYPE = 'ASP' AND CLOSE_MONTH = '3' and region = 'EEMEA' AND SCHEDULED_RESCHEDULED = 'Reactive'  then 1 else 0 end) FE_DELAY,
sum(case when  SUBCASE_MEASURED = '1' AND FE_TYPE = 'ASP' AND CLOSE_MONTH = '3' and region = 'EEMEA' AND SCHEDULED_RESCHEDULED = 'Reactive'  then 1 else 0 end)Onsite_Time,
sum(case when  SUBCASE_MEASURED = '1' AND FE_TYPE = 'ASP' AND  region = 'EEMEA' AND SCHEDULED_RESCHEDULED = 'Reactive' and LATE_START_CONF_INDICATOR = '0'then 1 else 0 end) Late_hours,
sum(case when  SUBCASE_MEASURED = '1' AND FE_TYPE = 'ASP' AND CLOSE_MONTH = '3' AND ASP_ON_TIME = '1' then 1 else 0 end) ON_TIME
from table_Control_kpi GROUP BY ASP_NAME , TIERING_2

然而，除了总和之外，我还想要平均现场时间。有人可以帮我将以下查询与上述查询结合起来吗

SELECT AVG(ONSITE_TIME) 
FROM table_Control_kpi
WHERE SCHEDULED_RESCHEDULED = 'Reactive' 
  AND ASP_NAME='3D Networks' 
  AND SUBCASE_MEASURED = '1' 
  AND FE_TYPE = 'ASP' 
  AND CLOSE_MONTH = '3'

标签： sqloracle

解决方案

也许使用window function?

SELECT TIERING_2, ASP_NAME ,
       --all the sums up there
       ,AVG(CASE 
          WHEN SCHEDULED_RESCHEDULED = 'Reactive' 
          AND ASP_NAME='3D Networks' 
          AND SUBCASE_MEASURED = '1' 
          AND FE_TYPE = 'ASP' 
          AND CLOSE_MONTH = '3'
        THEN ONSITE_TIME END) OVER ()
FROM table_Control_kpi GROUP BY ASP_NAME , TIERING_2

推荐阅读