search

TsubakiBotPad / pad-data-pipeline

Original repo: https://github.com/nachoapps/dadguide-data
9 stars 2 forks source link

Drop dupe data from wave_data #370

Closed RheingoldRiver closed 2 years ago

RheingoldRiver commented 2 years ago 
DELETE FROM wave_data WHERE wave_data_id NOT IN (SELECT wd1.wave_data_id FROM wave_data AS wd1 LEFT JOIN wave_data AS wd2 ON wd1.monster_id = wd2.monster_id AND wd1.dungeon_id = wd2.dungeon_id AND wd1.tstamp < wd2.tstamp WHERE wd2.tstamp IS NULL);
RheingoldRiver commented 2 years ago

jk delete from wave_data where drop_monster_id = 0 first

chasehult commented 2 years ago 
DELETE wd1 FROM wave_data AS wd1 LEFT JOIN wave_data AS wd2 ON wd1.drop_monster_id = wd2.drop_monster_id AND wd1.monster_id = wd2.monster_id AND wd1.dungeon_id = wd2.dungeon_id AND wd1.id < wd2.id WHERE wd2.id IS NOT NULL;
DELETE wd1 FROM wave_data AS wd1 LEFT JOIN wave_data AS wd2 ON wd1.monster_id = wd2.monster_id AND wd1.dungeon_id = wd2.dungeon_id AND wd1.id <> wd2.id WHERE wd1.drop_monster_id = 0 AND wd2.id IS NOT NULL;
chasehult commented 2 years ago 
DELETE FROM wave_data WHERE id NOT IN (SELECT maxid FROM (SELECT MAX(id) AS maxid FROM wave_data GROUP BY drop_monster_id, monster_id, dungeon_id, floor_id, stage) x);
DELETE wd1 FROM wave_data AS wd1 LEFT JOIN wave_data AS wd2 ON wd1.monster_id = wd2.monster_id AND wd1.dungeon_id = wd2.dungeon_id AND wd1.floor_id = wd2.floor_id AND wd1.stage = wd2.stage AND wd1.id <> wd2.id WHERE wd1.drop_monster_id = 0 AND wd2.id IS NOT NULL;