DISTINCT和DISTINCT ON具有完全不同的语义。
先说理论
DISTINCT适用于整个元组。一旦查询的结果被计算出来，DISTINCT将从结果中删除任何重复的元组。
例如，假设有一个名为R的表格，其内容如下：
#table r;
a | b
1 | a
2 | b
3 | c
3 | d
2 | e
1 | a
(共6行)
从R中选择distinct *将得到：
# 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对该语言的补充。它类似于group by，但并非完全相同。
它的语法如下：
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)
一些数据库管理系统（尤其是sqlite）允许您运行此查询：
SELECT a,b from R group by a;
这会给您一个类似的结果。
只有在从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;
计算维度1的数量（数据表中维度1不为空的元组数量）。此查询返回一个元组，始终是唯一的（因此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并执行查询的其余部分。这是按dimension1进行分组。因此查询的这部分将导致每个不同dimension1值的一个元组。
现在，考虑DISTINCT ON。它再次使用dimension1。但是由于已经通过group by唯一确定了dimension1，因此DISTINCT ON变得多余（它什么也不做）。最终结果只是对group by中所有元组的计数。
正如您所看到的，在以下查询中存在等价性（适用于具有属性a的任何关系）：
SELECT (DISTINCT ON a) a
FROM R
and
SELECT a FROM R group by a
和
SELECT DISTINCT a FROM R
警告
在查询中使用DISTINCT ON可能对于数据库的给定实例是不确定性的。
换句话说，对于相同的数据表，查询可能会返回不同的结果。
一个有趣的方面
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
推论
DISTINCT ON在分组查询中很有用，当您想要访问与分组依据属性相关的值时。换句话说，如果您知道对于每组属性，它们始终具有第三个属性的相同值，则使用DISTINCT ON该属性组。否则，您必须执行JOIN操作来检索第三个属性。
