我有以下 sql 查询,它给出了按月、周和日分组的总 h_time。相反,我想要月、周和日的 h_time 中位数。如何在 Oracle SQL 中执行此操作?
SELECT DAY,
MEDIAN(H_TIME) AS HANDLE_TIME
FROM(
select
MONTH, WEEK, DAY,
CASE
WHEN C.JOINED IS NOT NULL
THEN (NVL(C.TOTAL_TALK,0) + NVL(C.TOTAL_HOLD,0) + (NVL((C.DATETIME - C.START_DATETIME)*86400,0)) )/86400
ELSE 0 END AS H_TIME
from TABLE1 C
LEFT JOIN TABLE2 S
ON S.ID = C.ID
where c.direct = 'Inbound'
)
where UPPER(ITEM1) like 'SOMETHING%'
GROUP BY
DAY
OUTPUT:
DAY HANDLE_TIME
14-APR-17 .00567129629629629629629629629629629629629
15-APR-17 0
17-APR-17 0
17-APR-17 .00422453703703703703703703703703703703703
19-APR-17 .00269675925925925925925925925925925925925
19-APR-17 0
19-APR-17 0
19-APR-17 .00824074074074074074074074074074074074074
尝试更换:
SUM(H_TIME) AS HANDLE_TIME
by :
MEDIAN(H_TIME) AS HANDLE_TIME
(line 3)
编辑:
对于月份,替换:
select
MONTH, WEEK, DAY,
By:
select
MONTH,
And:
GROUP BY
MONTH
,WEEK
,DAY
By:
GROUP BY
MONTH
在几周内,更换:
select
MONTH, WEEK, DAY,
By:
select
MONTH, WEEK,
And:
GROUP BY
MONTH
,WEEK
,DAY
By:
GROUP BY
MONTH
,WEEK
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)