7.1 Reláció, NULL és elemzési szemcsézettség

7.1.1 Cél és konkrét helyzet

A Northstar laborban egy rendelés elemzése más eredményt ad rendelésenként, mint ügyfelenként. Ugyanaz a kérdés – hány érvényes megfigyelésünk van – kétféle szemcsézettségen értelmezhető: a megfigyelési egység lehet az orders.csv egy sora, tehát egy rendelés, vagy az orders.csv egyes sorai mögött álló egyedi ügyfél. Az előbbi a rendelések számát, az utóbbi az aktív ügyfelek – legalább egy érvényes rendeléssel rendelkező ügyfelek – számát adja.

Bemenet: lab/orders.csv, lab/customers.csv, valamint egy ötsoros kézi ellenőrző tábla. Cél: SQL-lel megszámolni az érvényes rendeléseket COUNT(*) módon, és az aktív ügyfeleket COUNT(DISTINCT customer_id) módon; a kettőt nem felcserélni. Előzetes becslés: a tisztaz orders.csv 2400 sora definíció szerint érvényes, az aktív ügyfelek száma legfeljebb a customers.csv 300 ügyfele; a pontos számot a futtatás adja. A kimenethez kézi ellenőrző minta is kell: egy kis, azonosítható sorhalmazon kézzel megszámolunk, és az SQL-eredményt ehhez hasonlítjuk.

7.1.2 A relációs tábla egy sorának jelentése

A relációs adatmodellben a tábla (relation) oszlopai attribútumok, sorai pedig azonos szerkezetű tények. A sor jelentését a megfigyelési egység (unit of observation) rögzíti: a Northstar orders táblájában egy sor egy rendelés, nem egy tétel és nem egy ügyfél. A customer_id itt csak egy attribútum, nem új szemcsézettség; ha egy ügyfél többször rendel, több sorban is megjelenik.

A sorok azonosítását kulcsok (keys) segítik. Az elsődleges kulcs (primary key) a táblán belül egyedi: order_id. Az idegen kulcs (foreign key) másik táblára hivatkozik: customer_id. A hiányos kulcs vagy hiányzó attribútum SQL-ben NULL: nem nulla, nem üres szöveg, hanem „nem ismert/nem alkalmazható”. A pandasbeli pd.NA relációs párja pont ez; az 6.1 sémajelentése ott, ez itt találkozik.

7.1.3 NULL és háromértékű logika

A NULL-os hasonlítás háromértékű logikát (three-valued logic) követ: az eredmény nem csak TRUE vagy FALSE, hanem UNKNOWN is lehet. A legfontosabb szabályok:

• NULL = NULL és NULL != NULL eredménye UNKNOWN, mert két ismeretlen egyenlőségét nem tudjuk eldönteni.
• IS NULL és IS NOT NULL nem hasonlítás, hanem közvetlen hiányvizsgálat; ezért ezeket használjuk.
• UNKNOWN AND FALSE = FALSE; UNKNOWN AND TRUE = UNKNOWN; UNKNOWN OR TRUE = TRUE; NOT UNKNOWN = UNKNOWN.

A WHERE csak azokat a sorokat tartja meg, ahol a feltétel TRUE. Emiatt egy látszólag ártatlan szűrés is csendben eldobhatja a NULL-os sorokat. Például distance_km >= 5 a NULL távolságra UNKNOWN, tehát a sor kiesik – néha pontosan ezt akarjuk, de akkor ezt mondjuk ki, ne mellékhatásként kapjuk.

7.1.4 SELECT, WHERE, GROUP BY, HAVING és COUNT

A SELECT nem sorokat gyárt, hanem egy halmaz kívánt alakját írja le. A logikai végrehajtási sorrend:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

A WHERE csoportosítás előtt, soronként szűr; a HAVING csoportosítás után, csoportokra vonatkozó feltétellel. A GROUP BY új szemcsézettséget hoz létre: az eredmény egy sora mostantól egy csoportot jelent, és a nem csoportosító oszlopokra aggregátumot kell használni. A COUNT három alakja:

• COUNT(*): a sorok számát adja, a NULL-os sorokat is beleszámítja.
• COUNT(column): a megadott oszlop nem NULL értékeinek számát adja.
• COUNT(DISTINCT column): a megadott oszlop egyedi, nem NULL értékeit számolja.

Az SQLite, a DuckDB és a PostgreSQL ebben a viselkedésben megegyezik. A dialektusok közötti eltérés nem ide tartozik, elsősorban a CSV-betöltésben és a típusszigorban jelentkezik: a DuckDB read_csv közvetlenül olvas CSV-t, a PostgreSQL COPY-t vagy FDW-t használ, az SQLite pedig lazább típuskezelésű.

