Ombor modellashtirish: fakt va o'lchov

diagramma(
    {
        "O'LCHOV\nsana": (0, 3.0, 2.2, 1.0, "saqlash"),
        "O'LCHOV\nrestoran": (0, 1.0, 2.2, 1.0, "saqlash"),
        "FAKT\nbuyurtma": (3.6, 2.0, 2.6, 1.4, "qayta"),
        "O'LCHOV\nkuryer": (7.4, 3.0, 2.2, 1.0, "saqlash"),
        "O'LCHOV\ntuman": (7.4, 1.0, 2.2, 1.0, "saqlash"),
    },
    [
        ("O'LCHOV\nsana", "FAKT\nbuyurtma"),
        ("O'LCHOV\nrestoran", "FAKT\nbuyurtma"),
        ("FAKT\nbuyurtma", "O'LCHOV\nkuryer"),
        ("FAKT\nbuyurtma", "O'LCHOV\ntuman"),
    ],
    nom="yulduz_sxema.png", kenglik=12,
    izoh="Yulduz sxemasi: o'rtada fakt, atrofida o'lchovlar.",
)

python3 -c "
import duckdb, pandas as pd

d = pd.read_csv('../datasets/buyurtma.csv', parse_dates=['vaqt'])
con = duckdb.connect('ombor.db')
con.execute('DROP TABLE IF EXISTS xom')
con.execute('CREATE TABLE xom AS SELECT * FROM d')
print(f'xom jadval: {len(d):,} qator, {len(d.columns)} ustun')
print()
for u in ('tuman', 'restoran_id', 'kuryer_id'):
    n = con.execute(f'SELECT count(DISTINCT {u}) FROM xom').fetchone()[0]
    print(f'  {u:14s}: {n:>6,} xil qiymat, {len(d):,} marta takrorlangan')
con.close()
"

"""Yulduz sxemasi: fakt va o'lchov jadvallari."""
import duckdb

OLCHOVLAR = {
    "olchov_tuman": ("tuman", ["tuman"]),
    "olchov_restoran": ("restoran_id", ["restoran_id"]),
    "olchov_kuryer": ("kuryer_id", ["kuryer_id"]),
}


def olchov_yarat(con: duckdb.DuckDBPyConnection, nom: str, kalit: str) -> int:
    """Noyob qiymatlardan o'lchov jadvali yasaydi va surrogat kalit beradi."""
    con.execute(f"DROP TABLE IF EXISTS {nom}")
    con.execute(f"""
        CREATE TABLE {nom} AS
        SELECT row_number() OVER (ORDER BY {kalit}) AS id, {kalit}
        FROM (SELECT DISTINCT {kalit} FROM xom WHERE {kalit} IS NOT NULL)
    """)
    return con.execute(f"SELECT count(*) FROM {nom}").fetchone()[0]


def fakt_yarat(con: duckdb.DuckDBPyConnection) -> int:
    """Fakt jadvali: o'lchovlarga id bilan bog'lanadi, matn saqlamaydi."""
    con.execute("DROP TABLE IF EXISTS fakt_buyurtma")
    con.execute("""
        CREATE TABLE fakt_buyurtma AS
        SELECT
            x.buyurtma_id,
            CAST(x.vaqt AS DATE)      AS sana,
            t.id                      AS tuman_id,
            r.id                      AS restoran_id,
            k.id                      AS kuryer_id,
            x.summa_ming,
            x.tayyorlash_daq,
            x.yol_daq
        FROM xom x
        LEFT JOIN olchov_tuman    t ON t.tuman       = x.tuman
        LEFT JOIN olchov_restoran r ON r.restoran_id = x.restoran_id
        LEFT JOIN olchov_kuryer   k ON k.kuryer_id   = x.kuryer_id
    """)
    return con.execute("SELECT count(*) FROM fakt_buyurtma").fetchone()[0]

