使用数据库数据计算每日增量：SQL窗口函数与PHP实现

SELECT DISTINCT
    DATE(`timestamp`) AS day,
    FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` ASC) AS start_day_count,
    FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) AS end_day_count
FROM your_table_name;

<?php
// 假设你已经建立了PDO连接 $pdo
// $dsn = 'mysql:host=localhost;dbname=your_database_name;charset=utf8mb4';
// $username = 'your_username';
// $password = 'your_password';
// try {
//     $pdo = new PDO($dsn, $username, $password);
//     $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// } catch (PDOException $e) {
//     die("数据库连接失败: " . $e->getMessage());
// }

// 查询特定日期（例如 '2021-11-21'）的增量
$targetDate = '2021-11-21'; 
$query = "
    SELECT 
        FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` ASC) AS start_day_count,
        FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) AS end_day_count
    FROM your_table_name 
    WHERE DATE(`timestamp`) = :targetDate
    LIMIT 1; -- 限制为1行，因为对于特定日期，结果是唯一的
";

$stmt = $pdo->prepare($query);
$stmt->bindParam(':targetDate', $targetDate);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

if ($row) {
    $startCount = $row['start_day_count'];
    $endCount = $row['end_day_count'];
    $dailyIncrease = $endCount - $startCount;
    echo "在 " . $targetDate . "，计数增加了: " . $dailyIncrease . "\n";
} else {
    echo "在 " . $targetDate . " 没有找到数据或无法计算增量。\n";
}

// 如果需要获取所有日期的增量，可以移除WHERE子句和LIMIT 1，并循环处理结果
// $queryAllDays = "
//     SELECT DISTINCT
//         DATE(`timestamp`) AS day,
//         FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` ASC) AS start_day_count,
//         FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) AS end_day_count
//     FROM your_table_name;
// ";
// $stmtAllDays = $pdo->query($queryAllDays);
// while ($row = $stmtAllDays->fetch(PDO::FETCH_ASSOC)) {
//     $day = $row['day'];
//     $startCount = $row['start_day_count'];
//     $endCount = $row['end_day_count'];
//     $dailyIncrease = $endCount - $startCount;
//     echo "在 " . $day . "，计数增加了: " . $dailyIncrease . "\n";
// }
?>

<?php
// 假设你已经建立了mysqli连接 $mysqli
// $mysqli = new mysqli("localhost", "your_username", "your_password", "your_database_name");
// if ($mysqli->connect_errno) {
//     die("数据库连接失败: " . $mysqli->connect_error);
// }

// 查询特定日期（例如 '2021-11-21'）的增量
$targetDate = '2021-11-21'; 
$query = "
    SELECT 
        FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` ASC) AS start_day_count,
        FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) AS end_day_count
    FROM your_table_name 
    WHERE DATE(`timestamp`) = '$targetDate'
    LIMIT 1;
";

$result = mysqli_query($mysqli, $query);

if ($result && mysqli_num_rows($result) > 0) {
    $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
    $startCount = $row['start_day_count'];
    $endCount = $row['end_day_count'];
    $dailyIncrease = $endCount - $startCount;
    echo "在 " . $targetDate . "，计数增加了: " . $dailyIncrease . "\n";
    mysqli_free_result($result);
} else {
    echo "在 " . $targetDate . " 没有找到数据或无法计算增量。\n";
}

// 如果需要获取所有日期的增量
// $queryAllDays = "
//     SELECT DISTINCT
//         DATE(`timestamp`) AS day,
//         FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` ASC) AS start_day_count,
//         FIRST_VALUE(`count`) OVER (PARTITION BY DATE(`timestamp`) ORDER BY `timestamp` DESC) AS end_day_count
//     FROM your_table_name;
// ";
// $resultAllDays = mysqli_query($mysqli, $queryAllDays);
// if ($resultAllDays) {
//     while ($row = mysqli_fetch_array($resultAllDays, MYSQLI_ASSOC)) {
//         $day = $row['day'];
//         $startCount = $row['start_day_count'];
//         $endCount = $row['end_day_count'];
//         $dailyIncrease = $endCount - $startCount;
//         echo "在 " . $day . "，计数增加了: " . $dailyIncrease . "\n";
//     }
//     mysqli_free_result($resultAllDays);
// }
// mysqli_close($mysqli);
?>