按星期计算呼叫类型，按类型分组，以识别趋势 [英] Count call types by week, grouped by type, to identify trends

问题描述

因此，下面的第一个代码段摘录了基于某个固定代码的所有呼叫类型。我试图按周和/或每天有效地分组，这样我就可以根据呼叫类型识别电话号码中的趋势。

  SELECT count（*），substr（FIXCODE，1,4）FROM opencall 
 WHERE（opencall.status> 15或opencall.status = 6）
和FIXCODE如'HPS0％'
和trunc（to_date（substr（CLOSEDATE，1,10），'DD-MM-YY'））BETWEEN '01 -JAN-14'和'24 -DEC-14'
 GROUP BY substr FIXCODE，1,4）
  

返回

  Count FixCode 
 2425 HPS0 
  

是自1月1日起以HPS0的固定码开始的所有呼叫的计数。

我有一段由StackOverflow提供的另一个非常有帮助的代码完成的代码，见下文，它将原始调用分组并按星期计数，但我没有知道如何将两者结合在一起。

将2425个HPS0电话细分为几个星期是非常好的，所以我可以看到有多少电话每周关闭以查看趋势。谁能帮忙？我也很乐意这样做。

row_number（）over（order by trunc（NEXT_DAY（to_date（substr（LOGDATE，1,10），'DD-MM-YY'），'FRIDAY'），'IW'））WEEK，
count（* ）
from opencall
where trunc（to_date（substr（LOGDATE，1,10），'DD-MM-YY'））BETWEEN '01 -JAN-14'AND '31 -DEC-14'
by trunc（NEXT_DAY（to_date（substr（LOGDATE，1,10），'DD-MM-YY'），'FRIDAY'），'IW'）
trunc（NEXT_DAY（to_date） （substr（LOGDATE，1,10），'DD-MM-YY'），'FRIDAY'），'IW'）;

解决方案

如果您想按周查看价值，在组中的一周内：

  SELECT to_char（closeddate， 'YYYY-IW'），substr（FIXCODE，1，4），COUNT（*）
 FROM opencall 
 WHERE（opencall.status> 15或opencall.status = 6）
和FIXCODE如'HPS0％'
和trunc（CLOSEDATE）BETWEEN '01 -JAN-14'和'24 -DEC-14'
 GROUP BY to_char（closeddate，'YYYY-IW'），substr FIXCODE，1，4）
 ORDER BY to_char（closeddate，'YYYY-IW'）
  

大概 closeddate 已经是一个日期，所以你不需要将它转换回日期。

I have another query that I am trying to group by week again.

So the first code excerpt below counts up all the call types based on a certain fixcode. I'm trying to effectively group these by week and/or day so I can identify trends in the call numbers based on the call type.

SELECT count(*),substr(FIXCODE,1,4) FROM opencall
WHERE (opencall.status > 15 OR opencall.status = 6) 
and FIXCODE like 'HPS0%'
and trunc(to_date(substr(CLOSEDATE,1,10), 'DD-MM-YY')) BETWEEN '01-JAN-14' AND '24-DEC-14' 
GROUP BY substr(FIXCODE,1,4)

This returns

Count   FixCode 
2425    HPS0

So this is a count of all the calls starting with a fixcode of HPS0 since January 1st.

I had a previous piece of code completed by another very helpful chap on StackOverflow, see below, which does group raw calls and counts them by week, but I don't know how to combine the two.

It would be great to subdivide the 2425 HPS0 calls into weeks so I can see how many of those calls were closed week by week to see a trend. Can anyone help? I'd love to do it by day too.

select 
'WEEK '|| row_number() over(order by trunc(NEXT_DAY(to_date(substr(LOGDATE,1,10), 'DD-MM-YY'),'FRIDAY'), 'IW')) WEEK,
count(*) 
from opencall
where trunc(to_date(substr(LOGDATE,1,10), 'DD-MM-YY')) BETWEEN '01-JAN-14' AND '31-DEC-14'
group by trunc(NEXT_DAY(to_date(substr(LOGDATE,1,10), 'DD-MM-YY'),'FRIDAY'), 'IW')   
order by trunc(NEXT_DAY(to_date(substr(LOGDATE,1,10), 'DD-MM-YY'),'FRIDAY'), 'IW');

解决方案

If you want to see the value by week, add in the week to the group by:

SELECT to_char(closeddate, 'YYYY-IW'), substr(FIXCODE, 1, 4), COUNT(*)
FROM opencall
WHERE (opencall.status > 15 OR opencall.status = 6) 
and FIXCODE like 'HPS0%'
and trunc(CLOSEDATE) BETWEEN '01-JAN-14' AND '24-DEC-14' 
GROUP BY to_char(closeddate, 'YYYY-IW'), substr(FIXCODE, 1, 4)
ORDER BY  to_char(closeddate, 'YYYY-IW')

Presumably closeddate is already a date, so you don't need to convert it back to a date.

这篇关于按星期计算呼叫类型，按类型分组，以识别趋势的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！