[求助] 如何用 SQL（或者其他方式）实现这个要求（内详）

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

V2EX 提问指南

这是一个创建于 1937 天前的主题，其中的信息可能已经有所发展或是发生改变。

数据格式如下： https://sm.ms/image/42rhuonQbDZYT1S

需求：汇总不同楼层的逗留时长。

想了下没有合适的办法，各位 v 友有没有好的办法，请赐教

第 1 条附言 · 2019-08-06 18:03:10 +08:00

问题已解决，感谢各位ღ( ´･ᴗ･` )

赐教

SQL

办法

楼层

23 条回复

dovme

2019-08-06 11:10:33 +08:00

各个字段什么意思？ t_time rank row_number

ebony0319

2019-08-06 11:18:20 +08:00

其实就是开窗函数。

leishi1313

2019-08-06 11:19:35 +08:00

逗留时长？那通过哪列能知道是进还是出啊

ebony0319

2019-08-06 11:20:05 +08:00

sorry，审题错误。

ebony0319

2019-08-06 11:21:35 +08:00

这是一个运动轨迹，代表各个时间段到达楼层的时间？

2bin

2019-08-06 11:23:24 +08:00

@dovme t_time: 在某楼层的时间点，rank:按楼层分组按 t_time 升序的排名，row_numer:行号。只看前三列就好，后面是我处理的

2bin

2019-08-06 11:24:03 +08:00

@ebony0319 是的

taotaodaddy

2019-08-06 11:24:17 +08:00 via Android

每天的每楼层逗留时长：按天和楼层 group by，计算 max 时间-min 时间

2bin

2019-08-06 11:27:32 +08:00

@leishi1313 不论进出，逗留时长=最大时间-最大时间

2bin

2019-08-06 11:30:02 +08:00

@taotaodaddy 试过这样不行，这样会重复算时长，比如说 1:00-2:00 在一楼，2:00-3:00 在二楼，4:00-5:00 又回到一楼。

taotaodaddy

2019-08-06 11:32:33 +08:00 via Android

@2bin 酱紫哦，那就复杂一些了，建议不用一条 sql 来实现，用 python 写代码计算吧

2bin

2019-08-06 11:33:14 +08:00

@2bin 打错，最大时间-最小时间

stelpen

2019-08-06 11:35:25 +08:00 via Android

自连接，on 楼层相等，rankno = rankno+1，取出两个时间，然后 group by 楼层，sum 俩时间差。应该可以，不知道描述清楚没

leishi1313

2019-08-06 11:41:20 +08:00

@2bin 如果每一条都是严格按时间增长的，可以先找出每个楼层的最小最大时间，这个用一群 where 和 row_number 可以搞定，然后参考这个：dba.stackexchange.com/questions/90987/querying-sums-of-grouped-consecutive-rows-in-postgresql-9 做最近两条的一个相减就好了。

taotaodaddy

2019-08-06 11:44:16 +08:00 via Android

跳跃一下，能改表结构不，加一个进出楼层标志列，这样统计起来就简单多了

loading

2019-08-06 11:45:53 +08:00 via Android

sql 不熟悉就先用手工模拟算一次，然后写成程序吧。
用自连接 sql 应该是可以的。

2bin

2019-08-06 11:47:51 +08:00

@taotaodaddy 谢谢，Python 我用的不熟，有思路可以提供吗

2bin

2019-08-06 11:49:23 +08:00

@stelpen 谢谢，我模拟一下

taotaodaddy

2019-08-06 11:50:19 +08:00 via Android

@2bin python 只是举例，可以用你任何擅长的语言，java,php 都可以

2bin

2019-08-06 11:51:28 +08:00

@leishi1313 @loading 感谢♪(･ω･)ﾉ，我试一下

su2018

2019-08-06 13:48:09 +08:00

不知道符不符合你要求

SELECT t.floor, SUM(t.wait_time)wait_time,t.floor_index
FROM (
SELECT visit_date,FLOOR,TIME_TO_SEC(TIMEDIFF(t_time,@startTime)) wait_time,@startTime:=t_time,IF(a.floor=@floor,@index,@index:=@index+1),@index floor_index,@floor := a.floor

FROM test1 a,(SELECT @startTime := '') AS b,(SELECT @floor :=0) AS c,(SELECT @index :=0) AS d WHERE 1

ORDER BY t_time

) t GROUP BY t.floor_index

x66

2019-08-06 14:17:11 +08:00

row_number 根据 t_time 排序，然后自连接 a.row_number = b.row_number +1 ,然后 group by floor,求差再求和就好了

zhuanggu

2019-08-06 19:59:17 +08:00

select floor
,sum(next_time-t_time) as ts
from
(
select floor
,t_time
,lead(t_time,1) over(partition by floor order by t_time ) as next_time
from tablename
) t
group by floor;