postgres删除总计数低的行

ql3eal8s  于 2021-08-01  发布在  Java
关注(0)|答案(1)|浏览(460)

我有一个表,其内容类似于:

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的跳闸)。

hlswsv35

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

相关问题