Início / dba / Perguntas / 86274
Accepted
stUrb
Asked: 2014-12-17 01:11:16 +0800 CST

Agrupar por intervalo de anos

  • 772

Eu tenho uma grande tabela (~ 9 milhões de linhas) e quero agrupar as linhas em um campo contendo o ano. Até aqui é bem fácil:

// greatly simplified:
SELECT count(*), year FROM dataset GROUP BY year ORDER BY 2;

Definimos alguns períodos de tempo irregulares abrangendo vários anos:

<1945, 1946-1964, 1965-1974, 1975-1991, 1992-2005 and >2005

Não tenho ideia de como agrupar esses resultados na cláusula group by. Eu poderia fazer subconsultas para cada período de tempo. 

SELECT
  ( SELECT count(*) FROM dataset WHERE year <= 1945 AND ...... ) AS pre1945,
  ( ....) AS period2,
  ....
FROM dataset

Mas isso não parece certo e estou me perguntando se seria possível deixar o Postgresql fazer isso. Especialmente porque a consulta é uma forte simplificação da consulta real: ela possui várias condições, entre elas uma cláusula ST_within abrangendo quatro tabelas. Portanto, escolher a abordagem de subconsulta resulta em uma consulta inchada.

Existe uma maneira melhor de criar esse resultado?

postgresql

2 respostas

  1. Best Answer

    Use a contagem condicional:

    select count(case when year <= 1945 then 1 end) as pre1945,
       count(case when year between 1946 and 1964 then 1 end) as period2,
       count(case when year between 1965 and 1974 then 1 end) as period3,
       ...
from ...
where ...;

    Isso funciona porque count()ignora valores nulos e a caseinstrução retorna nullvalores for fora do intervalo para o qual testa (an else nullestá implícito).

    Com a próxima versão 9.4, você pode reescrever isso como

    select count(*) filter (where year <= 1945) as pre1945,
       count(*) filter (where year between 1946 and 1964) as period2,
       count(*) filter (where year between 1965 and 1974) as period3,
       ...
from ...
where ...;
    • 8

  2. Se você deseja o resultado como linhas em vez de colunas como na resposta de @a_horse, crie os intervalos de anos em um CTE e junte a tabela a ele

    with years(year_range) as ( values
    (int4range(1900, 1945, '[]')),
    (int4range(1946, 1964, '[]')),
    (int4range(1965, 1974, '[]')),
    (int4range(1975, 1991, '[]')),
    (int4range(1992, 2005, '[]')),
    (int4range(2005, 2014, '[]'))
)
select year_range, count(*)
from
    dataset d
    left join
    years y on d.year <@ y.year_range
group by 1 
order by 1

    http://www.postgresql.org/docs/current/static/rangetypes.html

    • 8

relate perguntas