时间戳

首页 > 解决方案 > mysql - 所有用户每小时的汇总结果

mysql - mysql - 所有用户每小时的汇总结果

问题描述

我有一张如下表。

我实际上试图在 2 种情况下进行选择汇总报告。

+---------------------+---------------+-----------------+
| timestamp           | user          | open_pages      |
+---------------------+---------------+-----------------+
| 2018-09-19 12:27:02 | user1         |              66 |
| 2018-09-19 12:27:02 | user2         |              24 |
| 2018-09-19 12:27:10 | user2         |              24 |
| 2018-09-19 12:28:30 | user1         |              21 |
| 2018-09-19 12:28:30 | user2         |              20 |
| 2018-09-19 12:28:35 | user1         |              17 |
| 2018-09-19 12:28:35 | user2         |              11 |
| 2018-09-19 12:29:08 | user1         |               8 |
| 2018-09-19 12:29:08 | user2         |               8 |
| 2018-09-19 12:30:02 | user1         |               7 |
| 2018-09-19 12:30:02 | user2         |               6 |
+---------------------+---------------+-----------------+

案例 1：平均每分钟打开所有用户的页面

示例输出：

+--------+-------+------------+
| minute | User  | Open_pages |
+--------+-------+------------+
| 27     | User1 | 66         |
| 27     | User2 | 26         |
| 28     | user1 | 56         |
| 28     | user2 | 51         |
| 29     | user1 | 21         |
| 29     | user2 | 28         |
+--------+-------+------------+

我尝试了这个查询，但它没有显示正确的值。

     select minute(timestamp), user, avg(open_pages) 
    from tbl where DATE(timestamp)= '2018-09-19' 
group by minute(timestamp) order by 1;

案例 2：谁在一分钟内打开了最大页数

12:27:02 user2 连接两次，共打开48页。所以他在 27 分钟时打开的页面最高。像这样，我想计算每一分钟。

我不知道如何为此生成查询。

谁能帮我解决这两个问题？

标签： mysqlsqlselectgroup-by

解决方案

给定小时内每分钟/用户的平均打开页数。

ts=timestamp, un=username, op=openpges

Select minute(op.ts) as mm, un, cast(avg(op) as int) as avgop
From openpages op
Where (op.ts between '2018-09-19 12:00:00' and '2018-09-19 12:59:59.999')
Group By minute(op.ts), un
Order By op.ts

此查询计算每个给定小时的 open_pages 总和。它让每个用户都可见，但我不知道 yoo 如何每分钟只选择一个特定的行。也许在应用程序方面最容易做到，每分钟使用第一行？

  Select minute(op.ts) as mm, un, sum(op) as sumop
  From openpages op
  Where (op.ts between '2018-09-19 12:00:00' and '2018-09-19 12:59:59.999')
  Group By minute(op.ts), un
  Order By sumop desc

如果必须在应用程序端进行最终过滤，您可以将 SUM 和 AVG 放在同一个查询中。这一个查询足以给出两个答案。

  Select minute(op.ts) as mm, un, sum(op) as sumop
    , cast(avg(op) as int) as avgop
  From openpages op
  Where (op.ts between '2018-09-19 12:00:00' and '2018-09-19 12:59:59.999')
  Group By minute(op.ts), un
  Order By sumop desc

编辑我不得不花一些时间来解决这个问题，因为这是个人兴趣。我认为此查询可能会为您提供每给定小时分钟具有最大 open_pages 的用户列表。必须使用派生的临时表、正在运行的@variable 并基于该变量进行顶级过滤。

set @mm=-1;
Select 
  CASE WHEN @mm=aTemp.mm THEN 0
  ELSE 1
  END as rn,
  @mm:=aTemp.mm as mm,
  aTemp.un, aTemp.sumop
From (
  Select minute(op.ts) as mm, un, sum(op) as sumop
  From openpages op
  Where (op.ts between '2018-09-19 12:00:00' and '2018-09-19 12:59:59.999')
  Group By minute(op.ts), un
  Order By sumop desc
  ) aTemp
Having rn=1

推荐阅读