MySQL为表中的特定id选择两个时间戳列之间的平均时间差

问题描述：

I have a table visits that looks like this:

+--------------+-------------+------+-----+---------------------+----------------+
| Field        | Type        | Null | Key | Default             | Extra          |
+--------------+-------------+------+-----+---------------------+----------------+
| id           | int(11)     | NO   | PRI | NULL                | auto_increment |
| vis_id       | int(11)     | NO   | MUL | NULL                |                |
| unit         | int(11)     | NO   | MUL | NULL                |                |
| time_in      | timestamp   | NO   | MUL | CURRENT_TIMESTAMP   |                |
| time_out     | timestamp   | NO   | MUL | 0000-00-00 00:00:00 |                |
| in_username  | varchar(16) | NO   | MUL | NULL                |                |
| out_username | varchar(16) | NO   | MUL | NULL                |                |
+--------------+-------------+------+-----+---------------------+----------------+

I use this to keep track of visitors coming in and out of my building.

What I'd like is to be able to see the "average length of visit" in hours by a particular visitor (defined by vis_id). If the average visit length is less than an hour it should round up to 1.

Here's an example of what I'd like for output:

+--------+------+----------+
| vis_id | unit | avg_time |
+--------+------+----------+
|    156 |  216 | 5        |
|   1230 |  103 | 2        |
|    533 |  112 | 1        |  
|    802 |  201 | 3        |
|   1445 |  431 | 4        |
+--------+------+----------+

Suggestions?

我有一个表visits code>，如下所示： p> \ ñ

  + -------------- + ------------- + ------ + ----- +  --------------------- + ---------------- + \ N | 领域| 输入| 空| 钥匙| 默认| 额外| 
 + -------------- + ------------- + ------ + ----- + ----  ----------------- + ---------------- + \ N |  id |  int（11）| 没有|  PRI |  NULL |  auto_increment | 
 |  vis_id |  int（11）| 没有|  MUL |  NULL |  | 
 | 单位|  int（11）| 没有|  MUL |  NULL |  | 
 |  time_in | 时间戳| 没有|  MUL |  CURRENT_TIMESTAMP |  | 
 |  time_out | 时间戳| 没有|  MUL |  
 00：00：00 |  | 
 |  in_username |  varchar（16）| 没有|  MUL |  NULL |  | 
 |  out_username |  varchar（16）| 没有|  MUL |  NULL |  | \ N + -------------- + ------------- + ------ + ----- + -----  ---------------- + ---------------- + 
 代码>  PRE> 
 
 我用它来跟踪进出我建筑物的访客。 p> 
 
 
我希望能够在几小时内看到“平均访问时长” 特定访问者（由 vis_id  code>定义）。 如果平均访问长度小于一小时，它应该向上舍入为1.  p> 
 
 
以下是我想要输出的示例： p> 
 
 

  + -------- + ------ + ---------- + \ N |  vis_id | 单位|  avg_time | 
 + -------- + ------ + ---------- + 
 |  156 |  216 |  5 | 
 |  1230 |  103 |  2 | 
 |  533 |  112 |  1 |  
 |  802 |  201 |  3 | 
 |  1445 |  431 |  4 | 
 + -------- + ------ + ---------- + 
  code>  pre> 
 
 建议？   p> 
  div>

答

Use AVG() grouping function. As far as I see, you'll need two grouping fields, like below:

SELECT 
  vis_id,
  unit,
  ROUND(AVG((UNIX_TIMESTAMP(time_out)-UNIX_TIMESTAMP(time_in))/3600)) AS avg_time
FROM
  visits
GROUP BY
  vis_id,
  unit

Note, that I'm using ROUND() since you've not defined rules for dealing with non-integer values. ROUND() will use algebraic rule, but may be you'll want to drop non-integer part. Then look to FLOOR() - or, instead, add integer part (i.e. round to greatest) - then look to CEIL()

MySQL为表中的特定id选择两个时间戳列之间的平均时间差

相关推荐