Count number of phone calls at the same time

I have a MySQL table with phone calls. Every row means one phone call. Columns are:

start_time
start_date
duration

I need to get a maximum phone calls called at the same time. It's because of telephone exchange dimensioning.

My solution is to create two timestamp columns timestamp_start and timestamp_end . Then I run a loop second by second, day by day and ask MySQL something like:

SELECT Count(*) FROM tbl WHERE start_date IN (thisday, secondday) AND "this_second_checking" BETWEEN timestamp_start AND timestamp_end;

It's quite slow. Is there a better solution? Thank you!

EDIT - I use this solution and it gives me proper results. There is used SQL layer dibi - http://dibiphp.com/cs/quick-start .

$starts = dibi::query("SELECT ts_start, ts_end FROM " . $tblname . " GROUP BY ts_start");
if(count($starts) > 0):
  foreach ($starts as $row) {
    if(isset($result)) unset($result);
    $result = dibi::query('SELECT Count(*) FROM ' . $tblname . ' WHERE "'.$row->ts_start.'" BETWEEN ts_start AND ts_end');
    $num = $result->fetchSingle();
    if($total_max < $num):
      $total_max = $num;
    endif;    
  }  
endif;
echo "Total MAX: " . $total_max;

---

Instead of running it second by second, you should for each row (phonecall) see what other phone calls were active at that time. After that you group all of the results by the row's ID, and check which has the maximum count. So basically something like this:

SELECT MAX(calls.count)
FROM (
    SELECT a.id, COUNT(*) AS count
    FROM tbl AS a
    INNER JOIN tbl AS b ON (
        (b.timestamp_start BETWEEN a.timestamp_start AND a.timestamp_end)
        OR
        (b.timestamp_end BETWEEN a.timestamp_start AND a.timestamp_end)
    )
    GROUP BY a.id
) AS calls

Creating an index on the timestamp columns will help as well.

---

How about:

SELECT MAX(callCount) FROM (SELECT COUNT(duration) AS callCount, CONCAT(start_date,start_time) AS callTime FROM tbl GROUP BY callTime)

That would give you the max number of calls in a single "time". Assuming start_date and start_time are strings. If they're integer times, you could probably optimise it somewhat.

链接地址: http://www.djcxy.com/p/55756.html

上一篇: VIM for PHP：在Taglist中列出if（）和include（）

下一篇: 统计电话的数量