05.01.2015, Vladimír Klaus, navštíveno 14134x
Duplicity v tabulkách mohou být nepříjemné. Mohou jen zkreslovat statistické přehledy, mohou ale i škodit (např. vícenásobné proplacení faktury) nebo „jen“ ukazovat na potenciální problém v databázi nebo jejím plnění.
Možná se vám zdá, že by k odhalení duplicity posloužil predikát DISTINCT nebo DISTINCTROW. Ale to není bohužel přesné a nemusí to vyhovovat. Tento predikát nám odfiltruje duplicitní záznamy tak, aby ve výsledku všechny hodnoty byly jenom jednou. Ale co když nás nezajímají hodnoty, které jsou v tabulce právě jednou (tedy nemají žádné duplicity)? Anebo když budeme potřebovat odstranit všechny duplicitní záznamy, ale se zachováním právě jednoho „originálu“. To je ještě větší problém.
Začneme ale nejprve jednoduše – zjistíme, jaké všechny produkty se prodávají. To je velmi jednoduché.
SELECT DISTINCT IdProduktu FROM ObjednavkyFULL
Je jich pouze 10, zatímco v tabulce produktů jich je 12, takže 2 se ještě neprodaly. To všechno souhlasí. Příkaz upravíme tak, aby nám zobrazil pouze ty produkty, kterých se prodal více než 1 kus.
SELECT IdProduktu, COUNT(IdProduktu) AS Pocet
FROM ObjednavkyFULL
GROUP BY IdProduktu
HAVING COUNT(IdProduktu) > 1
Těchto je již pouze 9 a velice rychle objevíme, že 1x se prodal pouze produkt „Klávesnice a myš“. Pořád je všechno správné, ale pokud budeme chtít v dalším kroku vypsat jen duplicitní objednávky produktů, nebude to možné, protože seskupením (GROUP BY) jsme o konkrétní záznamy přišli. A bez seskupení zase těžko zjistíme, kolik se čeho prodalo.
Budeme tedy trochu kouzlit. Začneme se zdánlivě nesmyslným dotazem, který nám vrátí sadu nejmenších ID ze všech skupin prodaných produktů.
SELECT MIN(ID) FROM ObjednavkyFULL GROUP BY IdProduktu
A jak toho využijeme? To jsou totiž přesně ty „originální“ objednávky. Všechny ostatní objednávky ze skupin prodaných produktů už jsou duplicitní.
Napíšeme tedy dotaz, který nám zobrazí všechny objednávky, ale za podmínky, že jejich ID není z právě získané sady originálů. Jednoduché a geniální zároveň.
SELECT * FROM ObjednavkyFULL
WHERE ID NOT IN
(SELECT MIN(ID) FROM ObjednavkyFULL GROUP BY IdProduktu)
Lehce zkontrolujeme, že všech objednávek je 22, originálních je 10 a tento dotaz nám vrátil přesně zbývajících 12.
Zbývá už jen dodat, že pokud byste chtěli duplicitní záznamy určovat podle více polí – třeba IdProduktu a Castka, tak se dotaz jen lehce upraví – doplní se další pole do seskupení. V tomto případě to ale není potřeba, protože produkty prodáváme vždy za stejné částky.
SELECT * FROM ObjednavkyFULL
WHERE ID NOT IN
(SELECT MIN(ID) FROM ObjednavkyFULL GROUP BY IdProduktu, Castka)
Je zřejmé, že nelze nijak rozumně ovlivnit, které objednávky budou duplicitní a která bude považována za originál. Tady jsme použili funkci na získání nejmenšího ID, proto za originál považujeme tu nejdříve zadanou.
Odstranění těchto duplicit je už velmi jednoduché.
DELETE FROM ObjednavkyFULL
WHERE ID NOT IN
(SELECT MIN(ID) FROM ObjednavkyFULL GROUP BY IdProduktu)
Občas se můžete setkat ještě s jiným řešením, které zde uvedu především pro jednu specifickou vlastnost predikátu EXISTS.
Tímto dotazem vytvoříme kartézský součin tabulky s objednávkami s tabulkou objednávek. Je to asi trochu divné, ale teprve dvě podmínky udělají tu zajímavou práci. První zařídí, že budou vždy spojené jen stejné produkty a ta druhá podmínka zařídí, aby se zobrazily jen ty dvojice, ve kterých se liší ID. Jinak řečeno – budou zobrazené jen ty duplicitní, protože u originálů platí a.ID = b.ID.
SELECT * FROM ObjednavkyFULL a, ObjednavkyFULL b
WHERE a.IdProduktu = b.IdProduktu
AND a.ID > b.ID
V tabulce je pár záznamů navíc, kterých se zbavíme pomocí predikátu DISTINCT. Při tom kartézském součinu zkrátka vzniknou další nechtěné duplicity.
SELECT DISTINCT a.ID, a.IdZakaznika, a.IdProduktu
FROM ObjednavkyFULL a, ObjednavkyFULL b
WHERE a.IdProduktu = b.IdProduktu
AND a.ID > b.ID
Teď nám dotaz vrátí přesně ty hledané duplicitní objednávky. Na rozdíl od prvního jednoduššího řešení bude ale problém se těchto záznamů zbavit. Náhrada SELECT za DELETE zde není možná. Vrátíme se tedy k prvnímu dotazu a použijeme ho jako poddotaz, který bude určovat, které duplicitní objednávky se musí smazat. Poddotaz nám vrací 15 záznamů (jsou v něm i ty nechtěně vytvořené duplicity), ale to nevadí, protože my ty duplicitní chceme smazat a 2x smazat stejný záznam nelze, takže se smaže stejně jen těch 12.
DELETE FROM ObjednavkyFULL WHERE EXISTS
(SELECT * FROM ObjednavkyFULL a, ObjednavkyFULL b
WHERE a.IdProduktu = b.IdProduktu
AND a.ID > b.ID
)
Jenomže po vykonání tohoto dotazu se nám ukáže velmi podivný dialog, ve kterém není 12, ale všech 22 záznamů z celé tabulky objednávek.
Znamená to, že EXISTS nefunguje? Ne, pouze zde došlo k situaci, kdy poddotaz není s hlavním dotazem propojen. A pokud propojení neexistuje, pak stačí, aby poddotaz vrátil alespoň jeden záznam (vrací jich 15) a pak je podmínka WHERE EXISTS automaticky pravdivá a platí pro všechny záznamy té hlavní tabulky.
Nezbývá nám použít malý trik – doplnit další podmínku, díky které vytvoříme pomocné propojení hlavního dotazu s poddotazem. Pro hlavní tabulku vytvoříme další alias – „c“ a v podmínce uvedeme požadavek na rovnost ID.
DELETE FROM ObjednavkyFULL c WHERE EXISTS
(SELECT * FROM ObjednavkyFULL a, ObjednavkyFULL b
WHERE a.IdProduktu = b.IdProduktu
AND a.ID > b.ID
AND a.ID = c.ID
)
Po vykonání dotazu se už objeví korektní dialog.
Možná si nyní říkáte, proč je zde uvedeno toto druhé a mnohem komplikovanější řešení. Je velice dobré si ukázat, že některé věci se dají řešit více způsoby a něco se při tom i naučit. Druhý důvod je závažnější – pokud použijete IN a mezi hodnotami poddotazu bude i NULL, je všechno špatně. Takový poddotaz je jako výsledek pro hlavní dotaz nedefinovaný a způsobí, že podmínka není nikdy splněna. Výsledkem tedy nikdy nebude žádný záznam. To u zde ukázaného použití EXISTS nehrozí.
V našem případě tam NULL nikdy být nemůže, protože pracujeme s ID tabulky, která má nastaveno, že tento sloupec NULL nesmí nikdy obsahovat. Ale v jiném případě a u jiné tabulky tomu tak být nemusí.