在PostgreSQL中使用WITH + DELETE子句进行单个查询

Question

我有以下表结构，表名为listens，主键为(uid,timestamp)

     Column     |            Type             |                      Modifiers                       
----------------+-----------------------------+------------------------------------------------------    
 id             | integer                     | not null default nextval('listens_id_seq'::regclass)
 uid            | character varying           | not null
 date           | timestamp without time zone | 
 timestamp      | integer                     | not null
 artist_msid    | uuid                        | 
 album_msid     | uuid                        | 
 recording_msid | uuid                        | 
 json           | character varying           | 

我需要删除特定用户(uid)的所有条目，这些条目比最大时间戳旧，假设max为123456789 (以秒为单位)，delta为100000，则所有比max-100000更旧的记录都将被删除。

当表中只包含单个用户时，我已经成功创建了一个查询，但我无法使其适用于数据库中的每个用户。这个操作需要针对数据库中的每个用户执行。

WITH max_table as (
    SELECT max(timestamp) - 10000 as max 
    FROM listens 
    GROUP BY uid) 
DELETE FROM listens 
WHERE timestamp < (SELECT max FROM max_table);

有任何解决方案吗？

- Pinkesh Badjatiya

---

你想要关联到一个最大值，还是每个用户的最大值？ - Mureinik

Answer 1

我认为你所需要的是将这个查询变成一个相关子查询：

WITH max_table as (
    SELECT uid, max(timestamp) - 10000 as mx
    FROM listens 
    GROUP BY uid
) 
DELETE FROM listens 
WHERE timestamp < (SELECT mx
                   FROM max_table 
                   where max_table.uid = listens.uid);

---

^{顺便说一下： timestamp 是列的一个可怕的名称，特别是对于不包含时间戳值的列而言。其中一个原因是因为它也是一个关键字，但更重要的是它没有记录该列包含什么。注册时间戳？过期时间戳？最后活动时间戳？}

- user330315

---

timestamp 包含正在插入的记录的听取时间戳（实际时间戳）。数据是每个用户的收听数据。 - Pinkesh Badjatiya

Answer 2

或者，您可以使用 EXISTS() 来避免 MAX()。

DELETE FROM listens d
WHERE EXISTS (
        SELECT * FROM listens x
        WHERE x.uid = d.uid
        AND x.timestamp >= d.timestamp + 10000 
        );  

顺便提一下，timestamp作为列名有些丑陋，因为它也是一个类型名。

- wildplasser