跳过正文
  1. PostgreSQL大法师/

Distinct On 去除重复数据

·837 字·2 分钟· ·
PostgreSQL PG开发 SQL
冯若航
作者
冯若航
Pigsty 创始人, @Vonng
目录

Distinct On是PostgreSQL提供的特有语法,可以高效解决一些典型查询问题,例如,快速找出分组内具有最大最小值的记录。

前言
#

找出分组内具有最大最小值的记录,这是一个非常常见的需求。用传统SQL当然有办法解决,但是都不够优雅,PostgreSQL的SQL扩展语法Distinct ON能一步到位解决这一类问题。

DISTINCT ON 语法
#

SELECT DISTINCT ON (expression [, expression ...]) select_list ...

Here expression is an arbitrary value expression that is evaluated for all rows. A set of rows for which all the expressions are equal are considered duplicates, and only the first row of the set is kept in the output. Note that the “first row” of a set is unpredictable unless the query is sorted on enough columns to guarantee a unique ordering of the rows arriving at the DISTINCT filter. (DISTINCT ON processing occurs after ORDER BY sorting.)

Distinct On应用案例
#

例如,找出每台机器的最新日志在日志表中,取出按照机器node_id分组,时间戳ts最大的的日志记录。

CREATE TABLE nodes(node_id INTEGER, ts TIMESTAMP);

INSERT INTO test_data
SELECT (random() * 10)::INTEGER as node_id, t
FROM generate_series('2019-01-01'::TIMESTAMP, '2019-05-01'::TIMESTAMP, '1h'::INTERVAL) AS t;

这里可以制造一些随机数据

5	2019-01-01 00:00:00.000000
0	2019-01-01 01:00:00.000000
9	2019-01-01 02:00:00.000000
1	2019-01-01 03:00:00.000000
7	2019-01-01 04:00:00.000000
2	2019-01-01 05:00:00.000000
8	2019-01-01 06:00:00.000000
3	2019-01-01 07:00:00.000000
1	2019-01-01 08:00:00.000000
4	2019-01-01 09:00:00.000000
9	2019-01-01 10:00:00.000000
0	2019-01-01 11:00:00.000000
3	2019-01-01 12:00:00.000000
6	2019-01-01 13:00:00.000000
9	2019-01-01 14:00:00.000000
1	2019-01-01 15:00:00.000000
7	2019-01-01 16:00:00.000000
8	2019-01-01 17:00:00.000000
9	2019-01-01 18:00:00.000000
10	2019-01-01 19:00:00.000000
5	2019-01-01 20:00:00.000000
4	2019-01-01 21:00:00.000000

现在使用DistinctON,这里Distinct On后面的括号里代表了记录需要按哪一个键进行除重,在括号内的表达式列表上有着相同取值的记录会只保留一条记录。(当然保留哪一条是随机的,因为分组内哪一条记录先返回是不确定的)

SELECT DISTINCT ON (node_id) * FROM test_data

0	2019-04-30 17:00:00.000000
1	2019-04-30 22:00:00.000000
2	2019-04-30 23:00:00.000000
3	2019-04-30 13:00:00.000000
4	2019-05-01 00:00:00.000000
5	2019-04-30 20:00:00.000000
6	2019-04-30 11:00:00.000000
7	2019-04-30 15:00:00.000000
8	2019-04-30 16:00:00.000000
9	2019-04-30 21:00:00.000000
10	2019-04-29 18:00:00.000000

DistinctON有一个配套的ORDER BY子句,用于指明分组内哪一条记录将被保留,排序第一条记录会留下,因此如果我们想要每台机器上的最新日志,可以这样写。

SELECT DISTINCT ON (node_id) * FROM test_data ORDER BY node_id, ts DESC NULLS LAST

0	2019-04-30 17:00:00.000000
1	2019-04-30 22:00:00.000000
2	2019-04-30 23:00:00.000000
3	2019-04-30 13:00:00.000000
4	2019-05-01 00:00:00.000000
5	2019-04-30 20:00:00.000000
6	2019-04-30 11:00:00.000000
7	2019-04-30 15:00:00.000000
8	2019-04-30 16:00:00.000000
9	2019-04-30 21:00:00.000000
10	2019-04-29 18:00:00.000000

使用索引加速Distinct On查询
#

Distinct On查询当然可以被索引加速,例如以下索引就可以让上面的查询用上索引

CREATE INDEX ON test_data USING btree(node_id, ts DESC NULLS LAST);

set enable_seqscan = off;
explain SELECT DISTINCT ON (node_id) * FROM test_data ORDER BY node_id, ts DESC NULLS LAST;
Unique  (cost=0.28..170.43 rows=11 width=12)
  ->  Index Only Scan using test_data_node_id_ts_idx on test_data  (cost=0.28..163.23 rows=2881 width=12)

注意,排序的时候一定要确保NULLS FIRST|LAST与查询时实际使用的规则匹配。否则可能用不上索引。

相关文章

用 Exclude 实现互斥约束
·1713 字·4 分钟
PostgreSQL PG开发 SQL
Exclude约束是一个PostgreSQL扩展,它可以实现一些更高级,更巧妙的的数据库约束。
函数易变性等级分类
·1180 字·3 分钟
PostgreSQL PG开发 函数
PgSQL中的函数默认有三种易变性等级,合理使用可以显著改善性能。
GO与PG实现缓存同步
·2389 字·5 分钟
PostgreSQL PG开发 触发器
巧妙运用Pg的Notify功能,可以方便地通知应用元数据变更,实现基于触发器的逻辑复制。
用触发器审计数据变化
·658 字·2 分钟
PostgreSQL PG开发 触发器
有时候,我们希望记录一些重要的元数据变更,以便事后审计之用。PostgreSQL的触发器就可以很方便地自动解决这一需求。
SQL实现ItemCF推荐系统
·3292 字·7 分钟
PostgreSQL PG开发 推荐系统
用PostgreSQL 5分钟实现一个最简单ItemCF推荐系统
UUID性质原理与应用
·3431 字·7 分钟
PostgreSQL PG开发 架构
UUID性质原理与应用,以及如何利用PostgreSQL的存储过程操作UUID。