Page 2 of 2

Re: DDNet SQL dumps analysis

Posted: Wed Jul 24, 2019 8:48 pm
by Chairn
How did you do the connected graph for rank team?

Re: DDNet SQL dumps analysis

Posted: Wed Jul 24, 2019 9:52 pm
by miamia
Chairn wrote: Wed Jul 24, 2019 8:48 pm How did you do the connected graph for rank team?

First, create a server_mapping table that will be used to "sanitize" the data.
We will map GER2 to GER, and we won't take into account the servers that had a short life (FRA, KSA, vanilla...).

Code: Select all

CREATE TABLE server_mapping (
    Server char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    MappedServer char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    PRIMARY KEY (Server)
) AS
SELECT Server, SUBSTRING(Server, 1, 3) AS MappedServer
FROM record_race
WHERE Server != ''
GROUP BY Server
HAVING Count(*) > 20000;
Then, we compute the prefered server for each tee. It basically counts the number of races done on each server, and selects the most common.

Code: Select all

CREATE TABLE record_nationality (
    Name varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    Server char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    PRIMARY KEY (Name)
) AS
SELECT T2.Name, M.MappedServer AS Server FROM (
    SELECT T.Name, T.Server
    FROM (
        SELECT Name, Server, COUNT(*) AS Count
        FROM record_race
        GROUP BY Name, Server
        ORDER BY Count DESC
    ) T
    GROUP BY T.Name
) T2
JOIN server_mapping M ON T2.Server = M.Server;
Finally, we create the view that will be used for the graph import.
The node view lists each tee ranked in team, with its prefered server.

Code: Select all

CREATE VIEW gephi_nodes AS 
SELECT Name AS id, Server
FROM record_nationality
WHERE Name IN (SELECT Name FROM record_teamrace);
The edge view lists all tees that played together.

Code: Select all

CREATE VIEW gephi_edges AS
SELECT T1.Name AS source, T2.Name AS target
FROM record_teamrace T1
JOIN record_teamrace T2 ON T1.id = T2.id
WHERE T1.Name < T2.Name
GROUP BY T1.Name, T2.Name;

The graph rendering was done in Gephi (free software).

The rough "process" :

Code: Select all

File > Database import > List of edges...
    Create a new configuration, and test the connection to the database.
    Node query: SELECT * FROM gephi_nodes;
    Edges query: SELECT * FROM gephi_edges;
    OK
    graph type: undirected

Spatialisation
    Select "Atlas Force 2"
    Execute

Appearance > Node Color
    Partition
    Attribute: server
    Apply

Data lab
    Remove "nameless tee" and "brainless tee"

Requests
    NOT(Node)
        Giant component
    Select and remove

For the final reqult, you can tweak some values in the "preview" tab, and export as PNG.
And that's it.

This can be extended to color the nodes/edges with some other data (eg: score, "degree", ...).

Re: DDNet SQL dumps analysis

Posted: Wed Jul 24, 2019 10:41 pm
by Chairn
Very nice, never heard of the gephi software but seems like quite advanced & useful stuff.

Re: DDNet SQL dumps analysis

Posted: Mon Jul 06, 2020 3:12 pm
by Nirvana
Nice, how get last week/month points ?

Re: DDNet SQL dumps analysis

Posted: Mon Jul 06, 2020 10:38 pm
by deen
Not so easy straight with SQL I guess (but still possible), we did it in Python: https://github.com/ddnet/ddnet-scripts/ ... #L324-L326

Re: DDNet SQL dumps analysis

Posted: Tue Jul 07, 2020 5:35 am
by Nirvana
deen wrote: Mon Jul 06, 2020 10:38 pm Not so easy straight with SQL I guess (but still possible), we did it in Python: https://github.com/ddnet/ddnet-scripts/ ... #L324-L326
Ah, i tought its possible with SQL.

Re: DDNet SQL dumps analysis

Posted: Tue Jul 07, 2020 10:46 pm
by deen
I have given it a quick try, here you go:

Code: Select all

select sum(Points) from (select Points from record_race inner join record_maps on record_race.Map = record_maps.Map where Name = "murpi" group by record_race.Map having min(record_race.Timestamp) >= NOW() - INTERVAL 1 WEEK) as m;

select sum(Points) from (select Points from record_race inner join record_maps on record_race.Map = record_maps.Map where Name = "murpi" group by record_race.Map having min(record_race.Timestamp) >= NOW() - INTERVAL 1 MONTH) as m;