PostgreSQL DISTINCT 和 DISTINCT ON 语法的使用

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

标签

PostgreSQL , distinct , distinct on , window 函数


背景

本文介绍一下distinct的几种用法。

https://www.postgresql.org/docs/10/static/queries-select-lists.html#queries-distinct

1、返回唯一值

After the select list has been processed, the result table can optionally be subject to the elimination of duplicate rows.   
  
The DISTINCT key word is written directly after SELECT to specify this:  
  
SELECT DISTINCT select_list ...  
  
(Instead of DISTINCT the key word ALL can be used to specify the default behavior of retaining all rows.)  
  
Obviously, two rows are considered distinct if they differ in at least one column value. Null values are considered equal in this comparison.  

2、返回指定列唯一的任意行。 也可以使用窗口来实现类似功能, 但是性能没有distinct on好,因为它是任意的。

Alternatively, an arbitrary expression can determine what rows are to be considered distinct:  
  
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.)  
  
The DISTINCT ON clause is not part of the SQL standard and is sometimes considered bad   
style because of the potentially indeterminate nature of its results.   
  
With judicious use of GROUP BY and subqueries in FROM,   
this construct can be avoided, but it is often the most convenient alternative.  

3、返回唯一值个数

select count(distinct (表达式1, ....)) from tbl;  

或

select count(*) from (select 表达式,.... from tbl group by 表达式,....) t;  

例子

1、返回所有记录。

select ALL id, c1 from test;  
  
或  
  
select id, c1 from test;  

2、返回 id,c1 唯一值。(这里NULL视为相等)。

select DISTINCT id, c1 from test;   
  
或  
  
select id, c1 from test group by id, c1;  

3、返回c3唯一的任意行。

select distinct on (c3) c2,c3 from tbl;  
postgres=# explain (analyze,verbose,timing,costs,buffers) select distinct on (c3) c2,c3 from tbl;  
                                                            QUERY PLAN                                                              
----------------------------------------------------------------------------------------------------------------------------------  
 Unique  (cost=115063.84..120063.84 rows=11 width=8) (actual time=1865.317..2279.840 rows=11 loops=1)  
   Output: c2, c3  
   Buffers: shared hit=5406  
   ->  Sort  (cost=115063.84..117563.84 rows=1000000 width=8) (actual time=1865.312..2068.536 rows=1000000 loops=1)  
         Output: c2, c3  
         Sort Key: tbl.c3  
         Sort Method: quicksort  Memory: 71452kB  
         Buffers: shared hit=5406  
         ->  Seq Scan on public.tbl  (cost=0.00..15406.00 rows=1000000 width=8) (actual time=0.017..264.041 rows=1000000 loops=1)  
               Output: c2, c3  
               Buffers: shared hit=5406  
 Planning time: 0.070 ms  
 Execution time: 2291.536 ms  
(13 rows)  
postgres=# select distinct on (c3) c2,c3 from tbl;  -- c3 唯一, 但是可能返回任意行  
 c2  | c3   
-----+----  
 100 |  0  
  23 |  1  
  63 |  2  
  47 |  3  
  34 |  4  
  17 |  5  
  60 |  6  
   8 |  7  
  70 |  8  
  73 |  9  
  94 | 10  
(11 rows)  

使用窗口函数可以达到类似效果,但是可以确定返回哪行,因此也更慢一些:

select * from (select row_number() over (partition by c3) as rn, * from tbl) t where rn=1;  
  
postgres=# select * from (select row_number() over (partition by c3) as rn, * from tbl) t where rn=1;  
 rn | c1  | c2  | c3   
----+-----+-----+----  
  1 | 420 | 100 |  0  
  1 | 721 |  23 |  1  
  1 |  80 |  63 |  2  
  1 | 322 |  47 |  3  
  1 | 457 |  34 |  4  
  1 | 386 |  17 |  5  
  1 | 491 |  60 |  6  
  1 | 260 |   8 |  7  
  1 |  41 |  70 |  8  
  1 |  56 |  73 |  9  
  1 | 154 |  94 | 10  
(11 rows)  
postgres=# explain analyze select * from (select row_number() over (partition by c3) as rn, * from tbl) t where rn=1;  
                                                            QUERY PLAN                                                              
----------------------------------------------------------------------------------------------------------------------------------  
 Subquery Scan on t  (cost=115063.84..145063.84 rows=5000 width=20) (actual time=1855.132..2860.276 rows=11 loops=1)  
   Filter: (t.rn = 1)  
   Rows Removed by Filter: 999989  
   ->  WindowAgg  (cost=115063.84..132563.84 rows=1000000 width=20) (actual time=1855.129..2739.190 rows=1000000 loops=1)  
         ->  Sort  (cost=115063.84..117563.84 rows=1000000 width=12) (actual time=1855.115..2028.946 rows=1000000 loops=1)  
               Sort Key: tbl.c3  
               Sort Method: quicksort  Memory: 71452kB  
               ->  Seq Scan on tbl  (cost=0.00..15406.00 rows=1000000 width=12) (actual time=0.015..251.021 rows=1000000 loops=1)  
 Planning time: 0.115 ms  
 Execution time: 2871.551 ms  
