利用TopN算法实现复杂去重实例一则

最新推荐文章于 2020-04-23 16:08:09 发布

原创最新推荐文章于 2020-04-23 16:08:09 发布 · 1.7k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

Oracle 同时被 2 个专栏收录

12 篇文章

订阅专栏

5 篇文章

订阅专栏

本文介绍了一种在一小时内对重复数据进行去重的算法，并通过SQL实现。该算法选取每个小时内距离最小时间点最近的一条记录，适用于数据库操作。

昨天有网友提了一个问题，需要对如下数据按1小时的间隔进行去重。

1 15392    2012-8-31 23:59:00
2 15392    2012-9-1 0:13:00
3 15392    2012-9-1 0:16:25
4 15392    2012-9-1 12:10:25
5 15392    2012-9-1 12:15:25
6 15392    2012-9-1 13:15:25

以表中最小时间点为基准，每隔1小时只保留一条记录。对应到上面的样例数据，最小的时间点为第1条记录的2012-8-31 23:59:00，

那么对于所有时间在2012-9-1 00:59:00之前的数据都要舍去，也就是上面第2、3条记录都要舍去，以此类推。

那么最终得到的结果是保留第1、4、6条记录。

对于这个问题我们应该怎么解决呢？

我给出的方法是，用每条记录的时间点减去最小时间点得到一个差值a，然后只取差值最接近于每个整数的那条记录。

如差值列表如下：0、0.1、0.2、1.1、1.2、3

那么最终就取0、1、3，大家可以想象成对差值(oracle中记得要*24)按整数部分分组，然后取每个组的Top1。

下面给出具体实现步骤：

创建测试表

-- Create table
create table TTTTX
(
  id        INTEGER,
  id2       INTEGER,
  make_date DATE
)

插入测试数据

insert into TTTTX (id, id2, make_date)
values (1, 15392, to_date('31-08-2012 23:59:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into TTTTX (id, id2, make_date)
values (2, 15392, to_date('01-09-2012 00:13:00', 'dd-mm-yyyy hh24:mi:ss'));
insert into TTTTX (id, id2, make_date)
values (3, 15392, to_date('01-09-2012 00:16:25', 'dd-mm-yyyy hh24:mi:ss'));
insert into TTTTX (id, id2, make_date)
values (4, 15392, to_date('01-09-2012 12:10:25', 'dd-mm-yyyy hh24:mi:ss'));
insert into TTTTX (id, id2, make_date)
values (5, 15392, to_date('01-09-2012 12:15:25', 'dd-mm-yyyy hh24:mi:ss'));
insert into TTTTX (id, id2, make_date)
values (6, 15392, to_date('01-09-2012 13:15:25', 'dd-mm-yyyy hh24:mi:ss'));
commit;

编写SQL：

select id, id2, make_date
  from (select id,
               id2,
               make_date,
               row_number() over(partition by id2, trunc(a) order by a) lvl
          from (select id,
                       id2,
                       make_date,
                       (make_date -
                       to_date('2012/8/31 23:59:00', 'yyyy-mm-dd hh24:mi:ss')) * 24 a
                /*每条记录减去表中的最小时间点,注意这个最小时间点最好是常量，
                不要在此处用SQL获得以避免产生较多的recursive calls*/
                  from ttttx))
 where lvl = 1

完毕！