doubi4531 2018-02-04 08:07
浏览 61
已采纳

PHP循环X次基于$ interval,但如何处理休息值?

[UPDATED with a followup question see edit4]

So I have a function which returns score average of X number of consecutive dates. But I like to handle the rest value.

For example if I have found the following rows (based on some random data and the hardcoded personid = 133)

based on my random sample data the query returns

the average output I get when callling the PHP function getConsecutiveInterval(4);

Total Found: 10
Num Of Loops: 2.5
2.75
3.5

and I expect the averages to be

2.75, 3.5 and 3.5(based on the rest 2 dates not 4)

And when I use getConsecutiveInterval(3); I expect the average value to be

3.33, 3.33, 2.66 and 4 (based on 1 date)

and I got

Total Found: 10
Num Of Loops: 3.3333333333333
3.3333333333333
3.3333333333333
2.6666666666667

So how can I return / handle the rest rows as an average?

This is the php function:

function getConsecutiveInterval($interval) {
    //$interval Num Of Consecutive Days
    global $conn;
    $offset = '';
    // Query is the same and at the end of it you include LIMIT to be controlled by the loop.
        $q = "SELECT a.purdate, b.score, a.status FROM records a INNER JOIN scores2 b ON a.purdate = b.date AND a.personid = b.personid WHERE a.personid = 133 AND a.status IN('P','T') ORDER BY purdate ASC, score DESC ";

    //Total Found Use To Divide by
        $result = mysqli_query($conn, $q);
        $num_rows = mysqli_num_rows($result);
        echo 'Total Found: '.$num_rows.'<br/>';
        $numOfLoops = $num_rows/$interval;
        echo 'Num Of Loops: '. round($numOfLoops).'<br/>';
// For loop will control the results sets divided by 4 
    for ($i = 1; $i <= $numOfLoops; $i++) {
        // To add the offset after the first set
        if ($offset > 0) {
            $limitValues =  $offset. ", " . $interval . " ";
        } else {
            $limitValues = $interval;
        }
        $sqlquery = $q . "  LIMIT " . $limitValues;
        $avg = 0;
        $total = 0;
        //Total Found Use To Divide by
        $result = mysqli_query($conn, $q);
        $num_rows = mysqli_num_rows($result);
        //echo 'Total Found: '.$num_rows;
        foreach (mysqli_query($conn, $sqlquery) as $results) {
            // Do Something
            $total += $results['score'];
            $avg = $total / $interval;
            print_r($results['purdate'].' <-> ');
 //see [edit2] need only the first date in assoc array?? $results['purdate'][0] doesn't work
        }
        echo $avg . '<br/>';
        $offset += $interval;
    } 
}

[edit1] i add some code to get the startDate for each interval also some code for start to handle the rest value

function getConsecutiveInterval($interval) {
    //$interval Num Of Consecutive Days
    global $conn;
    $offset = '';
    // Query is the same and at the end of it you include LIMIT to be controlled by the loop.
        $q = "SELECT a.purdate, b.score, a.status FROM records a INNER JOIN scores2 b ON a.purdate = b.date AND a.personid = b.personid WHERE a.personid = 133 AND a.status IN('P','T') ORDER BY purdate ASC, score DESC ";

    //Total Found Use To Divide by
        $result = mysqli_query($conn, $q);
        $num_rows = mysqli_num_rows($result);
        echo 'Total Found: '.$num_rows.'<br/>';
        $numOfLoops = floor($num_rows/$interval);
        echo 'Num Of Loops: '. $numOfLoops.'<br/>';
        $rest = $num_rows - ($interval*$numOfLoops);
        echo 'Rest: '.$rest.'<br/>'; 
        $arrFound = [];
// For loop will control the results sets divided by 4 
    for ($i = 1; $i <= $numOfLoops; $i++) {
        // To add the offset after the first set
        if ($offset > 0) {
            $limitValues =  $offset. ", " . $interval . " ";
        } else {
            $limitValues = $interval;
        }
        $sqlquery = $q . "  LIMIT " . $limitValues;
        $avg = 0;
        $total = 0;
        //Total Found Use To Divide by
        $result = mysqli_query($conn, $q);
        $num_rows = mysqli_num_rows($result);
        //echo 'Total Found: '.$num_rows;
        $j = 0;

        foreach (mysqli_query($conn, $sqlquery) as $results) {
            if ($j == 0) {
                 echo $results['purdate'];
                 $arrFound[$i]['date'] = $results['purdate'];
            }
            // Do Something
            $total += $results['score'];
            $avg = $total / $interval;

            $j++;
        }
        $arrFound[$i]['avg'] = $avg;
        echo $avg . '<br/>';
        $offset += $interval;
    } 
    print_r($arrFound);
}

[edit2] In the end I also need the start purdate of each interval to be returned in the array as well.

[edit3]

For another table I need something similar but now I need to output also per code. The query looks like this :

$q = "SELECT date, code
      FROM Records2 
      WHERE studentid = 131
      AND date >= '2017-02-10' AND date <= '2017-06-13'
      AND code IN ('P','T','L','U') 
      ORDER BY date ASC;";

and with the help of astrangeloop I like this to output per code as well. This is to plot a chart where x-axis will be the start date and y-axis will plot the number of P, T, L or U. (well mostly P and T)

