20

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:

Example of rows that would ideally be included in a "set" but are not because my set is static by month.

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:

  • 1
    By 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 any entity_id ? Either way, please provide your table definition, Postgres version, some sample data and the expected result for the sample. – Erwin Brandstetter Jul 20 '15 at 7:18
  • In theory, I meant any day, but in practice there is no need to consider days where there are no transactions. I've posted the sample data and table definition. – tufelkinder Jul 20 '15 at 14:50
  • So you want to accumulate rows of the same 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 no UNIQUE or PK constraint, but constraints seem to be missing ... – Erwin Brandstetter Jul 20 '15 at 15:44
  • The only constraint is on id primary key. There can be multiple transactions per entity per day. – tufelkinder Jul 20 '15 at 15:46
  • About data distribution: are there entries (per entity_id) for most days? – Erwin Brandstetter Jul 20 '15 at 15:47
24

The query you have

You could simplify your query using a WINDOW clause, but that's just shortening the syntax, not changing the query plan.

SELECT id, trans_ref_no, amount, trans_date, entity_id
     , SUM(amount) OVER w AS trans_total
     , COUNT(*)    OVER w AS trans_count
FROM   transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date)
             ORDER BY trans_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
  • Also using the slightly faster count(*), since id is certainly defined NOT NULL?
  • And you don't need to ORDER BY entity_id since you already PARTITION BY entity_id

You can simplify further, though:
Don't add ORDER BY to the window definition at all, it's not relevant to your query. Then you don't need to define a custom window frame, either:

SELECT id, trans_ref_no, amount, trans_date, entity_id
     , SUM(amount) OVER w AS trans_total
     , COUNT(*)    OVER w AS trans_count
FROM   transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date);

Simpler, faster, but still just a better version of what you have, with static months.

The query you might want

... is not clearly defined, so I'll build on these assumptions:

Count transactions and amount for every 30-day period within the first and last transaction of any entity_id. Exclude leading and trailing periods without activity, but include all possible 30-day periods within those outer bounds.

SELECT entity_id, trans_date
     , COALESCE(sum(daily_amount) OVER w, 0) AS trans_total
     , COALESCE(sum(daily_count)  OVER w, 0) AS trans_count
FROM  (
   SELECT entity_id
        , generate_series (min(trans_date)::timestamp
                         , GREATEST(min(trans_date), max(trans_date) - 29)::timestamp
                         , interval '1 day')::date AS trans_date
   FROM   transactiondb 
   GROUP  BY 1
   ) x
LEFT JOIN (
   SELECT entity_id, trans_date
        , sum(amount) AS daily_amount, count(*) AS daily_count
   FROM   transactiondb
   GROUP  BY 1, 2
   ) t USING (entity_id, trans_date)
WINDOW w AS (PARTITION BY entity_id ORDER BY trans_date
             ROWS BETWEEN CURRENT ROW AND 29 FOLLOWING);

This lists all 30-day periods for each entity_id with your aggregates and with trans_date being the first day (incl.) of the period. To get values for each individual row join to the base table once more ...

The basic difficulty is the same as discussed here:

The frame definition of a window cannot depend on values of the current row.

And rather call generate_series() with timestamp input:

The query you actually want

After question update and discussion:
Accumulate rows of the same entity_id in a 30-day window starting at each actual transaction.

Since your data is distributed sparsely, it should be more efficient to run a self-join with a range condition, all the more since Postgres 9.1 does not have LATERAL joins, yet:

SELECT t0.id, t0.amount, t0.trans_date, t0.entity_id
     , sum(t1.amount) AS trans_total, count(*) AS trans_count
FROM   transactiondb t0
JOIN   transactiondb t1 USING (entity_id)
WHERE  t1.trans_date >= t0.trans_date
AND    t1.trans_date <  t0.trans_date + 30  -- exclude upper bound
-- AND    t0.entity_id = 114284  -- or pick a single entity ...
GROUP  BY t0.id  -- is PK!
ORDER  BY t0.trans_date, t0.id

SQL Fiddle.

A rolling window could only make sense (with respect to performance) with data for most days.

This does not aggregate duplicates on (trans_date, entity_id) per day, but all rows of the same day are always included in the 30-day window.

For a big table, a covering index like this could help quite a bit:

CREATE INDEX transactiondb_foo_idx
ON transactiondb (entity_id, trans_date, amount);

The last column amount is only useful if you get index-only scans out of it. Else drop it.

But it's not going to be used while you select the whole table anyway. It would support queries for a small subset.

  • This looks really good, testing it on the data now, and trying to understand everything your query is actually doing... – tufelkinder Jul 20 '15 at 15:06
  • @tufelkinder: Added a solution for the updated question. – Erwin Brandstetter Jul 20 '15 at 16:26
  • Reviewing it now. I'm intrigued that it runs in SQL Fiddle... When I try to run it directly on my transactiondb, it errors out with column "t0.amount" must appear in the GROUP BY clause... – tufelkinder Jul 20 '15 at 16:28
  • @tufelkinder: I cut the test case down to 100 rows. sqlfiddle limits the size of the test data. Jake (the author) reduced the limits limit a couple of months ago so the site is less easily stalled. – Erwin Brandstetter Jul 20 '15 at 16:31
  • 1
    Sorry for the delay, needed to test it on the full database. Your answer was superbly in-depth and educational, as always. Thank you! – tufelkinder Jul 21 '15 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.