7.1.5 Matematikai híd: súlyozott és súlyozatlan átlag

Ugyanaz az átlagszámítás más eredményt adhat, ha más szemcsézettségen végezzük. Legyen $K$ csoport, ahol a $k$-adik csoportban $n_k$ megfigyelés van, a csoportátlag pedig $\bar{x}_k$. A csoportátlagok súlyozatlan átlaga:

$$\bar{x}_{\text{unweighted}} = \frac{1}{K}\sum_{k=1}^{K} \bar{x}_k.$$

A teljes, tételszintű átlag viszont a csoportméretekkel súlyozott átlag:

$$\bar{x}_{\text{weighted}} = \frac{\sum_{k=1}^{K} n_k \bar{x}_k}{\sum_{k=1}^{K} n_k},$$

ahol $n_k$ a csoporton belüli megfigyelések darabszáma, $\bar{x}_k$ a csoportátlag, $K$ a csoportok száma. A két képlet csak akkor egyezik, ha minden $n_k$ egyenlő. Ez az „átlagok átlaga” csapda az SQL GROUP BY és a külső AVG egymásra építésekor.

Konkrét számpélda: az első kosárban három tétel ára egyaránt 100 EUR-cent, a másodikban egy tételé 500 EUR-cent. A kosarankénti átlagárak: 100 és 500, ezek súlyozatlan átlaga $(100+500)/2 = 300$. A tételszintű súlyozott átlag viszont $(3\cdot100 + 1\cdot500)/4 = 200$. Ugyanaz az adat, de a 300 háromszor képviseli a 100-at, ezért húzza lefelé a tételszintű átlagot. Aki a kosarankénti átlagokat egyenlő súllyal átlagolja, 300-at kap; aki a tételeket nézi, 200-at.

7.1.6 Teljes minimális munkapélda

Az alábbi SQLite munkamenet egy ötsoros orders_demo táblát hoz létre, majd kiszámolja az érvényes rendelések és az aktív ügyfelek számát. Platform: Ubuntu 24.04 bash, munkakönyvtár: a labor gyökere. A kimenetet nem gépi futásból másoljuk; a bemeneti sorokból közvetlenül levezethető, elméletileg várt érték:

# Platform: Linux/macOS terminál, munkakönyvtár: a labor gyökere
sqlite3 s025_demo.db <<'SQL'
CREATE TABLE orders_demo (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    basket_minor INTEGER NOT NULL,
    distance_km REAL,
    region TEXT NOT NULL
);

INSERT INTO orders_demo VALUES
    (1, 10, 3200, 12.0, 'north'),
    (2, 20,    0, 25.5, 'south'),
    (3, 30, 1800, NULL, 'central'),
    (4, 10, 9900,  4.0, 'north'),
    (5, 40, -100,  8.5, 'south');

SELECT order_id, customer_id, basket_minor, distance_km
FROM orders_demo
ORDER BY order_id;

SELECT COUNT(*)                              AS valid_orders,
       COUNT(DISTINCT customer_id)           AS active_customers
FROM orders_demo
WHERE distance_km IS NOT NULL
  AND basket_minor >= 0;
SQL

Az eredménytáblázat a következő: az első lekérdezés öt sort ad, a második egyet. Az érvényes rendelés definíciója itt: distance_km IS NOT NULL AND basket_minor >= 0. A 3. sor a NULL távolság miatt, az 5. sor a negatív kosárérték miatt esik ki. Marad három érvényes rendelés: a 10-es ügyfél kétszer, a 20-as egyszer szerepel, ezért az aktív ügyfelek száma 2, nem 3. A COUNT(customer_id) ezen a három érvényes soron 3-at adna, mert a duplikált 10-et kétszer számolná; a DISTINCT teszi ügyfélszemcsézettséggé az eredményt.

Ugyanez a Northstar tisztaz orders.csv-jén DuckDB-vel:

# Platform: Linux/macOS terminál, munkakönyvtár: lab/
duckdb :memory: "SELECT COUNT(*) AS valid_orders, COUNT(DISTINCT customer_id) AS active_customers FROM read_csv('orders.csv', header = true);"

PowerShellben a parancs formailag hasonló, de a külső idézőjelet a shell szerint kell kezelni. Ez a második lekérdezés a tiszta fájlra elméletileg 2400 érvényes rendelést ad, mert a lab/ADATOK.md szerint a tisztaz orders.csv 2400 sora már érvényes. Az aktív ügyfelek számát a futtatás adja; a tiszta customers.csv 300 ügyfele felső korlátot jelent.

