DDNet SQL dumps analysis
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.
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.
-
- Posts: 400
- Joined: Sat Apr 11, 2015 5:05 pm
- Player profile: http://ddnet.tw/players/Chairn/
- Clan: QuintessenZ
Re: DDNet SQL dumps analysis
How did you do the connected graph for rank team?
Re: DDNet SQL dumps analysis
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;
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;
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);
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.
This can be extended to color the nodes/edges with some other data (eg: score, "degree", ...).
-
- Posts: 400
- Joined: Sat Apr 11, 2015 5:05 pm
- Player profile: http://ddnet.tw/players/Chairn/
- Clan: QuintessenZ
Re: DDNet SQL dumps analysis
Very nice, never heard of the gephi software but seems like quite advanced & useful stuff.
Re: DDNet SQL dumps analysis
Nice, how get last week/month points ?
- deen
- Posts: 3576
- Joined: Mon May 05, 2014 2:30 pm
- Player profile: https://ddnet.org/players/deen/
- Discord: deen#5910
Re: DDNet SQL dumps analysis
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
Ah, i tought its possible with SQL.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
- deen
- Posts: 3576
- Joined: Mon May 05, 2014 2:30 pm
- Player profile: https://ddnet.org/players/deen/
- Discord: deen#5910
Re: DDNet SQL dumps analysis
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;
Who is online
Users browsing this forum: Baidu [Spider] and 7 guests