PARTITION BY и доля от итога

Ильва · геймдизайнер баланса «Эпохи Корон»

В патче 1.4 маги забрали почти треть всех побед. Треть! Фракций пять, на каждую должна приходиться пятая часть. По-моему, это и есть доказательство, что маги имба. Проверь и скажи, что я права.

В прошлом уроке окно приносило в каждую строку итог её группы: средний APM фракции, число матчей игрока. Сегодня делим строку на этот итог и получаем долю. Доли любят все заказчики, и на долях проще всего ошибиться в выводе.

Кусок пирога

Пирог разрезали на пять кусков. Чтобы сказать, что твой кусок «треть пирога», нужно знать две вещи: вес твоего куска и вес всего пирога. Доля это кусок, делённый на целое.

В SQL кусок лежит в строке, а целое приносит окно:

доля = значение строки / sum(значение) OVER (PARTITION BY группа)

PARTITION BY задаёт, что считать целым. Доля фракции в патче делится на сумму по патчу, доля игрока в команде делится на сумму по команде. Сменил PARTITION BY, сменил пирог.

Доля побед в патче

Сначала схлопнем строки до «патч, фракция» обычной группировкой, а потом посчитаем доли окном поверх готовых сумм. Окно выполняется после GROUP BY, поэтому видит уже сгруппированные строки:

WITH s AS (
  SELECT p.version, f.name AS faction,
         count(*) AS games,
         count(*) FILTER (WHERE mp.result = 'win') AS wins
  FROM match_players mp
  JOIN matches m  ON m.id = mp.match_id
  JOIN patches p  ON p.id = m.patch_id
  JOIN factions f ON f.id = mp.faction_id
  GROUP BY p.version, f.name
)
SELECT version, faction, games, wins,
       round(wins::numeric  / sum(wins)  OVER (PARTITION BY version), 3) AS share_of_wins,
       round(games::numeric / sum(games) OVER (PARTITION BY version), 3) AS share_of_games
FROM s
WHERE version = '1.4'
ORDER BY share_of_wins DESC;

 version | faction        | games | wins | share_of_wins | share_of_games
---------+----------------+-------+------+---------------+----------------
 1.4     | Орден Магов    |  2266 | 1207 |         0.310 |          0.291
 1.4     | Империя        |  1523 |  759 |         0.195 |          0.196
 1.4     | Северные Кланы |  1510 |  731 |         0.188 |          0.194
 1.4     | Нежить         |  1201 |  601 |         0.154 |          0.154
 1.4     | Лесной Союз    |  1284 |  594 |         0.153 |          0.165

Ильва права в цифре: маги забрали 31,0% побед патча 1.4. Но посмотри на соседний столбец. Магами сыграно 29,1% всех матчей патча. После патча за них стали играть чаще, и побед стало больше потому, что игр стало больше.

Посмотри, где стоит WHERE version = '1.4': в том же SELECT, что и окно. WHERE выполняется раньше оконных функций, так что окно видит только строки патча 1.4. Доли от этого не поменялись, потому что PARTITION BY version и так считает каждый патч отдельно. С фильтром по фракции такой трюк уже не пройдёт, об этом ниже.