18 lines
745 B
MySQL
18 lines
745 B
MySQL
|
-- get rid of excessive scores
|
||
|
|
--SELECT score_history_last_imported_at FROM generic_state LIMIT 1;
|
||
|
|
|
||
|
|
DROP FUNCTION IF EXISTS discard_scores();
|
||
|
|
CREATE FUNCTION discard_scores () RETURNS VOID AS $$
|
||
|
|
BEGIN
|
||
|
|
WITH scores_to_delete AS (
|
||
|
|
SELECT alocidispid, blocidispid, scorer, created_at FROM (SELECT *, row_number() OVER (PARTITION BY alocidispid, blocidispid, scorer ORDER BY scores.created_at DESC) AS rownum FROM scores) tmp WHERE rownum >= 6
|
||
|
|
)
|
||
|
|
DELETE FROM scores USING scores_to_delete WHERE
|
||
|
|
scores.alocidispid = scores_to_delete.alocidispid AND
|
||
|
|
scores.blocidispid = scores_to_delete.blocidispid AND
|
||
|
|
scores.scorer = scores_to_delete.scorer AND
|
||
|
|
scores.created_at = scores_to_delete.created_at;
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|