Início / dba / Perguntas / 114407
Accepted
Fake Name
Asked: 2015-09-08 14:48:48 +0800 CST

Como implementar adequadamente a filtragem composta de n maior

  • 772

Sim, mais perguntas do tipo n-por-grupo.

Dada a uma tabela releasescom as seguintes colunas:

 id         | primary key                 | 
 volume     | double precision            |
 chapter    | double precision            |
 series     | integer-foreign-key         |
 include    | boolean                     | not null

Quero selecionar o máximo composto de volume e, em seguida, capítulo para um conjunto de séries.

No momento, se eu consultar por séries distintas, posso fazer isso facilmente da seguinte maneira:

SELECT 
       releases.chapter AS releases_chapter,
       releases.include AS releases_include,
       releases.series AS releases_series
FROM releases
WHERE releases.series = 741
  AND releases.include = TRUE
ORDER BY releases.volume DESC NULLS LAST, releases.chapter DESC NULLS LAST LIMIT 1;

No entanto, se eu tiver um grande conjunto de series(e tenho), isso rapidamente se depara com problemas de eficiência em que estou emitindo mais de 100 consultas para gerar uma única página.

Eu gostaria de colocar tudo em uma única consulta, onde posso simplesmente dizer WHERE releases.series IN (1,2,3....), mas não descobri como convencer o Postgres a me deixar fazer isso.

A abordagem ingênua seria:

SELECT releases.volume AS releases_volume,
       releases.chapter AS releases_chapter,
       releases.series AS releases_series
FROM 
    releases
WHERE 
    releases.series IN (12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499, 
                        556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137, 
                        1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768, 
                        1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255, 
                        2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606, 
                        2634, 2636, 2695, 2696 )
  AND releases.include = TRUE
GROUP BY 
    releases_series
ORDER BY releases.volume DESC NULLS LAST, releases.chapter DESC NULLS LAST;

O que obviamente não funciona:

ERROR:  column "releases.volume" must appear in the 
        GROUP BY clause or be used in an aggregate function

Sem o GROUP BY, ele busca tudo, e com alguma filtragem processual simples até funcionaria, mas deve haver uma maneira "adequada" de fazer isso no SQL.

Seguindo os erros e adicionando agregados:

SELECT max(releases.volume) AS releases_volume,
       max(releases.chapter) AS releases_chapter,
       releases.series AS releases_series
FROM 
    releases
WHERE 
    releases.series IN (12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499, 
                        556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137, 
                        1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768, 
                        1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255, 
                        2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606, 
                        2634, 2636, 2695, 2696 )
  AND releases.include = TRUE
GROUP BY 
    releases_series;

Na maioria das vezes funciona, mas o problema é que os dois máximos não são coerentes. Se eu tiver duas linhas, uma em que volume:capítulo é 1:5 e 4:1, preciso retornar 4:1, mas os máximos independentes retornam 4:5.

Francamente, isso seria tão simples de implementar no código do meu aplicativo que devo estar perdendo algo óbvio aqui. Como posso implementar uma consulta que realmente satisfaça meus requisitos?

postgresql performance

1 respostas

  1. Best Answer

    A solução simples no Postgres é com DISTINCT ON:

    SELECT DISTINCT ON (r.series)
       r.volume  AS releases_volume
     , r.chapter AS releases_chapter
     , r.series  AS releases_series
FROM   releases r
WHERE  r.series IN (
    12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499
  , 556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137
  , 1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768
  , 1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255
  , 2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606
  , 2634, 2636, 2695, 2696)
AND    r.include
ORDER  BY r.series, r.volume DESC NULLS LAST, r.chapter DESC NULLS LAST;

    Detalhes:

    Dependendo da distribuição de dados, pode haver técnicas mais rápidas:

    Além disso, existem alternativas mais rápidas para listas longas do queIN () .

    Combinando uma matriz não aninhada com uma LATERALjunção:

    SELECT r.*
FROM   unnest('{12, 17, 44, 79, 88, 110, 129}'::int[]) t(i)  -- or many more items
     , LATERAL (
   SELECT volume  AS releases_volume
        , chapter AS releases_chapter
        , series  AS releases_series
   FROM   releases
   WHERE  series = t.i 
   AND    include
   ORDER  BY series, volume DESC NULLS LAST, chapter DESC NULLS LAST
   LIMIT  1
   ) r;

    Muitas vezes é mais rápido. Para melhor desempenho, você precisa de um índice de várias colunas correspondente, como:

    CREATE INDEX releases_series_volume_chapter_idx
ON releases(series, volume DESC NULLS LAST, chapter DESC NULLS LAST);

    Relacionado:

    E se houver mais do que algumas linhas onde includeis not true, enquanto você estiver interessado apenas nas linhas com include = true, considere um índice multicoluna parcial :

    CREATE INDEX releases_series_volume_chapter_idx
ON releases(series, volume DESC NULLS LAST, chapter DESC NULLS LAST)
WHERE include;
    • 3

relate perguntas