donglanfu5831 2016-05-11 02:25
浏览 72
已采纳

PHP多维数组按15分钟间隔排序

I am trying to sort a Multidimentional array into 15 minute increments with the following data, this is data from a Call Center Database for phone records

CallID      CallDateTime                    IsLost  IsAnswered
82650       May 10 2016 08:55:01:000AM      0       1
82666       May 10 2016 09:03:13:000AM      0       1
82677       May 10 2016 09:07:45:000AM      0       1
82688       May 10 2016 09:12:19:000AM      0       1
82689       May 10 2016 09:14:42:000AM      0       1
82702       May 10 2016 09:22:08:000AM      0       1
82708       May 10 2016 09:26:26:000AM      0       1
82737       May 10 2016 09:39:42:000AM      0       1
82739       May 10 2016 09:43:17:000AM      0       1
82748       May 10 2016 09:45:22:000AM      0       1
82786       May 10 2016 09:59:26:000AM      0       1
82789       May 10 2016 10:00:21:000AM      0       1
82820       May 10 2016 10:26:52:000AM      0       1
82842       May 10 2016 10:37:14:000AM      0       1
82846       May 10 2016 10:39:04:000AM      0       1
82859       May 10 2016 10:48:30:000AM      0       1
82865       May 10 2016 10:50:18:000AM      0       1
82880       May 10 2016 10:56:47:000AM      0       1
82911       May 10 2016 11:07:30:000AM      0       0
82925       May 10 2016 11:15:34:000AM      0       1
82926       May 10 2016 11:16:08:000AM      0       1
82927       May 10 2016 11:16:17:000AM      0       1
82943       May 10 2016 11:23:16:000AM      0       1
82944       May 10 2016 11:25:54:000AM      0       1
82947       May 10 2016 11:26:48:000AM      0       1
82966       May 10 2016 11:33:27:000AM      0       1
83048       May 10 2016 12:14:39:000PM      0       1

That I get from an MSSQL Server Database, placing data in via the following PHP Code

 $sql = "SELECT CallID, CallDateTime, IsLost, IsAnswered FROM vwCustomReportingCallsMain WHERE DDI = '2399' AND datediff(day, CallDateTime, '2016-05-10') = 0 ORDER BY CallDateTime ASC";
foreach ($dbh->query($sql) as $row)
    {
        if($row['CallID'] != $last) {
            $callDataArray[] = array("CallID" => $row['CallID'], "CallDateTime" => $row['CallDateTime'], "IsLost" => $row['IsLost'], "IsAnswered" => $row['IsAnswered']);
        }
        $last = $row['CallID'];
    }

The desired output would be like the following format somehow (data not correct)

Date/Time           IsAnswered IsLost
May 10 2016 08:30   7          0
May 10 2016 09:00   8          0
May 10 2016 09:30   14         0
May 10 2016 10:00   11         0
May 10 2016 10:30   11         0
May 10 2016 11:00   13         0
May 10 2016 11:30   12         0
May 10 2016 12:00   11         0
May 10 2016 12:30   8          0
May 10 2016 13:00   20         0
May 10 2016 13:30   9          0
May 10 2016 14:00   10         0
May 10 2016 14:30   12         0
May 10 2016 15:00   8          0
May 10 2016 15:30   14         0
May 10 2016 16:00   12         0
May 10 2016 16:30   11         0

Any ideas on how to in PHP or even MSSQL PDO ? Any help would be awesome - Cheers

  • 写回答

3条回答 默认 最新

  • duanfaxin7014 2016-05-11 03:08
    关注

    You can use the following code. It may not be perfect but it will give you an idea on how to proceed further.

    function roundToNearest30($timestring) {
        $minutes = date('i', $timestring);
        return sprintf("%02d", $minutes - ($minutes % 30));
    }
    
    $totalArray = array();
    $lastTime = NULL;
    foreach($callDataArray as $callData){
        $timeString = strtotime($callData['CallDateTime']);
        $currentHour = date('H', $timeString);
        $nearest30Minutes = roundToNearest30($timeString);
        $nearestTime = "{$currentHour}:{$nearest30Minutes}";
    
        //create array key based on nearest 30 minutes
        if (!isset($totalArray[$nearestTime])){
            //add the array element as it does not exist
            $totalArray[$nearestTime] = array("TotalLost" => $callData['IsLost'], "TotalAnswered" => $callData['IsAnswered']);
        }else{
            //update they array element with previous data
            $totalArray[$nearestTime]["TotalLost"] += $callData['IsLost'];
            $totalArray[$nearestTime]["TotalAnswered"] += $callData['IsAnswered'];
        }
    }
    
    foreach($totalArray as $nearest30 => $data){
        echo "{$nearest30}, Total Lost: {$data['TotalLost']}, Total Answered: {$data['TotalAnswered']}";
        echo "<br>";
    }
    

    Output:
    08:30, Total Lost: 0, Total Answered: 1
    09:00, Total Lost: 0, Total Answered: 6
    09:30, Total Lost: 0, Total Answered: 4
    10:00, Total Lost: 0, Total Answered: 2
    10:30, Total Lost: 0, Total Answered: 5
    11:00, Total Lost: 0, Total Answered: 6
    11:30, Total Lost: 0, Total Answered: 1
    12:00, Total Lost: 0, Total Answered: 1
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决