Início / dba / Perguntas / 337729
Accepted
anton
Asked: 2024-03-14 23:11:18 +0800 CST

Último valor da célula em uma consulta agrupada por

  • 772

Tenho uma tabela relacionada a resultados de jogos com mais de 300 mil registros, onde cada linha guarda o ataque do jogador. Este é um exemplo de tabela simplificado. Eu mantenho um ID de ataque, data, quando o ataque foi feito, ID exclusivo do jogador, nome do jogador (alterável por jogador) e resultado (valor bool, vitória ou derrota).
ataque_id data ID_do_jogador nome do jogador resultado
1 09/03/2024 00:00:00 1 gato 1
2 10/03/2024 00:00:00 1 panda 1
3 11/03/2024 00:00:00 2 cachorro 0
4 12/03/2024 00:00:00 3 lobo 1

Quero mostrar os 10 melhores jogadores de ataque e minha consulta é assim:

SELECT
player_id as id,
player_name as name,
count(attack_id) as attacks, 
sum(result) as victory,
( count(attack_id) - sum(result) ) as defeat,
( sum(result) - ( count(attack_id) - sum(result) ) ) as difference
FROM `my_table_name` 
GROUP BY player_id 
ORDER BY difference DESC
LIMIT 10

A consulta calcula as vitórias, derrotas dos jogadores e a diferença entre vitórias e derrotas. O problema aqui está relacionado ao nome do jogador. O jogador pode alterar um nome após alguns ataques, mas esta consulta retorna o primeiro player_name (relacionado ao player_id), não o último (atual).

Resultado desta consulta (de acordo com meu exemplo, em vez de gato, deveria ser panda, pois foi o nome do ataque do último jogador):

eu ia nome ataques vitória derrota diferença
1 gato 2 2 0 2
3 lobo 1 1 0 1
2 cachorro 1 0 1 -1

Como posso resolver esse problema?

mysql

3 respostas

  1. Seu design atual tem redundância e você deve separar o nome da seguinte forma:

    Tabela de ataque:

    • ataque_id
    • data
    • ID_do_jogador
    • resultado

    Mesa do Jogador

    • ID_do_jogador
    • nome do jogador

    Desta forma você tem um único ponto de entrada para o nome do jogador.

    • 0
  2. Best Answer

    No MySQL 8 você poderia usar a função de janela ROW_NUMBEr para obter o sobrenome de um user_id

    CREATE TABLE my_table_name (
  `attack_id` INTEGER,
  `date` datetime,
  `player_id` INTEGER,
  `player_name` VARCHAR(5),
  `result` INTEGER
);

INSERT INTO my_table_name
  (`attack_id`, `date`, `player_id`, `player_name`, `result`)
VALUES
  ('1', '2024-03-09 00:00:00', '1', 'cat', '1'),
  ('2', '2024-03-10 00:00:00', '1', 'panda', '1'),
  ('3', '2024-03-11 00:00:00', '2', 'dog', '0'),
  ('4', '2024-03-12 00:00:00', '3', 'wolf', '1');

    WITH CTE AS (SELECT
    player_id, player_name, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY `date`) rn
FROM  my_table_name)
SELECT
    m.player_id as id,
    MAX(m.player_name) as name,
    count(attack_id) as attacks, 
    sum(result) as victory,
    ( count(attack_id) - sum(result) ) as defeat,
    ( sum(result) - ( count(attack_id) - sum(result) ) ) as difference
FROM `my_table_name` m JOIN CTE c ON m.player_id = c.player_id AND c.rn = 1
GROUP BY m.player_id 
ORDER BY difference DESC
LIMIT 10
    eu ia nome ataques vitória derrota diferença
    1 panda 2 2 0 2
    3 lobo 1 1 0 1
    2 cachorro 1 0 1 -1

    violino

    • 0

  3. Dividir a mesa como sugeriu Peyman é uma boa ideia. Para adicionar a isso, você pode adicionar uma coluna à tabela do jogador chamada "is_current_name" e verificar isso. Você também pode adicionar colunas de horário de início e término se precisar consultar o nome de um jogador em um determinado momento.

    • 0

relate perguntas