sql时间段切分实现每隔x分钟出一份高速门架车流量

2022-09-03 15:12神芷迦蓝寺 Sql Server

本文主要介绍了sql时间段切分实现每隔x分钟出一份高速门架车流量，文中通过示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下

业务场景：

我们需要查询从表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来完成，具体使用方法参加我的旧文脚本实现：

				?

									SELECT t.time,

									        t.id,

									        COUNT(DISTINCT(t.chepai)) num

									FROM

									    (SELECT time,

									        id,

									        -- FROM_UNIXTIME把整型数字转为时间类型

									        FROM_UNIXTIME(

									            -- 一般年月日无需动，直接unix_timestamp先转为整型再加上时分秒即可

									            unix_timestamp(left(time,10))

									            -- 分*60+秒然后除以5，floor向下取整后再*5*60，具体思路见上

									            +5*60*floor((substr(PicTime,12,2)*60+substr(Pictime,15,2))/5)

									            ) time,

									        chepai

									    FROM T

									    WHERE left(time,10) = '2020-02-23'

									    ORDER BY  time asc)t

									GROUP BY  t.time,t.id

									ORDER BY  t.time,t.id