如何计算利率低于现有旧贷款利率的再贷款？

Question

我有一个贷款记录表，其中包括年利率（annual_interest_rate）、单调递增顺序ID（loan_id）和借款人ID（member_id）。你知道吗

我试着统计那些新贷款利率低于之前贷款利率的再借款人（即拥有多笔贷款的会员）。你知道吗

数据示例如下：

Annual_interest_rate | Loan_id | Member_id
 0.2850              |  1      | -9832
 0.1482              |  2      |  6982
 0.065               |  3      | -9832
 0.1754              |  4      |  1234
 0.2387              |  5      |  1234

在这个样本数据中，唯一一个利率较低的再借款者是会员9832，所以这样的再借款者的总数应该是一个。你知道吗

我如何计算这些再借贷者的总数？你知道吗

Answer 1

使用SQL有几种不同的方法来解决这个问题。所有这些都是非常标准的SQL查询，应该可以在任何或多或少与ANSI-SQL兼容的数据库上工作，而不仅仅是SQLite。你知道吗

可能概念上最简单的方法是使用subquery查找贷款记录，其中存在另一个具有相同成员ID和较低利率的较新贷款记录：

SELECT DISTINCT Member_id
FROM Loans AS OldLoan
WHERE EXISTS (
    SELECT 1 FROM Loans AS NewLoan
    WHERE NewLoan.Member_id = OldLoan.Member_id
      AND NewLoan.Loan_id > OldLoan.Loan_id
      AND NewLoan.Annual_interest_rate < OldLoan.Annual_interest_rate
)

此查询将返回至少有一对贷款且后一对贷款（按贷款ID）的利率低于前一对贷款的所有成员的ID。如果您只需要这些成员的数量，请将DISTINCT Member_id替换为COUNT(DISTINCT Member_id)。你知道吗

如果内部SELECT与至少一行匹配，则上述查询中的EXISTS条件的计算结果为true。请注意，内部查询应该返回哪些列并不重要，因为EXISTS只关心结果是否有任何行，所以我只告诉它为每个匹配行返回一个包含常量1的列。（在任何情况下，数据库引擎都可能优化这个常量。）

您可以将此查询看作是在表的每一行上循环，然后再次在相同的表上使用内部循环，以查看是否有另一行具有相同的Member_id、较高的Loan_id和较低的Annual_interest_rate。这可能不是SQLite引擎最终实现查询的方式，但是您将得到的结果在任何情况下都是相同的。你知道吗

---

或者，可以使用Loans表的self-join来代替子查询。有两种相同的方法可以写出来。例如，只需在FROM子句中列出表两次（当然，使用不同的别名），然后使用WHERE子句匹配行，如下所示：

SELECT DISTINCT OldLoan.Member_id
FROM Loans AS OldLoan, Loans AS NewLoan
WHERE NewLoan.Member_id = OldLoan.Member_id
  AND NewLoan.Loan_id > OldLoan.Loan_id
  AND NewLoan.Annual_interest_rate < OldLoan.Annual_interest_rate

（请注意，这里我们需要显式地将结果列指定为OldLoan.Member_id，因为OldLoan和NewLoan表（或者说，同一个表的别名）现在都在外部查询中使用，并且它们都有一个Member_id列。当然，WHERE子句可以确保所有匹配行的成员id都是相同的，但是SQLite解析器还不够聪明，无法实现这一点。）

对于像这样的简单查询，这种风格的编写工作得很好。但是，对于将多个表连接在一起的更复杂的查询，使用显式的JOIN。。。ON子句可以更整洁，例如：

SELECT DISTINCT OldLoan.Member_id
FROM Loans AS OldLoan
JOIN Loans AS NewLoan ON NewLoan.Member_id = OldLoan.Member_id
WHERE NewLoan.Loan_id > OldLoan.Loan_id
  AND NewLoan.Annual_interest_rate < OldLoan.Annual_interest_rate

由于您是通过匹配具有相同名称的列来连接两个表（或者更确切地说，在本例中，是两个不同名称下的同一个表），因此您甚至可以使用USING()的等效速记语法：

SELECT DISTINCT OldLoan.Member_id
FROM Loans AS OldLoan
JOIN Loans AS NewLoan USING(Member_id)
WHERE NewLoan.Loan_id > OldLoan.Loan_id
  AND NewLoan.Annual_interest_rate < OldLoan.Annual_interest_rate

上面所有的查询都是等价的，因为它们将返回相同的结果，最后三个（使用自连接）也应该给出完全相同的执行计划，因此运行速度同样快。子查询版本可能使用不同的执行计划，具体取决于您使用的数据库引擎。在SQLite上似乎是这样，但其他一些数据库可能会同时优化子查询和自连接，以便在后台以相同的方式执行。你知道吗

在任何情况下，要使这些查询中的任何一个高效地在一个大表上运行，您至少应该在Member_id列上有一个索引，这样数据库引擎就可以轻松地找到属于同一成员的贷款对，而不必扫描表中的每一对行。根据所使用的数据库、表的大小和每个成员的典型贷款数量，一些其他索引也可能有用，但这正进入数据库性能微调的深奥领域。在任何情况下，只要没有一个成员拥有大量的贷款，只要Member_id上的简单索引就可以了。你知道吗

Ps.Here's an SQLFiddle demonstrating all these queries.您可以使用它来检查它们是否工作，以及它们是否返回相同的结果。如果需要，还可以单击“查看执行计划”以获取有关SQLite如何实际运行这些查询的更多信息。你知道吗