如何在给定表的列之间执行差异

p1iqtdky 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(246)

我有一张有下面几列的table

STOCK_NAME                                         TRADE_DATE TRADE_TYPE                                         QUANTITY_BOUGHT        QUANTITY_SOLD          TOTAL_AMOUNT_SOLD      TOTAL_AMOUNT_BOUGHT
-------------------------------------------------- ---------- -------------------------------------------------- ---------------------- ---------------------- ---------------------- ----------------------
CACC                                               2020-04-08 buy                                                125                    0                      0                      41185
CACC                                               2020-04-08 sell                                               0                      125                    41031.25               0

HDK                                                2020-03-02 sell                                               0                      50                     59189.8                0
HDK                                                2020-03-05 buy                                                10                     0                      0                      11523.5
HDK                                                2020-06-03 sell                                               0                      10                     10248.5                0
MGH                                                2020-03-05 buy                                                1000                   0                      0                      52350
MGH                                                2020-04-07 buy                                                1000                   0                      0                      42500
MGH                                                2020-04-07 sell                                               0                      1000                   42750                  0
TNT                                                2020-05-19 buy                                                48                     0                      0                      19725.6
TNT                                                2020-06-01 buy                                                2                      0                      0                      995.2
TNT                                                2020-06-05 sell                                               0                      50                     28045.7                0

我的意图是
将给定股票名称下的所有卖出和买入数量相加，如果卖出的数量和买入的数量相同，则我计算买入的总金额和卖出的总金额之间的差额，可以称为该交易的净利润或净亏损
如果一个给定的股票名称的卖出和买入数量不一样，让我们来盘点一下，如果交易日期在同一天，卖出的总金额必须从相同数量的买入总金额中减去。为了详细说明1000个数量是在同一天买卖的，在这种情况下，必须从同一天售出的总金额中减去购买的总金额。
对于股票hdk，必须计算数量相同的股票的净损益。为了详细说明数量10是买卖的，因此必须减去相关金额以获得净损益，忽略hdk的其他条目，其中有另一个条目数量50必须忽略。
请帮忙。我一直在努力解决这个问题，为此写了一个查询：

SELECT SQ.STOCK_NAME, SQ.NP
FROM
(SELECT STOCK_NAME, TRADE_DATE, 
 SUM(TOTAL_AMOUNT_SOLD) - SUM(TOTAL_AMOUNT_BOUGHT) AS NP, 
 SUM(QUANTITY_BOUGHT) AS QB, SUM(QUANTITY_SOLD) AS QS
 FROM [STOCK_TRADING_dETAILS]
 GROUP BY STOCK_NAME, TRADE_DATE) SQ
 WHERE QB-QS = 0

但这包括在同一日期发生的交易，但不包括在不同日期进行的交易，这意味着尽管买卖的股票数量相同，但不包括股票名称tnt的交易。我一直在努力将这个场景与合并到同一个查询中。
最终结果集应为

STOCK_NAME                                         NET PROFIT/LOSS
-------------------------------------------------- ----------------------
MGH                                               250
CACC                                             -153.75
HDK                                              -1275.0
TNT                                               7324.9

如有任何建议，将不胜感激

sql sql-server subquery tsql

来源：https://stackoverflow.com/questions/62287556/how-to-perform-difference-between-the-columns-for-the-given-table

1条答案

按热度按时间

liwlm1x91#

听从开膛手杰克的建议，分头行动。
首先在股票买卖相同的情况下获得利润，并将其放在cte上：

WITH TOTAL_PROFITS AS (
  SELECT STOCK_NAME, SUM(TOTAL_AMOUNT_SOLD) - SUM(TOTAL_AMOUNT_BOUGHT) AS PROFITS
  FROM STOCK_TRADING_DETAILS
  GROUP BY STOCK_NAME
  HAVING (QUANTITY_BOUGHT) = SUM(QUANTITY_SOLD)
)
.....

然后在另一个cte上输入您已经准备好的同一天相等买卖的利润查询：

WITH DAILY_PROFITS AS (
  SELECT SQ.STOCK_NAME, SQ.PROFIT
  FROM (SELECT STOCK_NAME, TRADE_DATE, 
               SUM(TOTAL_AMOUNT_SOLD) - SUM(TOTAL_AMOUNT_BOUGHT) AS PROFIT, 
               SUM(QUANTITY_BOUGHT) AS QB, SUM(QUANTITY_SOLD) AS QS
        FROM STOCK_TRADING_DETAILS
        GROUP BY STOCK_NAME, TRADE_DATE) SQ
  WHERE QB-QS = 0
)
.....

