简介:
前一段时间遇到一个需求问题,关于固定时间间隔对数据去重,例如要统计一个区域内某个人的出现频率,但是在一段时间内(15s)多次出现又会影响数据分析的准确性,所以为了提高统计数据的准确性,所以有效的出现次数时间间隔大于15s,如下图:
由上图可知,此人在0s、5s、8s、16s、25s、35s处出现过,我们以0s处出现为起始点,以15s为间隔时间计算,满足条件的出现次数为:0s、16s和35s处出现的点,而5s、8s、25s出现的点我们认为是重复数据,可丢弃;
实现分析:
在程序中能够实现这个数据筛选的方式还是比较多得,比如队列+哈希表
就可以实现,但是在程序中实现首先把数据加载到内存
中,在数据量比较大的情况下会比较占用内存,同时也要消耗数据从数据库加载到内存的性能,所以比较高效的实现方式是在数据库
中实现数据的筛选去重(注:我们占不考虑数据库的性能损耗,和业务场景有关系
),在数据库中通过sql的方式实现业务逻辑就比较复杂,本人也是思考了一段时间,主要考虑点有两个:1)功能实现;2)sql的执行性能
,尤其是第二点,一个比较耗费性能的sql实现等于没有实现;
通过sql实现固定时间间隔去重
的功能,主要依赖于数据库中的两个函数:递归函数
和动态窗口函数
;
实现思路:
1)首先按照人员进行分组
,对每组数据进行升序排序
,并设置序号;
在不同分组中,每条数据被赋予序号,按照排序信息递增,s1、s2、s3...sn;
2)以升序数据序列第一条数据为起始点,固定时间15s为窗口大小,找到窗口内距离窗口起始数据最远的数据wLast1,组成一条数据;依次类推,计算每条数据作为起始点是,满足窗口大小数据中的距离最远的数据;
3)从起始点开始计算间隔时间大于15s的数据,第一条数据为起始点数据,第二条数据为wLast1数据的序号+1,第三条数据为第二条数据的wLastn数据的序号+1,通过递归函数依次计算,得到筛选后的数据;方便理解,请看下图:
在查询s1的下一条数据时,下一条数据肯定是w1窗口之外的第一条数据s3,而s3等于窗口内的最后一条数据s2+1;
样例数据库表:
CREATE TABLE IF NOT EXISTS `person_appear_record` (
`id` int(6) unsigned NOT NULL,
`face_id` varchar(32) unsigned NOT NULL,
`appear_time` bingint unsigned NOT NULL
PRIMARY KEY (`id`)
)
业务实现sql:
WITH RECURSIVE person_temp_01 AS (
SELECT
person_id,
appear_time
ROW_NUMBER () OVER ( PARTITION BY person_id ORDER BY appear_time ASC ) row_id,
LAST_VALUE ( appear_time ) OVER ( PARTITION BY person_id ORDER BY appear_time ASC RANGE BETWEEN 0 PRECEDING AND 15000 FOLLOWING ) lastValue
FROM
person_appear_record
),
person_temp_02 AS (
SELECT
t01.person_id,
t01.appear_time,
t01.row_id,
t01.lastValue,
t02.person_id person_id2,
t02.appear_time appear_time2,
t02.row_id row_id2,
t02.lastValue lastValue2
FROM
person_temp_01 t01,
person_temp_01 t02
WHERE
1=1
AND t01.person_id = t02.person_id
AND t01.row_id + 1 = t02.row_id
),
person_temp_03 AS (
SELECT
f01.person_id,
f01.appear_time,
f01.lastValue,
f01.row_id
FROM
person_temp_02 f01
WHERE
f01.row_id = 1 UNION
SELECT
t21.person_id2 person_id,
t21.appear_time2 appear_time,
t21.lastValue2 lastValue,
t21.row_id2 row_id
FROM
person_temp_02 t21,
person_temp_03 t50
WHERE
1=1
AND t21.person_id = t50.person_id
AND t21.appear_time = t50.lastValue
)
SELECT
*,
TO_CHAR( TO_TIMESTAMP( appear_time / 1000 ), 'yyyy-mm-dd HH24:MI:SS' )
FROM
person_temp_03
WHERE
1 = 1
AND person_id = 33
ORDER BY
person_id,
appear_time ASC
注:本人使用的是postgresql数据库