В чем разница между Postgres DISTINCT и DISTINCT ON?

DISTINCT и DISTINCT ON имеют совершенно разные семантики

Сначала теоретическая часть

DISTINCT применяется ко всей записи (tuple). После вычисления результата запроса, DISTINCT удаляет любые дублирующие записи из результата.

Рассмотрим пример таблицы R с следующими данными:

#table r;
 a | b 
---+---
 1 | a
 2 | b
 3 | c
 3 | d
 2 | e
 1 | a
(6 rows)

Запрос 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. Он применяется ко всей записи, а не к атрибуту результата.

DISTINCT ON — это расширение языка в PostgreSQL. Оно похоже, но не идентично GROUP BY.

Его синтаксис:

SELECT DISTINCT ON (attributeList) <дальнейшая часть запроса>

Например:

SELECT DISTINCT ON (a) * FROM R

Его семантику можно описать так. Сначала вычисляется обычный результат — без 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;

И это даст схожий результат.

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;

вычисляет количество dimension1 (число записей в data_table, где dimension1 не равно NULL). Этот запрос возвращает одну запись, которая всегда уникальна (поэтому 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.

Теперь DISTINCT ON снова использует dimension1. Но dimension1 уже уникален (из-за GROUP BY). Следовательно, это делает DISTINCT ON избыточным (он ничего не делает). В конечном итоге COUNT — это просто подсчет всех записей в 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. Он просто выбирает случайное значение b из любой из записей в группе одинаковых значений a. В PostgreSQL этот оператор недопустим. Вместо этого вы должны использовать DISTINCT ON и написать:

SELECT DISTINCT ON (a) a, b FROM R

Корреляция

DISTINCT ON полезен в GROUP BY, когда вы хотите получить значение, которое функционально зависит от атрибутов GROUP BY. Другими словами, если вы знаете, что для каждой группы атрибутов они всегда имеют одно и то же значение третьего атрибута, то используйте DISTINCT ON для этой группы атрибутов. В противном случае вам придется делать JOIN, чтобы получить этот третий атрибут.

Чтобы понять и разобраться в происходящем, полезно использовать визуальный пример.

Вот небольшой фрагмент SQL для выполнения в PostgreSQL:

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;
-- возвращает: 4
-- Потому что в таблице 4 записи.

SELECT DISTINCT count(*) as total2 FROM test_table;
-- возвращает: 4
-- count(*) возвращает одно значение. Сделать 1 уникальным значением может лишь результат в 1. 
-- Поэтому DISTINCT здесь бессмысленно.

SELECT col1, count(*) as total3 FROM test_table GROUP BY col1 ORDER BY col1;
-- возвращает 3 строки: ('bar', 1), ('foo', 2), (NULL, 1)
-- Потому что существует 3 уникальных значения в col1. NULL включены.

SELECT DISTINCT col1, count(*) as total4 FROM test_table GROUP BY col1 ORDER BY col1;
-- возвращает 3 строки: ('bar', 1), ('foo', 2), (NULL, 1)
-- Результат уже сгруппирован и, следовательно, уже уникален. 
-- Таким образом, DISTINCT здесь ничего дополнительно не делает.

SELECT count(DISTINCT col1) as total5 FROM test_table;
-- возвращает 2
-- NULL не учитываются при подсчете значений. Поэтому считаются только 'foo' и 'bar'.

SELECT DISTINCT ON (col1) id, col1 FROM test_table ORDER BY col1 ASC, id DESC;
-- возвращает 3 строки: (2, 'foo'), (3, 'bar'), (4, NULL)
-- Таким образом, получаем записи с максимальным id для каждого уникального col1.
-- Обратите внимание, что "ORDER BY" здесь важно. Если изменить DESC на ASC, получим минимальный id.

SELECT count(*) as total6 FROM (SELECT DISTINCT ON (col1) id, col1 FROM test_table ORDER BY col1 ASC, id DESC) as q;
-- возвращает 3. 
-- После предыдущего запроса, что еще можно ожидать?

SELECT DISTINCT col1 FROM test_table ORDER BY col1;
-- возвращает 3 уникальных значения: ('bar'), ('foo'), (NULL)

SELECT DISTINCT id, col1 FROM test_table ORDER BY col1;
-- возвращает все записи. 
-- Поскольку id является первичным ключом, каждая возвращенная строка уникальна.

Надеюсь, это поможет вам лучше понять, что происходит в каждом из запросов! Если есть вопросы, не стесняйтесь задавать!

Первый запрос возвращает количество ненулевых значений в dimension1, тогда как второй — количество различных значений в этом столбце. Эти числа, очевидно, не равны, если в столбце есть дубликаты или NULL.

Слово 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 -- избыточно
    ) AS tmp_table;

Последний запрос выдаёт количество всех (нулевых и ненулевых) различных значений в столбце.