Postgres中的distinct()函数（不是select限定符）

Question

我刚接触到一条SQL查询语句，针对的是一个Postgres数据库，并使用了名为"distinct"的函数。具体如下：

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

请注意，这里的DISTINCT并不是SELECT语句中普通的去重关键字——至少它不是DISTINCT关键字的常规语法，注意到圆括号的存在。它似乎将DISTINCT用作函数，或者这是一些特殊的语法。

你有什么想法吗？

我试着进行一些尝试，如果我写：

select distinct(foo)
from bar

我得到的结果与

select distinct foo
from bar

当我将它与同一选择中的其他字段组合时，我不清楚它到底在做什么。

我在Postgres文档中找不到任何内容。

感谢任何帮助！

- Jay

Answer 1

^{(这个问题很久了，但在谷歌搜索结果中排名很高，“sql distinct is not a function”的搜索结果中排名靠前（Stack Overflow的第二，第一），然而仍然缺少一个令人满意的答案，所以...)}

实际上，这是在SELECT语句中普通的DISTINCT限定符--但具有误导性的语法（在这一点上，您是正确的）。

DISTINCT从来不是函数，总是关键字。这里它被错误地用作函数，但是

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

实际上等同于以下所有形式：

_{-- 在distinct之后添加一个空格：}

select distinct (pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

_{-- 删除列名周围的括号：}

select distinct pattern as pattern, style, ... etc ...
from styleview
where ... etc ...

_{-- 缩进从属篇章内容：}

select distinct
    pattern as pattern, style, ... etc ...
from
    styleview
where
    ... etc ...

_{-- 删除与列名相同的冗余别名：}

select distinct
    pattern, style, ... etc ...
from
    styleview
where
    ... etc ...

相关阅读：

• http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx
• https://dev59.com/h0jSa4cB1Zd3GeqPGIqn#1164529

---

注意：在这个问题的一个回答中，OMG Ponies提到了PostgreSQL所支持的DISTINCT ON扩展。
但是（正如Jay在评论中指出的那样），这不是在此处使用的方法，因为查询结果将会不同，例如：

select distinct on(pattern) pattern, style, ... etc ...
from styleview
where ... etc ...
order by pattern, ... etc ...

相当于：

select  distinct on (pattern)
    pattern, style, ... etc ...
from
    styleview
where
    ... etc ...
order by
    pattern, ... etc ...

相关阅读：

• http://www.noelherrick.com/blog/postgres-distinct-on

---

注：Lukas Eder在回答本问题时提到了在聚合函数中使用DISTINCT关键字的语法：
HSQLDB使用的COUNT(DISTINCT(foo, bar, ...))语法
（或适用于MySQL、PostgreSQL、SQL Server、Oracle和其他可能的COUNT(DISTINCT foo, bar, ...)语法）。
但显然这不是在这里使用的方法。

- gx_

---

我早已离开了那个出现问题的公司，这些日子也没有 Postgres。但是你的回答听起来很有道理。我猜原来的程序员可能是想说“on”，但不小心漏了，或者最初括号里面还有其他东西，后来简化成了只有“pattern”，使括号和“as”变得多余。 - Jay

Answer 2

根据文档：

如果指定了DISTINCT，则从结果集中删除所有重复的行（保留每组重复行中的一行）。ALL表示相反：保留所有行；这是默认设置。

DISTINCT ON（expression [,...]）仅保留给定表达式评估为相等的每组行的第一行。 DISTINCT ON 表达式使用与 ORDER BY 相同的规则进行解释（请参见上文）。请注意，每个集合的“第一行”是不可预测的，除非使用 ORDER BY 确保所需行首先出现。例如：

ON 部分是可选的，因此它实际上取决于：

1. 是否使用括号
2. 查询中的位置 - 如果在 SELECT 子句的任何位置使用 DISTINCT，SQL Server 和 MySQL 会抛出错误

据我所知，PostgreSQL 是唯一支持此语法的数据库。

- OMG Ponies

---

谢谢您的回复，但我认为那不是答案，或者至少不完全是。我熟悉“distinct on”选项。文档没有表明“on”是可选的，如果我写“select distinct(foo) from bar”，我会得到foo的列表，但如果我写“select distinct on (foo) from bar”，我会收到一个错误消息，因为我没有给出任何选择字段（“select distinct on (foo) foo from bar”可以工作）。但您是正确的，“distinct”如果不是紧接在“select”之后，它会报错。所以也许这只是一种“distinct on”的替代语法？ - Jay

@Jay：抱歉，我手头没有 PostgreSQL 进行测试。 - OMG Ponies

这份文档的这部分内容为什么事情是这样工作提供了一个提示，但更好的解释可以从 SQL-1999 标准中获得，该标准非常好地规定了这个语法。 - Lukas Eder

我建议 [ ON (... [,...]) ] 与答案无关。对于这个具体的问题，括号只是被忽略了。请注意，您还必须将 ON 的表达式与 ORDER BY 匹配。 - Paul Maxwell

Answer 3

这可能是打字错误或者是某人对自己写的内容理解不够清楚。

我不知道所有细节，但是你可以使用括号作为优先级运算符（就像在数学中一样）。但是，我认为最终你可以在很多东西周围加上括号而实际上并不改变它们的含义。

例如，以下两个查询返回完全相同的结果：

select foo
from bar

select (foo)
from bar

这很令人困惑，因为您还可以使用括号将列分组为记录，例如：

select (foo, baz)
from bar

所以在您的原始查询中，他们实际上写的内容相当于这个：

select distinct *
from
(
    select pattern as pattern, style, ... etc ...
    from styleview
    where ... etc ...
)

这可能是他们意图的内容，也可能不是。如果我要猜测的话，我会猜测他们使用了一些其他答案中提到的 "DISTINCT ON(...)" 语法。

- Emery Lapinski

Answer 4

来自PostgreSQL文档：

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]

