经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 程序设计 » SQL语言 » 查看文章
sql时间段切分实现每隔x分钟出一份高速门架车流量
来源:jb51  时间:2022/2/28 15:24:35  对本文有异议

业务场景:

我们需要查询从表T里查询某一天按照5分钟为一段间隔通过高速门架的车流量。部分脱敏数据如下

表T里包含time,id,chepai,部分数据为(’2020-02-23 00:43:27','某高速门架0010','浙XXXXXX')

按照正常情况下我们的限定where条件是time >= '2020-02-23 0:00' and time < '2020-02-23 0:00'  或者 between等,但是这样来写一两个还行,要每隔5分钟切分一次,需要写二百多,过于麻烦

解决思路:

  • 需要把时间进行下降到其5分钟开始的最初时间处理,即00:12:30是在00:10:00-00:15:00之间,要转换为00:10:00,同样10:21:39在10:20:00-10:25:00,所以转换为10:20:00,这样一来五分钟内的数据都下降统一成5分初的时间,也就是5分钟这一段的数据就有了。当然也可以把数据进行上升处理,只不过23:55:00-23:59:59这个时间段就上升到第二天的00:00了
  • 对时间段进行下降处理思想有了,问题是如何实现:可以把时间字段转换为数字整形,即每一个时间都对应着一串数字,只要把这个数字由年月日时分秒切割相加再除以5,然后向下取整再乘以5,最后再转换为时间,这样就能达到下降了,这里的核心思想就是无法整除5的那部分余数就是5分钟这个时间段的,现在我们向下取整,比如2.75向下取整是2,那么就能把时间下降了。同样的如果是每隔10分钟,那就是除以10,每隔1小时的,直接substr切分时间字段就可以了
  • 这里使用强大的两个函数FROM_UNIXTIME和UNIX_TIMESTAMP来完成,具体使用方法参加我的旧文https://www.jb51.net/article/239100.htm脚本实现:
  1. SELECT t.time,
  2. t.id,
  3. COUNT(DISTINCT(t.chepai)) num
  4. FROM
  5. (SELECT time,
  6. id,
  7. -- FROM_UNIXTIME把整型数字转为时间类型
  8. FROM_UNIXTIME(
  9. -- 一般年月日无需动,直接unix_timestamp先转为整型再加上时分秒即可
  10. unix_timestamp(left(time,10))
  11. -- 分*60+秒然后除以5floor向下取整后再*5*60,具体思路见上
  12. +5*60*floor((substr(PicTime,12,2)*60+substr(Pictime,15,2))/5)
  13. ) time,
  14. chepai
  15. FROM T
  16. WHERE left(time,10) = '2020-02-23'
  17. ORDER BY time asc)t
  18. GROUP BY t.time,t.id
  19. ORDER BY t.time,t.id

到此这篇关于sql时间段切分实现每隔x分钟出一份高速门架车流量的文章就介绍到这了,更多相关sql时间段切分内容请搜索w3xue以前的文章或继续浏览下面的相关文章希望大家以后多多支持w3xue!

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号