SQL równoważący linie turnieju na "piątkach" w JFR Pary

rozstawienie.sql

-- modify table to store intermediate data
ALTER TABLE zawodnicy ADD COLUMN linia INT(4);
ALTER TABLE zawodnicy ADD COLUMN los DECIMAL(6,2);
ALTER TABLE zawodnicy MODIFY COLUMN grupa INT(4);

-- first, some entropy
UPDATE zawodnicy SET los = RAND() * 10000;

-- mising pairs will be on tables #5 in the last lines, for now
-- and put in the weakest (last) group
UPDATE zawodnicy SET wk = -1, los = -1 WHERE cezar = '00000';

-- write pairs order in the table, put missing pairs with the weakest group
UPDATE zawodnicy
LEFT JOIN (
    SELECT z2.idp, COUNT(DISTINCT z1.idp) place
    FROM (
        SELECT idp, SUM(COALESCE(wk, 0)) wk FROM zawodnicy GROUP BY idp
    ) z1
    JOIN (
        SELECT idp, SUM(COALESCE(wk, 0)) wk FROM zawodnicy GROUP BY idp
    ) z2
    ON z1.wk > z2.wk OR (z1.wk = z2.wk AND z1.idp >= z2.idp)
    GROUP BY z2.idp
) wk
ON zawodnicy.idp = wk.idp
SET zawodnicy.grupa = wk.place;

-- count pairs
SET @pairs = (SELECT COUNT(DISTINCT idp) FROM zawodnicy);

-- group pairs into 5 equal-ish groups
UPDATE zawodnicy SET grupa = CEIL(grupa / (@pairs / 5.0));

-- save random order within groups, creating lines
UPDATE zawodnicy
JOIN (
    SELECT COUNT(z1.idp) linia, z2.idp FROM (
        SELECT idp, AVG(los) los, grupa
        FROM zawodnicy
        GROUP BY idp
    ) z1
    JOIN (
        SELECT idp, AVG(los) los, grupa
        FROM zawodnicy
        GROUP BY idp
    ) z2
    ON z1.grupa = z2.grupa
       AND (z1.los > z2.los OR (z1.los = z2.los AND z1.idp >= z2.idp))
    GROUP BY z2.idp
) groups
ON zawodnicy.idp = groups.idp
SET zawodnicy.linia = groups.linia;

-- randomize lines, missing pairs go to last lines, for now
UPDATE zawodnicy SET los = RAND() * 10000 WHERE los >= 0;

-- save random order within lines, creating table numbers
UPDATE zawodnicy
JOIN (
    SELECT COUNT(z1.idp) stol, z2.idp FROM (
        SELECT idp, AVG(los) los, linia
        FROM zawodnicy
        GROUP BY idp
    ) z1
    JOIN (
        SELECT idp, AVG(los) los, linia
        FROM zawodnicy
        GROUP BY idp
    ) z2
    ON z1.linia = z2.linia
       AND (z1.los > z2.los OR (z1.los = z2.los AND z1.idp >= z2.idp))
    GROUP BY z2.idp
) groups
ON zawodnicy.idp = groups.idp
SET zawodnicy.cover = groups.stol;

-- renumber pairs
UPDATE zawodnicy SET idp = (linia - 1) * 5 + 10 + cover;

-- renumber players
UPDATE zawodnicy SET id = idp + 10600 WHERE id > 600; 
UPDATE zawodnicy SET id = idp + 10000 WHERE id <= 600;
UPDATE zawodnicy SET id = id - 10000;

-- if the number of pairs in the last section is 1-6, there's no problem
-- as there's at most one missing pair and it's already in the correct place
-- (last pair number = stationary in Howell or stationary in last Appendix table)
-- we have to check if it's not a full section (number of pairs in the last section is 7-9)
-- to switch missing pairs to correct places

-- first, count missing pairs
SET @missing_pairs = (SELECT COUNT(*) FROM (SELECT idp, SUM(wk = -1) missing FROM zawodnicy GROUP BY idp) m WHERE missing = 2);
-- and find the last line
SET @last_line = (SELECT MAX(linia) FROM zawodnicy);

-- if there's only one pair missing, switch it from last line (last section, EW) to the penultimate line (last section, NS)

-- switch 5EW from last section with a group 5 pair from last section, NS
UPDATE zawodnicy z1
JOIN zawodnicy z2
ON
    z1.linia = @last_line AND MOD(z1.idp, 5) = 0
    AND z2.linia = @last_line - 1 AND z2.grupa = 5
    AND ((z1.id > 600) = (z2.id > 600))
    AND @missing_pairs = 1
SET
    z1.idp = z2.idp + 10000,
    z1.linia = z2.linia,
    z1.cover = z2.cover,
    z1.grupa = z2.grupa,
    z2.idp = z1.idp + 10000,
    z2.linia = z1.linia,
    z2.cover = z1.cover,
    z2.grupa = z1.grupa;
