DDNet SQL dumps analysis

Read and discuss official announcements, information and news about the DDRaceNetwork.
Forum rules
Please consider wheter there is a more appropiate subforum before creating a new thread here.
This is for offical DDRaceNetwork-related information and discussion only.
Chairn
Posts: 400
Joined: Sat Apr 11, 2015 5:05 pm
Player profile: http://ddnet.tw/players/Chairn/
Clan: QuintessenZ

Re: DDNet SQL dumps analysis

Post by Chairn »

How did you do the connected graph for rank team?
miamia
Posts: 3
Joined: Thu Jul 18, 2019 12:31 am

Re: DDNet SQL dumps analysis

Post 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", ...).
Chairn
Posts: 400
Joined: Sat Apr 11, 2015 5:05 pm
Player profile: http://ddnet.tw/players/Chairn/
Clan: QuintessenZ

Re: DDNet SQL dumps analysis

Post by Chairn »

Very nice, never heard of the gephi software but seems like quite advanced & useful stuff.
Nirvana
Posts: 7
Joined: Mon Jul 06, 2020 8:57 am

Re: DDNet SQL dumps analysis

Post by Nirvana »

Nice, how get last week/month points ?
User avatar
deen
TECHNICAL Team
Posts: 3575
Joined: Mon May 05, 2014 2:30 pm
Player profile: https://ddnet.org/players/deen/
Discord: deen#5910

Re: DDNet SQL dumps analysis

Post 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
Nirvana
Posts: 7
Joined: Mon Jul 06, 2020 8:57 am

Re: DDNet SQL dumps analysis

Post 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.
User avatar
deen
TECHNICAL Team
Posts: 3575
Joined: Mon May 05, 2014 2:30 pm
Player profile: https://ddnet.org/players/deen/
Discord: deen#5910

Re: DDNet SQL dumps analysis

Post 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;
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest