How to implement data grouping and statistical functions in PHP

How to implement data grouping and statistical functions in PHP

In the actual development process, we often encounter the need for data grouping and statistics. Whether it is grouping statistics on data in a database or operating data in an array, PHP provides a wealth of functions and methods to meet our needs.

The following will demonstrate how to group and count database and array data in PHP.

Data grouping and statistics in the database

Suppose we have a student performance table with the following structure:

CREATE TABLE `scores` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `subject` varchar(50) NOT NULL,
  `score` decimal(5,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We want to calculate the total score and average of each student point. You can use the following code for grouping and statistics:

// 连接数据库
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');

// 查询数据
$query = "SELECT student_id, SUM(score) AS total, AVG(score) AS average FROM scores GROUP BY student_id";
$stmt = $pdo->prepare($query);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 输出结果
foreach ($results as $result) {
    echo "学生ID：" . $result['student_id'] . "，总分：" . $result['total'] . "，平均分：" . $result['average'] . "<br>";
}

In the above code, we use PDO to connect to the database, then execute the query statement and store the results in the $results array. Finally, the total score and average score of each student are output through the foreach loop.

Grouping and Statistics of Array Data

Suppose we have an array containing a group of students. Each student has three attributes: name, age and grade, as follows:

$students = [
    ['name' => '张三', 'age' => 18, 'score' => 85],
    ['name' => '李四', 'age' => 19, 'score' => 92],
    ['name' => '王五', 'age' => 20, 'score' => 78],
    ['name' => '赵六', 'age' => 18, 'score' => 89],
    ['name' => '钱七', 'age' => 19, 'score' => 95],
    ['name' => '孙八', 'age' => 20, 'score' => 82],
];

We want to group students by age and calculate the average and highest scores of students in each group. The following is the code to implement grouping and statistics:

// 按照年龄分组
$groups = [];
foreach ($students as $student) {
    $age = $student['age'];
    if (!isset($groups[$age])) {
        $groups[$age] = [];
    }
    $groups[$age][] = $student;
}

// 统计每个分组中的平均分和最高分
foreach ($groups as $age => $group) {
    $total = 0;
    $maxScore = 0;
    foreach ($group as $student) {
        $total += $student['score'];
        if ($student['score'] > $maxScore) {
            $maxScore = $student['score'];
        }
    }
    $average = $total / count($group);
    echo "年龄：" . $age . "，平均分：" . $average . "，最高分：" . $maxScore . "<br>";
}

In the above code, we first use the foreach loop to group students according to age and store them in the $groups array. Then it traverses each group through a nested foreach loop, calculates the average score and the highest score, and finally outputs the result.

Through the above code examples, we can see how to use PHP to implement data grouping and statistical functions. Whether you are operating on data in a database or on data in an array, you can choose the appropriate method to implement it according to your needs. These functions are very common in actual development and are widely used in fields such as data analysis and report generation.

The above is the detailed content of How to implement data grouping and statistical functions in PHP. For more information, please follow other related articles on the PHP Chinese website!