7.1.7 Eredmény értelmezése és kézi ellenőrzés

A s025_demo.db demónál a fenti eredmény kézzel is ellenőrizhető: az 1., 2. és 4. sor érvényes, tehát három rendelés; ezek közül a customer_id értékek 10, 20 és ismét 10, tehát két egyedi ügyfél. A COUNT(*) alakzat a sorok számát, a COUNT(DISTINCT customer_id) a szemcsézettség-váltást végzi el. A két szám nem felcserélhető: az első rendelésszám, a második ügyfélszám.

A nagy fájlon a kézi ellenőrző minta elkészítéséhez érdemes az első hat order_id szerinti sort kiválasztani, majd az ott látható customer_id értékeket kézzel egyedíteni:

WITH sample AS (
    SELECT * FROM read_csv('orders.csv', header = true)
    ORDER BY order_id
    LIMIT 6
)
SELECT COUNT(*)                              AS sample_rows,
       COUNT(DISTINCT customer_id)           AS sample_customers
FROM sample;

A minta sorainak száma mindig 6; a mintán belüli egyedi ügyfelek számát a kapott hat sor customer_id oszlopából számoljuk ki, és ezt vetjük össze az SQL-eredménnyel. Mivel az orders.csv rendezett, a LIMIT 6 determinisztikus mintát ad.

7.1.8 Feladatok

1. Értelmezés. Egy kolléga szerint az aktív ügyfelek számát a SELECT COUNT(customer_id) FROM orders; adja. Miért téved? Mi lenne a helyes lekérdezés, és miben különbözik az eredmény jelentése?

2. Implementálás. Futtasd a fenti DuckDB-lekérdezést a labor orders.csv fájlján, és készítsd el a hat soros kézi ellenőrző mintát is. Írd fel a kapott két számot a minta szintjén: COUNT(*) és COUNT(DISTINCT customer_id); ellenőrizd, hogy a minta kézi listázásával egyeznek-e.

3. Hibadiagnózis. A s025_demo.db táblán valaki ezt futtatja: SELECT * FROM orders_demo WHERE distance_km != NULL; és üres eredményt kap. Miért? Add meg a javított lekérdezést, és indokold a háromértékű logika segítségével.

4. Transzfer, matematikai híd. Készítsd el a szövegben szereplő kéts kosaras order_items_demo táblát három 100-as és egy 500-as egységárral. Számold ki SQL-lel a kosarankénti átlagárak súlyozatlan átlagát, majd a tételszintű AVG-et. Az elvárt számok 300 és 200; mutasd meg a kézi számítást is.

5. Megváltozott adat. Vedd fel a orders_demo táblába a következő sort: (6, 50, 0, NULL, 'west'). Mi lesz ezután a COUNT(*), a COUNT(distance_km) és az érvényes rendelések száma? Mi marad az aktív ügyfelek száma?

7.1.9 Megoldások és indoklás

1. A COUNT(customer_id) a nem NULL customer_id értékek számát adja, tehát rendeléssorokat számol, nem egyedi ügyfeleket. Ha a 10-es ügyfél kétszer rendelt, a COUNT(customer_id) mindkét sort beleszámolja. A helyes ügyfélszemcsézettségű lekérdezés COUNT(DISTINCT customer_id), amely a 10-es ügyfelet egyszer számolja. A két eredmény akkor egyezik, ha minden ügyfél pontosan egyszer rendelt; ez itt nem áll fenn.

2. A teljes fájlra a tiszta adatszerződés szerint az érvényes rendelések száma 2400. Az aktív ügyfelek száma a futtatásból derül ki, és legfeljebb 300. A hat soros minta esetén a sample_rows mindig 6; a sample_customers megegyezik a hat sor kézi listájának egyedi customer_id számával. Ha a két módszer eltér, akkor a minta kiválasztásában vagy a kézi egyedítésben van hiba. Alternatív ellenőrzés: a customers.csv 300 sorához képest az aktív ügyfelek száma nem lehet nagyobb.

3. A distance_km != NULL soha nem TRUE, mert a NULL-os hasonlítás eredménye UNKNOWN. A WHERE csak TRUE feltételt fogad el, ezért minden sor kiesik, még azok is, ahol a távolság valós szám. A javítás: SELECT * FROM orders_demo WHERE distance_km IS NOT NULL;. Ez a háromértékű logika miatt nem ekvivalens a NULL-os egyenlőségvizsgálattal.

4. Az order_items_demo tábla:

CREATE TABLE order_items_demo (
    order_id INTEGER NOT NULL,
    line_id INTEGER NOT NULL,
    unit_price_minor INTEGER NOT NULL
);

