Solución:
DISTINCT y DISTINCT ON tienen semánticas completamente diferentes.
Primero la teoría
DISTINCT se aplica a una tupla completa. Una vez que se calcula el resultado de la consulta, DISTINCT elimina las tuplas duplicadas del resultado.
Por ejemplo, suponga una tabla R con el siguiente contenido:
#table r;
a | b
---+---
1 | a
2 | b
3 | c
3 | d
2 | e
1 | a
(6 filas)
SELECT distinto * de R resultará:
# select distinct * from r;
a | b
---+---
1 | a
3 | d
2 | e
2 | b
3 | c
(5 rows)
Tenga en cuenta que distinto se aplica a toda la lista de atributos proyectados: por lo tanto
select distinct * from R
es semánticamente equivalente a
select distinct a,b from R
No puedes emitir
select a, distinct b From R
DISTINCT debe seguir a SELECT. Se aplica a toda la tupla, no a un atributo del resultado.
DISTINTO EN es una adición de postgresql al lenguaje. Es similar, pero no idéntico, agrupar por.
Su sintaxis es:
SELECT DISTINCT ON (attributeList) <rest as any query>
Por ejemplo:
SELECT DISTINCT ON (a) * from R
Su semántica se puede describir de la siguiente manera. Calcule la consulta como de costumbre, pero antes de la proyección del resultado, ordene el resultado actual y agrúpelo de acuerdo con la lista de atributos en DISTINCT ON (similar a agrupar por). Ahora, haga la proyección usando la primera tupla de cada grupo e ignore las otras tuplas.
Ejemplo:
SELECT DISTINCT on (a) * from r;
a | b
---+---
1 | a
2 | b
3 | c
(3 rows)
Ahora, volvamos a tu problema:
Primera consulta:
SELECT DISTINCT count(dimension1)
FROM data_table;
calcula el recuento de dimension1 (número de tuplas en data_table que donde dimension1 no es nulo). Esta consulta devuelve una tupla, que siempre es única (por lo tanto, DISTINCT es redundante).
Consulta 2:
SELECT count(*)
FROM (SELECT DISTINCT ON (dimension1) dimension1
FROM data_table
GROUP BY dimension1) AS tmp_table;
Esta es una consulta en una consulta. Déjame reescribirlo para mayor claridad:
WITH tmp_table AS (
SELECT DISTINCT ON (dimension1)
dimension1 FROM data_table
GROUP by dimension1)
SELECT count(*) from tmp_table
Calculemos primero tmp_table. Como mencioné anteriormente, primero ignoremos DISTINCT ON y hagamos el resto de la consulta. Este es un grupo por dimensión1. Por lo tanto, esta parte de la consulta dará como resultado una tupla por valor diferente de dimensión1.
Ahora, el DISTINCT ON. Utiliza dimension1 de nuevo. Pero dimension1 ya es única (debido al grupo de). Por lo tanto, esto hace que DISTINCT ON superflouos (no hace nada). El recuento final es simplemente un recuento de todas las tuplas del grupo por.
Como puede ver, existe una equivalencia en la siguiente consulta (se aplica a cualquier relación con un atributo a):
SELECT (DISTINCT ON a) a
FROM R
y
SELECT a FROM R group by a
y
SELECT DISTINCT a FROM R
Advertencia
El uso de resultados DISTINCT ON en una consulta puede no ser determinista para cualquier instancia de la base de datos. En otras palabras, la consulta puede devolver resultados diferentes para las mismas tablas.
Un aspecto interesante
Distinct ON emula un malo comportamiento de sqlite y mysql de una manera mucho más limpia. Suponga que R tiene dos atributos ayb:
SELECT a, b FROM R group by a
es una declaración ilegal en SQL. Sin embargo, se ejecuta en mysql y sqlite. Simplemente toma un valor aleatorio de b de cualquiera de las tuplas en el grupo de los mismos valores de a. En Postgresql, esta declaración es ilegal. En su lugar, debe usar DISTINCT ON y escribir:
SELECT DISTINCT ON (a) a,b from R
* Corolario *
DISTINCT ON es útil en un grupo cuando desea acceder a un valor que depende funcionalmente del grupo por atributos. En otras palabras, si sabe que para cada grupo de atributos siempre tienen el mismo valor del tercer atributo, utilice DISTINCT ON ese grupo de atributos. De lo contrario, tendría que realizar un JOIN para recuperar ese tercer atributo.
La primera consulta da el número de valores no nulos de dimension1, mientras que el segundo devuelve el número de valores distintos de la columna. Obviamente, estos números no son iguales si la columna contiene duplicados o nulos.
La palabra DISTINCT en
SELECT DISTINCT count(dimension1)
FROM data_table;
no tiene sentido, ya que la consulta devuelve una sola fila. Tal vez tu querias
SELECT count(DISTINCT dimension1)
FROM data_table;
que devuelve el número de valores distintos no nulos de dimension1. Tenga en cuenta que no es lo mismo que
SELECT count(*)
FROM (
SELECT DISTINCT ON (dimension1) dimension1
FROM data_table
-- GROUP BY dimension1 -- redundant
) AS tmp_table;
La última consulta arroja el número de todos los valores distintos (nulos o no) de la columna.