V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
2bin
V2EX  ›  问与答

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

  •  
  •   2bin · 2019-08-06 11:04:20 +08:00 · 1415 次点击
    这是一个创建于 1696 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

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

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

    第 1 条附言  ·  2019-08-06 18:03:10 +08:00
    问题已解决,感谢各位ღ( ´・ᴗ・` )
    dovme
        1
    dovme  
       2019-08-06 11:10:33 +08:00
    各个字段什么意思? t_time rank row_number
    ebony0319
        2
    ebony0319  
       2019-08-06 11:18:20 +08:00
    其实就是开窗函数。
    leishi1313
        3
    leishi1313  
       2019-08-06 11:19:35 +08:00
    逗留时长?那通过哪列能知道是进还是出啊
    ebony0319
        4
    ebony0319  
       2019-08-06 11:20:05 +08:00
    sorry,审题错误。
    ebony0319
        5
    ebony0319  
       2019-08-06 11:21:35 +08:00
    这是一个运动轨迹,代表各个时间段到达楼层的时间?
    2bin
        6
    2bin  
    OP
       2019-08-06 11:23:24 +08:00
    @dovme t_time: 在某楼层的时间点,rank:按楼层分组按 t_time 升序的排名,row_numer:行号。只看前三列就好,后面是我处理的
    2bin
        7
    2bin  
    OP
       2019-08-06 11:24:03 +08:00
    @ebony0319 是的
    taotaodaddy
        8
    taotaodaddy  
       2019-08-06 11:24:17 +08:00 via Android
    每天的每楼层逗留时长:按天和楼层 group by,计算 max 时间-min 时间
    2bin
        9
    2bin  
    OP
       2019-08-06 11:27:32 +08:00
    @leishi1313 不论进出,逗留时长=最大时间-最大时间
    2bin
        10
    2bin  
    OP
       2019-08-06 11:30:02 +08:00
    @taotaodaddy 试过这样不行,这样会重复算时长,比如说 1:00-2:00 在一楼,2:00-3:00 在二楼,4:00-5:00 又回到一楼。
    taotaodaddy
        11
    taotaodaddy  
       2019-08-06 11:32:33 +08:00 via Android
    @2bin 酱紫哦,那就复杂一些了,建议不用一条 sql 来实现,用 python 写代码计算吧
    2bin
        12
    2bin  
    OP
       2019-08-06 11:33:14 +08:00
    @2bin 打错,最大时间-最小时间
    stelpen
        13
    stelpen  
       2019-08-06 11:35:25 +08:00 via Android
    自连接,on 楼层相等,rankno = rankno+1,取出两个时间,然后 group by 楼层,sum 俩时间差。应该可以,不知道描述清楚没
    leishi1313
        14
    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
        15
    taotaodaddy  
       2019-08-06 11:44:16 +08:00 via Android
    跳跃一下,能改表结构不,加一个进出楼层标志列,这样统计起来就简单多了
    loading
        16
    loading  
       2019-08-06 11:45:53 +08:00 via Android
    sql 不熟悉就先用手工模拟算一次,然后写成程序吧。
    用自连接 sql 应该是可以的。
    2bin
        17
    2bin  
    OP
       2019-08-06 11:47:51 +08:00
    @taotaodaddy 谢谢,Python 我用的不熟,有思路可以提供吗
    2bin
        18
    2bin  
    OP
       2019-08-06 11:49:23 +08:00
    @stelpen 谢谢,我模拟一下
    taotaodaddy
        19
    taotaodaddy  
       2019-08-06 11:50:19 +08:00 via Android
    @2bin python 只是举例,可以用你任何擅长的语言,java,php 都可以
    2bin
        20
    2bin  
    OP
       2019-08-06 11:51:28 +08:00
    @leishi1313 @loading 感谢♪(・ω・)ノ,我试一下
    su2018
        21
    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
        22
    x66  
       2019-08-06 14:17:11 +08:00
    row_number 根据 t_time 排序,然后自连接 a.row_number = b.row_number +1 ,然后 group by floor,求差再求和就好了
    zhuanggu
        23
    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;
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5475 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 34ms · UTC 09:06 · PVG 17:06 · LAX 02:06 · JFK 05:06
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.