DISTINCT和DISTINCT ON具有完全不同的语义。

首先是理论

DISTINCT应用于整个元组。一旦计算出查询结果，DISTINCT就会从结果中删除任何重复的元组。

例如，假设一个表R包含以下内容：

# table r;
a | b 
---+---
1 | a
2 | b
3 | c
3 | d
2 | e
1 | a

(6行)

SELECT DISTINCT*FOR R结果为：

# select distinct * from r;
 a | b 
---+---
 1 | a
 3 | d
 2 | e
 2 | b
 3 | c
(5 rows)

请注意，DISTINCT适用于投影属性的整个列表：

select distinct * from R

在语义上等同于

select distinct a,b from R

你不能签发

select a, distinct b From R

DISTINCT必须跟在SELECT之后。它应用于整个元组，而不是结果的属性。

DISTINCT ON是对该语言的PostgreSQL添加。它与分组依据相似，但不完全相同。

其语法为：

SELECT DISTINCT ON (attributeList) <rest as any query>

例如：

SELECT DISTINCT ON (a) * from R

IT语义可以描述如下。像往常一样计算--不包括DISTINCT ON(A)--但在结果投影之前，对当前结果进行排序，并根据DISTINCT ON中的属性列表对其进行分组(类似于GROUP BY)。现在，使用每组中的第一个元组进行投影，忽略其他元组。

示例：

select * from r order by a;
     a | b 
    ---+---
     1 | a
     2 | e
     2 | b
     3 | c
     3 | d
    (5 rows)

然后，对于a的每个不同值(在本例中为1、2和3)，取第一个元组。这与以下内容相同：

SELECT DISTINCT on (a) * from r;
  a | b 
 ---+---
 1 | a
 2 | b
 3 | c
 (3 rows)

某些DBMS(尤其是SQLite)将允许您运行以下查询：

SELECT a,b from R group by a;

这会给你一个类似的结果。

当且仅当存在从a到b的函数依赖时，当且仅当存在从a到b的函数依赖时，PostgreSQL才允许该查询。换句话说，如果对于关系R的任何示例，每个值或a只有一个唯一的元组(因此选择第一个元组是确定性的：只有一个元组)，则该查询将有效。

例如，如果R的主键是a，则a->b并且：

SELECT a,b FROM R group by a

等同于：

SELECT DISTINCT on (a) a, b from r;

现在，回到你的问题：

第一个查询：

SELECT DISTINCT count(dimension1)
FROM data_table;

计算Dimension1的计数(data_table中的元组数，其中Dimsion1不为空)。该查询返回一个元组，它始终是唯一的(因此DISTINCT是冗余的)。

问题2：

SELECT count(*)
FROM (SELECT DISTINCT ON (dimension1) dimension1
FROM data_table
GROUP BY dimension1) AS tmp_table;

这是查询中的查询。为了清楚起见，让我重写一遍：

WITH tmp_table AS (
   SELECT DISTINCT ON (dimension1) 
     dimension1 FROM data_table
     GROUP by dimension1) 
SELECT count(*) from tmp_table

让我们先计算一下tmp_table。正如我在上面提到的，让我们首先忽略DISTINCT ON，并执行查询的其余部分。这是按维度1分组。因此，查询的这一部分将为每个不同的Dimension1值生成一个元组。

现在，不同的是。它再次使用了Dimsion1。但是维度1已经是唯一的(由于GROUP BY)。因此，这使得DISTINCT on Superflous(它什么都不做)。最后的计数只是GROUP BY中所有元组的计数。

如您所见，在以下查询中存在等价性(它适用于具有属性a的任何关系)：

SELECT (DISTINCT ON a) a
FROM R

和

SELECT a FROM R group by a

和

SELECT DISTINCT a FROM R

警告

对于给定的数据库示例，在查询中使用DISTINCT ON RESULTS可能是不确定的。换句话说，对于相同的表，查询可能返回不同的结果。

一个有趣的方面

DISTINCT ON以一种更干净的方式模拟了SQLite的坏行为。假设R有两个属性a和b：

SELECT a, b FROM R group by a

是SQL中的非法语句。然而，它运行在SQLite上。它只是从a的相同值的组中的任何元组中取一个随机值b。在PostgreSQL中，此语句是非法的。相反，您必须使用DISTINCT ON并写入：

SELECT DISTINCT ON (a) a,b from R

• 推论*

当您要访问在功能上依赖于GROUP BY属性的值时，DISTINCT ON在GROUP BY中非常有用。换句话说，如果您知道对于每组属性，它们的第三个属性的值总是相同的，那么对该组属性使用DISTINCT。否则，您将不得不进行联接以检索第三个属性。

第一个查询给出dimension1的非空值的数量，而第二个查询返回该列的不同值的数量。如果列包含重复项或空值，则这些数字显然不相等。

中的单词DISTINCT

SELECT DISTINCT count(dimension1)
FROM data_table;

没有任何意义，因为查询返回单行。也许你想要

SELECT count(DISTINCT dimension1)
FROM data_table;