example chart would be: stacked graph example

  $intervals = array();
    foreach (array_chunk($records, $interval_size) as 
    $interval_records) {
        $first_record = reset($interval_records);
        $interval = array(
            'start' => $first_record['date'],
            'codes' => array_column($interval_records, 'code'),
            /*Is It Possible to Order and/or Calculate with the 
        code values? Like another array*/,
        );
        $intervals[] = $interval;
    }
    return $intervals;

At the moment this returns:

[5] => Array
        (
            [start] => 2017-03-23
            [codes] => Array
                (
                    [0] => P
                    [1] => P
                    [2] => P
                    [3] => P
                    [4] => P
                )

            [count] => 5
        )

And I like the js output which looks likes this:

['start', #num_of_codes_P, #num_of_codes_T, #num_of_codes_L, #num_of_codes_U,'count'],
['start', #num_of_codes_P, #num_of_codes_T, #num_of_codes_L, #num_of_codes_U,'count'],

For future reference I think I found a workable solution (not using array_count_values because of Notice error) and this is how I get the codes:

$records = getRecordsWithIntervals($records, 5);
foreach ($records as $record) {
   echo count(array_keys($record['codes'], 'P')).', '.count(array_keys($record['codes'], 'U')).'<br/>';
}

[edit4] I should ask this in a seperate question, but how do I add the ability to set the interval_size to weekly, monthly or customize quarters (custom beginDate-endDate = divide a educational year in 4 parts or more)

展开全部

  • 写回答

1条回答 默认 最新

  • dphj737575 2018-02-04 09:33
    关注

    As you already have a query to select all the scores from the database at once, one possible approach might be to do this in php rather than by querying the database again for each interval. You can fetch everything from the database in one go, then use php's array_chunk() function to slice an array into the intervals you're interested in and calcluate averages etc. from there:

    function getRecords()
    {
        global $conn;
        $sql = "
            SELECT a.purdate
                 , b.score
                 , a.status
              FROM records a 
              JOIN scores2 b 
                ON a.purdate = b.date 
               AND a.personid = b.personid 
             WHERE a.personid = 133 
               AND a.status IN('P','T') 
             ORDER 
                BY purdate ASC
                 , score DESC
                 ;
        ";
        $result = mysqli_query($conn, $sql);
        $records = mysqli_fetch_all($result, MYSQLI_ASSOC);
        mysqli_free_result($result);
        return $records;
    }
    
    function getIntervalsWithAverageScores(array $records, $interval_size)
    {
        $intervals = array();
        foreach (array_chunk($records, $interval_size) as $interval_records) {
            $first_record = reset($interval_records);
            $interval = array(
                'start' => $first_record['purdate'],
                'scores' => array_column($interval_records, 'score'),
                'count' => count($interval_records),
                'average' => array_sum(array_column($interval_records, 'score')) / count($interval_records),
            );
            $intervals[] = $interval;
        }
        return $intervals;
    }
    

    Using these functions as follows:

    $records = getRecords();
    var_dump(getIntervalsWithAverageScores($records, 4));
    var_dump(getIntervalsWithAverageScores($records, 3));
    

    Will give output like this (tested in php 5.6):

    array(3) {
      [0]=>
      array(4) {
        ["start"]=>
        string(10) "2015-06-11"
        ["scores"]=>
        array(4) {
          [0]=>
          string(1) "5"
          [1]=>
          string(1) "2"
          [2]=>
          string(1) "3"
          [3]=>
          string(1) "1"
        }
        ["count"]=>
        int(4)
        ["average"]=>
        float(2.75)
      }
      [1]=>
      array(4) {
        ["start"]=>
        string(10) "2016-01-02"
        ["scores"]=>
        array(4) {
          [0]=>
          string(1) "5"
          [1]=>
          string(1) "4"
          [2]=>
          string(1) "4"
          [3]=>
          string(1) "1"
        }
        ["count"]=>
        int(4)
        ["average"]=>
        float(3.5)
      }
      [2]=>
      array(4) {
        ["start"]=>
        string(10) "2016-10-13"
        ["scores"]=>
        array(2) {
          [0]=>
          string(1) "3"
          [1]=>
          string(1) "4"
        }
        ["count"]=>
        int(2)
        ["average"]=>
        float(3.5)
      }
    }
    // second internal array omitted ...
    

    You can see the last entry in the array has a count of 2 and only 2 scores, rather than 4.

    As an aside, if you will be using user input instead of hard-coded values in future, please look in to using prepared statements to protect yourself from sql injection.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥50 数据库开发问题求解答
  • ¥15 安装anaconda时报错
  • ¥20 如何用Python处理单元格内连续出现的重复词语?
  • ¥15 小程序有个导出到插件方式,我是在分包下引入的插件,这个export的路径对吗,我看官方文档上写的是相对路径
  • ¥20 希望有人能帮我完成这个设计( *ˊᵕˋ)
  • ¥100 将Intptr传入SetHdevmode()将Intptr传入后转换为DEVMODE的值与外部代码不一致
  • ¥50 基于ERA5数据计算VPD
  • ¥15 寻找杂志《Tornatzky, L. G., & Fleischer, M. (1990). The Processes of Technological Innovation. 》
  • ¥15 前台多人编辑时怎么让每个人保存刷新都互不干扰
  • ¥20 如何用Python删除单元格内连续出现的重复词?