7.2 Join-kardinalitás és duplázódó célváltozó

7.2.1 Cél és konkrét helyzet

A Northstar laborban az orders tábla rendelésenként egy sort, az order_items tábla pedig rendelési tételenként egy sort tartalmaz. Egy rendeléshez több tétel tartozhat. Az üzleti kérdés: „A rendelések mekkora hányada késik a hét napos ígéretet meghaladóan?” A csatlakoztatás után a sor már nem feltétlenül rendelést jelent; a késési címke az adott rendelés valamennyi tételsorán ismétlődik. A cél, hogy a kulcsokat, a kapcsolat kardinalitását és a számítás nevezőjét a lekérdezés előtt ellenőrizzük.

Tanulási eredmények. Az olvasó képes (1) egy-a-többhöz és egy-az-egyhez kapcsolatot ellenőrizni; (2) join előtti és utáni sorszámot értelmezni; (3) hibás tételszintű átlagot felismerni; (4) helyes rendelésenkénti aggregációt építeni; (5) az adatszerződés sérülésekor tudatosan hibával megállni.

7.2.2 Matematikai híd: mit súlyoz a csatlakoztatás?

Legyen az $i$-edik rendelés késési jelzője $y_i\in\{0,1\}$, tételsorainak száma $m_i\geq 1$. A rendelésalapú késési arány

$$\hat p_{\mathrm{order}}=\frac{1}{n}\sum_{i=1}^{n}y_i.$$

Ha rendelés- és tételtáblát összekapcsolunk, majd az így létrejött sorokon átlagolunk, akkor ehelyett

$$\hat p_{\mathrm{item}}=\frac{\sum_{i=1}^{n} m_i y_i}{\sum_{i=1}^{n}m_i}$$

keletkezik. Ez tételszámmal súlyozott rendelési átlag. Csak külön feltételek mellett egyezik meg a keresett rendelésalapú aránnyal. LEFT JOIN esetén a nulla tételes rendelések egy NULL-lal kiegészített sort adhatnak; ezt a külön esetet nem szabad észrevétlenül ugyanebbe a képletbe beleérteni.

7.2.3 Kézzel ellenőrizhető SQL-példa

A példa SQLite-ban végrehajtható, és nem igényel külső adatfájlt:

CREATE TABLE orders_demo (
    order_id TEXT PRIMARY KEY,
    delivery_days INTEGER NOT NULL,
    promised_days INTEGER NOT NULL
);
CREATE TABLE items_demo (
    item_id TEXT PRIMARY KEY,
    order_id TEXT NOT NULL REFERENCES orders_demo(order_id),
    line_total_minor INTEGER NOT NULL
);
INSERT INTO orders_demo VALUES ('A', 9, 7), ('B', 5, 7);
INSERT INTO items_demo VALUES
    ('A1', 'A', 100), ('A2', 'A', 200), ('A3', 'A', 300),
    ('B1', 'B', 400);

-- HELYTELEN a rendelések arányához: minden tételsort egyszer számol.
SELECT AVG(CASE WHEN o.delivery_days > o.promised_days
                THEN 1.0 ELSE 0.0 END) AS weighted_rate
FROM orders_demo AS o
JOIN items_demo AS i ON i.order_id = o.order_id;

-- HELYES: a címke rendelésenként egyetlen alkalommal szerepel.
SELECT AVG(CASE WHEN delivery_days > promised_days
                THEN 1.0 ELSE 0.0 END) AS order_rate
FROM orders_demo;

-- Egy másik HELYES eljárás: tételeket előbb rendelésenként aggregálunk.
WITH item_totals AS (
    SELECT order_id, COUNT(*) AS item_rows,
           SUM(line_total_minor) AS item_total_minor
    FROM items_demo GROUP BY order_id
)
SELECT o.order_id, o.delivery_days > o.promised_days AS target_late,
       t.item_rows, t.item_total_minor
FROM orders_demo AS o
LEFT JOIN item_totals AS t ON t.order_id = o.order_id
ORDER BY o.order_id;

Kézi kontroll: a hibás érték $3/4=0{,}75$, a helyes rendelésalapú késési arány $1/2=0{,}50$. Az item_totals eredménye A rendelésre 3 tétel és 600 cent, B-re 1 tétel és 400 cent. A LEFT JOIN után két sor marad.

7.2.4 Ellenőrzés Pythonból

import sqlite3

