0

I have a history table containing a single entry when an entry in another table changes. I need to perform a query that produces the sum() or count() of the most recent entries for each time period.

Here's the relevant bits of my table structure:

CREATE TABLE opportunity_history (
  "id" BIGSERIAL PRIMARY KEY,
  "opportunity_id" TEXT NOT NULL,
  "employee_external_id" TEXT NOT NULL,
  "item_date" TIMESTAMP NOT NULL,
  "amount" NUMERIC(18,2) NOT NULL DEFAULT 0
);

So for example if I have a single opportunity created in January, and updated twice in February, I want to count it once in Jan, and only once in Feb.

The other similar queries I have (which don't involve history - just singular data points) work fine by joining to a generate_series() in a single query. I would love to be able to achieve something similar. Here's an example using generate_series:

SELECT Periods.day, sum(amount) as value
FROM (
        SELECT generate_series('2012-01-01', '2013-01-01', '1 month'::interval)::date AS day
    ) as Periods LEFT JOIN opportunity ON (
      employee_external_id='...'
AND   close_date >= Periods.day
AND   close_date <  Periods.day
)
GROUP BY 1
ORDER BY 1

However that doesn't work for opportunity_history, because if a single item is listed in the same month twice you get duplication.

I'm really stumped on this one. I've tried doing it via WITH RECURSIVE and nothing seems to unfold properly for me.

Edit:

Example data (skipping id columns and using dates instead of timestamps):

'foo', 'user1', 2013-01-01, 100
'bar', 'user1', 2013-01-02, 50
'foo', 'user1', 2013-01-12, 100
'bar', 'user1', 2013-01-13, 55
'foo', 'user1', 2013-01-23, 100
'foo', 'user1', 2013-02-04, 100
'foo', 'user1', 2013-02-15, 100
'bar', 'user1', 2013-03-01, 55

For sum I would want:

2013-01   155 (foo on 2013-01-23 and bar on 2013-01-13)
2013-02   100 (foo on 2013-02-15)
2013-03   55  (bar on 2013-03-01)

Or for count:

2013-01   2
2013-02   1
2013-03   1

Also note I'm happy to use "extended" SQL such as CTEs or WITH RECURSIVE or window functions if required. I'd rather avoid a loop in a Pg/plsql function if I can do it in a single query.

Matt Sergeant
  • 2,762
  • 16
  • 12
  • Can you provide some sample data and desired results? – Gordon Linoff Feb 04 '14 at 21:28
  • Your query uses `close_date` which is not in the `opportunity_history` table. – Turophile Feb 04 '14 at 21:41
  • The query is a sample of a similar query which works, against the table that history is generated against. I want to do something similar, but work where there may be multiple entries per time period but select the most recent one to use. – Matt Sergeant Feb 04 '14 at 21:52
  • Also it is not clear what you want. Do you want one total amount for each `opportunity_id` for each month where there is 1 or more opportunity_history rows? As @Gordon Linoff says, more details to clarify would help us to give you a correct answer. – Turophile Feb 04 '14 at 22:00
  • 1
    CTEs (including recursive) and window functions are not "Postgres specific extension". That is defined by the SQL standard and supported by all modern DBMS. –  Feb 04 '14 at 22:02
  • Thanks - I just thought it was worth mentioning just so people know I'm happy to dig into what might be considered "extended" SQL. – Matt Sergeant Feb 04 '14 at 22:06

2 Answers2

1
select item_month, count(*), sum(amount)
from (
   select opportunity_id, 
          item_date,
          amount,
          to_char(item_date, 'yyyy-mm') as item_month,
          row_number() over (partition by opportunity_id, to_char(item_date, 'yyyy-mm') order by item_date desc) as rn
   from opportunity_history
) t
where rn = 1
group by item_month
order by 1;

SQLFiddle example: http://sqlfiddle.com/#!15/c4152/1

0

Is this what you need?

select 
  opportunity_id, 
  extract(year from item_date) as year,
  extract(month from item_date) as month,
  count(*), 
  sum(amount)
from opportunity_history
group by opportunity_id, year, month
order by opportunity_id, year, month

If not, please explain what else you require/why it is wrong.

See fiddle.

Turophile
  • 3,367
  • 1
  • 13
  • 21