Fórmula de contagem de pontos

A página de contagem de pontos é uma das partes mais complexas da ferramenta, com diversas subconsultas durante o cálculo para contemplar todas as opções designadas no data.php. A árvore das consultas é formada com essa hierarquia:

• Fórmula geral
  • user_table
  • points
    • t1
      • edits_ruled
    • t2
      • distinct

edits_ruled

Uma das primeiras etapas do cálculo é a consulta 'edits_rules'. Seu propósito é:

• Coletar as edições que foram avaliadas e onde o valor de bytes é maior que zero.
• Agrupar as edições que tenham sido feitas pelo mesmo editor no mesmo artigo (par editor-artigo).
• Somar o número total de bytes em cada par editor-artigo.
• Contar a quantidade de edições em cada par editor-artigo.
• Regular o número máximo possível de bytes contabilizável em cada par editor-artigo, de acordo com o parâmetro especificado em max_bytes_per_article, estabelecendo o teto caso o número de bytes tenha sido ultrapassado.

SELECT 
  edits.`article`, 
  edits.`user`, 
  CASE WHEN SUM(edits.`bytes`) > ${contest[ 'max_bytes_per_article' ]} THEN ${contest[ 'max_bytes_per_article' ]} ELSE SUM(edits.`bytes`) END AS `bytes`, 
  COUNT(edits.`valid_edit`) AS `valid_edits` 
FROM 
  `edits` 
WHERE 
  edits.`valid_edit` IS NOT NULL 
  AND edits.`bytes` > 0 
GROUP BY 
  `user`, 
  `article` 
ORDER BY 
  NULL

t1

A segunda etapa do cálculo é contabilizar o total de pontos obtidos a partir dos bytes contabilizados na etapa anterior. O propósito da fórmula é:

• Agrupar os resultados da tabela anterior por editor;
• Somar o total de bytes inseridos por cada editor;
• Somar o total de edições realizada por cada editor;
• Dividir o total de bytes pelo número estabelecido de bytes necessários para receber 1 ponto (bytes_per_points), arredondando para baixo.

SELECT 
  edits_ruled.`user`, 
  SUM(edits_ruled.`bytes`) AS `sum`, 
  SUM(edits_ruled.`valid_edits`) AS `total edits`, 
  FLOOR(
    SUM(edits_ruled.`bytes`) / ${contest[ 'bytes_per_points' ]}
  ) AS `bytes points` 
FROM 
  ( /* edits_ruled */ ) AS edits_ruled 
GROUP BY 
  edits_ruled.`user` 
ORDER BY 
  NULL

distinct

Essa é a fórmula responsável pelo cálculo inicial das imagens. Seu propósito é:

• Coletar as edições que foram avaliadas e onde campo de avaliação esteja preenchido. Toda edição avaliada tem obrigatoriamente esse campo preenchido com 0 (não foi inserida imagem) ou outro valor inteiro maior que zero.
• Realizar o agrupamento das edições.
  • Caso o parâmetro pictures_mode seja 0 (padrão), será contabilizado apenas se foi inserido alguma imagem em alguma edição em cada artigo pelo editor. Para tal, será feito um agrupamento por cada trio editor-artigo-imagem distinto. Mesmo que o editor tenha inserido imagens por diferentes edições em um mesmo artigo, o valor sempre será 1 imagem para cada trio.
  • Caso o parâmetro pictures_mode seja diferente de 0, será feito um agrupamento pela chave primária da tabela (agrupamento fictício) para que todas as edições com imagens inseridas sejam retornadas.

SELECT 
  edits.`user`, 
  edits.`article`, 
  edits.`pictures`, 
  edits.`n` 
FROM 
  `edits` 
WHERE 
  edits.`pictures` IS NOT NULL 
GROUP BY 
  CASE WHEN ${contest[ 'pictures_mode' ]} = 0 THEN edits.`user` END, 
  CASE WHEN ${contest[ 'pictures_mode' ]} = 0 THEN edits.`article` END, 
  CASE WHEN ${contest[ 'pictures_mode' ]} = 0 THEN edits.`pictures` ELSE edits.`n` END

t2

Essa etapa é responsável por calcular os pontos obtidos pelas inserções de imagens a partir dos dados do cálculo anterior. O propósito da fórmula é:

• Agrupar os resultados da tabela anterior por editor;
• Somar o total de imagens inseridas por cada editor;
• Dividir o total de imagens pelo número estabelecido de bytes necessários para receber 1 ponto (pictures_per_points), arredondando para baixo.

SELECT 
  `distinct`.`user`, 
  `distinct`.`article`, 
  SUM(`distinct`.`pictures`) AS `total pictures`, 
  CASE WHEN ${contest[ 'pictures_per_points' ]} = 0 THEN 0 ELSE FLOOR(
    SUM(`distinct`.`pictures`) / ${contest[ 'pictures_per_points' ]}
  ) END AS `pictures points` 
FROM 
  ( /* DISTINCT */ ) AS `distinct` 
GROUP BY 
  `distinct`.`user` 
ORDER BY 
  NULL

points

Etapa simples, responsável por somar os pontos de cada editor da tabela t1 (por bytes) e da tabela t2 (por imagens)

SELECT 
  t1.`user`, 
  t1.`sum`, 
  t1.`total edits`, 
  t1.`bytes points`, 
  t2.`total pictures`, 
  t2.`pictures points`, 
  ( t1.`bytes points` + t2.`pictures points` ) AS `total points` 
FROM 
  ( /* T1 */ ) AS t1 
LEFT JOIN 
  ( /* T2 */ ) AS t2 
ON 
  t1.`user` = t2.`user`

user_table

Etapa também simples, apenas coleta os nomes dos editores na tabela de edições e faz uma junção inner com a tabela de usuários inscritos para fazer uma interseção entre ambas. Sua função é:

• Desconsiderar as edições de editores que não se inscreveram no concurso e;
• Desconsiderar os editores inscritos que não realizaram nenhuma edição válida no âmbito do concurso.

SELECT 
  DISTINCT `edits`.`user` 
FROM 
  `edits` 
INNER JOIN 
  `users` 
ON 
  `users`.`user` = `edits`.`user`

Fórmula geral

Essa última fórmula gera a tabela final com os dados de cada participante utilizando user_table como base. Seu propósito é:

• Unir a tabela de usuários com a tabela dos pontos;
• Atribuir "zero" aos campos incompletos (IFNULL). Isso ocorre quando editores estão inscritos e editaram algum artigo no âmbito do concurso, porém não tiveram suas edições validadas. *Realiza o ordenamento da tabela pelo total de pontos, em seguida pelo total de bytes e, por fim, por ordem alfabética.

SELECT 
  `user_table`.`user`, 
  IFNULL(`points`.`sum`, 0) AS `sum`, 
  IFNULL(`points`.`total edits`, 0) AS `total edits`, 
  IFNULL(`points`.`bytes points`, 0) AS `bytes points`, 
  IFNULL(`points`.`total pictures`, 0) AS `total pictures`, 
  IFNULL(`points`.`pictures points`, 0) AS `pictures points`, 
  IFNULL(`points`.`total points`, 0) AS `total points` 
FROM 
  ( /* USER_TABLE */ ) AS `user_table` 
LEFT JOIN 
  ( /* POINTS */ ) AS `points` 
ON 
  `user_table`.`user` = `points`.`user` 
ORDER BY 
  `points`.`total points` DESC, 
  `points`.`sum` DESC, 
  `user_table`.`user` ASC