MySQL计算指定月份前6个月平均值失败，如何用PHP实现？

Let's tackle this problem together! First, let's break down why your original MySQL query isn't doing what you need, then walk through two solid ways to implement this calculation with PHP.

Why Your Original Query Fails #

Your original SQL has two key issues that prevent it from working as intended:

SELECT Avg(dt.number_of_employees) FROM (SELECT number_of_employees FROM my_table ORDER BY date DESC LIMIT 1, 6) dt;

1. Unfiltered range: LIMIT 1,6 just skips the newest record and grabs the next 6. If your date field has gaps (missing months) or isn't strictly 1 entry per month, this will pull the wrong data.
2. No target month alignment: It doesn't anchor to a specific target month—you can't specify which month's preceding 6 months you want to calculate.

Solution 1: Fix the SQL + Execute via PHP (Recommended) #

This approach leverages the database to do the heavy lifting (more efficient than processing in PHP), and uses PHP to dynamically calculate the correct date range for your target month.

Step-by-Step Code #

Assume your target month is something like 2024-04 (format: Y-m), and your my_table has:

• A date field (either DATE/DATETIME or a string like 2024-04 for the month)
• A number_of_employees field with the count for that month

<?php
// Define your target month (adjust this as needed)
$targetMonth = "2024-04";

// Calculate the date range for the 6 months before the target
$targetDate = new DateTime($targetMonth . "-01");
// Start date: 6 months before the first day of the target month
$startDate = $targetDate->modify("-6 months")->format("Y-m-d");
// End date: The day before the first day of the target month (to exclude the target month itself)
$targetDate->modify("first day of this month"); // Reset to target month's start
$endDate = $targetDate->modify("-1 day")->format("Y-m-d");

// Database connection (use your own credentials)
$dsn = "mysql:host=localhost;dbname=your_database;charset=utf8mb4";
$dbUser = "your_username";
$dbPass = "your_password";

try {
    // Initialize PDO connection
    $pdo = new PDO($dsn, $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // If your `date` field is a month string (e.g., '2024-04'), use this SQL instead:
    // $sql = "SELECT AVG(number_of_employees) AS avg_employees FROM my_table WHERE date >= :start_month AND date < :target_month";
    // $startMonth = $targetDate->modify("-6 months")->format("Y-m");

    // SQL for DATE/DATETIME field
    $sql = "SELECT AVG(number_of_employees) AS avg_employees 
            FROM my_table 
            WHERE date BETWEEN :start_date AND :end_date";

    // Prepare and execute query
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":start_date", $startDate);
    $stmt->bindParam(":end_date", $endDate);
    $stmt->execute();

    // Fetch the result
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $average = $result["avg_employees"] ?? 0;

    echo "Average number of employees over the 6 months before {$targetMonth}: " . number_format($average, 2);
} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage();
}
?>

Solution 2: Fetch Data First, Calculate Average in PHP #

If you need extra processing (like validating missing months or transforming data), you can fetch all the relevant employee counts first, then compute the average in PHP.

<?php
$targetMonth = "2024-04";
$targetDate = new DateTime($targetMonth . "-01");
$startMonth = $targetDate->modify("-6 months")->format("Y-m");

// Database connection (same as above)
try {
    $pdo = new PDO($dsn, $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT number_of_employees 
            FROM my_table 
            WHERE date >= :start_month AND date < :target_month
            ORDER BY date ASC";

    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":start_month", $startMonth);
    $stmt->bindParam(":target_month", $targetMonth);
    $stmt->execute();

    // Get all employee counts as an array
    $employeeCounts = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

    // Handle empty data case
    if (empty($employeeCounts)) {
        echo "No employee data found for the specified 6-month range.";
    } else {
        $average = array_sum($employeeCounts) / count($employeeCounts);
        echo "Average number of employees over the 6 months before {$targetMonth}: " . number_format($average, 2);
    }
} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage();
}
?>

Important Notes #

• If some months are missing data, you might want to generate a list of consecutive months and LEFT JOIN with your table to include 0 for missing counts—otherwise, the average will only account for months with existing data.
• Always use prepared statements (like the PDO examples above) to avoid SQL injection risks.

内容的提问来源于stack exchange，提问作者Ozan Yıldırım