AnolisOS 8.8 PostgreSQL 数据去重

在项目实现中我们经常会遇到这样的需求，就是需要对重复数据的去重实现。我们说简单的获取最大或者最小的我们可以结合 GROUP BY 和 MIN 或者 MAX 来实现，但是如果是想获取最大或者最小的记录的完整的数据整行记录就比较麻烦，那么在 PostgreSQL 中有没有什么简单的方法可以实现这一需求。下面我以一个案例来说明该需求的具体实现

假设有这样的一个表结构，其内数据如下：
CREATE TABLE distinct_test(id bigserial,cno int,cage int,ctime timestamp(0),PRIMARY KEY(id));
CREATE INDEX IF NOT EXISTS idx_distinct_test_cno ON public.distinct_test USING btree(cno);

INSERT INTO distinct_test(cno,cage,ctime) values(1001,100,NOW());
INSERT INTO distinct_test(cno,cage,ctime) values(1002,200,NOW());
INSERT INTO distinct_test(cno,cage,ctime) values(1003,300,NOW());
INSERT INTO distinct_test(cno,cage,ctime) values(1004,400,NOW());
INSERT INTO distinct_test(cno,cage,ctime) values(1001,500,NOW());
INSERT INTO distinct_test(cno,cage,ctime) values(1002,400,NOW());
INSERT INTO distinct_test(cno,cage,ctime) values(1003,80,NOW());
INSERT INTO distinct_test(cno,cage,ctime) values(1004,30,NOW());
INSERT INTO distinct_test(cno,cage,ctime) values(1005,50,NOW());

SELECT * FROM distinct_test;

1   1001    100 2024-08-07 08:47:51
2   1002    200 2024-08-07 08:47:51
3   1003    300 2024-08-07 08:47:51
4   1004    400 2024-08-07 08:47:51
5   1001    500 2024-08-07 08:47:51
6   1002    400 2024-08-07 08:47:51
7   1003    80  2024-08-07 08:47:51
8   1004    30  2024-08-07 08:47:51
9   1005    50  2024-08-07 08:47:51

现在说一下具体需求：我需要这个结果集中 cno 去重，按照 cage 倒序排序，如果 cage 相同的情况下，再按照 ctime 倒序，如果 ctime 还是相同则按照 id 倒序排序，这里需要注意的是我需要的是该 cno 去重后的所有的字段。最后结果集如下：

1001    500 2024-08-07 08:47:51 5
1002    400 2024-08-07 08:47:51 6
1003    300 2024-08-07 08:47:51 3
1004    400 2024-08-07 08:47:51 4
1005    50  2024-08-07 08:47:51 9

SQL 实现：
SELECT distinct on (cno) cno,cage,ctime,id FROM distinct_test ORDER BY cno ASC,cage DESC,ctime DESC,id DESC;

变种需求：如果我需要的是 cage 是正序，相同情况下 ctime 也是正序，再相同则 id 倒序。
SELECT distinct on (cno) cno,cage,ctime,id FROM distinct_test ORDER BY cno ASC,cage ASC,ctime ASC,id DESC;

1001    100 2024-08-07 08:47:51 1
1002    200 2024-08-07 08:47:51 2
1003    80  2024-08-07 08:47:51 7
1004    30  2024-08-07 08:47:51 8
1005    50  2024-08-07 08:47:51 9

备注：这里非常巧妙的运用了 PostgreSQL 特有的 DISTINCT ON (column_name) 特性。
当然了，这个 SQL 逻辑的实现还有别的方法，比如窗口函数或者嵌套自关联等都可以实现这一目标，这里重点主要是介绍一下 DISTINCT ON 的巧妙运用。

窗口函数的实现：
WITH aa AS(
SELECT cno,cage,ctime,id,row_number() OVER (PARTITION BY cno ORDER BY cage DESC) AS rownums
FROM distinct_test)

SELECT cno,cage,ctime,id FROM aa WHERE rownums = 1;

备注：我这个案例有一个缺点就是插入时间都是一样的，如果改成不一样的时间会更加的直观一些。