sql - 随时间重复消除

Question

我为我的智能家居创建了一个数据库，但我在编程过程中犯了一个错误:应用程序将内容两次发布到数据库中。我想删除所有包含重复项的行。对于重复，我的意思是一个元组，数据中的内容与同一类型的最后一个相同。我在这个例子中用“<<”标记重复项，请同时注意最后 3 行。我想保留第一个新数据，所以我想删除它们之后的所有重复数据。我仍然希望你能帮助我解决我的问题。

SmartHome=# select * from sensordata order by time desc Limit 21;
 type | data |            time
------+------+----------------------------
    8 | 2459 | 2019-08-09 23:10:39.530087 << 
    8 | 2459 | 2019-08-09 23:10:39.356908
    8 | 2445 | 2019-08-09 23:05:39.933269 <<
    8 | 2445 | 2019-08-09 23:05:39.789173
   10 | 6105 | 2019-08-09 22:50:50.40792  <<
   10 | 6105 | 2019-08-09 22:50:50.096132
    8 | 2459 | 2019-08-09 22:50:41.429681 <<
    8 | 2459 | 2019-08-09 22:50:41.357483
    8 | 2474 | 2019-08-09 22:45:42.13396  <<
    8 | 2474 | 2019-08-09 22:45:41.813046
   10 | 6221 | 2019-08-09 22:40:51.107709 <<
   10 | 6221 | 2019-08-09 22:40:51.076903
   10 | 6105 | 2019-08-09 22:35:51.737255 <<
   10 | 6105 | 2019-08-09 22:35:51.544886
   10 | 6221 | 2019-08-09 22:30:52.493895 <<
   10 | 6221 | 2019-08-09 22:30:51.795203
    8 | 2459 | 2019-08-09 22:30:43.193447 <<
    8 | 2459 | 2019-08-09 22:30:43.045599
   10 | 6105 | 2019-08-09 22:25:52.571793 << Duplicate like them above
   10 | 6105 | 2019-08-09 22:25:52.442844 << Also a Duplicate with much more
   10 | 6105 | 2019-08-09 22:20:51.356846    time between the rows
(21 rows)

SmartHome=# \d sensordata
                   Table "public.sensordata"
 Column |            Type             |       Modifiers
--------+-----------------------------+------------------------
 type   | integer                     | not null
 data   | character varying(20)       | not null
 time   | timestamp without time zone | not null default now()
Indexes:
    "smarthome_idx" UNIQUE, btree (type, "time")
Foreign-key constraints:
    "sensordata_type_fkey" FOREIGN KEY (type) REFERENCES sensortype(id)

如果我跑

with a as (Select *, row_number() over(partition by type,data order by time) from sensordata) select * from a where row_number=1 order by time desc;

输出是:

 10 | 17316 | 2019-08-09 09:43:46.938507 |          1
   10 | 18276 | 2019-08-09 09:38:47.129788 |          1
   10 | 18176 | 2019-08-09 09:33:47.889064 |          1
   10 | 17107 | 2019-08-08 10:36:11.383106 |          1
   10 | 17921 | 2019-08-08 09:56:15.889191 |          1
   10 | 17533 | 2019-08-03 09:30:11.047639 |          1

那不是我的意思:/(ß抱歉不知道如何以这种方式在评论中将这些东西标记为代码块

Answer 1

有很多方法可以做到这一点。最快的通常是相关子查询，但我永远记不起语法，所以我通常使用窗口函数，特别是 row_number()。

如果你跑

Select *, row_number() over(partition by type,data order by date) from sensor data

这应该给出您的表的一个版本，其中您要保留的所有行的编号为 1，重复项的编号为 2、3、4...在删除查询中使用相同的字段，您将被排序.

编辑:我现在明白您只想删除同一类型中连续出现的重复项。这也可以使用 row_number 和 join 来实现。此查询应该只为您提供所需的数据。

WITH s as (SELECT *,row_number() over(partition by type order by date) as rnum from sensordata)
SELECT a.* 
FROM s a 
JOIN s b 
ON a.rnum=b.rnum+1 AND a.type=b.type
WHERE NOT a.data=b.data

如果这很重要，这可能需要稍微调整以避免错过第一个条目。