Início / dba / Perguntas / 66553
Accepted
user39509
Asked: 2014-06-05 06:21:29 +0800 CST

Conta onde duas ou mais colunas seguidas estão acima de um determinado valor [basquetebol, duplo duplo, triplo duplo]

  • 772

Eu jogo um jogo de basquete que permite gerar suas estatísticas como um arquivo de banco de dados, para que se possa calcular as estatísticas que não são implementadas no jogo. Até agora não tive problemas para calcular as estatísticas que queria, mas agora me deparei com um problema: contar o número de duplos duplos e/ou triplos duplos que um jogador fez ao longo da temporada a partir de suas estatísticas de jogo.

A definição de duplo duplo e triplo duplo é a seguinte:

Duplo duplo:

Um double-double é definido como um desempenho no qual um jogador acumula um número total de dois dígitos em duas das cinco categorias estatísticas - pontos, rebotes, assistências, roubos de bola e tocos - em um jogo.

Triplo duplo:

Um triplo-duplo é definido como um desempenho no qual um jogador acumula um total de dois dígitos em três das cinco categorias estatísticas - pontos, rebotes, assistências, roubos de bola e tocos - em um jogo.

Quádruplo-duplo (adicionado para esclarecimento)

Um quádruplo-duplo é definido como um desempenho no qual um jogador acumula um total de dois dígitos em quatro das cinco categorias estatísticas - pontos, rebotes, assistências, roubos de bola e tocos - em um jogo.

A tabela "PlayerGameStats" armazena estatísticas para cada jogo que um jogador joga e tem a seguinte aparência:

CREATE TABLE PlayerGameStats AS SELECT * FROM ( VALUES
  ( 1, 1,  1, 'Nuggets',    'Cavaliers',  6,  8,  2, 2,  0 ),
  ( 2, 1,  2, 'Nuggets',     'Clippers', 15,  7,  0, 1,  3 ),
  ( 3, 1,  6, 'Nuggets', 'Trailblazers', 11, 11,  1, 2,  1 ),
  ( 4, 1, 10, 'Nuggets',    'Mavericks',  8, 10,  2, 2, 12 ),
  ( 5, 1, 11, 'Nuggets',       'Knicks', 23, 12,  1, 0,  0 ),
  ( 6, 1, 12, 'Nuggets',         'Jazz',  8,  8, 11, 1,  0 ),
  ( 7, 1, 13, 'Nuggets',         'Suns',  7, 11,  2, 2,  1 ),
  ( 8, 1, 14, 'Nuggets',        'Kings', 10, 15,  0, 3,  1 ),
  ( 9, 1, 15, 'Nuggets',        'Kings',  9,  7,  5, 0,  4 ),
  (10, 1, 17, 'Nuggets',      'Thunder', 13, 10, 10, 1,  0 )
) AS t(id,player_id,seasonday,team,opponent,points,rebounds,assists,steals,blocks);

A saída que eu quero alcançar é assim:

| player_id |    team | doubleDoubles | tripleDoubles |
|-----------|---------|---------------|---------------|
|         1 | Nuggets |             4 |             1 |

A única solução que encontrei até agora é tão horrível que me dá vontade de vomitar... ;o)... Fica assim:

SELECT 
  player_id,
  team,
  SUM(CASE WHEN(points >= 10 AND rebounds >= 10) OR
               (points >= 10 AND assists  >= 10) OR
               (points >= 10 AND steals   >= 10) 
                THEN 1 
                ELSE 0 
      END) AS doubleDoubles
FROM PlayerGameStats
GROUP BY player_id

... e agora você provavelmente também está vomitando (ou rindo muito) depois de ler isso. Eu nem mesmo escrevi tudo o que seria necessário para obter todas as combinações duplo-duplo e omiti a declaração de caso para os triplos-duplos porque é ainda mais ridículo.

Existe uma maneira melhor de fazer isso? Seja com a estrutura de tabela que tenho ou com uma nova estrutura de tabela (eu poderia escrever um script para converter a tabela).

Posso usar MySQL 5.5 ou PostgreSQL 9.2.

Aqui está um link para SqlFiddle com dados de exemplo e minha terrível solução que postei acima: http://sqlfiddle.com/#!2/af6101/3

