Re: DDNet SQL dumps analysis
Posted: Wed Jul 24, 2019 8:48 pm
How did you do the connected graph for rank team?
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;
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;
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.
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
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;