在引用的语法的第一行中，您会发现ON部分是可选的，但是也正是这个ON部分引用了括号。换句话说，除非有ON存在，否则括号是没有意义的。

因此，对于这个问题，[ ON ( expression [, ...] ) ]不相关。

这里是一些非常简单的测试数据：

CREATE TABLE bar
    (foo varchar(3), fub varchar(1), flut timestamp)
;

INSERT INTO bar
    (foo, fub, flut)
VALUES
    ('one', 'a', '2016-01-01 01:01:03'),
    ('one', 'b', '2016-01-01 01:01:02'),
    ('one', 'c', '2016-01-01 01:01:01'),
    ('two', 'd', '2016-01-01 01:01:03'),
    ('two', 'e', '2016-01-01 01:01:02'),
    ('two', 'f', '2016-01-01 01:01:01')
;

让我们首先集中注意括号。在select后面跟着一个表达式时，单独使用括号会产生什么效果？例如：

select (foo) from bar;

| foo |
|-----|
| one |
| one |
| one |
| two |
| two |
| two |

我相信你会发现这个结果与不使用括号包围列foo的查询完全相同，所以我们从那个查询中得到的结论是括号没有任何作用。然而，如果我们引入DISTINCT会发生什么呢？

select distinct(foo) from bar;

| foo |
|-----|
| two |
| one |

select distinct foo from bar;

| foo |
|-----|
| two |
| one |

我们再次看到，括号根本没有任何影响。如果我们回顾一下语法，这是一致的。DISTINCT不是函数，在DISTINCT后面放置一个表达式括号并不会改变它的工作方式。

所以，对于这个问题：

我刚刚遇到了一个针对Postgres数据库的SQL查询，使用了一个名为“distinct”的函数。即：

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

DISTINCT不是一个函数！并且在该示例查询中，括号会被忽略。

---

---

如果使用可选的[ ON (expression) ]，确实会改变结果。

测试a：

select distinct ON (foo) foo, fub, flut from bar order by foo

| foo | fub |                      flut |
|-----|-----|---------------------------|
| one |   a | January, 01 2016 01:01:03 |
| two |   d | January, 01 2016 01:01:03 |

测试 b：

select distinct ON (fub) foo, fub, flut from bar order by fub

| foo | fub |                      flut |
|-----|-----|---------------------------|
| one |   a | January, 01 2016 01:01:03 |
| one |   b | January, 01 2016 01:01:02 |
| one |   c | January, 01 2016 01:01:01 |
| two |   d | January, 01 2016 01:01:03 |
| two |   e | January, 01 2016 01:01:02 |
| two |   f | January, 01 2016 01:01:01 |

测试c:

select distinct ON (flut) foo, fub, flut from bar order by flut

| foo | fub |                      flut |
|-----|-----|---------------------------|
| one |   c | January, 01 2016 01:01:01 |
| one |   b | January, 01 2016 01:01:02 |
| one |   a | January, 01 2016 01:01:03 |

[ ON (expression) ] 功能非常有用，因为它可以在不同的列表中提供“第一个”、“最后一个”、“最早的”或“最近的”行。但请记住，此功能与 ORDER BY 子句耦合，实际上，除非 ORDER BY 子句还引用 SELECT DISTINCT ON PostgreSQL 中使用的表达式，否则会产生错误：

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

上面的示例可以在 sqlfiddle 这里 运行。

---

虽然我不想复杂化我的回答，但有一点需要提及：

select distinct (foo,fub) from bar;

现在括号有作用了，但它们所做的与distinct没有直接关系。请参见“复杂类型”

- Paul Maxwell