现在我们用第三个病例的结果建立一个新的cte

WITH DAILY_BUYS AS (
  SELECT STOCK_NAME, TRADE_DATE, (QUANTITY_BOUGHT) AS QB, SUM(TOTAL_AMOUNT_BOUGHT) AS AB
  FROM STOCK_TRADING_DETAILS
  GROUP BY STOCK_NAME, TRADE_DATE
), 
DAILY_SALES AS (
  SELECT STOCK_NAME, TRADE_DATE, (QUANTITY_SOLD) AS QS, SUM(TOTAL_AMOUNT_SOLD) AS AS
  FROM STOCK_TRADING_DETAILS
  GROUP BY STOCK_NAME, TRADE_DATE
),
DIFFERENT_DATE_PROFITS (
  SELECT DAILY_BUYS.STOCK_NAME, DAILY_SALES.AS - DAILY_BUYS.AB AS PROFIT
  FROM DAILY_BUYS
       INNER JOIN DAILY_SALES ON DAILY_SALES.STOCK_NAME = DAILY_BUYS.STOCK_NAME AND
                                 DAILY_SALES.TRADE_DATE <> DAILY_BUYS.TRADE_DATE AND
                                 DAILY_SALES.QS = DAILY_BUYS.QB
) 
.....

最后，将所有这些部分合并到同一个查询中，将总利润和日利润（那些已经不在总利润中的产品）合并起来。

WITH TOTAL_PROFITS AS (
  SELECT STOCK_NAME, SUM(TOTAL_AMOUNT_SOLD) - SUM(TOTAL_AMOUNT_BOUGHT) AS PROFITS
  FROM STOCK_TRADING_DETAILS
  GROUP BY STOCK_NAME
  HAVING (QUANTITY_BOUGHT) = SUM(QUANTITY_SOLD)
),
DAILY_PROFITS AS (
  SELECT SQ.STOCK_NAME, SQ.PROFIT
  FROM (SELECT STOCK_NAME, TRADE_DATE, 
               SUM(TOTAL_AMOUNT_SOLD) - SUM(TOTAL_AMOUNT_BOUGHT) AS PROFIT, 
               SUM(QUANTITY_BOUGHT) AS QB, SUM(QUANTITY_SOLD) AS QS
        FROM STOCK_TRADING_DETAILS
        GROUP BY STOCK_NAME, TRADE_DATE) SQ
  WHERE QB-QS = 0
),
DAILY_BUYS AS (
  SELECT STOCK_NAME, (QUANTITY_BOUGHT) AS QB, SUM(TOTAL_AMOUNT_BOUGHT) AS AB
  FROM STOCK_TRADING_DETAILS
  GROUP BY STOCK_NAME, TRADE_DATE
), 
DAILY_SALES AS (
  SELECT STOCK_NAME, (QUANTITY_SOLD) AS QS, SUM(TOTAL_AMOUNT_SOLD) AS AS
  FROM STOCK_TRADING_DETAILS
  GROUP BY STOCK_NAME, TRADE_DATE
),
DIFFERENT_DATE_PROFITS (
  SELECT DAILY_BUYS.STOCK_NAME, DAILY_SALES.AS - DAILY_BUYS.AB AS PROFIT
  FROM DAILY_BUYS
       INNER JOIN DAILY_SALES ON DAILY_SALES.STOCK_NAME = DAILY_BUYS.STOCK_NAME AND
                                 DAILY_SALES.QS = DAILY_BUYS.QB
) 
SELECT STOCK_NAME, PROFITS
FROM TOTAL_PROFITS
UNION ALL
SELECT STOCK_NAME, PROFIT
FROM DAILY_PROFITS
WHERE STOCK_NAME NOT IN (SELECT STOCK_NAME FROM TOTAL_PROFITS)
UNION ALL
SELECT STOCK_NAME, PROFITS
FROM DIFFERENT_DATE_PROFITS 
WHERE STOCK_NAME NOT IN (SELECT STOCK_NAME FROM TOTAL_PROFITS
                         UNION
                         SELECT STOCK_NAME FROM DAILY PROFITS)

赞(0）回复(0）举报 2021-07-29

我来回答

如何在给定表的列之间执行差异

1条答案

相关问题

热门标签

最新问答