7.4 Paraméterezett lekérdezés és lekérdezési terv

A CTE, window és előzményből számolt feature alfejezetben elkészített prior_order_count CTE akkor válik biztonságos adatkinyeréssé, ha a felhasználó által megadott időhatárokat nem szövegösszefűzéssel illesztjük az SQL-be, hanem paraméterként adjuk át. Ebben az alfejezetben a paraméterezést, a kimeneti oszlopok megválasztását, a szűrés alakját, valamint a lekérdezési terv ellenőrzését nézzük meg három motoron: SQLite, DuckDB és PostgreSQL.

7.4.1 Helyzet, bemenet és induló becslés

A Northstar labor orders.csv és customers.csv tábláiból olyan dátumablakos kinyerést készítünk, amely a CTE, window és előzményből számolt feature alfejezetben bemutatott előzményfeature-t is tartalmazza. A kérés:

megfigyelési egység: egy rendelés, order_id;
bemeneti paraméterek: start_ts, end_ts, min_basket_minor;
kimeneti oszlopok: order_id, order_ts, customer_id, basket_minor, prior_order_count, segment;
szűrés: order_ts a [start_ts, end_ts) féloldalas ablakban van, és basket_minor >= min_basket_minor.

A rendelések 180 napra oszlanak el, összesen 2400 sorral. Egy 7 napos ablakra ezért nagyságrendileg

$\hat{N} \approx 2400 \cdot \frac{7}{180} \approx 93$

rendelést jósol. Ha min_basket_minor = 5000 is szerepel, a sorok száma ennél kevesebb lehet. Ez az előzetes becslés csak ellenőrzési támpont, nem a végső eredmény.

7.4.2 Fogalmi magyarázat

7.4.2.1 Paraméterezés és SQL injection

A paraméterezett lekérdezésben (prepared statement) a változó értékeket placeholder jelöli, például ? vagy %s. A motor a paramétereket adatként kezeli, nem pedig SQL-szövegként. Ha a felhasználó például a

2025-05-01' OR '1'='1

értéket adja meg cutoffként, a naiv szövegösszefűzés ezt az SQL utasítás részévé teszi:

cutoff = "2025-05-01' OR '1'='1"
sql = f"SELECT order_id FROM orders WHERE order_ts >= '{cutoff}'"

Ez az OR '1'='1' miatt az összes sort visszaadja, tehát a lekérdezés fut, csak éppen nem azt csinálja, amit akartunk. A biztonságos változatban a ? értékét a motor külön paraméterként köti, így az idézőjel és az OR is egyszerű adatkarakter marad.

7.4.2.2 Projection

A projekció a lekérdezés SELECT utáni oszloplistája. A SELECT * rövid, de hátrányos:

a tábla sémaváltozásakor a kimenő oszlopkészlet észrevétlenül megváltozik;
felesleges oszlopokat mozgat a lemez, a memória és a hálózat felé;
a kód nem dokumentálja, mire épít a downstream.

A példában ezért csak a ténylegesen használt hat oszlopot vetítjük.

7.4.2.3 Filter és half-open időablak

A WHERE záradék a sorok kiválasztását végzi. Dátumoszlopnál az

WHERE order_ts >= ? AND order_ts < ?

féloldalas, [start, end) intervallumot ad. Ez azért fontos, mert az egymás utáni ablakok hézag és átfedés nélkül illeszkednek: a következő ablak start-ja pontosan az előző end-je. A BETWEEN vagy a <= end kettős beszámolást okozhat, ha a határpont több csoportba is esik.

Az indexhasználathoz az is kell, hogy a szűrés az oszlopra közvetlenül vonatkozzon, ne egy kiszámított kifejezésre. A WHERE date(order_ts) = '2025-04-01' minden sorra kiszámolja a dátumot, ezért a motor jellemzően teljes táblát olvas. A sargable változat:

WHERE order_ts >= '2025-04-01T00:00:00+00:00'
  AND order_ts <  '2025-04-02T00:00:00+00:00'

Ez tartományszűrést ad, amely indexszel támogatható.

7.4.2.4 Index és oszlopos tárolás

A hagyományos, sororientált motorokban — mint az SQLite vagy a PostgreSQL — egy B-tree index az order_ts oszlopon a rendezett kulcsok alapján gyorsan megtalálhatja a tartomány kezdőpontját, majd sorban olvashat a határig. Az index akkor hasznos, ha a predikátum szelektív, vagyis a tábla kis hányadát érinti. Példánkban a 7/180 arány körülbelül 3,9%, ami jellemzően kedvez az indexnek. Ha viszont a szűrés a sorok 90%-át visszaadná, a teljes táblázatolvasás gyakran olcsóbb, mert az indexbejegyzés és a sorok közötti ide-oda ugrálás drága.

