一个汇总查询使用Oracle SQL

Question

我有一个表“AuctionResults”像下面

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

现在我需要在随后的几轮扣除SELLS后聚集通过每年都要购买拍卖结果有一定的逻辑网一个“先到先得净基础“

所以在第一回合我买了6股股票，然后在第二回合卖掉了2，第三回合剩下”4“，总净利润为6 * 200-2 * 50-4 * 80 = 780

第二回合我在第三回合买了5股并卖出了“1”（因为之前的“4”bel onged到ROUND1）与5×100-1×80 = 420

净利润......所以得到的输出应该是这样的：

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

我们能做到这一点只使用的Oracle SQL（10克），而不是PL-SQL

在此先感谢

Answer 1

我知道这是一个老问题并不会使用到原来的海报，但我想花刺在这一点，因为这是一个有趣题。我没有对它进行足够的测试，所以我预计这还需要进行修正和调整。但我相信这种方法是合法的。我不建议在产品中使用这样的查询，因为这很难维护或理解（并且我不认为这是真正可扩展的）。创建一些备用数据结构会更好。话说回来，这是我在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 

而结果：

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

把它分解成块，我开始与这组数据：

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) 

“WITH”子句中的查询将一些运行总计添加到表中。

• “previous_net_shares”表示在当前记录之前有多少股可以卖出。这也告诉我在我开始分配给这个'买入'之前，我需要跳过多少'卖出'股票。

• “previous_sells”是遇到的“SELL”份额数的运行计数，所以两个“previous_sells”之间的差值表示当时使用的'SELL'份额的数量。

  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) 
  

有了这张表，我们可以做一个自连接，其中每个“买入”的记录与每个未来的“沽售”记录相关联。其结果是这样的：

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) 

然后就是试图来计算的可供选择以出售VS尚未出售尚未对买入过股票数量股份数量疯狂的一部分。以下是一些帮助解决这个问题的注释。以“0”表示的“最大”电话只是表示如果我们处于否定状态，我们不能分配任何股票。

-- 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 

感谢David他assistance