SQL查询：从PostgreSQL中删除具有相同值的行

Question

我有一个数据库，我已经设法得到一些重复的行。

该数据库包含以下值：

-------------------------------------------------------------- 
| id | did | sensorid | timestamp | data | db_timestamp | 
-------------------------------------------------------------- 
| int | string | int  | bigint | jsonb | bigint  | 
-------------------------------------------------------------- 

的timestamp和db_timestamp是UNIX时间戳（毫秒自1970年1月1日12:00:00 AM）

我已经成功地得到了很多的重复值，我需要一个快速的方法来“丢弃”它们。显示它们重复的值是did,sensorid和timestamp。这意味着如果我找到一个行相同的行，那么它们是重复的。

我已经做了以下查询来找到重复，现在我只需要找出如何删除它们。

SELECT did, sensorid, timestamp, COUNT(*) 
FROM <db_name> 
GROUP BY did, sensorid, timestamp 
HAVING COUNT(*) > 1 
ORDER BY sensorid, timestamp; 

我的数据库包含了几乎1.200.000.000行，知道我有太多的这些重复的，所以我创建了一个python脚本，将删除这些对我来说。然而，我设法发现，使用我的python脚本并没有像我需要的那样快，所以我希望使用SQL查询删除我的重复项可以做到。

这里是一个打印出来，从我上面的查询输出：

---------------------------------------------------- 
|  did  | sensorid | timestamp | count | 
|"358188056470108"| 910 |1492487704000| 61 | 
|"358188056470108"| 910 |1492487705000| 61 | 
---------------------------------------------------- 

我知道，确实可能是一个bigint或类似以提高性能，但有不这样做的理由。

我想删除所有重复项，但重要的是我保留其中一行，理想情况下最低行id这行将成为“原始”行。

我希望这里的某个人可以帮助我创建这样的查询。

Answer 1

使用CTE与ROW_NUMBER（）来识别ID的删除，然后删除它们

with CTE as 
(
select t1.*, row_number() over(partition by did, sensorid order by id) as rn 
from MyTable t1 
) 
delete 
from MyTable 
where id in (select id from CTE where rn > 1)