PHP如何基于时间戳统计各时间区间页面访问量并计数

• 搭建管理员面板，展示页面总浏览量数据
• 已在用户切换页面、刷新页面时，存储了每次点击行为对应的时间戳
• 需要基于已存储的时间戳，统计对应日期的用户访问量，最终对接图表组件完成展示

首先通过PHP时间函数手动计算过去数天的时间节点，初始代码如下：

$Today = time();

$EightDaysSec  = 691200;
$SevenDaysSec  = 604800;
$SixDaysSec    = 518400;
$FiveDaysSec   = 432000;
$FourDaysSec   = 345600;
$ThreeDaysSec  = 259200;
$TwoDaysSec    = 172800;
$OneDaySec     = 86400;

$FoundEightDaysAgo  = $Today - $EightDaysSec;
$FoundSevenDaysAgo  = $Today - $SevenDaysSec;
$FoundSixDaysAgo    = $Today - $SixDaysSec;
$FoundFiveDaysAgo   = $Today - $FiveDaysSec;
$FoundFourDaysAgo   = $Today - $FourDaysSec;
$FoundThreeDaysAgo  = $Today - $ThreeDaysSec;
$FoundTwoDaysAgo    = $Today - $TwoDaysSec;
$FoundOneDaysAgo    = $Today - $OneDaySec;

查询数据库拿到所有存储时间戳的记录数组后，通过foreach()循环遍历数组时，仅能写出时间区间的判断逻辑，不知道怎么对符合区间条件的记录做计数、输出对应统计值，初始查询遍历代码如下：

$Query          = $db->prepare('SELECT * FROM new_user_last ');
$Query->execute();
$QueryNumber    = $Query->rowCount();
$QueryRecords   = $Query->fetchAll();

foreach ($QueryRecords as $Records ) {
  
  $UserDate = $Records['Date'];

  if($UserDate >= $FoundSevenDaysAgo and $UserDate <= $FoundSixDaysAgo){

  // 条件成立后，无法完成符合条件值的统计与展示

  }

}

需要实现的效果是筛选出落在各时间区间内的记录并完成计数，最终把统计结果作为访问量数据传入图表组件渲染。

不再在PHP层遍历判断时间区间，直接通过SQL时间函数在查询层完成时间区间匹配，通过累计区间总数做差值得到单日访问量，最终直接生成图表组件需要的数据格式，代码如下：

// 查询近7天累计访问量
$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 7 DAY ORDER BY `Date`');
$Query->execute();
$SeventhDay    = $Query->rowCount();

// 查询近6天累计访问量
$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 6 DAY ORDER BY `Date`');
$Query->execute();
$SixthDay    = $Query->rowCount();

// 查询近5天累计访问量
$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 5 DAY ORDER BY `Date`');
$Query->execute();
$FifthDay    = $Query->rowCount();

// 查询近4天累计访问量
$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 4 DAY ORDER BY `Date`');
$Query->execute();
$FourthDay    = $Query->rowCount();

// 查询近3天累计访问量
$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 3 DAY ORDER BY `Date`');
$Query->execute();
$ThirdDay    = $Query->rowCount();

// 查询近2天累计访问量
$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 2 DAY ORDER BY `Date`');
$Query->execute();
$SecondDay    = $Query->rowCount();

// 查询近1天累计访问量
$Query = $db->prepare('SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 1 DAY ORDER BY `Date`');
$Query->execute();
$FirstDay    = $Query->rowCount();

// 计算单日访问量
$Seven   = $SeventhDay - $SixthDay;
$Six     = $SixthDay - $FifthDay;
$Five    = $FifthDay - $FourthDay;
$Four    = $FourthDay - $ThirdDay;
$Three   = $ThirdDay - $SecondDay;
$Two     = $SecondDay- $FirstDay;
$One     = $FirstDay; 

// 生成图表所需的数据点格式
$dataPoints = array(
    array("y" => $Seven, "label" => $DayLast),
    array("y" => $Six, "label" => $DaySix),
    array("y" => $Five, "label" => $DayFive),
    array("y" => $Four, "label" => $DayFour),
    array("y" => $Three, "label" => $DayThree),
    array("y" => $Two, "label" => $DayTwo),
    array("y" => $One, "label" => $DayFirst)
);

内容的提问来源于stack exchange，提问作者erday