php - 在电子表格/Excel 中正确循环查询以按用户分组

Question

我目前正在尝试修复此脚本，以根据来自 mySql 查询的当前数据创建带有公式的电子表格。查询完全符合预期，我终于得到了循环来正确打印行。

这是一个看起来像这样的图像:

我无法将其转换为创建我需要的公式。基本上，我需要计算 C、D、E 和 F 4 个主要列中数字的平均值、中位数和众数。

我相信这需要通过关联数组中的索引来完成，但我并不肯定。如图所示，B 列是用户 ID，因此我需要按其进行分组。基本上，就是说如果这是用户 ID 48 的最后一条记录，则为 =average()、=Mean() 和 =median() 创建一个单元格并继续为其他用户创建单元格。在下一个用户之前首先，我想要一个用于 4 列(C、D、E 和 F)的均值、中位数和众数的单元格。

我之前有这个数组和 while 循环，但我无法让它迭代并且它继续打印相同的记录:

          $conn = new mysqli($servername, $username, $password, $dbName);

            // Check connection
            if ($conn->connect_error) {
                die("Connection failed: " . $conn->connect_error);
            }
            echo "Connected successfully";

            $sql = "
            select 
             concat(u.first_name, '  ', u.last_name) as Name,
             t.ext_id as ID,
             t.total_talk_time_minutes as TalkTime,
             t.total_outbound as Outbound,
             t.total_inbound as Inbound,
             t.dealers_contacted as Dealers,
             t.date_of_report as Date
            From
                ambition.ambition_totals t
            INNER JOIN
                ambition.ambition_users u
            ON 
                t.extension = u.extension
            where
                date_of_report 
            between
                curdate() - interval 5 day and curdate()"  or die(mysqli_error( $conn));

            $result=mysqli_query($conn,$sql);

            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet ->getColumnDimension('A') -> setAutoSize(true);
            $sheet ->getColumnDimension('B') -> setAutoSize(true);
            $sheet ->getColumnDimension('C') -> setAutoSize(true);
            $sheet ->getColumnDimension('D') -> setAutoSize(true);
            $sheet ->getColumnDimension('E') -> setAutoSize(true);
            $sheet ->getColumnDimension('F') -> setAutoSize(true);
            $sheet ->getColumnDimension('G') -> setAutoSize(true);

            $sheet->setCellValue('A1', 'Name');
            $sheet->setCellValue('B1', 'User ID');
            $sheet->setCellValue('C1', 'Talk Time Per Call');
            $sheet->setCellValue('D1', 'Outbound Calls');
            $sheet->setCellValue('E1', 'Inbound Calls');
            $sheet->setCellValue('F1', 'Dealer Contacts');
            $sheet->setCellValue('G1', 'Date');


            $data = [];

            while ($row = mysqli_fetch_assoc($result)) {
                if (!isset($data[$row['ID']]))
                    $data[$row['ID']] = ['records' => [], 'mmm' => []];
                $data[$row['ID']]['records'] = $row;
            }

            $analyze = ['TalkTime', 'Outbound', 'Inbound', 'Dealers'];

             foreach ($data AS $user) {
                 foreach ($analyze AS $attr) {
                     $set = []; 
                     foreach ($user['records'] AS $record){
                        $set[] = $record[$attr];
                        $user['mmm'][$attr] = [];
                        $user['mmm'][$attr]['mean'] = calcMean($set);
                        $user['mmm'][$attr]['median'] = calcMedian($set);
                        $user['mmm'][$attr]['mode'] = calcMode($set);
                    }
                }
            }

            var_dump($record);


            mysqli_close($conn);

            $writer = new xlsx($spreadsheet);
            $writer->save('Coaching Report - Weekly.xlsx');

Answer 1

我希望包含数据和分析的用户数组是最好的方法。

$data = [];
while ($row = mysqli_fetch_assoc($result)) {
    // New user? Build data structure
    if (!isset($data[$row['ID']]))
        $data[$row['ID']] = ['records' => [], 'mmm' => []];
    // Collect rows
    $data[$row['ID']]['records'][] = $row;
}

然后迭代用户条目以计算平均值、中位数和众数 (mmm):

$analyze = ['TalkTime', 'Outbound', 'Inbound', 'Dealers'];
foreach ($data AS &$user) {
    foreach ($analyze AS $attr) {
        $set = []; // Set of data for this attribute
        foreach ($user['records'] AS $record)
            $set[] = $record[$attr];
        $user['mmm'][$attr] = [];
        $user['mmm'][$attr]['mean'] = calcMean($set);
        $user['mmm'][$attr]['median'] = calcMedian($set);
        $user['mmm'][$attr]['mode'] = calcMode($set);
    }
}

然后只需决定如何在工作表上输出数据即可。