PostgreSQL: Uživatelské zmatky kolem count() a distinct

Kolega provedl test jedné mé serverové rutiny a oznámil mi, že je špatně, protože kontrolním selectem dostal hodnotu o jedna menší než mněla být. Když jsem požádal o daný kontrolní select, tak select skutečně ukázal hodnotu o jedna menší.

Použil tuto konstrukci select příkazu :

create temporary table test
(id serial primary key,skupina text, hodnota integer);

insert into test (skupina,hodnota) values ('a',23),(null,15),('c',15),
('a',20),(null,23),('b',2),('c',5),(null,2),('a',27);

select count(distinct skupina) from test;

count
-------
     3 

Ale když jsem nechal vypsat seznam skupin, výsledek byl správný.

select distinct skupina from test;

skupina
---------

 c
 b
 a
(4 řádky) 

V tomto konkrétním případě i skupina null byla významová, protože se přenášela do datového skladu jako „Nepřirazeno“.

Kouzlo špatného výsledku bylo v použití agregované funkce count().

Funkce count(skupina) vrací počet řádků, které mají v sloupci skupina not null hodnotu, teda 3.

Zatímco count(*) celkový počet řádků výsledku, tedy 4.

Správný dotaz mněl být:

select count(*)
from  (select distinct skupina from test) a;

count
-------
     4
(1 řádka) 

Poznámka k distinct :

Klíčové slovo distinct eliminuje duplicitní řádky ve výsledku. Ale distinct lze použít i velice netradičně. Vše můžete vyzkoušet na výše uvedené tabulce.

1. Základní tvar který vrací jedinečné hodnoty.

select distinct skupina,hodnota
from test 

2. Nejnižší hodnota ve skupině.

select distinct on ( skupina ) skupina,hodnota
from test
order by skupina , hodnota 

3. Nejvyšší hodnota ve skupině.

select distinct on ( skupina ) skupina,hodnota
from test
order by skupina , hodnota desc 

4. První hodnota ve skupině.

select distinct on ( skupina ) skupina,hodnota
from test
order by skupina ,id 

5. Poslední hodnota ve skupině.

select distinct on ( skupina ) skupina,hodnota
from test
order by skupina ,id desc 

Samozřejmě vše lze dělat pomocí uživatelsky definovaných funkcí first() a last(), které můžete najít pro PostgreSQL na wiki

First/last (aggregate) – PostgreSQL wiki