SQL:如何使用SQL选择查询计算月至今的损益 - SQL: how to calculate month to date P&L (proft and loss) with SQL select query?

我需要计算一系列金融交易的月至今损益。假设下面的表格，一个是交易，另一个是价格:

事务:

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，并且我有不止一个资产(= asset_code[1…n])所以，如果我在7月31日，我会看到这样的内容:

<>之前Tkt_num |Asset_code|dt_tkt | cost |上一页价格|收盘价| MTD损益表2014-12-05| 700 | 780 | 720 | -602015-01-30| 750 | 780 | 720 | -602015-07-15| 800 | 800 | 720 | -80之前

公式:MTD =收盘价(7月31日)-日期价格[max(EOM日期，ticket_date)]在上面的表中，第一行和第二行之前的价格应该是截至6月30日的价格，最后一行的价格应该是这些交易的成本，因为它发生在最后一个月结束之后(或在同一个月)。我的问题是:我可以用SQL Select查询计算这个MTD P&L吗?我知道可以用PHP逐行计算，但我想知道是否可以用SQL Select。

我将把它分解，一次一个地解决它。我建议您存储一个变量来跟踪结束日期，以便将来可以轻松地重新运行此查询:

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

要获得先前的价格，请考虑以下步骤:

如果门票与截止日期发生在同一个月/年份，则之前的价格与门票成本相同。
如果门票发生在前一个月/是的，前一个月的价格是最近一个月底的价格。

在您的示例中，您想要获得在2015-07-01之前发生的最新价格。因为您可以有多个asset_code，我建议您像这样获取每个asset_code的最新日期:

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;

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

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;

最后要做的事情是获取P&L列，可以通过选择pu - closing_price - previous_price来实现。不幸的是，您不能在选择中使用列别名，因此您必须重新编写公式:

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替换为您的日期即可。