a Rollup query with some logical netting using Ora

Question

I have a table "AuctionResults" like below

Auction  Action    Shares  ProfitperShare   
------------------------------------------- 
Round1   BUY        6      200    
Round2   BUY        5      100   
Round2   SELL      -2      50   
Round3   SELL      -5      80

Now I need to aggregate results by every auction with BUYS after netting out SELLS in subsequent rounds on a "First Come First Net basis"

so in Round1 I bought 6 Shares and then sold 2 in Round2 and rest "4" in Round3 with a total NET profit of 6 * 200-2 * 50-4 * 80 = 780

and in Round2 I bought 5 shares and sold "1" in Round3(because earlier "4" belonged to Round1) with a NET Profit of 5 * 100-1 * 80 = 420

...so the Resulting Output should look like:

Auction   NetProfit
------------------
Round1    780    
Round2    420   

Can we do this using just Oracle SQL(10g) and not PL-SQL

Thanks in advance

Answer 1

I know this is an old question and won't be of use to the original poster, but I wanted to take a stab at this because it was an interesting question. I didn't test it out enough, so I would expect this still needs to be corrected and tuned. But I believe the approach is legitimate. I would not recommend using a query like this in a product because it would be difficult to maintain or understand (and I don't believe this is really scalable). You would be much better off creating some alternate data structures. Having said that, this is what I ran in Postgresql 9.1:

    WITH x AS (
        SELECT round, action
              ,ABS(shares) AS shares
              ,profitpershare
              ,COALESCE( SUM(shares) OVER(ORDER BY round, action
                                          ROWS BETWEEN UNBOUNDED PRECEDING 
                                                   AND 1 PRECEDING)
                        , 0) AS previous_net_shares
              ,COALESCE( ABS( SUM(CASE WHEN action = 'SELL' THEN shares ELSE 0 END)
                            OVER(ORDER BY round, action
                                     ROWS BETWEEN UNBOUNDED PRECEDING 
                                              AND 1 PRECEDING) ), 0 ) AS previous_sells
          FROM AuctionResults
          ORDER BY 1,2
    )

    SELECT round, shares * profitpershare - deduction AS net
      FROM (

           SELECT buy.round, buy.shares, buy.profitpershare
                 ,SUM( LEAST( LEAST( sell.shares, GREATEST(buy.shares - (sell.previous_sells - buy.previous_sells), 0)
                                    ,GREATEST(sell.shares + (sell.previous_sells - buy.previous_sells) - buy.previous_net_shares, 0)
                                   )
                             ) * sell.profitpershare ) AS deduction
             FROM x buy
                 ,x sell
             WHERE sell.round > buy.round
               AND buy.action = 'BUY'
               AND sell.action = 'SELL'
             GROUP BY buy.round, buy.shares, buy.profitpershare

           ) AS y

And the result:

     round | net
    -------+-----
         1 | 780
         2 | 420
    (2 rows)

To break it down into pieces, I started with this data set:

    CREATE TABLE AuctionResults( round int, action varchar(4), shares int, profitpershare int);

    INSERT INTO AuctionResults VALUES(1, 'BUY', 6, 200);
    INSERT INTO AuctionResults VALUES(2, 'BUY', 5, 100);
    INSERT INTO AuctionResults VALUES(2, 'SELL',-2, 50);
    INSERT INTO AuctionResults VALUES(3, 'SELL',-5, 80);
    INSERT INTO AuctionResults VALUES(4, 'SELL', -4, 150);  

    select * from auctionresults;

     round | action | shares | profitpershare
    -------+--------+--------+----------------
         1 | BUY    |      6 |            200
         2 | BUY    |      5 |            100
         2 | SELL   |     -2 |             50
         3 | SELL   |     -5 |             80
         4 | SELL   |     -4 |            150
    (5 rows)

The query in the "WITH" clause adds some running totals to the table.

• "previous_net_shares" indicates how many shares are available to sell before the current record. This also tells me how many 'SELL' shares I need to skip before I can start allocating it to this 'BUY'.

• "previous_sells" is a running count of the number of "SELL" shares encountered, so the difference between two "previous_sells" indicates the number of 'SELL' shares used in that time.

   round | action | shares | profitpershare | previous_net_shares | previous_sells
  -------+--------+--------+----------------+---------------------+----------------
       1 | BUY    |      6 |            200 |                   0 |              0
       2 | BUY    |      5 |            100 |                   6 |              0
       2 | SELL   |      2 |             50 |                  11 |              0
       3 | SELL   |      5 |             80 |                   9 |              2
       4 | SELL   |      4 |            150 |                   4 |              7
  (5 rows)
  

With this table, we can do a self-join where each "BUY" record is associated with each future "SELL" record. The result would look like this:

    SELECT buy.round, buy.shares, buy.profitpershare
          ,sell.round AS sellRound, sell.shares AS sellShares, sell.profitpershare AS sellProfitpershare
      FROM x buy
          ,x sell
      WHERE sell.round > buy.round
        AND buy.action = 'BUY'
        AND sell.action = 'SELL'

     round | shares | profitpershare | sellround | sellshares | sellprofitpershare
    -------+--------+----------------+-----------+------------+--------------------
         1 |      6 |            200 |         2 |          2 |                 50
         1 |      6 |            200 |         3 |          5 |                 80
         1 |      6 |            200 |         4 |          4 |                150
         2 |      5 |            100 |         3 |          5 |                 80
         2 |      5 |            100 |         4 |          4 |                150
    (5 rows)

And then comes the crazy part that tries to calculate the number of shares available to sell in the order vs the number over share not yet sold yet for a buy. Here are some notes to help follow that. The "greatest"calls with "0" are just saying we can't allocate any shares if we are in the negative.

   -- allocated sells 
   sell.previous_sells - buy.previous_sells

   -- shares yet to sell for this buy, if < 0 then 0
   GREATEST(buy.shares - (sell.previous_sells - buy.previous_sells), 0)

   -- number of sell shares that need to be skipped
   buy.previous_net_shares

Thanks to David for his assistance