php - SQL:如何使用SQL选择查询来计算当月至今的损益(损益)？

Closed. This question needs to be more focused。它目前不接受答案。
想改进这个问题吗？更新问题，使其只关注一个问题editing this post。
四年前关闭。
我需要计算一系列金融交易的每月至今（MTD）损益（损益）。假设下表一个有交易，另一个有价格：
交易：

Ticket_number   Asset_code  dt_ticket   Cost
1                  1        2014-12-05  700
2                  1        2015-01-30  750
3                  1        2015-07-15  800

价格：

 Asset_code | Dt_price  | PU
    1       | 2014-12-30| 800
    1       | 2015-06-30| 780
    1       | 2015-07-15| 715
    1       | 2015-07-31| 720

假设所有的量都等于1，并且我有多于资产（=资产代码[1…n]）
所以，如果我在7月31日，我会看到这样的事情：
Tkt|u num| Asset|u code| dt|u Tkt| cost | Prev.公司。价格|收盘价|中期损益
1 | 1 | 2014-12-05 | 700 | 780 | 720 |-60
2 | 1 | 2015-01-30 | 750 | 780 | 720 |-60
3 | 1 | 2015-07-15 | 800 | 800 | 720 |-80
公式：MTD=收盘价（7月31日）–当日价格[最长（EOM日期，票证日期）]
在上表中，第一行和第二行的前一个价格应为6月30日的价格，最后一行的价格应为本次交易的成本，与上个月（或同一个月）结算后发生的价格相同。
我的问题是：我能用SQL Select查询计算这个MTD损益吗？
我知道我可以使用PHP，逐行计算，但我想知道它是否可以用SQL Select完成。

最佳答案

我要把它拆开，一次一块解决。我建议您存储一个变量来跟踪截止日期，以便以后可以轻松地重新运行此查询：

SET @closing_date := '2015-07-31';

要获得先前的价格，请考虑以下步骤：
如果车票发生在截止日期的同一个月/年，则之前的价格与车票成本相同。
如果车票是在上个月/是的，上个价格是最近一个月底的价格。
在您的示例中，您希望得到2015-07-01之前的最新价格。因为您可以有多个资产代码，我建议您获取每个资产代码的最新日期，如下所示：

SELECT asset_code, MAX(price_date) AS latestPriceDate
FROM prices
WHERE price_date < DATE_FORMAT(@closing_date, '%Y-%m-01')
GROUP BY asset_code;

要获取这些日期的价格，您需要返回到原始表：

SELECT p.*
FROM prices p
JOIN(
  SELECT asset_code, MAX(price_date) AS latestPriceDate
  FROM prices
  WHERE price_date < DATE_FORMAT(@closing_date, '%Y-%m-01')
  GROUP BY asset_code) tmp ON tmp.asset_code = p.asset_code AND tmp.latestPriceDate = p.price_date;

现在可以接受该子查询并将其连接到事务表。您需要一个CASE语句来检查日期是否在关闭的月份/年份内。如果小于此值，则可以选择交易成本。我还将联接条件调整为只拉取小于或等于截止日期的交易：

SELECT t.ticket_number,
  t.asset_code,
  t.ticket_date,
  CASE WHEN MONTH(t.ticket_date) = MONTH(@closing_date) AND YEAR(t.ticket_date) = YEAR(@closing_date) THEN cost ELSE p.pu END AS 'Previous Price'
FROM transactions t
JOIN(
  SELECT p.*
  FROM prices p
  JOIN(
    SELECT asset_code, MAX(price_date) AS latestPriceDate
    FROM prices
    WHERE price_date < DATE_FORMAT(@closing_date, '%Y-%m-01')
    GROUP BY asset_code) tmp ON tmp.asset_code = p.asset_code AND tmp.latestPriceDate = p.price_date
  ) p ON p.asset_code = t.asset_code AND t.ticket_date <= @closing_date;

要获得每个资产代码的收盘价，您必须进行类似的聚合。首先获取小于或等于结束日期的最新日期，然后连接回表以获取值：

SELECT p.asset_code, p.pu
FROM prices p
JOIN(
  SELECT asset_code, MAX(price_date) AS latestPriceDate
  FROM prices
  WHERE price_date <= @closing_date
  GROUP BY asset_code) tmp ON tmp.asset_code = p.asset_code AND tmp.latestPriceDate = p.price_date;

一旦你有了它，你就可以像这样把它处理到你的整个查询中：

SELECT t.ticket_number,
  t.asset_code,
  t.ticket_date,
  CASE WHEN MONTH(t.ticket_date) = MONTH(@closing_date) AND YEAR(t.ticket_date) = YEAR(@closing_date) THEN cost ELSE p.pu END AS 'Previous Price',
  cp.pu AS closing_price
FROM transactions t
JOIN(
  SELECT p.*
  FROM prices p
  JOIN(
    SELECT asset_code, MAX(price_date) AS latestPriceDate
    FROM prices
    WHERE price_date < DATE_FORMAT(@closing_date, '%Y-%m-01')
    GROUP BY asset_code) tmp ON tmp.asset_code = p.asset_code AND tmp.latestPriceDate = p.price_date
  ) p ON p.asset_code = t.asset_code AND t.ticket_date <= @closing_date
JOIN(
  SELECT p.asset_code, p.pu
  FROM prices p
  JOIN(
    SELECT asset_code, MAX(price_date) AS latestPriceDate
    FROM prices
    WHERE price_date <= @closing_date
    GROUP BY asset_code) tmp ON tmp.asset_code = p.asset_code AND tmp.latestPriceDate = p.price_date
  ) cp ON cp.asset_code = p.asset_code;

最后一件事是得到你的损益表列，你可以通过选择pu-收盘价-上一个价格。不幸的是，您不能在选择中使用列别名，因此必须再次写出公式：

SELECT t.ticket_number,
  t.asset_code,
  t.ticket_date,
  CASE WHEN MONTH(t.ticket_date) = MONTH(@closing_date) AND YEAR(t.ticket_date) = YEAR(@closing_date) THEN cost ELSE p.pu END AS 'Previous Price',
  cp.pu AS 'Closing Price',
  cp.pu - CASE WHEN MONTH(t.ticket_date) = MONTH(@closing_date) AND YEAR(t.ticket_date) = YEAR(@closing_date) THEN cost ELSE p.pu END AS 'P&L'
FROM transactions t
JOIN(
  SELECT p.*
  FROM prices p
  JOIN(
    SELECT asset_code, MAX(price_date) AS latestPriceDate
    FROM prices
    WHERE price_date < DATE_FORMAT(@closing_date, '%Y-%m-01')
    GROUP BY asset_code) tmp ON tmp.asset_code = p.asset_code AND tmp.latestPriceDate = p.price_date
  ) p ON p.asset_code = t.asset_code AND t.ticket_date <= @closing_date
JOIN(
  SELECT p.asset_code, p.pu
  FROM prices p
  JOIN(
    SELECT asset_code, MAX(price_date) AS latestPriceDate
    FROM prices
    WHERE price_date <= @closing_date
    GROUP BY asset_code) tmp ON tmp.asset_code = p.asset_code AND tmp.latestPriceDate = p.price_date
  ) cp ON cp.asset_code = p.asset_code;

下面是一个使用示例数据的SQL Fiddle示例。如果不想使用不必使用的变量，只需在每个位置用日期替换@closing_date。

关于php - SQL:如何使用SQL选择查询来计算当月至今的损益(损益)？ ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31793405/