【数据分析】sql实现数据固定时间间隔去重！！！

简介： 前一段时间遇到一个需求问题，关于固定时间间隔对数据去重，例如要统计一个区域内某个人的出现频率，但是在一段时间内（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数据库