Hiányzó adatok SQL adatbázisban
júl 9, 21:08, Tippek és trükkök | Software
Adatbázisokkal dolgozó alkalmazásokban szükség lehet az adatok ellenőrzésére, például hiányzó sorok detektálására. A feladat első közelítésben abszurdnak tűnik, hiszen ha nem létezik, hogyan is lehetne megtalálni? Aki tudja a megoldást, annak már könnyű. A többiek kutathatnak az interneten.
Minta adatok
A probléma bemutatásához készítsük el pl. alapanyagok (product) és azok rendelt mennyisége (orders) tábláit. A bemutatott kódok kipróbáltan működtek SQLite 3.6.16 adatbázis kezelővel. Íme a teszt adatok feltöltése az alapanyagokhoz:
BEGIN TRANSACTION; CREATE TABLE product (pid INT, name TEXT); INSERT INTO product VALUES(1,'Liszt'); INSERT INTO product VALUES(2,'Tojas'); INSERT INTO product VALUES(3,'Eleszto'); INSERT INTO product VALUES(5,'Olaj'); INSERT INTO product VALUES(6,'So'); COMMIT;
valamint a rendelésekhez:
BEGIN TRANSACTION; CREATE TABLE orders (pcode INT, quantity REAL); INSERT INTO orders VALUES(1,3.5); INSERT INTO orders VALUES(1,0.5); INSERT INTO orders VALUES(2,15.0); INSERT INTO orders VALUES(4,2.0); INSERT INTO orders VALUES(4,5.1); INSERT INTO orders VALUES(6,1.2); INSERT INTO orders VALUES(7,3.0); COMMIT;
Természetesen egy üzleti alkalmazás ennél sokkal bonyolultabb, további oszlopokkal és indexekkel. Ezt mindenki saját fantáziájára bízom, itt csak a lényeget szeretném bemutatni.
SQL lekérdezések
Egy tipikus rendelési napló a következő módon hozható létre:
SELECT product.*,SUM(orders.quantity) FROM product,orders WHERE product.pid=orders.pcode GROUP BY product.pid ORDER BY product.name;
Aminek az eredménye:
1 Liszt 4.0 6 So 1.2 2 Tojas 15.0
Ez eddig rendben is lenne, de van-e esetleg olyan rendelt alapanyag, amelynek nincs párja a törzsadatok között?
SELECT DISTINCT orders.pcode FROM orders LEFT JOIN product ON orders.pcode=product.pid WHERE product.pid IS NULL;
Valóban, a fenti adatok alapján kiderül, hogy a 4 és 7 kódokhoz nem rendeltünk nevet, így a rendelési listában sem jelentek meg. Ez az ellenőrzés azért fontos, mert inkonzisztens adatokra hívja fel a figyelmet.
Fordított irányban is érdekes lehet a kérdés: van-e olyan alapanyag, amelyből nem rendeltünk?
SELECT DISTINCT product.name FROM product LEFT JOIN orders ON product.pid=orders.pcode WHERE orders.pcode IS NULL;
Aminek az eredménye a fenti adatokra:
Eleszto Olaj
A DISTINCT kulcsszó azért szükséges, hogy csupán egyetlen példányban listázzuk ki a neveket, ne pedig az összes előfordulásukat.
Habár ez a trükk sok helyen fellelhető az interneten, gondoltam én is megosztom egy kisebb példával együtt.
Megjegyzés
Ehhez a cikkhez nem fűzhető megjegyzés!
