asterisk users - Nov 2019 - CDR extract call numbers on interval on unique callers

hi,

we want to extract the information when the most callers are entering 
our phone system based on an interval of 15 minutes. this is quite 
simple (although not perfect) with
select calldate, count(*) as anzahl from cdr where calldate > 
'2019-10-12' group by unix_timestamp(calldate) DIV 900 having ;

Unfortunately we have lots of callers who calls multiple times when they 
are forwarded to a queue instead of being answered by a human 
immediately. But to know when we need more people I want to count same 
caller-ids within an interval as one call.

Any ideas how to do this?

kind regards,
andre

https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count-distinct

Use something like count(distinct src) instead of count(*)

On Tue, Nov 12, 2019, 07:35 Andre Gronwald <andregronwald78 at gmail.com>
wrote:
> hi,
>
> we want to extract the information when the most callers are entering
> our phone system based on an interval of 15 minutes. this is quite
> simple (although not perfect) with
> select calldate, count(*) as anzahl from cdr where calldate >
> '2019-10-12' group by unix_timestamp(calldate) DIV 900 having ;
>
> Unfortunately we have lots of callers who calls multiple times when they
> are forwarded to a queue instead of being answered by a human
> immediately. But to know when we need more people I want to count same
> caller-ids within an interval as one call.
>
> Any ideas how to do this?
>
> kind regards,
> andre
>
> --
> _____________________________________________________________________
> -- Bandwidth and Colocation Provided by http://www.api-digital.com --
>
> Check out the new Asterisk community forum at:
> https://community.asterisk.org/
>
> New to Asterisk? Start here:
>       https://wiki.asterisk.org/wiki/display/AST/Getting+Started
>
> asterisk-users mailing list
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://lists.digium.com/pipermail/asterisk-users/attachments/20191112/2fb50402/attachment.html>

thanks john,

that is a good idea and really easy.
I selected both values to have a good comparison:

select calldate, count(distinct(clid)), count(clid) from cdr where 
calldate > '2019-10-12' group by unix_timestamp(calldate) DIV 900 ;

now it would be nice to have intervals starting always in the same 
manner. currently the output is like:

MariaDB [asteriskcdrdb]> select calldate, count(distinct(clid)), 
count(clid) from cdr where calldate > '2019-10-12' group by 
unix_timestamp(calldate) DIV 900 ;
+---------------------+-----------------------+-------------+
| calldate            | count(distinct(clid)) | count(clid) |
+---------------------+-----------------------+-------------+
| 2019-10-14 08:04:36 |                     5 |          24 |
| 2019-10-14 08:16:42 |                     6 |          14 |
| 2019-10-14 08:30:55 |                     7 |          29 |
| 2019-10-14 08:45:10 |                     3 |           6 |
| 2019-10-14 09:00:46 |                     6 |          19 |
| 2019-10-14 09:35:57 |                     4 |           5 |
| 2019-10-14 09:45:05 |                     4 |          19 |
| 2019-10-14 10:01:12 |                     6 |          45 |

[...]

would be better to have dates starting with "2019-10-14 08:00:00", 
"2019-10-14 08:15:00" etc...
any quick idea? i will search for that anyway.

regards,
andre

i've got it:

select from_unixtime(round((ceiling(unix_timestamp(calldate)/ 900) 
*900))) as intervall, count(distinct(clid)), count(clid) from cdr where 
calldate > '2019-09-01' group by intervall;


Am 12.11.19 um 15:16 schrieb Andre Gronwald:
> would be better to have dates starting with "2019-10-14
08:00:00",
> "2019-10-14 08:15:00" etc...
> any quick idea? i will search for that anyway.
>
> regards,
> andre