mysql - SQL查询-按月到期的数据透视贷款摘要

这看起来很简单，但我正把头撞在墙上想办法。
我有一个单一的贷款数据表，我想为每个月的付款汇总。这类似于“静态池分析”，将行和月的每个贷款池作为列。您可以使用SQL Fiddle查看示例数据。
我创建了一个90 second screencast来更好地解释我需要的轴心点和摘要结果（如果有帮助的话）。谢谢你的帮助！

最佳答案

如果不需要动态，那么在MySQL中，使用SUM中的CASE WHEN或If语句作为轴心：

SELECT
  PoolMonth,
  SUM(OriginationAmt) AS Origination,
  SUM(IF(PmtDue = 201512, AmtPaid, 0)) AS `201512`,
  SUM(IF(PmtDue BETWEEN 201512 AND 201601, AmtPaid, 0)) AS `201601`,
  SUM(IF(PmtDue BETWEEN 201512 AND 201602, AmtPaid, 0)) AS `201602`
FROM
  Loans
GROUP BY
  PoolMonth;

http://sqlfiddle.com/#!9/47701/16
这对于获取total OriginationAmt（按PoolMonth/PmtDue计算的运行总数）来说有点冗长，而且只输出最新的运行总数，而不进行任何硬编码，但是我们现在开始了：-）

SELECT
  t.PoolMonth,
  t.TtlOriginationAmt,
  t.PmtDue,
  t.RtAmtPaid
FROM
  (
    SELECT
      l.PoolMonth,
      l.OriginationAmt,
      orig.TtlOriginationAmt,
      l.PmtDue,
      /* Row_Number() equivalent for MySQL http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/ */
      /* Assign a Row Number for each Payment Due month for the individual Pool month in ascending order (ORDER BY clause important in this subquery) */
      @RowNumber := CASE WHEN @PoolMonth = l.PoolMonth AND @PmtDue = l.PmtDue THEN @RowNumber + 1 ELSE 1 END AS PoolPmtRowNumber,
      /* Use the total count of PmtDue month rows for each PoolMonth so we can limit our results to the final row */
      lr.PoolPmtLastRow,
      l.AmtPaid,
      /* Running total of Amount Paid for the individual Pool month in order of Payment Due month (ORDER BY clause important in this subquery) */
      @RtAmtPaid := CASE WHEN @PoolMonth = l.PoolMonth THEN @RtAmtPaid + l.AmtPaid ELSE l.AmtPaid END AS RtAmtPaid,
      /* Keep track of the Pool month we're totalling */
      @PoolMonth := l.PoolMonth,
      /* Keep track of the Payment Due month we're ordering */
      @PmtDue := l.PmtDue
    FROM
      Loans l
    JOIN
      /* Get the Total Origination Amount */
      (SELECT PoolMonth, SUM(OriginationAmt) AS TtlOriginationAmt FROM Loans GROUP BY PoolMonth) orig ON orig.PoolMonth = l.PoolMonth
    JOIN
      /* Get the total number of records by Pool/Payment due month so we can filter to the last row */
      (SELECT PoolMonth, PmtDue, COUNT(1) AS PoolPmtLastRow FROM Loans GROUP BY PoolMonth, PmtDue) AS lr ON lr.PoolMonth = l.PoolMonth AND lr.PmtDue = l.PmtDue
    CROSS JOIN
      /* Reset the variables we need for tracking */
      (SELECT @RtAmtPaid:=0,@PoolMonth:=0,@PmtDue:=0,@RowNumber:=0) var
    /* Order by Pool/Payment Due month */
    ORDER BY
      l.PoolMonth,
      l.PmtDue
  )t
WHERE
  /* Filter to the last row */
  t.PoolPmtRowNumber = t.PoolPmtLastRow;

http://sqlfiddle.com/#!9/47701/45
从那里开始，在Excel或其他任何地方转换结果应该非常容易。

关于mysql - SQL查询-按月到期的数据透视贷款摘要，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35327580/