Partitioning by specific rows

Question

What we have is this, a table sorted by date and time:

date       | time        | amount | type
-----------+-------------+--------+-------
11/09/2014 | 11:13:03 AM | 1      | USE
11/09/2014 | 11:14:03 AM | 2      | USE
11/09/2014 | 12:13:03 AM | 10     | BUY
11/09/2014 | 12:15:03 AM | 1      | USE
11/09/2014 | 12:17:03 AM | 3      | USE
11/09/2014 | 12:18:03 AM | 1      | USE
11/09/2014 | 12:19:03 AM | 4      | USE
11/09/2014 | 13:13:03 AM | 15     | BUY
11/09/2014 | 14:13:03 AM | 1      | USE

We want to build up a growing sum of USE transactions between each BUY transaction. I.e. we want to come up with a table like this:

date       | time        | amount | type
-----------+-------------+--------+-------
11/09/2014 | 11:13:03 AM | 1      | USE
11/09/2014 | 11:14:03 AM | 3      | USE
11/09/2014 | 12:13:03 AM | 10     | BUY
11/09/2014 | 12:15:03 AM | 1      | USE
11/09/2014 | 12:17:03 AM | 4      | USE
11/09/2014 | 12:18:03 AM | 5      | USE
11/09/2014 | 12:19:03 AM | 9      | USE
11/09/2014 | 13:13:03 AM | 15     | BUY
11/09/2014 | 14:13:03 AM | 1      | USE

You see that the amount in each USE transaction is the sum of the old value and the value in the previous USE transaction. And it resets after every BUY transaction.

Partitioning via BUY/USE only creates two partitions, and thus the sum over them is not the one we want to have. We want to partition at every BUY transaction.

Afer having spent a day on trying to solve this, I want to ask how to do this in SQL and whether it is even possible? We are using SAP HANA, in case it matters.

This is possible in SQL. However, SAP Hana supports neither CTEs nor window functions, making it much, much more difficult in that dialect. — Gordon Linoff, Sep 01 '15 at 01:43
You might be referring to an earlier version. Windows functions are [possible now](http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a353327519101495dfd0a87060a0d3/content.htm). Could you please elaborate? — Felix Wolff, Sep 01 '15 at 04:48

score 0 · Answer 1 · answered Sep 01 '15 at 21:37

You need to identify groups that end in "USE". One method is to do a cumulative reverse sum of "USE" values. Then do a cumulative sum, partitioned on this group.

I don't have access to SAP Hana, but this is standard SQL:

select t.*,
       sum(amount) over (partition by grp order by date, time) as cume_amount
from (select t.*,
             sum(case when type = 'BUY' then 1 else 0 end) over (order by date desc, time desc) as grp
      from table t
     )  t

Partitioning by specific rows

1 Answers1