postgres删除总计数低的行

ql3eal8s 于 2021-08-01 发布在 Java

关注(0)|答案(1)|浏览(479)

我有一个表，其内容类似于：

user_id |   session_id   |       timestamp        |    lat    |    lon     
---------+----------------+------------------------+-----------+------------
       1 | 20081023025304 | 2008-10-23 02:53:04+01 | 39.984702 | 116.318417
       1 | 20081023025304 | 2008-10-23 02:53:10+01 | 39.984683 |  116.31845
       1 | 20081023025304 | 2008-10-23 02:53:15+01 | 39.984686 | 116.318417
       2 | 1020081023055305 | 2008-10-23 05:53:05+01 | 39.984094 | 116.319236
       2 | 1020081023055305 | 2008-10-23 05:53:06+01 | 39.984198 | 116.319322
       2 | 1020081023055305 | 2008-10-23 05:53:11+01 | 39.984224 | 116.319402
       2 | 1020081023055305 | 2008-10-23 05:53:16+01 | 39.984211 | 116.319389
       2 | 1020081023055305 | 2008-10-23 05:53:21+01 | 39.984217 | 116.319422
       2 | 1020081023055305 | 2008-10-23 05:53:23+01 |  39.98471 | 116.319865
       2 | 1020081023055305 | 2008-10-23 05:53:28+01 | 39.984674 |  116.31981
       3 | 2020081023124523 | 2008-10-23 12:45:23+01 | 39.927938 | 116.338967
       3 | 2020081023124523 | 2008-10-23 12:45:24+01 | 39.927527 | 116.338899

似乎很少有对我的分析没有价值的示例会影响我的统计计算（离群值）。我想删除所有行数较少的行程。
因此，我想删除低采样计数的跳闸（在这种情况下，假设行计数小于5的跳闸）。

sql postgresql count subquery sql-delete

来源：https://stackoverflow.com/questions/62663692/postgres-delete-rows-having-low-total-count

1条答案

按热度按时间

hlswsv351#

你可以用 delete ... using 语法：

delete from mytable t
using (
    select session_id
    from mytable 
    group by session_id
    having count(*) < 5
) t1
where t1.session_id = t.session_id

赞(0）回复(0）举报 2021-08-01

我来回答

postgres删除总计数低的行

1条答案

相关问题

热门标签

最新问答