主页 / dba / 问题 / 29854
Accepted
icefex
Asked: 2012-12-06 02:34:33 +0800 CST

每小时删除重复条目

  • 772

我最近问了关于 MySQL 的相同问题，该问题已被迁移到 Postgres。 老问题。

把它们加起来：

我有一个带有 id（类型 UUID）、时间戳和一些文本（row3）的表。如何为 row3 的每个不同值每小时只保留一个条目？

我尝试的是这样的：

INSERT INTO log_table
SELECT * FROM table1
WHERE id IN (
    SELECT DISTINCT ON(id) id
    FROM table1
    GROUP BY row3, EXTRACT(HOUR FROM "time"), id
);
TRUNCATE table1;

但不幸的是，这会插入 table1 中的每一行。

postgresql

1 个回答

  1. Best Answer

    通用案例

    假设您想max(id)为每个小时和每个不同的值保留属于 的row3值（但请参阅下面的 UUID 部分）。您通过以下查询获得的这些 ID

    SELECT max(id)
FROM table1
GROUP BY row3, EXTRACT(hour FROM "time")
;

    （这将从您的数据中省略日期，这可能是您想要的，也可能不是。在后一种情况下，您可能想要使用它date_trunc('hour', "time")。）

    然后你可以使用上面的查询来填充一个表，就像你在你的问题中所做的那样，或者删除其他所有table1内容

    DELETE FROM table1
WHERE id NOT IN (
    [the query above comes here]
)
;

    UUID

    UUID没有max()。因此，您的情况需要另一种解决方案，例如：

    SELECT DISTINCT first_value(id) OVER w
FROM table1
WINDOW w AS (PARTITION BY row3, EXTRACT(hour FROM "time")
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
;

    这个应用了一个窗口函数，分组是通过PARTITION BY子句 and来实现的DISTINCT。

    （致谢：感谢 AndriyM 的创意！）

    • 5

相关问题