Materiál k cvičení DBM1 týden #5, sezona 2023/2024

Využití dimenzí a faktů v analýze dat

Z předchozího cvičení je připravená posloupnost operací, která vytvoří DuckDB databázi obsahující dimenzní tabulku popisující hry v Stormgate a faktovou tabulku o hráčích. Data jsou získána z CSV souboru.

💾 Pro referenci je zde Java projekt, který by měl odpovídat stavu po cvičení #4.

Dále je připraveno prostředí pro dotazování se do vytvořené databáze.

Základní agregace

Pro ukázku využití databáze k analytickým účelům je připraveno několik komentovaných SQL dotazů.

Základní technikou je využití agregací, které umožňují seskupit záznamy podle nějakého klíče a nad seskupenými záznamy provést výpočet. Například může být zajímavé rozčlenit hry podle serveru, na kterém byly odehrány, a zjistit počty her na jednotlivých serverech.

  
-- #1
SELECT server, COUNT(*) AS c
FROM matches
GROUP BY server
ORDER BY c DESC
;

V dotazu byla použita klauzule GROUP BY pro seskupení záznamů podle atributu server. V tento moment jsou jednotlivé záznamy z tabulky matches rozděleny do skupin podle hodnoty atributu server. Nad každou skupinou je následně proveden výpočet, který je definován v klauzuli SELECT. V tomto případě je to funkce COUNT(*), která počítá počet záznamů v každé skupině.

V SELECT je vždy možné mít pouze atribut použitý v GROUP BY a ostatní atributy musí být součástí agregace, díky které z množiny N hodnot vznikne hodnota pouze jedna.

Využití dimenzní tabulky

Poměrně často bude potřeba použít i dimenzní tabulky společně s faktovou. V řešeném případě je v dimenzní tabulce players informace o posledním známém jménu hráče.

※ Herní jméno lze měnit a používaný INSERT OR REPLACE INTO players vždy přepisuje původní jméno novým.

Pro vypsání počtu odehraných her jednotlivých hráčů a jejich aktuální jméno lze uvažovat o následujícím dotazu:

  
-- #2a
SELECT p.id, p.name, COUNT(*) AS c
FROM matches m JOIN players p ON m.player1_id = p.id
GROUP BY p.id, p.name
ORDER BY c DESC
;

Databáze sice nabídne zdánlivě správné řešení, nicméně je potřeba uvědomit si význam player1_* a player2_* attributů. Jelikož v JOIN klauzuli jsou spojeny tabulky skrze player1_id, je výsledek omezen pouze na hráče, kteří byli uváděni ve hře jako hráč na první pozici (což je zcela náhodné).

Pro zohlednění této znalosti o datasetu je možné přistoupit k několika alternativním přístupům. Pro tento dotaz lze upravit spojovací podmínku, aby propojila oba hráče (#2b níže), nebo lze využít funkce UNION pro spojení dvou výsledků do jednoho.

  
-- #2b
SELECT p.id, p.name, COUNT(*) AS c
FROM matches m JOIN players p ON m.player1_id = p.id OR m.player2_id = p.id
GROUP BY p.id, p.name
ORDER BY c DESC
;

Obě varianty značně komplikují dotazování. Pro zjednodušení bude použit trik, který by v klasické provozní databázi nebyl vhodný kvůli duplikování dat, ale u datových skladů si lze podobné excesy s denormalizací schématu a zavedením redundantních dat dovolit.

Pro každý zápas bude vytvořen extra záznam v tabulce matches, ve kterém budou prohozeny data o hráči na pozici 1 a 2. Tedy hodnoty z player1_* budou v player2_* a naopak. Tím se změní sémantika atributů tím, že player1 bude vždy hráč zájmu a player2 jeho protihráč.

Zavedení redundance zápasů s prohozenými stranami

Naznačené řešení lze implementovat úpravou insertMatch metody v DuckDBOperations třídě.

  
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
    pstmt.setInt(1, match.duration);
    pstmt.setString(2, match.server);
    pstmt.setString(3, match.leaderboard);
    pstmt.setString(4, match.match_id);
    pstmt.setString(5, match.created_at);
    pstmt.setString(6, match.player1_id);
    pstmt.setString(7, match.player1_result);
    pstmt.setString(8, match.player1_race);
    pstmt.setDouble(9, match.player1_mmr);
    pstmt.setInt(10, match.player1_ping);
    pstmt.setString(11, match.player2_id);
    pstmt.setString(12, match.player2_result);
    pstmt.setString(13, match.player2_race);
    pstmt.setDouble(14, match.player2_mmr);
    pstmt.setInt(15, match.player2_ping);

    pstmt.executeUpdate();


    pstmt.setInt(1, match.duration);
    pstmt.setString(2, match.server);
    pstmt.setString(3, match.leaderboard);
    pstmt.setString(4, match.match_id + "-swap");
    pstmt.setString(5, match.created_at);
    pstmt.setString(11, match.player1_id);
    pstmt.setString(12, match.player1_result);
    pstmt.setString(13, match.player1_race);
    pstmt.setDouble(14, match.player1_mmr);
    pstmt.setInt(15, match.player1_ping);
    pstmt.setString(6, match.player2_id);
    pstmt.setString(7, match.player2_result);
    pstmt.setString(8, match.player2_race);
    pstmt.setDouble(9, match.player2_mmr);
    pstmt.setInt(10, match.player2_ping);

    pstmt.executeUpdate();
}

