7.3 CTE, window és előzményből számolt feature

A Northstar prediktív projektben egy rendelés beérkezésekor szeretnénk jellemezni az ügyfél múltbéli aktivitását. A prior_order_count jellemző azt mondja meg, hogy az adott ügyfélnek hány korábbi rendelése volt. A kimenetet rendelésenként egy sorban képzeljük el:

Kézi előzetes becslés a fejezetben használt mini adatra: a C1 ügyfél három rendelésénél a korábbi rendelésszám rendre 0, 1, 2, az előző rendelés óta eltelt idő pedig a második és harmadik sornál 20 - 10 = 10, illetve 30 - 20 = 10 nap.

7.3.2 Sorozat, rendezés és prefixösszeg

ahol (t_i) az (i)-edik rendelés időpontja, (n) az ügyfél rendeléseinek száma. A korábbi rendelésszám az aktuális sor előtti elemek száma:

Ez egy prefixösszeg: minden lépésben 1-et adunk az előző összeghez, de az aktuális elemet már nem számítjuk bele. A (p_i) mértékegysége darab rendelés, értéke nemnegatív egész, az első rendelésnél mindig (0).

napokban az előző rendelés óta eltelt idő; (\Delta_1) nem értelmezhető, ezért hiányzó (NULL). A formális jelölés kimeneti alakja: (N) bemeneti rendeléssorból (N) kimeneti sor keletkezik, minden sorhoz három új numerikus vagy idő típusú érték tartozik.

7.3.3 WITH, PARTITION BY, ROW_NUMBER, LAG és a frame

Az SQL-ben a common table expression (CTE, WITH után megadott ideiglenes névvel ellátott lekérdezés) akkor hasznos, amikor egy részeredményt több lépésben, olvashatóan akarunk felépíteni. A WITH placed AS (...) például először rendezi és sorszámozza a rendeléseket, majd a következő CTE ezt használja további ablak típusú számításokhoz.

Az ablakfüggvény (window function) az eredményhalmaz minden sorához egy adott tartomány, az ablakkeret (window frame) alapján számol értéket. A OVER kulcsszó után megadott

PARTITION BY customer_id
ORDER BY order_ts, order_id

azt mondja meg, hogy az ablak egy-egy ügyfélre korlátozódik, és az ügyfélen belül az időpont, majd a rendelésazonosító szerint rendezünk. Egy rendeléshez több sor is tartozhat azonos időponttal; a order_id a tiebreaker, tehát a rendezést determinisztikussá teszi.

ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY order_ts, order_id
)

Az első rendeléshez 1, a másodikhoz 2 tartozik. Ebből ROW_NUMBER() - 1 elvileg a korábbi rendelésszámot adja, de az explicit kerettel megadott COUNT erősebb kontraktust ad, és elkerüli, hogy valaki elfelejtse a -1-et.

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING

Ez partíción belül az aktuális sort megelőző összes sort jelenti, az aktuális sort nem. A ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW a jelen sorral bezárólag számolna; ez a gyakori, ám itt hibás alapértelmezett viselkedés.

A LAG(order_ts, 1) a rendezett partícióban az eggyel korábbi sor order_ts értékét adja. Az első sornál nincs előző elem, ezért az eredmény NULL. A frame nem helyettesíti a LAG-ot: a frame aggregátumokhoz való, a LAG közvetlenül az előző sor értékét másolja.

7.3.4 Teljes minimális SQL-munkapélda

Az alábbi Python-példa csak a standard sqlite3 modult használja, tehát a lab virtuális környezetben is azonnal futtatható. A munkakönyvtár legyen az a könyvtár, ahová a s027_lab.py fájlt mentjük; a fájlt Linuxon/macOS-en és Windows PowerShellben is a python s027_lab.py paranccsal indíthatjuk.

import sqlite3

con = sqlite3.connect(":memory:")

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

