php 实现groupby

在 PHP 中实现 GROUP BY 功能

使用 SQL 查询实现 GROUP BY

通过 SQL 语句的 GROUP BY 子句可以直接在数据库层面实现分组功能。适用于从数据库中提取已分组的数据。

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

// 执行 GROUP BY 查询
$stmt = $pdo->query('SELECT department, COUNT(*) as employee_count FROM employees GROUP BY department');

// 获取分组结果
$groupedData = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 输出结果
foreach ($groupedData as $row) {
    echo "Department: " . $row['department'] . ", Count: " . $row['employee_count'] . "\n";
}

使用数组函数实现 GROUP BY

对于已存在于 PHP 数组中的数据，可以通过数组函数实现类似 GROUP BY 的功能。

$employees = [
    ['id' => 1, 'name' => 'John', 'department' => 'HR'],
    ['id' => 2, 'name' => 'Jane', 'department' => 'IT'],
    ['id' => 3, 'name' => 'Doe', 'department' => 'HR'],
    ['id' => 4, 'name' => 'Smith', 'department' => 'IT']
];

$grouped = [];
foreach ($employees as $employee) {
    $dept = $employee['department'];
    if (!isset($grouped[$dept])) {
        $grouped[$dept] = [];
    }
    $grouped[$dept][] = $employee;
}

// 输出分组结果
foreach ($grouped as $dept => $emps) {
    echo "Department: $dept\n";
    foreach ($emps as $emp) {
        echo "- " . $emp['name'] . "\n";
    }
}

使用 Laravel 集合的 groupBy 方法

如果使用 Laravel 框架，可以利用其强大的集合功能实现分组。

$employees = collect([
    ['id' => 1, 'name' => 'John', 'department' => 'HR'],
    ['id' => 2, 'name' => 'Jane', 'department' => 'IT'],
    ['id' => 3, 'name' => 'Doe', 'department' => 'HR'],
    ['id' => 4, 'name' => 'Smith', 'department' => 'IT']
]);

$grouped = $employees->groupBy('department');

// 输出分组结果
$grouped->each(function ($group, $key) {
    echo "Department: $key\n";
    $group->each(function ($item) {
        echo "- " . $item['name'] . "\n";
    });
});

实现高级分组统计

对于需要同时进行分组和统计的情况，可以结合数组函数实现更复杂的分组逻辑。

$orders = [
    ['id' => 1, 'product' => 'A', 'amount' => 100, 'date' => '2023-01-01'],
    ['id' => 2, 'product' => 'B', 'amount' => 200, 'date' => '2023-01-01'],
    ['id' => 3, 'product' => 'A', 'amount' => 150, 'date' => '2023-01-02'],
    ['id' => 4, 'product' => 'B', 'amount' => 250, 'date' => '2023-01-02']
];

$stats = [];
foreach ($orders as $order) {
    $product = $order['product'];
    if (!isset($stats[$product])) {
        $stats[$product] = [
            'total' => 0,
            'count' => 0,
            'dates' => []
        ];
    }
    $stats[$product]['total'] += $order['amount'];
    $stats[$product]['count']++;
    $stats[$product]['dates'][] = $order['date'];
}

// 输出统计结果
foreach ($stats as $product => $data) {
    echo "Product: $product\n";
    echo "Total Sales: " . $data['total'] . "\n";
    echo "Order Count: " . $data['count'] . "\n";
    echo "Order Dates: " . implode(', ', $data['dates']) . "\n\n";
}

这些方法涵盖了从数据库查询到内存数据处理的不同场景，可以根据具体需求选择最适合的实现方式。