Všimněte si přečíslování indexů parametrů v druhé polovině kódu a úpravy match_id v druhém volání pstmt.setInt(4, match.match_id + "-swap"). Jinak by došlo ke kolizi klíčů a tedy přepsání prvního záznamu.

Po této úpravě dotaz #2a vrátí již očekávané správné výsledky.

Vlastní výpočty v projekci

V některých případech bude potřeba provést vlastní výpočet metriky, která není přímo dostupná v tabulce. Například může být zajímavé zjistit, jaký byl rozdíl v MMR mezi hráči v jednotlivých hrách, což může být použito pro posouzení kvality párovacího algoritmu.

※ MMR (matchmaking rating) je hodnota sloužící pro popsání dovednostní úrovně hráče založená na 📚 Elo systému

※ Párovací algoritmus [v 1v1 hrách] se snaží namapovat vhodné protihráče z množiny momentálně aktivních hráčů. Jedná se o optimalizační problém, který se snaží minimalizovat rozdíl v MMR, dobu čekání, vzdálenost od serveru a případně další faktory.

  
-- #3
SELECT
    ABS(player1_mmr - player2_mmr)
    ROUND(ABS(player1_mmr - player2_mmr)*2,-2)/2,
    FLOOR(ABS(player1_mmr - player2_mmr)/50)*50,
FROM matches m    
;

Dotaz #3 demonstruje, jak lze vyjádřit rozdíl v MMR u jednotlivých zápasů a zároveň i dva způsoby, jak přistupovat k diskretizaci reálných hodnot. V tomto případě je ukázáno zaokrouhlení na nejbližší padesátku a zaokrouhlení na nižší padesátku. V praxi tedy jde o seskupení záznamů do intervalů o šířce 50 s tím rozdílem, že první generuje intervaly 0-25, 25-75, 75-125, ... zatímco druhý 0-50, 50-100, 100-150, ...

Takto diskretizovanou hodnotu lze uplatnit v seskupující klauzuli následovně:

  
-- #4
SELECT
    FLOOR(ABS(player1_mmr - player2_mmr)/50)*50,
    COUNT(*) AS c
FROM matches m    
GROUP BY 1
ORDER BY 1
;

Všimněte si použití GROUP BY 1, což značí, že se seskupuje podle prvního výrazu v SELECT klauzuli. Podobně lze použít i ORDER BY 1. Tento způsob značně zjednodušuje psaní dotazů, ale pro pochopení může být účelnější psát výrazy plně, případně použít alias.

Vybrané speciální analytické funkce v DuckDB

DuckDB nabízí několik speciálních funkcí, které mohou být užitečné pro datovou analýzu. Například můžeme chtít u každého hráče pozorovat, s jak kvalitními hráči hrál (viz diskretizovaný rozdíl v MMR výše). Pro tento účel by se hodilo vypočítat histogram rozdílů v MMR, případně stanovit statistický modus této hodnoty.

  
-- #5
SELECT 
    p.id,
    mode(FLOOR(ABS(player1_mmr - player2_mmr)/50)*50),
    histogram(FLOOR(ABS(player1_mmr - player2_mmr)/50)*50)
FROM matches m JOIN players p ON m.player1_id = p.id
GROUP BY p.id
;

Funkce mode vrací nejčastěji se vyskytující hodnotu v daném sloupci. Funkce histogram vrací mapu hodnot a jejich četností. Výsledek je tedy vhodný pro další zpracování v aplikaci.

※ Ani jedna z těchto funkcí není standardní součástí SQL a je specifická pro DuckDB.

Další zajímavou funkcí je argmin a argmax, které vrací hodnotu z jiného sloupce, která odpovídá extrémní hodnotě v daném sloupci. Tímto například můžeme získat informaci o zápase, ve kterém byl největší rozdíl v MMR nebo jiná zajímavá extrémní hodnota sledované metriky. V dotazu #6 je řešen výběr serveru s nejlepším a nejhorším pingem pro daného hráče. Tuto informaci lze například použít k odhadu regionu, ze kterého hráč pochází.

  
-- #6
SELECT p.id,
    round(avg(m.player1_ping)),
    argmin(server, m.player1_ping),
    argmax(server, m.player1_ping),
FROM matches m JOIN players p ON m.player1_id = p.id
GROUP BY p.id
;