with sqlite3.connect(":memory:") as conn:
    conn.executescript("""
    CREATE TABLE orders_demo(order_id TEXT PRIMARY KEY,
                             delivery_days INTEGER, promised_days INTEGER);
    CREATE TABLE items_demo(item_id TEXT PRIMARY KEY, order_id TEXT,
                            line_total_minor INTEGER);
    INSERT INTO orders_demo VALUES ('A',9,7),('B',5,7);
    INSERT INTO items_demo VALUES ('A1','A',100),('A2','A',200),
                                  ('A3','A',300),('B1','B',400);
    """)
    wrong = conn.execute("""SELECT AVG(CASE WHEN o.delivery_days > o.promised_days
                                         THEN 1.0 ELSE 0.0 END)
       FROM orders_demo o JOIN items_demo i ON o.order_id=i.order_id""").fetchone()[0]
    right = conn.execute("""SELECT AVG(CASE WHEN delivery_days > promised_days
                                         THEN 1.0 ELSE 0.0 END)
       FROM orders_demo""").fetchone()[0]
    assert abs(wrong - 0.75) < 1e-12
    assert abs(right - 0.50) < 1e-12
    assert conn.execute("SELECT COUNT(*) FROM orders_demo").fetchone()[0] == 2
    assert conn.execute("SELECT COUNT(*) FROM items_demo").fetchone()[0] == 4

A kódblokk szintaktikája és az ellenőrző értékek külső fájlt nem igényelnek. A teljes Northstar-adatkészlet sorszámait azonban kizárólag a mellékelt laboradatokból szabad igazolni.

7.2.5 A csatlakoztatás előtti adatszerződés

Az alábbi ellenőrzéseket célszerű beépíteni a feldolgozó folyamatba:

1. orders.order_id egyedi és nem hiányzó; order_items.item_id szintén egyedi.
2. Minden order_items.order_id létező rendelésre mutat (árva tétel nincs), vagy az eltérés dokumentáltan karanténba kerül.
3. Az orders és item_totals közötti összekapcsolás után a rendelések száma és az order_id egyedisége változatlan.
4. A shipping_fee_minor rendelésenkénti attribútumot nem összegezzük közvetlenül a nyers tételsorokon; máskülönben a díj a tételszámmal sokszorozódik.
5. A belső join hiányzó tételsor esetén rendelést is eldobhat. A LEFT JOIN megőrzi a rendelést, de a hiányzó tételösszegre vonatkozó üzleti szabályt külön kell meghatározni: a NULL nem minden esetben jogosan alakítható nullává.

7.2.6 Feladatok

1. Értelmezés. Három rendelés címkéje 1, 0 és 0; rendre 5, 1 és 2 tételük van. Mennyi a rendelés- és mennyi a tételszinten számolt késési arány?

2. Implementálás. Írj olyan SQL-lekérdezést, amely a orders és order_items táblákból rendelésenként adja vissza az item_rows és item_total_minor értékeket, és megtartja a nulla tételes rendeléseket is.

3. Hibakeresés. Mi történik, ha a customers táblában ugyanaz a customer_id kétszer szerepel, majd orders-szel összekapcsoljuk? Mit kell ellenőrizni a join előtt?

4. Transzfer. A tételszámok 5, 1, 2, a késési címkék 1, 0, 0. Mi változik, ha a legnagyobb tételszámú rendelés még tíz tételt kap? Miért nem változhat a helyes üzleti mutató?

7.2.7 Megoldások és indoklás

1. Rendelésszinten $1/3\approx 0{,}333$; tételszinten $5/(5+1+2)=5/8=0{,}625$. A különbség a tételszámból eredő implicit súlyozás.
2. WITH t AS (SELECT order_id, COUNT(*) AS item_rows, SUM(line_total_minor) AS item_total_minor FROM order_items GROUP BY order_id) SELECT o.order_id, t.item_rows, t.item_total_minor FROM orders o LEFT JOIN t ON t.order_id=o.order_id; A hiányzó összegek üzleti jelentését ezután kell rendezni.
3. A rendelések megduplázódhatnak, mert a feltételezett egy-a-többhöz kapcsolat megsérül. A customers.customer_id egyediségét és hiánytalanságát ellenőrizni kell, és a nem egyedi törzset először javítani vagy elutasítani.
4. A hibás tételszintű arány $15/18\approx0{,}833$ lesz, miközben a rendelésalapú arány változatlanul $1/3$. Egyetlen rendelés több tétele nem több megfigyelési egység.

7.2.8 Gyakori tévedés és összefoglalás

A JOIN szintaktikailag hibátlan lehet akkor is, ha statisztikailag rossz nevezőt hoz létre. A mérőszám megfigyelési egységét előbb rögzítjük, és csak utána választjuk meg a csatlakoztatást. A következő alfejezetben az időben helyes korábbi rendeléseket CTE-vel és ablakfüggvényekkel állítjuk elő.

7.2.9 Kiadás előtti szakmai ellenőrzés

Join-invariáns. Amikor rendelésenként egy kimenetet kérünk, a csatlakoztatás után ellenőrizni kell a order_id egyediségét, vagy a tételszintű adatot előbb rendelésenként aggregálni. A sorok számának változása önmagában nem feltétlen hiba, de a mérendő egység és a súlyozás megváltozása dokumentálást igényel.