Observe que não estou realmente interessado em quádruplos-duplos (veja acima), pois eles não ocorrem no jogo que eu jogo até onde eu sei, mas seria uma vantagem se a consulta fosse facilmente expansível sem muito reescrever a conta para quádruplos-duplos.

mysql postgresql

6 respostas

  1. Best Answer

    Não sei se esta é a melhor forma. Primeiro fiz uma seleção para descobrir se uma estatística é de dois dígitos e atribuí a ela um 1, se for. Somou tudo isso para descobrir o número total de dois dígitos por jogo. A partir daí é só somar todos os duplos e triplos. Parece funcionar 

    select a.player_id, 
a.team, 
sum(case when a.doubles = 2 then 1 else 0 end) as doubleDoubles, 
sum(case when a.doubles = 3 then 1 else 0 end) as tripleDoubles
from
(select *, 
(case when points > 9 then 1 else 0 end) +
(case when rebounds > 9 then 1 else 0 end) +
(case when assists > 9 then 1 else 0 end) +
(case when steals > 9 then 1 else 0 end) +
(case when blocks > 9 then 1 else 0  end) as Doubles
from PlayerGameStats) a
group by a.player_id, a.team
    • 9

  2. Tente isso (funcionou para mim no MySQL 5.5):

    SELECT 
  player_id,
  team,
  SUM(
    (   (points   >= 10)
      + (rebounds >= 10)
      + (assists  >= 10)
      + (steals   >= 10)
      + (blocks   >= 10) 
    ) = 2
  ) double_doubles,
  SUM(
    (   (points   >= 10)
      + (rebounds >= 10)
      + (assists  >= 10)
      + (steals   >= 10)
      + (blocks   >= 10) 
    ) = 3
  ) triple_doubles
FROM PlayerGameStats
GROUP BY player_id, team

    Ou ainda mais curto, arrancando descaradamente o código de JChao de sua resposta, mas retirando as CASEdeclarações desnecessárias, já que a expr booleana é avaliada como {1,0} quando {True,False}:

    select a.player_id, 
a.team, 
sum(a.doubles = 2) as doubleDoubles, 
sum(a.doubles = 3) as tripleDoubles
from
(select *, 
(points > 9) +
(rebounds > 9) +
(assists > 9) +
(steals > 9) +
(blocks > 9) as Doubles
from PlayerGameStats) a
group by a.player_id, a.team

    Com base nos comentários de que o código acima não será executado no PostgreSQL, pois não gosta de boolean + boolean. Eu ainda não gosto CASE. Aqui está uma saída no PostgreSQL (9.3), convertendo para int:

    select a.player_id, 
a.team, 
sum((a.doubles = 2)::int) as doubleDoubles, 
sum((a.doubles = 3)::int) as tripleDoubles
from
(select *, 
(points > 9)::int +
(rebounds > 9)::int +
(assists > 9)::int +
(steals > 9)::int +
(blocks > 9)::int as Doubles
from PlayerGameStats) a
group by a.player_id, a.team
    • 6

  3. Aqui está outra abordagem do problema.

    A meu ver, você está essencialmente trabalhando com dados dinâmicos para o problema atual, então a primeira coisa a fazer é desativá-los. Infelizmente o PostgreSQL não fornece boas ferramentas para fazer isso, então sem entrar na geração dinâmica de SQL em PL/PgSQL, podemos pelo menos fazer:

    SELECT player_id, seasonday, 'points' AS scoretype, points AS score FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'rebounds' AS scoretype, rebounds FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'assists' AS scoretype, assists FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'steals' AS scoretype, steals FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'blocks' AS scoretype, blocks FROM playergamestats

    Isso coloca os dados em uma forma mais maleável, embora certamente não seja bonita. Aqui eu assumo que (player_id, seasonday) é suficiente para identificar exclusivamente os jogadores, ou seja, o ID do jogador é único entre as equipes. Se não for, você precisará incluir outras informações suficientes para fornecer uma chave exclusiva.

    Com esses dados não dinâmicos, agora é possível filtrá-los e agregá-los de maneiras úteis, como:

    SELECT
  player_id,
  count(CASE WHEN doubles = 2 THEN 1 END) AS doubledoubles,
  count(CASE WHEN doubles = 3 THEN 1 END) AS tripledoubles
