时间戳

首页 > 解决方案 > 如何在给定表的列之间执行差异

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

问题描述

我有一个包含以下列的表格

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

我的意图是

  1. 总结在给定股票名称下买卖的所有数量，如果数量和购买的数量相同，那么我在 Total_amount_bought 和 Total_amount_sold 之间做差异，可以称为该交易的净利润或净亏损

  2. 如果给定 stock_name 的买卖数量不同，让我们以 stock_name MGH 为例，如果交易日期在同一日期，则必须从 Total_amount_bought 中减去相同数量的 Total_amount_sold。为了详细说明在同一日期买入和卖出 1000 的 1000 数量，在这种情况下，必须在同一日期从 Total_amount_sold 中减去 Total_amount_bought。

  3. 对于股票 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

但这包括发生在同一日期的交易，但不包括在不同日期完成的交易，这意味着 Stock_name TNT 的交易不包括在内，尽管买卖的股票数量相同。我一直在努力将这种情况与同一个查询合并。

最终结果集应该是

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

任何建议将不胜感激

标签： sqlsql-servertsqlsubquery

解决方案

听从开膛手杰克的建议，按部就班。

首先在买入和卖出的股票相同时获取利润，并将其放在 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)

推荐阅读