使用Oracle SQL进行一些逻辑网络的汇总查询 [英] a Rollup query with some logical netting using Oracle SQL
问题描述
我有一个如下的"AuctionResults"表
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
现在，我需要在以后的回合中以先到先得"的基础扣除卖出价格后，再用每次拍卖的结果与BUYS进行汇总.
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"
因此，在第一轮中，我买了6股股票，然后在第二轮中卖出了2股，在第三轮中剩下了"4"，总净利润为6 * 200-2 * 50-4 * 80 = 780
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
在Round2中，我买入5股股票并在Round3中卖出"1"(因为之前的"4"属于Round1)，其净利润为5 * 100-1 * 80 = 420
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
我们可以仅使用Oracle SQL(10g)而不使用PL-SQL来做到这一点
Can we do this using just Oracle SQL(10g) and not PL-SQL
预先感谢
推荐答案
我知道这是一个老问题，不会对原始海报有用，但我想对此稍作尝试，因为这很有趣问题.我没有对其进行足够的测试，因此我希望仍然需要对其进行纠正和调整.但是我相信这种方法是合法的.我不建议在产品中使用这样的查询，因为这样很难维护或理解(而且我不认为这确实是可扩展的).创建一些备用数据结构会更好.话虽如此，这就是我在Postgresql 9.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
结果:
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)
"WITH"子句中的查询将一些运行总计添加到表中.
The query in the "WITH" clause adds some running totals to the table.
- "previous_net_shares"指示在当前记录之前可以出售多少股票.这也告诉我在开始将其分配给此购买"之前，需要跳过多少卖出"份额.
-
"previous_sells"是所遇到的卖出"份额数量的连续计数，因此，两个"previous_sells"之间的差表示该时间使用的卖出"份额数量.
- "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)
然后出现了一个疯狂的部分，它试图计算订单中可供出售的股票数量与尚未购买的总股数.这里有一些注意事项，以帮助您遵循.带"0"的最大"调用仅表示如果我们为负数，则无法分配任何份额.
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
感谢David的协助
这篇关于使用Oracle SQL进行一些逻辑网络的汇总查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
