php - 如何从mysql获取每小时的数据？

Question

我有一个 mysql 数据库，其中包含我的客户详细信息，我想在数组中显示每小时的访问量、页面 View 。我在下面给出了 mysql 查询。

              <?php 
$siteid=$_GET["site"];

          $DB = mysqli_connect('localhost','username','password','databasename');
          $Q = "SELECT time, COUNT(*), pw FROM clientstats WHERE siteid='$siteid' AND time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 26970 SECOND)) GROUP BY HOUR( FROM_UNIXTIME( `time`) ) ORDER BY time ASC";
          $R = mysqli_query($DB,$Q);
          //start loop
          //while or foreach
          while($row = mysqli_fetch_assoc($R)){

date_default_timezone_set('Asia/Kolkata');
$timestamp = $row['time'];
$timee = date('H', $timestamp);
$times = $row['COUNT(*)']+$row['wh'];


            echo ",['$timee', {$row['COUNT(*)']}, $times]\r\n";

          }
          ?>

其中 26970 是从午夜到现在经过的秒数。这段代码工作正常并输出一个像这样的数组

,['02', 2, 3] ,['02', 1, 2] ,['04', 1, 2] ,['05', 1, 2] ,['06', 2, 3]

这个数组结构是 ['HOUR', 访问次数, pw] 但问题是这不会连续显示每个小时的值，如 1,2,3,4,5,6.... 等。我想要显示每个小时的值。如果该小时的值为零，那么我想将该小时值输出为 ['hour', 0, 0]，此外，第二个小时的上述输出加倍。如何纠正它。

请帮助我，提前致谢。

Answer 1

要使用 SELECT 语句获取从 MySQL 返回的结果，需要有一个行源来返回我们从中获取计数的表中“缺失”的小时值。

(从查询中获取此结果只是一种选择；我们可以采用不同的方法，并在客户端中执行此操作。例如，用我们想要的小时数填充数组，然后运行原始查询，并分配计数到适当的匹配数组元素。这个答案演示了我们如何让 MySQL 返回指定的结果。)

作为我们如何做到这一点的一个示例，下面是一个返回 24 行整数值 0 到 23 的查询。

         SELECT 00 AS hr     UNION ALL SELECT 01 UNION ALL SELECT 02 UNION ALL SELECT 03
         UNION ALL SELECT 04 UNION ALL SELECT 05 UNION ALL SELECT 06 UNION ALL SELECT 07
         UNION ALL SELECT 08 UNION ALL SELECT 09 UNION ALL SELECT 10 UNION ALL SELECT 11
         UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
         UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23

我们可以将该查询包装在括号中，并在另一个查询中引用它来代替表。

SELECT i.hr
  FROM ( -- 0 thru 23
         SELECT 00 AS hr     UNION ALL SELECT 01 UNION ALL SELECT 02 UNION ALL SELECT 03
         UNION ALL SELECT 04 UNION ALL SELECT 05 UNION ALL SELECT 06 UNION ALL SELECT 07
         UNION ALL SELECT 08 UNION ALL SELECT 09 UNION ALL SELECT 10 UNION ALL SELECT 11
         UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
         UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
       ) i
  ORDER BY i.hr

还有其他方法可以返回整数集。此示例只是查询中包含的内联 View (派生表)的示例。这不依赖于数据库中的任何其他表。

如果数据库中有一个表，其中包含 24 行且整数值为 0 到 23，我们可以引用该表来代替内联 View 。

我们还可以向外部查询添加一个 WHERE 子句，以排除 hr 值晚于当前小时的行。

如果我们想返回两位数的小时(带前导零)，我们可以在 SELECT 列表中使用表达式。有几种表达式可以实现这一目的。

SELECT RIGHT(CONCAT('0',i.hr),2) AS hh
  FROM ( -- 0 thru 23
         SELECT 00 AS hr     UNION ALL SELECT 01 UNION ALL SELECT 02 UNION ALL SELECT 03
         UNION ALL SELECT 04 UNION ALL SELECT 05 UNION ALL SELECT 06 UNION ALL SELECT 07
         UNION ALL SELECT 08 UNION ALL SELECT 09 UNION ALL SELECT 10 UNION ALL SELECT 11
         UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
         UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
       ) i
  WHERE i.hr <= HOUR(NOW())
  ORDER BY i.hr

我们将返回的行与原始查询返回的计数“匹配”。我们可以使用外连接操作，以便在计数查询中没有匹配行时返回一个小时的行。

为了将“缺失”计数返回为零，我们执行条件测试，并将 NULL 值替换为 0。

SELECT RIGHT(CONCAT('0',i.hr,2)) AS hh
     , IFNULL(t.cnt,0) AS visits
     , IFNULL(t.pw,0)  AS pw
  FROM ( -- 0 thru 23
         SELECT 00 AS hr UNION ALL SELECT 01 UNION ALL SELECT 02 UNION ALL SELECT 03
         UNION ALL SELECT 04 UNION ALL SELECT 05 UNION ALL SELECT 06 UNION ALL SELECT 07
         UNION ALL SELECT 08 UNION ALL SELECT 09 UNION ALL SELECT 10 UNION ALL SELECT 11
         UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
         UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
        ) i
  LEFT
  JOIN ( -- counts by hour
         SELECT HOUR(FROM_UNIXTIME(s.time)) AS hr
              , COUNT(*) AS cnt
              , MAX(s.pw) AS pw
           FROM clientstats s
          WHERE s.siteid = '$siteid'
            AND s.time >= UNIX_TIMESTAMP(DATE(NOW()))
            AND s.time <  UNIX_TIMESTAMP(NOW())
          GROUP BY HOUR(FROM_UNIXTIME(s.time))
       ) t
    ON t.hr = i.hr
 WHERE i.hr <= HOUR(NOW())
 ORDER BY i.hr ASC

<小时/>

有几种不同的查询将返回指定的结果。但要返回零计数，诀窍是要有一个行源来返回我们想要返回的小时值，即使这些小时值不存在于我们从中获取计数的表中。