(10 rows)  

4、返回有多少个唯一值

select count(distinct (表达式,....)) from tbl;  
postgres=# select count(distinct c3) from tbl;  
 count   
-------  
    11  
(1 row)  
  
postgres=# select count(distinct (c3,c2)) from tbl;  
 count   
-------  
  1111  
(1 row)  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(distinct (c3,c2)) from tbl;;  
                                                         QUERY PLAN                                                           
----------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=17906.00..17906.01 rows=1 width=8) (actual time=6905.660..6905.661 rows=1 loops=1)  
   Output: count(DISTINCT ROW(c3, c2))  
   Buffers: shared hit=5406  
   ->  Seq Scan on public.tbl  (cost=0.00..15406.00 rows=1000000 width=8) (actual time=0.017..156.436 rows=1000000 loops=1)  
         Output: c1, c2, c3  
         Buffers: shared hit=5406  
 Planning time: 0.062 ms  
 Execution time: 6905.727 ms  
(8 rows)  

或使用group by的方法

select count(*) from (select 表达式,.... from tbl group by 表达式,....) t;  
postgres=# select count(*) from (select c2,c3 from tbl group by c2,c3) t;  
 count   
-------  
  1111  
(1 row)  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from (select c2,c3 from tbl group by c2,c3) t;  
                                                            QUERY PLAN                                                              
----------------------------------------------------------------------------------------------------------------------------------  
 Aggregate  (cost=20431.00..20431.01 rows=1 width=8) (actual time=674.609..674.609 rows=1 loops=1)  
   Output: count(*)  
   Buffers: shared hit=5406  
   ->  HashAggregate  (cost=20406.00..20417.11 rows=1111 width=8) (actual time=674.093..674.409 rows=1111 loops=1)  
         Output: tbl.c2, tbl.c3  
         Group Key: tbl.c2, tbl.c3  
         Buffers: shared hit=5406  
         ->  Seq Scan on public.tbl  (cost=0.00..15406.00 rows=1000000 width=8) (actual time=0.014..143.904 rows=1000000 loops=1)  
               Output: tbl.c1, tbl.c2, tbl.c3  
               Buffers: shared hit=5406  
 Planning time: 0.120 ms  
 Execution time: 674.684 ms  
(12 rows)  
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
7月前
|
SQL Oracle 关系型数据库
实时计算 Flink版操作报错之往GREENPLUM 6 写数据,用postgresql-42.2.9.jar 报 ON CONFLICT (uuid) DO UPDATE SET 语法有问题。怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
483 0
|
6月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL数据库的字符串拼接语法使用说明
【6月更文挑战第11天】PostgreSQL数据库的字符串拼接语法使用说明
646 1
|
7月前
|
关系型数据库 PostgreSQL
postgresql字符串拼接语法
【5月更文挑战第6天】postgresql字符串拼接语法
174 0
|
7月前
|
SQL 关系型数据库 MySQL
MySQL【实践 02】MySQL迁移到PostgreSQL数据库的语法调整说明及脚本分享(通过bat命令修改mapper文件内的SQL语法)
MySQL【实践 02】MySQL迁移到PostgreSQL数据库的语法调整说明及脚本分享(通过bat命令修改mapper文件内的SQL语法)
276 0
|
SQL Oracle 关系型数据库
Polar DB-O (兼容 Oracle 语法版本)和Polar DB PostgreSQL 版本概述(二)
Polar DB-O (兼容 Oracle 语法版本)和Polar DB PostgreSQL 版本概述(二)
1847 0
|
SQL 安全 关系型数据库
PostgreSQL 12 文档: SQL 语法
SQL 命令 这部分包含PostgreSQL支持的SQL命令的参考信息。每条命令的标准符合和兼容的信息可以在相关的参考页中找到。
142 0
|
SQL 弹性计算 关系型数据库
PostgreSQL 12 preview - CTE 增强,支持用户语法层控制 materialized 优化
标签 PostgreSQL , CTE , materialized , not materialized , push down 背景 PostgreSQL with 语法,能跑非常复杂的SQL逻辑,包括递归,多语句物化计算等。 在12以前的版本中,WITH中的每一个CTE(common table express),都是直接进行物化的,也就是说外层的条件不会推到CTE(物化节点)里
1021 0
|
SQL 存储 移动开发
PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)|学习笔记
快速学习3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)
 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)|学习笔记
|
SQL 关系型数据库 数据库
3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(三)|学习笔记
快速学习3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(三)

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版