A DuckDB oszlopos motort használ: az egyes oszlopok külön tárolódnak, és minden oszlopszegmenshez min/max jellegű metaadat tartozik. Egy order_ts >= start szűrésnél a motor a metaadatokkal egész szegmenseket kihagyhat, anélkül hogy külön B-tree indexet kellene karbantartani. Ezért DuckDB-n általában nincs szükség kézzel létrehozott indexre a gyakori oszlopos szűrésekhez; a tervező a zónatérképek és a statisztikák alapján dönt.

7.4.2.5 SQL-dialektus

A három motor között a paraméter- és típuskezelésben fontos különbségek vannak:

Motor	Paraméter placeholder	`order_ts` típus	Végrehajtási terv
SQLite Pythonból	`?`	jellemzően ISO-szöveg	`EXPLAIN QUERY PLAN`
DuckDB Pythonból	`?`	`TIMESTAMP`	`EXPLAIN`
PostgreSQL psycopg2-vel	`%s`	`TIMESTAMPTZ`	`EXPLAIN (ANALYZE, BUFFERS)`

Az SQLite-ban nincs külön időbélyeg-típus; ha az oszlopot szövegként tároljuk, a lexikografikus összehasonlítás csak akkor helyes, ha minden érték azonos formátumú, például YYYY-MM-DDTHH:MM:SS+00:00. DuckDB-ben a CSV-betöltés után a DESCRIBE orders paranccsal ellenőrizzük, hogy a motor TIMESTAMP típusra következtetett-e. PostgreSQL-ben a TIMESTAMPTZ időzónával együtt tárol, ezért a bindolt paramétert is zónatudatos értékként küldjük.

7.4.3 Minimális, kézzel ellenőrizhető példa

Az alábbi Python-kód nem a teljes Northstar-adatot használja, hanem egy hat soros memória-táblát, amelyen a várt eredmény kézzel is kiszámítható. A kód a Python standard könyvtárával fut; a kimenetet itt kézi levezetés alapján várhatóként jelöljük, nem pedig mért futásként.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (
    order_id TEXT PRIMARY KEY,
    order_ts TEXT NOT NULL,
    customer_id TEXT NOT NULL,
    basket_minor INTEGER NOT NULL
);

CREATE TABLE customers (
    customer_id TEXT PRIMARY KEY,
    segment TEXT NOT NULL
);

INSERT INTO orders VALUES
    ('o1','2025-04-01T10:00:00+00:00','c1',8000),
    ('o2','2025-04-02T10:00:00+00:00','c2',4000),
    ('o3','2025-04-05T11:00:00+00:00','c1',1000),
    ('o4','2025-04-07T12:00:00+00:00','c1',6000),
    ('o5','2025-04-08T10:00:00+00:00','c2',9000),
    ('o6','2025-04-01T09:00:00+00:00','c3',3000);

INSERT INTO customers VALUES
    ('c1','business'),
    ('c2','consumer'),
    ('c3','business');