FROM (
    SELECT
      player_id, seasonday, count(*) AS doubles
    FROM
    (
        SELECT player_id, seasonday, 'points' AS scoretype, points AS score FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'rebounds' AS scoretype, rebounds FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'assists' AS scoretype, assists FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'steals' AS scoretype, steals FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'blocks' AS scoretype, blocks FROM playergamestats
    ) stats
    WHERE score >= 10
    GROUP BY player_id, seasonday
) doublestats
GROUP BY player_id;

    Isso está longe de ser bonito e provavelmente não é tão rápido. No entanto, é sustentável, exigindo alterações mínimas para lidar com novos tipos de estatísticas, novas colunas, etc.

    Portanto, é mais um "ei, você pensou nisso" do que uma sugestão séria. O objetivo era modelar o SQL para corresponder à declaração do problema o mais diretamente possível, em vez de torná-lo rápido.

    Isso ficou muito mais fácil com o uso de inserções de vários valores e citações ANSI em seu SQL orientado ao MySQL. Obrigada; é bom não ver backticks pela primeira vez. Tudo o que tive que mudar foi a geração da chave sintética.

    • 5

  4. O que @Joshua exibe para MySQL também funciona no Postgres. Booleanos valores podem ser convertidos integere somados. O elenco precisa ser explícito, no entanto. Faz um código muito curto:

    SELECT player_id, team
     , count(doubles = 2 OR NULL) AS doubledoubles
     , count(doubles = 3 OR NULL) AS tripledoubles
FROM  (
   SELECT player_id, team,
          (points   > 9)::int +
          (rebounds > 9)::int +
          (assists  > 9)::int +
          (steals   > 9)::int +
          (blocks   > 9)::int AS doubles
   FROM playergamestats
   ) a
GROUP  BY 1, 2;

    No entanto, CASE- embora mais detalhado - é tipicamente um pouco mais rápido. E mais portátil, se isso importa:

    SELECT player_id, team
     , count(doubles = 2 OR NULL) AS doubledoubles
     , count(doubles = 3 OR NULL) AS tripledoubles
FROM  (
   SELECT player_id, team,
          CASE WHEN points   > 9 THEN 1 ELSE 0 END +
          CASE WHEN rebounds > 9 THEN 1 ELSE 0 END +
          CASE WHEN assists  > 9 THEN 1 ELSE 0 END +
          CASE WHEN steals   > 9 THEN 1 ELSE 0 END +
          CASE WHEN blocks   > 9 THEN 1 ELSE 0 END AS doubles
   FROM playergamestats
   ) a
GROUP  BY 1, 2;

    SQL Fiddle.

    • 5

  5. Usando divisão inteira e conversão binária

    SELECT player_id
     , team
     , SUM(CASE WHEN Doubles = 2 THEN 1 ELSE 0 END) DoubleDouble
     , SUM(CASE WHEN Doubles = 3 THEN 1 ELSE 0 END) TripleDouble
FROM   (SELECT player_id
             , team
             , (BINARY (points DIV 10) > 0)
             + (BINARY (rebounds DIV 10) > 0)
             + (BINARY (assists DIV 10) > 0)
             + (BINARY (steals DIV 10) > 0)
             + (BINARY (blocks DIV 10) > 0)
             AS Doubles
        FROM   PlayerGameStats) d
GROUP BY player_id, team
    • 1

  6. Só quero deixar aqui uma variação da versão do @Craig Ringers que encontrei por acaso, talvez seja útil para alguém no futuro.

    Em vez de vários UNION ALL, ele usa unnest e array. Fonte de inspiração: https://stackoverflow.com/questions/1128737/unpivot-and-postgresql

    
SELECT
  player_id,
  count(CASE WHEN doubles = 2 THEN 1 END) AS doubledoubles,
  count(CASE WHEN doubles = 3 THEN 1 END) AS tripledoubles
FROM (
    SELECT
      player_id, seasonday, count(*) AS doubles
    FROM
    (
        SELECT 
          player_id, 
          seasonday,
          unnest(array['Points', 'Rebounds', 'Assists', 'Steals', 'Blocks']) AS scoretype,
          unnest(array[Points, Rebounds, Assists, Steals, Blocks]) AS score
        FROM PlayerGameStats
    ) stats
    WHERE score >= 10
    GROUP BY player_id, seasonday
) doublestats
GROUP BY player_id;

    SQL Fiddle: http://sqlfiddle.com/#!12/4980b/3

    • 1

relate perguntas