python3 -c "
import sys, pathlib; sys.path.insert(0, 'src')
import duckdb
from quvur.ombor import OLCHOVLAR, olchov_yarat, fakt_yarat

con = duckdb.connect('ombor.db')
for nom, (kalit, _) in OLCHOVLAR.items():
    n = olchov_yarat(con, nom, kalit)
    print(f'{nom:18s}: {n:>6,} qator')
n = fakt_yarat(con)
print(f'{\"fakt_buyurtma\":18s}: {n:>6,} qator')

def hajm(jadval):
    return con.execute(f'SELECT count(*) FROM {jadval}').fetchone()[0]

print()
con.execute('DROP TABLE IF EXISTS yassi')
con.execute('CREATE TABLE yassi AS SELECT * FROM xom')
con.close()
"

python3 -c "
import duckdb, pathlib, os
con = duckdb.connect('ombor.db')

def parquet_hajm(jadval):
    f = f'/tmp/{jadval}.parquet'
    con.execute(f\"COPY {jadval} TO '{f}' (FORMAT PARQUET, COMPRESSION ZSTD)\")
    return pathlib.Path(f).stat().st_size

yassi = parquet_hajm('yassi')
yulduz = parquet_hajm('fakt_buyurtma')
for t in ('olchov_tuman','olchov_restoran','olchov_kuryer'):
    yulduz += parquet_hajm(t)

print(f'yassi jadval    : {yassi/1024:>8.1f} KB')
print(f'yulduz sxemasi  : {yulduz/1024:>8.1f} KB')
print(f'tejaldi         : {(1-yulduz/yassi)*100:>8.1f}%')
con.close()
"

python3 -c "
import duckdb, time
con = duckdb.connect('ombor.db')

def olch(sql, n=5):
    con.execute(sql).fetchall()
    v = []
    for _ in range(n):
        t0 = time.perf_counter()
        con.execute(sql).fetchall()
        v.append((time.perf_counter()-t0)*1000)
    return min(v)

yassi_sql = '''
    SELECT tuman, avg(yol_daq) FROM yassi
    WHERE tuman = 'Yunusobod' GROUP BY tuman
'''
yulduz_sql = '''
    SELECT t.tuman, avg(f.yol_daq)
    FROM fakt_buyurtma f JOIN olchov_tuman t ON t.id = f.tuman_id
    WHERE t.tuman = 'Yunusobod' GROUP BY t.tuman
'''
a, b = olch(yassi_sql), olch(yulduz_sql)
print(f'yassi jadval  : {a:6.2f} ms')
print(f'yulduz (join) : {b:6.2f} ms')
print()
print(f'natija bir xilmi: {con.execute(yassi_sql).fetchall()[0][1] == con.execute(yulduz_sql).fetchall()[0][1]}')
con.close()
"

python3 -c "
import duckdb, random
con = duckdb.connect('ombor.db')

# restoran turini QO'SHAMIZ - faqat o'lchov jadvaliga
random.seed(42)
con.execute('ALTER TABLE olchov_restoran ADD COLUMN IF NOT EXISTS turi TEXT')
con.execute(\"\"\"UPDATE olchov_restoran
                SET turi = CASE WHEN id % 3 = 0 THEN 'fast-food'
                                WHEN id % 3 = 1 THEN 'milliy'
                                ELSE 'kafe' END\"\"\")
n = con.execute('SELECT count(*) FROM olchov_restoran').fetchone()[0]
print(f'o\\'zgartirilgan qatorlar: {n:,} (o\\'lchov jadvalida)')
print(f'fakt jadvali tegilmadi  : 60,000 qator o\\'zgarmadi')
print()

r = con.execute('''
    SELECT r.turi, count(*) AS buyurtma, round(avg(f.yol_daq),2) AS ort_yol
    FROM fakt_buyurtma f JOIN olchov_restoran r ON r.id = f.restoran_id
    GROUP BY r.turi ORDER BY buyurtma DESC
''').fetchall()
print(f'{\"turi\":12s} {\"buyurtma\":>10s} {\"ort_yol\":>9s}')
for t, b, y in r:
    print(f'{t:12s} {b:>10,d} {y:>9.2f}')
con.close()
"

import pandas as pd
show(pd.DataFrame([
    {"Turi": "1-tur (Type 1)", "Nima qiladi": "Eski qiymat ustiga yozadi",
     "Tarix": "Yo'qoladi", "Qachon": "Xato tuzatish"},
    {"Turi": "2-tur (Type 2)", "Nima qiladi": "Yangi qator qo'shadi, eskisini yopadi",
     "Tarix": "Saqlanadi", "Qachon": "Haqiqiy o'zgarish"},
    {"Turi": "3-tur (Type 3)", "Nima qiladi": "'oldingi_qiymat' ustuni qo'shadi",
     "Tarix": "Faqat bitta qadam", "Qachon": "Kamdan-kam"},
]))

python3 -c "
import duckdb
con = duckdb.connect('ombor.db')

con.execute('DROP TABLE IF EXISTS kuryer_t1')
con.execute(\"\"\"CREATE TABLE kuryer_t1 AS
    SELECT id, kuryer_id, 'Chilonzor' AS tuman FROM olchov_kuryer\"\"\")

con.execute('DROP TABLE IF EXISTS kuryer_t2')
con.execute(\"\"\"CREATE TABLE kuryer_t2 AS
    SELECT id, kuryer_id, 'Chilonzor' AS tuman,
           DATE '2026-01-01' AS boshi, DATE '9999-12-31' AS oxiri, TRUE AS joriy
    FROM olchov_kuryer\"\"\")

print('--- O\\'ZGARISHDAN OLDIN ---')
for j in ('kuryer_t1', 'kuryer_t2'):
    n = con.execute(f\"SELECT count(*) FROM {j} WHERE kuryer_id='K0789'\").fetchone()[0]
    print(f'{j}: {n} qator')

# K0789 Yunusobodga o'tdi
con.execute(\"UPDATE kuryer_t1 SET tuman='Yunusobod' WHERE kuryer_id='K0789'\")

con.execute(\"\"\"UPDATE kuryer_t2 SET oxiri=DATE '2026-09-15', joriy=FALSE
               WHERE kuryer_id='K0789' AND joriy\"\"\")
con.execute(\"\"\"INSERT INTO kuryer_t2
    SELECT id, kuryer_id, 'Yunusobod', DATE '2026-09-16', DATE '9999-12-31', TRUE
    FROM olchov_kuryer WHERE kuryer_id='K0789'\"\"\")

print()
print('--- O\\'ZGARISHDAN KEYIN ---')
r1 = con.execute(\"SELECT tuman FROM kuryer_t1 WHERE kuryer_id='K0789'\").fetchall()
print(f'1-tur: {len(r1)} qator -> {[x[0] for x in r1]}')
print('  savol: u SENTABRDA qayerda ishlagan? javob yo\\'q, tarix o\\'chdi')

r2 = con.execute(\"\"\"SELECT tuman, boshi, oxiri FROM kuryer_t2
                    WHERE kuryer_id='K0789' ORDER BY boshi\"\"\").fetchall()
print(f'2-tur: {len(r2)} qator')
for t, b, o in r2:
    print(f'  {t:12s} {b} .. {o}')
print('  savol: u SENTABRDA qayerda ishlagan? -> 1-qator, Chilonzor')
con.close()
"

python3 -c "
import duckdb, pathlib
con = duckdb.connect('ombor.db')
con.execute('DROP TABLE IF EXISTS yassi')
con.close()
for f in pathlib.Path('/tmp').glob('*.parquet'):
    f.unlink(missing_ok=True)
print('vaqtinchalik fayllar tozalandi')
print('ombor.db saqlanadi (18-bobda kerak)')
"