In a database of transactions spanning 1,000s of entities over 18 months, I would like to run a query to group every possible 30-day period by entity_id
with a SUM of their transaction amounts and COUNT of their transactions in that 30-day period, and return the data in a way that I can then query against. After a lot of testing, this code accomplishes much of what I want:
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;
And I will use in a larger query structured something like:
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;
The case that this query doesn't cover is when the transaction counts would span multiple months, but still be within 30 days of each other. Is this type of query possible with Postgres? If so, I welcome any input. Many of the other topics discuss "running" aggregates, not rolling.
Update
The CREATE TABLE
script:
CREATE TABLE transactiondb (
id integer NOT NULL,
trans_ref_no character varying(255),
amount numeric(18,2),
trans_date date,
entity_id integer
);
Sample data can be found here. I'm running PostgreSQL 9.1.16.
Ideal output would include SUM(amount)
and COUNT()
of all transactions over a rolling 30-day period. See this image, for example:
The green date highlighting indicates what's being included by my query. The yellow row highlighting indicates records what I would like to become part of the set.
Previous reading:
every possible 30-day period by entity_id
you mean the period can start any day, so 365 possible periods in a (non-leap) year? Or do you only want to consider days with an actual transaction as start of a period individually for anyentity_id
? Either way, please provide your table definition, Postgres version, some sample data and the expected result for the sample.entity_id
in a 30-day window starting at each actual transaction. Can there be multiple transactions for the same(trans_date, entity_id)
or is that combination defined unique? Your table definition has noUNIQUE
or PK constraint, but constraints seem to be missing ...id
primary key. There can be multiple transactions per entity per day.