sql - 删除具有指定参数但在 Postgresql 中最新的所有行

Question

我有一个哈希表:

CREATE TABLE hash_table ( hash_id bigserial, 
user_name varchar(80),
 hash varchar(80), 
exp_time bigint,
 PRIMARY KEY (hash_id));


INSERT INTO hash_table (hash_id, user_name, exp_time) VALUES
(1, 'one',   10),
(2, 'two',   20),
(3, 'three', 31),
(4, 'three', 32),
(5, 'three', 33),
(6, 'three', 33),
(7, 'three', 35),
(8, 'three', 36),
(9, 'two',   40),
(10, 'two',   50),
(11, 'one',   60),
(12, 'three', 70);

exp_time - 哈希的过期时间。 exp_time = now() + delta_time 行创建时

我需要一个结果:

(1, 'one',   10),
(2, 'two',   20),
(7, 'three', 35),
(8, 'three', 36),
(9, 'two',   40),
(10, 'two',   50),
(11, 'one',   60),
(12, 'three', 70);

它包含很多 user_name-hash 对。 user_name 可能会重复很多时间。

如何删除指定 user_name 的所有行(几行，例如 10 行)？

我找到了 this解决方案(适用于 mySQL，但我希望它能工作)但它会删除所有其他用户名

DELETE FROM `hash_table`
WHERE user_name NOT IN (
  SELECT user_name
  FROM (
    SELECT user_name
    FROM `hash_table`
    ORDER BY exp_time DESC
    LIMIT 10 -- keep this many records
  )
);

Answer 1

这将为每个用户名保留 3 个最新记录:

DELETE FROM hash_table h
USING
(
  SELECT
    user_name,
    exp_time,
    row_number() OVER (PARTITION BY user_name ORDER BY exp_time DESC) AS r
  FROM
    hash_table
) AS s
WHERE
  h.user_name = s.user_name
  AND h.exp_time<s.exp_time
  AND s.r=3