""")

query = """
WITH prior AS (
    SELECT
        order_id,
        customer_id,
        order_ts,
        basket_minor,
        COUNT(*) OVER (
            PARTITION BY customer_id
            ORDER BY order_ts, order_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prior_order_count
    FROM orders
)
SELECT
    p.order_id,
    p.order_ts,
    p.basket_minor,
    p.prior_order_count,
    c.segment
FROM prior AS p
JOIN customers AS c ON c.customer_id = p.customer_id
WHERE p.order_ts >= ?
  AND p.order_ts <  ?
  AND p.basket_minor >= ?
ORDER BY p.order_ts, p.order_id;
"""

start = "2025-04-01T00:00:00+00:00"
end = "2025-04-08T00:00:00+00:00"
min_basket = 5000

print(list(con.execute(query, (start, end, min_basket))))

Várt kimenet:

[('o1', '2025-04-01T10:00:00+00:00', 8000, 0, 'business'),
 ('o4', '2025-04-07T12:00:00+00:00', 6000, 2, 'business')]

A prior_order_count a teljes múltbeli sorrendet tükrözi, nem csak a szűrt ablakot. c1 három rendelése: o1, o3, o4. Az o1 az első, ezért prior_order_count=0; az o4 előtt két korábbi rendelés van, ezért 2. Az o3 az ablakba beleesne időben, de a kosárértéke 3000 cent, így a min_basket_minor=5000 kiszűri. Az o5 időben kívül esik, mert a féloldalas ablak 2025-04-08T00:00:00-nál már nem veszi fel.

Az indexhatás szemléltetésére ugyanebben a SQLite-kapcsolatban indexet hozunk létre, majd lekérdezési tervet kérünk:

con.execute("CREATE INDEX idx_orders_order_ts ON orders(order_ts);")
con.execute("ANALYZE orders;")

plan_rows = con.execute(
    "EXPLAIN QUERY PLAN SELECT order_id FROM orders "
    "WHERE order_ts >= ? AND order_ts < ?"
).fetchall()
print(plan_rows)

A kimenet nagyon kis táblán is lehet teljes táblázatolvasás, mert a tervező joggal dönthet úgy, hogy hat sort nem érdemes indexen keresni. Nagyobb adaton a szelektív tartományszűréssel jellemzően SEARCH orders USING INDEX idx_orders_order_ts típusú sor jelenik meg. A fontos tanulság: a terv a motor becslését mutatja, nem a kívánságunkat.

A Northstar-adaton ugyanez a lekérdezés DuckDB-vel a következő keretben futtatható. A fájlnevek a labor projektgyökeréből értendők, a kimenet nem előre mért, hanem a szerződésből várt:

# lab/s028_window_query.py
import duckdb

START = "2025-05-01T00:00:00+00:00"
END = "2025-05-08T00:00:00+00:00"
MIN_BASKET = 5000

con = duckdb.connect()

# Betöltés és típusellenőrzés: DESCRIBE orders mutassa, hogy az order_ts
# TIMESTAMP lett-e. Ha szöveg marad, a lexikografikus összehasonlítás csak
# +00:00 kezdetű ISO értékekkel biztonságos.
con.execute(
    "CREATE TABLE orders AS "
    "SELECT * FROM read_csv_auto('lab/orders.csv')"
)
con.execute(
    "CREATE TABLE customers AS "
    "SELECT * FROM read_csv_auto('lab/customers.csv')"
)

query = """
WITH prior AS (
    SELECT
        order_id,
        customer_id,
        order_ts,
        basket_minor,
        COUNT(*) OVER (
            PARTITION BY customer_id
            ORDER BY order_ts, order_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prior_order_count
    FROM orders
)
SELECT
    p.order_id,
    p.order_ts,
    p.customer_id,
    p.basket_minor,
    p.prior_order_count,
    c.segment
FROM prior AS p
JOIN customers AS c ON c.customer_id = p.customer_id
WHERE p.order_ts >= ?
  AND p.order_ts <  ?
  AND p.basket_minor >= ?
ORDER BY p.order_ts, p.order_id;
"""

df = con.execute(query, [START, END, MIN_BASKET]).fetchdf()
print(df.head())
print(df.shape)

A ? helyettesítő SQLite és DuckDB Python-eszközökben is működik. PostgreSQL psycopg2 esetén ugyanezt a szöveget %s helyettesítőkre cseréljük, és a paramétereket tuple-ként adjuk át:

cur.execute(query, (start, end, min_basket))

7.4.4 Eredmény értelmezése és ellenőrző invariantok

A mini példán a várt eredmény két sor, és a következő invariánsok ellenőrizhetők:

a kiválasztott order_ts minden értéke a [start, end) ablakban van;
prior_order_count egyik sorban sem nagyobb, mint az adott ügyfél összes, az adott rendelés előtti rendeléseinek száma;
a basket_minor minden sorban legalább min_basket_minor;
a kimenet order_ts és order_id szerint rendezett.

A teljes Northstar-adatra a 7 napos ablakra közel 93 sor várható. Ha a mért sorok száma nagyságrendileg ettől eltér, akkor valószínűleg hibás az order_ts besorolása: az oszlop szöveg maradt időzóna nélkül, vagy a paramétert nem sikerült megfelelően bindolni. Ilyenkor először a betöltött tábla DESCRIBE kimenetét és a min(order_ts), max(order_ts) értékét érdemes megnézni.

7.4.5 Feladatok

Értelmezés. A mini táblán miért nem szerepel o5 a kimenetben, ha az end = '2025-04-08T00:00:00+00:00'? Mi történne, ha a szűrés <= lenne? Rajzolja fel a [start, end) és a [start, end] intervallum különbségét egy egységes, 30 perces léptékű idővonalon.
Megvalósítás. Futtassa a mini SQLite-példát start='2025-04-02T00:00:00+00:00', end='2025-04-08T00:00:00+00:00', min_basket=1000 paraméterekkel. Mely sorok jelennek meg, és mekkora a prior_order_count az egyes sorokban?
Hibakeresés. Egy kolléga ezt írja: WHERE date(order_ts) = '2025-04-01'. Miért lehet ez teljes táblázatolvasás akkor is, haz order_ts-n van index? Írja át a szűrést olyan tartománnyá, amely ugyanazt az egy napot jelöli, és sargable.
Transzfer. A DuckDB-példát PostgreSQL-re kell portolnia. Írja le a két szükséges változtatást: milyen lesz a placeholder, és milyen típust várjon az order_ts oszlop? Ezután magyarázza el, miért nem jelent automatikusan gyorsabb futást, ha az EXPLAIN kimenetében rövidebb terv szerepel.

7.4.6 Megoldások és indoklás

Az o5 értéke 2025-04-08T10:00:00+00:00, ami később van, mint az intervallum jobb oldali, nyílt határa. A [start, end) féloldalas ablak az end pillanatáig tart, de azt már nem tartalmazza. Így a start=2025-04-01T00:00, end=2025-04-08T00:00 ablak az áprilisi 1-i 00:00-tól 8-i 00:00-ig terjed, összesen pontosan 7 nap. Ha a szűrés <= lenne, az o5 is bekerülne, és a következő ablakkal átfedés keletkezne. Az egymás után fűzött féloldalas ablakok hézagmentesen fedik az időt.
A paraméterekkel a mini táblából o2, o3, o4 kerül ki. Az o1 időben az ablak előtt van, az o5 időben a határ után, az o6 pedig a kosárérték vagy időablak? o6 időben 04-01 09:00, tehát a start 04-02 előtt, ezért kiesik. A megmaradt sorok prior értékei: o2 előtt c2-nek nincs rendelése, tehát 0; o3 előtt c1-nek van o1, tehát 1; o4 előtt c1-nek van o1 és o3, tehát 2. Ez megmutatja, hogy a paraméterezett ablakon kívüli múltbeli rendelés is beleszámít az előzményfeature-be.
A date(order_ts) kifejezés minden sorra új számítást végez, ezért a motor nem tudja közvetlenül az order_ts indexből kiolvasni a keresett tartományt. Ehelyett beolvassa az összes sort, kiszámolja a dátumot, és utána hasonlít. A helyes megfogalmazás:
```
WHERE order_ts >= '2025-04-01T00:00:00+00:00'
  AND order_ts <  '2025-04-02T00:00:00+00:00'
```
Ez a naptári napot ugyanúgy jelöli, de tartományszűrés, amely az order_ts indexszel támogatható.
PostgreSQL alatt a placeholder %s, és a paramétereket tuple-ként, a psycopg2 execute(sql, params) hívásában kell átadni. Az order_ts oszlop érdemes TIMESTAMPTZ típusú, hogy a zónainformáció ne vesszen el. A rövidebb EXPLAIN terv nem bizonyítja, hogy a lekérdezés gyorsabb: a terv a becsült költséget mutatja; a tényleges futásidőhöz gépet, adatméretet, cache-állapotot, ismétlésszámot és mért eredményt kell rögzíteni. Egyetlen EXPLAIN ANALYZE futás sem elegendő stabil teljesítménykövetkeztetéshez.

7.4.7 Gyakori tévedés és ellenpélda

Gyakori csapda, hogy valaki a helyes eredményből arra következtet, hogy a lekérdezés biztonságos és olcsó is. A naiv f-stringes változat a fenti cutoff = "2025-05-01' OR '1'='1" értékkel szintén visszaadhat sok sort, sőt akár ugyanazokat, mint a paraméterezett változat, mégis SQL injectionre alkalmas. Ugyanígy egy SELECT *-ot és date(order_ts) szűrést használó kód megadhatja a kívánt sorokat, de a teljes táblát mozgatja. A Dátumok, időzónák és kategóriák alfejezetből ismert időzóna- és típusfegyelem itt visszaköszön: a paraméterezett lekérdezés csak akkor értelmes, ha az order_ts értékek konzisztensen +00:00 kezdetű, rendezhető formában vannak. Önellenőrzés: nézzük meg a tervet, a paramétereket nem szabad egyszerű szövegként a querybe írni, és a kimenő order_ts értékeket mindig ellenőrizzük az ablak határai ellen.

A lekérdezésparaméterezés az értékek biztonságos behelyettesítésére szolgál, nem általában a táblanevek vagy SQL-kulcsszavak dinamikus kezelésére. Változó azonosítóknál engedélyezett listát használjunk. Egy index és egy végrehajtási terv előnye a motortól, az adatoktól és a statisztikáktól függ, ezért az EXPLAIN megfigyeléseit mérésnek kell követnie.

7.4.8 Rövid összefoglaló

A paraméterezett lekérdezésben a felhasználói érték ? vagy %s placeholder útján adat marad, nem pedig SQL-kóddá váló szöveg. A projekció explicit oszloplistát ad SELECT * helyett. A szűrés féloldalas [start, end) ablakot és sargable oszlophivatkozást használ. Sororientált motorokban a szelektív B-tree index gyorsít; DuckDB oszlopos tárolásban min/max zónatérképekkel kerüli a felesleges szegmenseket. A motorok eltérnek a paraméter- és típuskezelésben, ezért a kikötést mindig az adott dialektusban kell megfogalmazni. Az EXPLAIN a végrehajtási tervet mutatja, nem a helyes eredményt. A következő alkalmazás a CSV-től Parquet-ig: séma és partíció alfejezetben ezt a kinyerést fájlformátum, séma és partíció szempontjából viszi tovább.