INSERT INTO order_items_demo VALUES
    (1, 1, 100),
    (1, 2, 100),
    (1, 3, 100),
    (2, 1, 500);

A súlyozatlan kosárátlag:

WITH order_mean AS (
    SELECT order_id, AVG(unit_price_minor) AS avg_price
    FROM order_items_demo
    GROUP BY order_id
)
SELECT AVG(avg_price) AS unweighted_mean
FROM order_mean;

A tételszintű súlyozott átlag:

SELECT AVG(unit_price_minor) AS weighted_mean
FROM order_items_demo;

Az első eredmény elméletileg 300.0, mert a két kosárátlag (100+500)/2. A második 200.0, mert (100+100+100+500)/4. Az ellenőrző invariant: a két képlet csak akkor egyenlő, ha a kosarak tételszáma megegyezik; itt nem egyezik, tehát a különbség várható.

5. A beszúrás után a táblának hat sora van, ezért COUNT(*) = 6. A distance_km oszlopban három nem NULL érték van: 12.0, 25.5, 4.0 és 8.5 – ez négy, tehát COUNT(distance_km) = 4. Az érvényes rendelések száma továbbra is 3, mert a hatodik sor NULL távolságú, az ötödik sor negatív kosárértékű, a harmadik pedig NULL távolságú. Az aktív ügyfelek száma is 2 marad: a 10-es és a 20-as ügyfél.

7.1.10 Gyakori tévedés, visszautalás és önellenőrzés

A leggyakoribb tévedés a COUNT(*) és a COUNT(column) felcserélése. Az ötsoros demón a COUNT(*) 5, a COUNT(distance_km) viszont 4, mert a 3. sor távolsága NULL. Ha valaki a hiányzó távolságú sorokat úgy akarja kizárni, hogy COUNT(distance_km)-et használ, akkor a rendelésszám csendben 4-re csökken, pedig a WHERE distance_km IS NOT NULL nélkül a többi oszlop adatai megvannak. Hasonló, második tévedés az átlagok átlaga: a kosarankénti 100 és 500 átlaga 300, a tételszintű átlag 200. A 300 csak akkor lenne helyes, ha mindkét kosár ugyanannyi tételt tartalmazna.

Ez az alfejezet visszautal 6.1 hiányzóérték-kezelésére: a pandas pd.NA és az SQL NULL ugyanazt a hiányt jelenti, de más szintaxissal vizsgáljuk. A 6.4 zónatudatos időbélyegei sem változtatnak a sor jelentésén: az order_ts továbbra is egy rendelés attribútuma, nem új szemcsézettség. A 3.1 környezetkezelése pedig arra figyelmeztet, hogy a fenti parancsok a ténylegesen elérhető CLI-t használják: a duckdb/sqlite3 parancsok a környezet részét képzik, nem a notebook részei.

Önellenőrzés: egy 10 soros táblán, ahol egy oszlopban 3 NULL van, a COUNT(*) 10, a COUNT(column) 7; miért? Mert a COUNT(*) a sorokat, a COUNT(column) a nem hiányzó értékeket számolja. Ha egy feltétel NULL-ra UNKNOWN, bekerül-e a sor a WHERE eredményébe? Nem, mert csak a TRUE feltételű sorok maradnak. Mikor használunk HAVING-et? Akkor, ha a csoportosítás utáni aggregált értékre akarunk feltételt, például HAVING COUNT(*) > 1 azokat az ügyfeleket adja, akik legalább két érvényes rendeléssel bírnak.

Az aggregátumok nevezőjére is figyelni kell: a COUNT(*) az összes sort, a COUNT(oszlop) csak a nem NULL értékeket számolja; az AVG szintén kihagyja a NULL-okat. Ezért az átlag nevezője eltérhet a tábla sorszámától.

7.1.11 Rövid összefoglaló

A relációs tábla sorának jelentését a megfigyelési egység rögzíti; a Northstar orders táblában egy sor egy rendelés, ezért COUNT(*) rendelésszámot ad, COUNT(DISTINCT customer_id) pedig ügyfélszámot. A NULL háromértékű logikát hoz: a hasonlítás UNKNOWN, ezért a WHERE kiejtheti a hiányos sorokat; a COUNT(column) nem számolja a NULL-okat. A GROUP BY új szemcsézettséget hoz létre, a HAVING a csoportokra szűr. A súlyozott és súlyozatlan átlag különbsége az „átlagok átlaga” csapda: a kosarankénti 100 és 500 átlaga 300, a tételszintű átlag 200. A következő alfejezetben a join-kardinalitást vizsgáljuk, ahol egy one-to-many kapcsolat a sorokat és a célváltozót is többszörözheti.