我有一张像下面这样的table。
我实际上试图做一个选择汇总报告在两个案件。
+---------------------+---------------+-----------------+
| 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：谁在一分钟内打开了max页面
在12:27:02，user2连接了两次，总共打开了48页。所以他在最短的27页有最高的开放页面。像这样，我要计算每一分钟。
我不知道如何为此生成查询。
有人能帮我回答这两个问题吗？
1条答案
按热度按时间zvms9eto1#
给定小时内每分钟/用户的平均打开页面数。
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
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
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
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