它返回dimension1的非空值的个数。请注意，它不同于

SELECT count(*)
FROM (
    SELECT DISTINCT ON (dimension1) dimension1
    FROM data_table
    -- GROUP BY dimension1 -- redundant
    ) AS tmp_table;

最后一个查询生成该列的所有不同值(是否为空)的数量。

通过直观的例子来学习和理解所发生的事情。
下面是在PostgreSQL上执行的一些SQL代码：

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
    id int NOT NULL primary key,
    col1 varchar(64) DEFAULT NULL
);
INSERT INTO test_table (id, col1) VALUES 
(1,'foo'), (2,'foo'), (3,'bar'), (4,null);
select count(*) as total1 from test_table;
-- returns: 4
-- Because the table has 4 records.
select distinct count(*) as total2 from test_table;
-- returns: 4
-- The count(*) is just one value. Making 1 total unique can only result in 1 total. 
-- So the distinct is useless here.
select col1, count(*) as total3 from test_table group by col1 order by col1;
-- returns 3 rows: ('bar',1),('foo',2),(NULL,1)
-- Since there are 3 unique col1 values. NULL's are included.
select distinct col1, count(*) as total4 from test_table group by col1 order by col1;
-- returns 3 rows: ('bar',1),('foo',2),(NULL,1)
-- The result is already grouped, and therefor already unique. 
-- So again, the distinct does nothing extra here.
select count(distinct col1) as total5 from test_table;
-- returns 2
-- NULL's aren't counted in a count by value. So only 'foo' & 'bar' are counted
select distinct on (col1) id, col1 from test_table order by col1 asc, id desc;
-- returns 3 rows: (2,'a'),(3,'b'),(4,NULL)
-- So it gets the records with the maximum id per unique col1
-- Note that the "order by" matters here. Changing that DESC to ASC would get the minumum id.
select count(*) as total6 from (select distinct on (col1) id, col1 from test_table order by col1 asc, id desc) as q;
-- returns 3. 
-- After seeing the previous query, what else would one expect?
select distinct col1 from test_table order by col1;
-- returns 3 unique values : ('bar'),('foo'),(null)
select distinct id, col1 from test_table order by col1;
-- returns all records. 
-- Because id is the primary key and therefore makes each returned row unique

以下是一个更直接的摘要，可能对谷歌用户有用，它回答了标题，但没有回答全文的复杂之处：

SELECT DISTINCT

• 可用性：ISO
• 行为：

SELECT DISTINCT col1, col2, col3 FROM mytable

返回col1、col2和col3，并忽略所有col1、col2、col3都相同的行。例如，您可能会得到如下结果：

因为这两行由于4而不相同。但你永远得不到：

因为1 2 3出现两次，并且两行完全相同。这就是DISTINCT所阻止的。

• vs GROUP BY：SELECT DISTINCT基本上是GROUP BY的子集，其中不能使用聚合函数：Is there any difference between GROUP BY and DISTINCT

SELECT DISTINCT ON

• 可用性：PostgreSQL扩展，WONTFIXED by SQLite

• 行为：与DISTINCT不同，DISTINCT ON允许您将

• 你想要独一无二的东西

• 从你想要退还的东西

例如：

SELECT DISTINCT ON(col1), col2, col3 FROM mytable

返回col2和col3，不返回具有相同col1的任何两行。例如：

无法发生，因为我们在col1上有两次1。

以及例如：

SELECT DISTINCT ON(col1, col2), col2, col3 FROM mytable

将防止任何重复的(col1、col2)元组，例如，您可以获得：

因为它具有不同的(1，2)和(1，4)元组，但没有：

如果(1，2)出现两次，则这两次中只能出现一次。

我们可以使用ORDER BY唯一地确定将选择哪一行可能的行，这保证了第一个匹配，例如：

SELECT DISTINCT ON(col1, col2), col2, col3 FROM mytable
  ORDER BY col1 DESC, col2 DESC, col3 DESC

将确保在以下方面：

只有1 2 4会被选中，因为它首先出现在我们的DESC排序中。

• VS GROUP BY：DISTINCT ON不是GROUP BY的子集，因为它允许您访问GROUP BY中不存在的额外行，这在GROUP BY中通常是不允许的，除非：

• 在postgres(unique not null is a TODO for them)中按主键分组

• 或者如果在SQLite/MySQL中允许作为ISO扩展

这使得DISTINCT ON对于实现“查找达到某些列的最大/最小值的整行”的常见用例非常有用：Is there any difference between GROUP BY and DISTINCT

RANK和ROW_NUMBER窗口函数

这些基本可以用作DISTINCT ON的超集，并在SQLite 3.34和PostgreSQL 14.3中进行了实现测试。我强烈建议也研究一下它们，例如：如何从一列中选择不同的列并获得其他列？

尝试

SELECT count(dimension1a)
FROM (SELECT DISTINCT ON (dimension1) dimension1a
FROM data_table
ORDER BY dimension1) AS tmp_table;

DISTINCT ON似乎与GROUP BY同义。