INSERT INTO orders VALUES
  ('o1','C1','2025-01-10T08:00:00+00:00',1000),
  ('o2','C1','2025-01-20T08:00:00+00:00', 500),
  ('o3','C1','2025-01-30T08:00:00+00:00', 800),
  ('o4','C2','2025-01-15T08:00:00+00:00', 300),
  ('o5','C2','2025-01-22T08:00:00+00:00', 700);
""")

query = r"""
WITH placed AS (
  SELECT
    order_id,
    customer_id,
    order_ts,
    basket_minor,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_ts, order_id
    ) AS order_seq
  FROM orders
),
past_features AS (
  SELECT
    order_id,
    customer_id,
    order_ts,
    order_seq - 1 AS prior_order_count,
    LAG(order_ts, 1) OVER (
      PARTITION BY customer_id
      ORDER BY order_ts, order_id
    ) AS prev_order_ts,
    SUM(basket_minor) OVER (
      PARTITION BY customer_id
      ORDER BY order_ts, order_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prior_basket_minor
  FROM placed
)
SELECT
  order_id,
  customer_id,
  order_ts,
  prior_order_count,
  prev_order_ts,
  ROUND(JULIANDAY(order_ts) - JULIANDAY(prev_order_ts)) AS days_since_prev
FROM past_features
ORDER BY customer_id, order_ts, order_id;
"""

for row in con.execute(query):
    print(row)

con.close()

A várható kimenet a mini tábla kézi kiértékeléséből, nem helyi futási jegyzékből:

A kód a prior_basket_minor értéket is kiszámítja, de a fő kimenetből az áttekinthetőség kedvéért elhagytuk; a C1-nél o1 esetén NULL, o2 esetén 1000, o3 esetén 1500, C2-nél pedig NULL, majd 300. A COUNT üres keretnél 0-t ad, a SUM viszont NULL-t; ha a kosárösszegnél üzletileg a nulla a kívánt érték, COALESCE(..., 0) szükséges.

A CTE-közötti kapcsolat fontos: az első CTE a táblát rendezi és indexeli, a második pedig már a rendezett index segítségével számol. A prior_order_count megegyezik order_seq - 1-gyel, tehát a két számítás egymás ellenőrzése is.

7.3.5 SQLite, DuckDB és PostgreSQL összevetés

A WITH, PARTITION BY, ROW_NUMBER, LAG és a frame szintaxisa a három rendszerben közel azonos. A típus- és dátumkezelésben van fontos különbség.

Kérdés	SQLite	DuckDB	PostgreSQL
`order_ts` tárolása	`TEXT`, az állandó `+00:00` miatt itt a szöveges rendezés is időrend	`TIMESTAMP` értelmezhető	`TIMESTAMPTZ` javasolt
Napkülönbség	`JULIANDAY(order_ts) - JULIANDAY(prev_order_ts)`	`date_diff('day', prev_order_ts, order_ts)`	`EXTRACT(EPOCH FROM (order_ts - prev_order_ts)) / 86400`
Ablak szintaxisa	azonos	azonos	azonos

A Northstar kezdőlabor SQLite-ot használ; a DuckDB a Fájlformátumok, Arrow, DuckDB és Polars alfejezetben kap részletes szerepet. A táblázat célja csak az, hogy a platformváltásnál a dátum kifejezést külön kell ellenőrizni, magát a window-szerkezetet nem.

7.3.6 Ellenőrző invariánsok

A Northstar orders.csv tiszta fájljára is alkalmazhatjuk ezt a lekérdezést. Az orders_raw.csv szándékos hibáit nem szabad közvetlenül a feature-számításba engedni; a tisztítás és a kulcs-egyediség ellenőrzése a Duplikáció, kulcs és adatminőségi szerződés alfejezet feladata. A tiszta tábla kontrollja után az ott bemutatott szándék: egy rendelés egy sor, tehát a window nem szaporítja a sorokat.

7.3.7 Feladatok

7.3.8 1. Értelmezés a mini táblán

Add meg a C1 ügyfél o1, o2, o3 soraihoz az alábbi oszlopok értékét: order_seq, prior_order_count, prev_order_ts, days_since_prev. A mini táblát a főpélda INSERT utasítása tartalmazza.

7.3.9 2. Implementálás a Northstar adatra

A lab/orders.csv tiszta fájlt töltsd be SQLite-ba orders néven, legalább az order_id, customer_id, order_ts, basket_minor oszlopokkal. A betöltéshez használhatsz Python csv.DictReader-t és executemany-t a basket_minor egész átalakításával. Futtasd a főpélda CTE-jét, és ellenőrizd:

A feladathoz minden szükséges bemenet az ADATOK.md orders.csv szerződése; más külső letöltés nem kell.

7.3.10 3. Hibadiagnózis

WITH x AS (
  SELECT
    order_id,
    customer_id,
    COUNT(*) OVER (
      PARTITION BY customer_id
      ORDER BY order_ts, order_id
    ) AS prior_order_count
  FROM orders
)
SELECT * FROM x
WHERE customer_id = 'C1'
ORDER BY order_ts, order_id;

Magyarázd el, miért rossz a prior_order_count név, és pontosan mi lesz a C1 három sorában. Add meg a javítást a frame segítségével.

7.3.11 4. Megváltozott feltétel: korábbi kosárösszeg és napkülönbség

A főpélda past_features CTE-jében a prior_basket_minor összeg már szerepel. Írj SQL-lekérdezést, amely rendelésenként kiírja a prior_basket_minor és a days_since_prev értékeket. Ellenőrizd a mini adaton, hogy o3 értéke 1500, o5 értéke 300, az első rendeléseknél pedig NULL szerepel. Döntsd el, hogy a NULL vagy a 0 a megfelelő üzleti jelentés; indokold a választást.

7.3.12 Megoldások és indoklás

7.3.13 1. megoldás

order_id	order_seq	prior_order_count	prev_order_ts	days_since_prev
o1	1	0	NULL	NULL
o2	2	1	2025-01-10T08:00:00+00:00	10
o3	3	2	2025-01-20T08:00:00+00:00	10

A ROW_NUMBER a rendezett partícióban valóban 1, 2, 3; a -1 a 0, 1, 2 prefixösszeget adja. A LAG az eggyel korábbi időpontot másolja, ezért a második sornál 10, a harmadiknál szintén 10 nap a különbség.

7.3.14 2. megoldás

import csv
import sqlite3

con = sqlite3.connect("northstar.sqlite")
con.execute("DROP TABLE IF EXISTS orders")
con.execute("""
CREATE TABLE orders (
  order_id     TEXT PRIMARY KEY,
  customer_id  TEXT NOT NULL,
  order_ts     TEXT NOT NULL,
  basket_minor INTEGER NOT NULL
)
""")

with open("orders.csv", newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)
    rows = [
        {
            "order_id": row["order_id"],
            "customer_id": row["customer_id"],
            "order_ts": row["order_ts"],
            "basket_minor": int(row["basket_minor"]),
        }
        for row in reader
    ]

con.executemany(
    "INSERT INTO orders VALUES (:order_id, :customer_id, :order_ts, :basket_minor)",
    rows,
)
con.commit()

Ezután a főpélda query változóját változtatás nélkül futtathatjuk. Az ellenőrző lekérdezés:

SELECT
  COUNT(*) AS row_count,
  COUNT(DISTINCT order_id) AS distinct_orders,
  MIN(prior_order_count) AS min_prior
FROM past_features;

A várt szám 2400, 2400, 0. Az ügyfélen belüli monotonitást a korábbi ROW_NUMBER - 1 = prior_order_count invariant is ellenőrzi; ehhez a past_features CTE-t ki kell egészíteni az order_seq oszloppal.

7.3.15 3. megoldás

A hibás lekérdezés nem ad meg frame-et, ezért az SQLite a COUNT-ra az alapértelmezett RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW keretet használja. Ez az aktuális sort is beleszámítja, tehát C1 sorai 1, 2, 3 lesznek, miközben a név prior_order_count. A helyes cél 0, 1, 2. A javítás:

COUNT(*) OVER (
  PARTITION BY customer_id
  ORDER BY order_ts, order_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS prior_order_count

Ugyanez a ROW_NUMBER() - 1 számítás is, de a ROWS frame expliciten dokumentálja, hogy az aktuális sor nem tartozik a múltbéli ablakba.

7.3.16 4. megoldás

WITH placed AS (
  SELECT order_id, customer_id, order_ts, basket_minor
  FROM orders
),
past_features AS (
  SELECT
    order_id,
    customer_id,
    order_ts,
    LAG(order_ts) OVER (
      PARTITION BY customer_id
      ORDER BY order_ts, order_id
    ) AS prev_order_ts,
    COALESCE(
      SUM(basket_minor) OVER (
        PARTITION BY customer_id
        ORDER BY order_ts, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
      ),
      0
    ) AS prior_basket_minor
  FROM placed
)
SELECT
  order_id,
  customer_id,
  prior_basket_minor,
  ROUND(JULIANDAY(order_ts) - JULIANDAY(prev_order_ts)) AS days_since_prev
FROM past_features
ORDER BY customer_id, order_ts, order_id;

Mini adaton a prior_basket_minor a C1-nél 0, 1000, 1500, a C2-nél 0, 300. A COALESCE az első rendelésnél 0-t ad, ami akkor megfelelő, ha az üzleti kérdés a „korábban elköltött összeg”: nincs korábbi rendelés, tehát nulla az összeg. Ha a hiányt külön akarjuk megkülönböztetni az „új ügyfél” állapottól, a NULL is indokolható; a szerződést a modellkártyán kell rögzíteni.

7.3.17 Gyakori tévedés ellenpéldával

A legveszélyesebb hiba a frame nélküli ablak, amely a teljes ügyféltörténetet használja. Például

AVG(basket_minor) OVER (PARTITION BY customer_id)

a C1 minden sorához (1000 + 500 + 800) / 3 = 766.67-et ad. Ez a rendelés pillanatában a jövőbeli o3 kosárértékét is tartalmazza, tehát a feature a célváltozóhoz képest jövőinformációt szivárogtat be. A helyes múltbeli átlag például o3 előtt (1000 + 500) / 2 = 750, o2 előtt pedig 1000. A Join-kardinalitás és duplázódó célváltozó alfejezet join-kardinalitási szabályához hasonlóan itt is a feladat a szemcsézettség megőrzése: a window nem join, de a keret megválasztása ugyanúgy eldönti, mely sorok magyarázhatják az adott sort. A Duplikáció, kulcs és adatminőségi szerződés alfejezetből az is következik, hogy a featurét csak tisztított, egyedi rendeléskulcsú táblára számítsuk.

7.3.18 Önellenőrzés

Azonos order_ts mellett a másodlagos order_id rendezés determinisztikus sorrendet ad, de nem teszi az egyik rendelést a másiknál időben korábbivá. Ha a „korábbi” jelentése szigorúan order_ts < aktuális, az időbélyeg-holtversenyek kizárását külön kell biztosítani; a ROW_NUMBER önmagában nem elegendő.

7.3.19 Rövid összefoglaló

A WITH CTE lépésekre bontja a feature-előállítást; a PARTITION BY customer_id és az ORDER BY order_ts, order_id határozza meg a sorrendet. A ROW_NUMBER a rendezett sorozat indexét adja, a LAG a szomszédos elem eltérését, míg a ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING keret a jelen sort kizáró prefixösszeget számolja. Ez adja a predikció pillanatában ismert prior_order_count-ot úgy, hogy sem az aktuális, sem a jövőbeli rendelés nem kerül a jellemzőbe. A következő lépés a Paraméterezett lekérdezés és lekérdezési terv alfejezetben a paraméterezett lekérdezés és a lekérdezési terv: ugyanezt a CTE-t hogyan futtassuk biztonságosan változó cutofftal anélkül, hogy szövegből összefűzött SQL-t használnánk.

7.3.1 Cél és konkrét helyzet