UPDATE zawodnicy SET idp = idp - 10000 WHERE idp > 10000;

-- switch group 5 pair from last section, NS, with 5NS of that section
UPDATE zawodnicy z1
JOIN zawodnicy z2
ON
    z1.linia = @last_line - 1 AND MOD(z1.idp, 5) = 0
    AND z2.linia = @last_line - 1 AND z2.grupa = 5
    AND ((z1.id > 600) = (z2.id > 600))
    AND @missing_pairs = 1
SET
    z1.idp = z2.idp + 10000,
    z1.linia = z2.linia,
    z1.cover = z2.cover,
    z1.grupa = z2.grupa,
    z2.idp = z1.idp + 10000,
    z2.linia = z1.linia,
    z2.cover = z1.cover,
    z2.grupa = z1.grupa;
UPDATE zawodnicy SET idp = idp - 10000 WHERE idp > 10000;

-- if there are 2 or 3 pairs missing, missing pair from last section, 5EW
-- should land in the penultimate section, 5NS

-- switch 5EW from last section with a group 5 pair from penultimate section, NS
UPDATE zawodnicy z1
JOIN zawodnicy z2
ON
    z1.linia = @last_line AND MOD(z1.idp, 5) = 0
    AND z2.linia = @last_line - 3 AND z2.grupa = 5
    AND ((z1.id > 600) = (z2.id > 600))
    AND @missing_pairs IN (2, 3)
SET
    z1.idp = z2.idp + 10000,
    z1.linia = z2.linia,
    z1.cover = z2.cover,
    z1.grupa = z2.grupa,
    z2.idp = z1.idp + 10000,
    z2.linia = z1.linia,
    z2.cover = z1.cover,
    z2.grupa = z1.grupa;
UPDATE zawodnicy SET idp = idp - 10000 WHERE idp > 10000;

-- switch group 5 pair from penultimate section, NS, with 5NS of that section
UPDATE zawodnicy z1
JOIN zawodnicy z2
ON
    z1.linia = @last_line - 3 AND MOD(z1.idp, 5) = 0
    AND z2.linia = @last_line - 3 AND z2.grupa = 5
    AND ((z1.id > 600) = (z2.id > 600))
    AND @missing_pairs IN (2, 3)
SET
    z1.idp = z2.idp + 10000,
    z1.linia = z2.linia,
    z1.cover = z2.cover,
    z1.grupa = z2.grupa,
    z2.idp = z1.idp + 10000,
    z2.linia = z1.linia,
    z2.cover = z1.cover,
    z2.grupa = z1.grupa;
UPDATE zawodnicy SET idp = idp - 10000 WHERE idp > 10000;

-- if there are 3 missing pairs, missing pair from penultimate section, 5EW
-- should land in the last section, 4NS

-- switch 5EW from penultimate section with a group 4 pair from last section, NS
UPDATE zawodnicy z1
JOIN zawodnicy z2
ON
    z1.linia = @last_line - 2 AND MOD(z1.idp, 5) = 0
    AND z2.linia = @last_line - 1 AND z2.grupa = 4
    AND ((z1.id > 600) = (z2.id > 600))
    AND @missing_pairs = 3
SET
    z1.idp = z2.idp + 10000,
    z1.linia = z2.linia,
    z1.cover = z2.cover,
    z1.grupa = z2.grupa,
    z2.idp = z1.idp + 10000,
    z2.linia = z1.linia,
    z2.cover = z1.cover,
    z2.grupa = z1.grupa;
UPDATE zawodnicy SET idp = idp - 10000 WHERE idp > 10000;

-- switch group 4 pair from last section, NS, with 4NS of that section
UPDATE zawodnicy z1
JOIN zawodnicy z2
ON
    z1.linia = @last_line - 1 AND MOD(z1.idp, 5) = 4
    AND z2.linia = @last_line - 1 AND z2.grupa = 4
    AND ((z1.id > 600) = (z2.id > 600))
    AND @missing_pairs = 3
SET
    z1.idp = z2.idp + 10000,
    z1.linia = z2.linia,
    z1.cover = z2.cover,
    z1.grupa = z2.grupa,
    z2.idp = z1.idp + 10000,
    z2.linia = z1.linia,
    z2.cover = z1.cover,
    z2.grupa = z1.grupa;
UPDATE zawodnicy SET idp = idp - 10000 WHERE idp > 10000;

-- renumber players once again
UPDATE zawodnicy SET id = idp + 10600 WHERE id > 600; 
UPDATE zawodnicy SET id = idp + 10000 WHERE id <= 600;
UPDATE zawodnicy SET id = id - 10000;

-- cleanup
UPDATE zawodnicy SET grupa = NULL, cover = NULL;
UPDATE zawodnicy SET wk = NULL WHERE cezar = '00000';
ALTER TABLE zawodnicy DROP COLUMN linia;
ALTER TABLE zawodnicy DROP COLUMN los;