Início / dba / Perguntas / 31794
Accepted
Faheem Mitha
Asked: 2013-01-12 10:49:18 +0800 CST

classificando grupos de linhas relacionadas por valores médios enquanto mantém os grupos juntos

  • 772

Estou usando o PostgreSQL 8.4, mas gostaria de uma solução SQL padrão, se possível. Considere a tabela a seguir.

corrmodel=# SELECT * from data limit 1;
   id    | datasubgroup_id | datafile_id |           sequence           | index | seqindex | margstat | pvalue 
---------+-----------------+-------------+------------------------------+-------+----------+----------+--------
 1033473 |               3 |          10 | GGTGACCCCAAGCTCAGGGCTGACCTGC | 19042 |          |  70.7634 |      0

Desejo retornar a consulta que possui as seguintes propriedades.

  1. Todas as linhas com o mesmo datafile_ide indexsão agrupadas.
  2. Os grupos são classificados primeiro por média pvaluedescendente, depois por average
    margstat, onde as médias estão em cada grupo.

As duas tabelas nas quais estou fazendo esta consulta têm 2,2 milhões e 3,1 milhões de linhas, então gostaria de algo razoavelmente eficiente. Cada grupo consiste em 5 linhas. Esta solução de @Lamak funciona, mas tive alguns problemas para entender e acho que uma solução usando funções de janela pode ser algo que eu realmente possa entender. O seguinte está próximo, mas não correto, pois o grupo não é preservado neste caso.

SELECT datafile_id, 
       index, 
       pvalue, 
       margstat, 
       Avg(pvalue) 
         OVER ( 
           partition BY datafile_id, index) AS avg_pval, 
       Avg(margstat) 
         OVER ( 
           partition BY datafile_id, index) AS avg_margstat 
FROM   data 
ORDER  BY avg_pval DESC, 
          avg_margstat;

Aqui estão as primeiras 10 linhas do resultado da consulta para um dos meus conjuntos de dados. Eu gostaria de algo assim, mas correto.

datafile_id | index | pvalue | margstat | avg_pval | avg_margstat 
-------------+-------+--------+----------+----------+--------------
          30 |   781 |      1 |  13.1568 |    0.998 |     12.52546
          30 |   781 |      1 |  12.3585 |    0.998 |     12.52546
          30 |   781 |      1 |  12.3495 |    0.998 |     12.52546
          30 |   781 |   0.99 |  11.9554 |    0.998 |     12.52546
          30 |   781 |      1 |  12.8071 |    0.998 |     12.52546
          23 |  1428 |   0.99 |  12.1711 |    0.998 |      12.6777
          23 |  1428 |      1 |  12.6451 |    0.998 |      12.6777
          23 |  1428 |      1 |  12.8814 |    0.998 |      12.6777
          23 |  1428 |      1 |  12.8969 |    0.998 |      12.6777
          23 |  1428 |      1 |   12.794 |    0.998 |      12.6777
postgresql sorting

1 respostas

  1. Best Answer

    Como @ypercube apontou nos comentários, minha consulta está bem próxima da resposta correta. A classificação por avg_pval DESC, avg_margstatestá realmente próxima da classificação correta, apenas incorreta se a (avg_pval, margstat)tupla tiver empates. Assim, pode-se classificar novamente, para um fixo (avg_pval, margstat), no datafile_id, indexqual reunirá os grupos novamente. Finalmente, pode-se classificar opcionalmente dentro dos grupos, por pvalue DESC, margstat, Juntando tudo, obtém-se

    SELECT datafile_id, 
       index, 
       pvalue, 
       margstat, 
       Avg(pvalue) 
         OVER ( 
           partition BY datafile_id, index) AS avg_pval, 
       Avg(margstat) 
         OVER ( 
           partition BY datafile_id, index) AS avg_margstat 
FROM   data 
ORDER  BY avg_pval DESC, 
          avg_margstat, 
          datafile_id,
          index,
          pvalue DESC, 
          margstat;
    • 1

relate perguntas