Em um banco de dados de transações abrangendo milhares de entidades ao longo de 18 meses, gostaria de executar uma consulta para agrupar todos os períodos possíveis de 30 dias entity_idcom uma SOMA de seus valores de transação e COUNT de suas transações nesse período de 30 dias e retornar os dados de uma maneira que eu possa consultar. Depois de muitos testes, este código realiza muito do que eu quero:
SELECT id, trans_ref_no, amount, trans_date, entity_id,
SUM(amount) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_total,
COUNT(id) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_count
FROM transactiondb;
E vou usar em uma query maior estruturada algo como:
SELECT * FROM (
SELECT id, trans_ref_no, amount, trans_date, entity_id,
SUM(amount) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_total,
COUNT(id) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_count
FROM transactiondb ) q
WHERE trans_count >= 4
AND trans_total >= 50000;
O caso que essa consulta não cobre é quando as contagens de transações abrangem vários meses, mas ainda estão dentro de 30 dias uma da outra. Esse tipo de consulta é possível com o Postgres? Se sim, aceito qualquer contribuição. Muitos dos outros tópicos discutem agregações " executando ", não rolando .
Atualizar
O CREATE TABLEroteiro:
CREATE TABLE transactiondb (
id integer NOT NULL,
trans_ref_no character varying(255),
amount numeric(18,2),
trans_date date,
entity_id integer
);
Dados de exemplo podem ser encontrados aqui . Estou executando o PostgreSQL 9.1.16.
A saída ideal incluiria todas SUM(amount)as COUNT()transações em um período contínuo de 30 dias. Veja esta imagem, por exemplo:
O destaque verde da data indica o que está sendo incluído na minha consulta. A linha amarela destacada indica o que eu gostaria que fizesse parte do conjunto.
Leitura anterior: