5

Following the answer to this question, I have managed to produce the following output to get a running sum of values:

id      creation                    operation       value       running sum
SyJw-c  2016-09-01 00:11:08.307419  positive_op_1   1.33        28.82
SyJw-c  2016-08-21 08:32:54.431662  negative_op_1   -1          27.49
SyJw-c  2016-08-18 07:38:33.878365  positive_op_2   1           28.49
SyJw-c  2016-08-14 18:12:03.599797  negative_op_1   -1          27.49
SyJw-c  2016-08-02 15:44:29.693303  positive_op_1   1.33        28.49
SyJw-c  2016-07-31 12:08:50.659905  override_op_1   4.66        27.16
SyJw-c  2016-06-26 06:53:54.537603  negative_op_1   -3.5        22.5
SyJw-c  2016-05-31 13:34:08.005687  negative_op_1   -1          26
SyJw-c  2016-05-31 13:34:04.776970  negative_op_1   -1          27
SyJw-c  2016-05-31 11:27:09.502983  override_op_2   28          28

BUT my case is more complex. Not only do I need to sum up the values, I need to be able to perform a conversion first to some rows based on the running sum of the row right beneath it.

Let me first explain the motivation:

Currently I have a table with incremental, decremental and override operations. I would like to port the data to a table with only incremental and decremental operations such that I would be able to straightforwardly sum up the values. I am not looking to maintain the old table, simply a way to migrate the data into a simpler model and henceforward to append data to the new table only.

Taken the "raw" table above, I would like to write a query (I am running on postgresql 9.5) and get a table as closely resembling the below. (Conversely, I would like to know that what I am attempting is impossible)

Note the override operators are interspersed between the normal operators, they may appear more than twice as in the example, also, all initial operators (the earliest in the table) are override with an initial value that should be taken into account as in the example below. Moreover I had shown only data belonging to one group (same id) but the general idea is to perform this migration for all groups. Lastly I show the math in parentheses, I don't need that in the result, it is for the example only:

id      creation    oper      transformed_op   value   transformed_value       running sum
SyJw-c  2016- ...  pos_op_1                    1.33    1.33                    10.98
SyJw-c  2016- ...  neg_op_1                    -1      -1                      9.65
SyJw-c  2016- ...  pos_op_2                    1       1                       10.65
SyJw-c  2016- ...  neg_op_1                    -1      -1                      9.65
SyJw-c  2016- ...  pos_op_1                    1.33    1.33                    10.65
SyJw-c  2016- ...  ovr_op_1   new_rel_op_1     4.66    (4.66-22.5) = -17.84    4.66
SyJw-c  2016- ...  neg_op_1                    -3.5    -3.5                    22.5
SyJw-c  2016- ...  neg_op_1                    -1      -1                      26
SyJw-c  2016- ...  neg_op_1                    -1      -1                      27
SyJw-c  2016- ...  ovr_op_2   new_rel_op_2     28      (28-0) = 28             28

The table is shown ordered from last to first. The 22.5 is 26 - 3.5. This subtraction should be done (this_value - previous_sum) depends on the value of the transformed_op. When the original op had been an override op I would like to perform some action based on the running sum of the row proceeding it (ordered by creation desc), in this case subtract that running sum value from the value in the value column.

5

Based on this table definition:

CREATE TABLE tbl (  -- no PK?
  id text NOT NULL
, creation timestamp UNIQUE NOT NULL
, operation text NOT NULL
, value numeric NOT NULL
, running_sum numeric  -- optional (not needed for task)
);

Data types and constraints are almost always essential.
(creationdoes not strictly have to be unique. But if there can be duplicate values per group (id), you need to do more.)

Basic query to compute your special running sum

SELECT id, creation, operation, value
     , sum(value) OVER (PARTITION BY id, run ORDER BY creation) AS running_sum
FROM   (
   SELECT *, count(*) FILTER (WHERE operation  LIKE 'override_op_%')
                      OVER (PARTITION BY id ORDER BY creation) AS run
   FROM   tbl
   ) t
ORDER  BY id, creation DESC;

Any operation name starting with 'override_op_' indicates the start of a new run (group, patch, partition).

In addition to the related answer you already linked to:

Consider this related question for details how to partition rows into groups (run in this query, since you are using the term "group" for the id column)

I use the new aggregate FILTER clause for the partial count:

You could use the simpler (less clear) expression in older versions:

count(operation LIKE 'override_op_%' OR NULL)

Compute delta

Based on this, you can easily compute the desired delta:

SELECT *
     , running_sum - lag(running_sum, 1, numeric '0')  -- data type must match!
                     OVER (PARTITION BY id ORDER BY creation) AS transformed_value 
FROM (
   SELECT id, creation, operation, value
        , sum(value) OVER (PARTITION BY id, run ORDER BY creation) AS running_sum
   FROM   (
      SELECT *, count(*) FILTER (WHERE operation  LIKE 'override_op_%')
                OVER (PARTITION BY id ORDER BY creation) AS run
      FROM   tbl
      ) t
   ) t
ORDER  BY id, creation DESC;

I use the 3-parameter form of the window function lag() to provide 0 (data type must match!) as default value for the first row in the table.

  • Thanks so much Erwin! There is a PK in the original data (it actually is a join on two similar but not identical tables), I had not selected it when I created the table and then I had, also, elided some uninteresting columns when asking the question, but your guess as to the columns presented here is correct. I will attempt to run your solution on the data and report back on the status of the run. – Yaneeve Sep 11 '16 at 12:27
  • seemed to have worked, thanks ever so much. I will analyze my results more in depth and will select your answer when I am done – Yaneeve Sep 11 '16 at 12:50
  • if you will allow me to add one more layer of complexity (tell me if to spin it off as a new question). Going back to my real life data, I noticed that there is a subtle difference to my override_op_s. override_op_1 cannot override data if its value is 0 and an override_op_2 had been noticed somewhere in the related sequence of events before that. I can think of a heuristic, but I am not sure it would work for all the cases - modify the FILTER such as FILTER (WHERE type = 'override_op_2' OR (type = 'override_op_1' AND value > 0.0)) – Yaneeve Sep 12 '16 at 5:57
  • 1
    @Yaneeve: Yes, a new question should really be a new question. Comments are not the place. You can always link to this one for context. and you can get my attention with a comment here. Since the new question is related, linking to it with a comment is useful to the general public, too. – Erwin Brandstetter Sep 12 '16 at 11:16
  • 1
    Thanks, asked the question: dba.stackexchange.com/questions/149409/…. I must say that the merit of asking a new question is now clear to me since I can see that my proposed solution in the comment above is clearly wrong – Yaneeve Sep 12 '16 at 12:20

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.