Postgresql – Compute new records based on existing ones (e.g., sum of 2 records)

postgresql

Background
I have an inventory where I register the number of fruits I have in stock — both expected and actual quantities.

For some fruits (e.g., here Banana and Cherry), I might not have both records.

My table looks then like this:

| Fruit  | RecordType | Qty |
|--------|------------|----:|
| Apple  | Expected   |  13 |
| Apple  | Actual     |  17 |
| Banana | Actual     |   8 |
| Cherry | Expected   |  42 |
| Durian | Actual     |   0 |
| Durian | Expected   |   1 |

This table gets partially updated over time (i.e. I might only change the Apple | Actual quantity, or add the Banana | Expected record).

Question
➥ Is it possible to automatically compute new records based on the existing ones? If yes, how?

In our example, I might want to compute the Variation between the actual and the expected value: i.e. add the {fruit} | Variation | [{fruit | Actual}(Qty) − {fruit | Expected}(Qty)] record when I have both Actual and Expected records for a given {fruit}.

In our case, I'd like that my table becomes this:

| Fruit  | RecordType | Qty |
|--------|------------|----:|
| Apple  | Expected   |  13 |
| Apple  | Actual     |  17 |
| Banana | Actual     |   8 |
| Cherry | Expected   |  42 |
| Durian | Actual     |   0 |
| Durian | Expected   |   1 |
| Apple  | Variation  |   4 | ← newly computed record
| Durian | Variation  |  -1 | ← newly computed record

You can see that there is no Variation record for Banana or Cherry, because they are missing their Expected or Actual record, respectively.

Best Answer

I don't like the design, but sometimes we just have to deal with the what we find even if we don't like it. Here is a query that gets you the 'Variation' part you are looking for:

select fruit, 'Variation' as recordtype, act_qty-exp_qty as qty from
   (select fruit, qty as exp_qty from foobar where recordtype ='Expected') as ex 
join 
   (select fruit,qty as act_qty from foobar where recordtype='Actual') as ac 
using (fruit);

You can either insert those results into the table (after clearing out the old results) or you can create a view with UNION ALL so that the Variation rows appear in the view, without ever physically existing.

Related Solutions

Postgresql – Storing logs on per day basis in PostgreSQL

The best option IMO is to use a cron job to generate the logs for 'yesterday'::date. You could also use triggers before insert/update/delete to update the other table but this adds complexity and overhead, and for the current day, but this gets pretty complicated. Generate your historical logs once the data won't change anymore.

In this case you write a sql query and run it via psql and cron.

I would also add a trigger denying update or delete to records covered in your historical data if you can.

This gives you a few benefits:

It is more obvious when it breaks
It is simpler, with simpler failure cases

Now, as per your concerns:

You say you need rows for every day. This can be handled a number of relatively easy ways in PostgreSQL (remember that dates support integer math so you can take a base date and add a series to it, to generate a date series). This is a pretty easy way to get around if you are generating rows per day of week, etc.
You say you cant guarantee things won't change. The key question here is what your change window is and to do your historical reports after this window has closed. For example if it is after a month, you can generate reports for all dates in a month a month prior (i.e. generate all dates in January during early March). You can then rely on a view to handle newer rows vs older rows in a live basis. You can then have a trigger which ensures that the date of an inserted row in the orders table is newer than the newest date in the other table.

In my experience worrying about keeping this as a live summary usually isn't necessary. Small organizations (with small data sets) tend to close out books at least once a year, and live reporting is an option there. Larger organizations with larger data sets tend to close out receivables and payables (i.e. invoices) once a month or so, and so the only areas that have to be reported live (because they are subject to adjustment or revision) are open orders (which can be revised) and invoices which may need to be reviewed occasionally (and should never be revised but may have adjustments issued against them which might or might not need to be tracked in such a system).

PostgreSQL – How to SUM Previous Sum

Improving (?) on Craig Ringer's answer. Less code but not sure if it is more readable or more confusing:

SELECT
  id,
  SUM(SUM(no)) OVER (ORDER BY id ASC) AS sum_of_no
FROM table_a
GROUP BY id 
ORDER BY id ;

Tested at SQL-Fiddle

You are right in your comment, when a window function (or an aggregate with OVER()) has an ORDER BY, then the default window is: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which produces a running total with SUM().

The logical flow of execution is:

FROM table_a            -- get all rows of table_a

-- WHERE                -- void here

GROUP BY id             -- make groups of rows, one for each value of "id"
      -- SUM(no) AS y   -- and calculate aggregates, like SUM(no)

-- HAVING               -- void here

SELECT                  -- calculate window functions and window aggregates 
  id,                   -- and any other function used in SELECT or ORDER BY 
  SUM(y) OVER (ORDER BY id ASC) AS sum_of_no

ORDER BY id             -- order the result set

Best Answer

Related Solutions

Postgresql – Storing logs on per day basis in PostgreSQL

PostgreSQL – How to SUM Previous Sum

Related Question