Início / dba / Perguntas / 84779
Accepted
chaos
Asked: 2014-12-11 01:26:25 +0800 CST

Contando ocorrências repetidas para um grupo de campos

  • 772

Estou tentando contar o valor repetido contínuo de um campo para obter o máximo de ocorrências repetidas para cada pessoa na tabela. por exemplo:

Time         person        Result
08:12:36     Dave          Heads
08:12:37     Dave          Heads
08:12:39     Sue           Tails
08:12 41     Dave          Tails
08:12:42     Dave          Heads
08:12:44     Dave          Heads
08:12:45     Sue           Heads
08:12:46     Sue           Heads
08:12:47     Sue           Tails
08:12:48     Dave          Heads
08:12:49     Dave          Heads
08:12:50     Dave          Tails
08:12:51     John          Heads

O resultado deve ficar assim

Dave     4
Sue      2
John     1

A contagem para Dave seria 4 entre 08:12:42 e 08:12:49, 4 opções de cara ocorrem para Dave antes de Dave receber uma opção de coroa.

postgresql postgresql-9.3

1 respostas

  1. Best Answer
    SELECT person, max(ct) AS max_ct
FROM  (
   SELECT person, count(*) AS ct
   FROM  (
      SELECT person, result
           , row_number() OVER (PARTITION BY person ORDER BY time)
           - row_number() OVER (PARTITION BY person, result ORDER BY time) AS grp
      FROM   tablex
      ) sub1
   GROUP BY person, result, grp
   ) sub2
GROUP  BY person
ORDER  BY max(ct) DESC, person;

    SQL Fiddle.

    Resultado exatamente como desejado.

    Após formar grupos ( grp) de pares consecutivos (person, result), a contagem tem que ser por par e grupo (person, result, grp), não apenas por pessoa e grupo (person, grp). Pode (e vai) haver um grupo com o mesmo número de grupo por pessoa e resultado, esses devem ser contados separadamente .

    Além disso, não é eficiente usar CTEs aqui. No Postgres, o desempenho geralmente é superior para subconsultas, pois os CTEs são cercas de otimização que sempre materializam o resultado. Use CTEs somente onde forem realmente necessários.

    • 2

relate perguntas