sql - Postgres DISTINCT 与 DISTINCT ON 之间有什么区别？

Question

我有一个使用以下语句创建的 Postgres 表。该表由另一个服务的数据转储填充。

CREATE TABLE data_table (
    date date DEFAULT NULL,
    dimension1 varchar(64) DEFAULT NULL,
    dimension2 varchar(128) DEFAULT NULL
) TABLESPACE pg_default;

我正在构建的 ETL 中的步骤之一是提取 dimension1 的唯一值。并将它们插入另一个中间表中。
但是，在一些测试中，我发现下面的 2 个命令不会返回相同的结果。我希望两者都返回相同的金额。
与第二个命令相比，第一个命令返回更多的结果(1466 行与 1504 行。

-- command 1
SELECT DISTINCT count(dimension1)
FROM data_table;

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

对此有任何明显的解释吗？除了解释之外，是否有任何建议对我应该做的数据进行检查？

编辑:以下查询都返回 1504 (与“简单” DISTINCT 相同)

SELECT count(*)
FROM data_table WHERE dimension1 IS NOT NULL;

SELECT count(dimension1)
FROM data_table;

谢谢!

Answer 1

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 * from 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 之后。它适用于整个元组，而不适用于结果的一个属性。
区别于 是该语言的 postgresql 补充。分组依据类似，但不完全相同。
它的语法是:

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

例如:

 SELECT DISTINCT ON (a) * from R

它的语义可以描述如下。像往常一样计算查询——不带DISTINCT ON(a)——但在投影结果之前，对当前结果进行排序，并根据DISTINCT ON中的属性列表对其进行分组(类似于group by)。现在，使用每组中的第一个元组进行投影并忽略其他元组。
例子:

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

然后对于 a 的每个不同值，取第一个元组。这与以下内容相同:

 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;

这会给你一个类似的结果。
Postgresql 将允许这个查询，当且仅当存在从 a 到 b 的函数依赖。换句话说，如果对于关系 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 的计数(data_table 中维度 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 并执行其余的操作
询问。这是一个按维度 1 分组。因此这部分查询
将导致每个维度 1 的不同值产生一个元组。
现在，DISTINCT ON。它再次使用维度 1。但是维度 1 已经是唯一的(由于 group by)。因此
这使得 DISTINCT ON superflouos(它什么都不做)。
最终计数只是对 group by 中所有元组的计数。
如您所见，以下查询中存在等价关系(适用于具有属性 a 的任何关系):

SELECT (DISTINCT ON a) a
FROM R

和

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

推论
当您想要访问在功能上依赖于 group by 属性的值时，DISTINCT ON 在 group by 中很有用。换句话说，如果您知道对于每组属性，它们的第三个属性始终具有相同的值，则在该组属性上使用 DISTINCT。否则，您必须通过 JOIN 来检索第三个属性。