大量数据的自定义排序算法 - Custom sorting algorithm for a large amount of data

我有大量的数据需要根据搜索查询以特定的方式进行排序，但我不确定该采取什么样的最佳方法。

我试图整理的数据是一个按学校分组的课程列表。每门课程由一所学校教授。每个学校可能属于任何数量的"伙伴关系"，这代表了多个学校之间的关系。用户可以通过课程名称搜索任意数量的课程。

我需要对数据进行如下排序：

课程按学校分组，每页显示10所学校。
可以提供用户搜索到的每门课程的学校应该出现在列表的第一位。
在这些结果之后，属于可以容纳用户搜索的所有课程的合作伙伴关系的学校应该并排出现。

这里有一个例子：

A教授历史、法语和英语课程
B教授法语和数学
C教授历史
B和C是合伙企业
D教授历史。
用户搜索"历史"answers"法语"。
A应该首先出现在结果中，包括历史和法语课程，因为它可以提供用户想要的两门课程。
B，然后是C出现在后面，后面列出了它教授的相关课程，因为合作伙伴关系可以提供用户的两门必修课程。
D接下来出现，因为它只提供1个相关课程。

数据存储在Microsoft SQL Server数据库中的几个表中。这里有一个简化的模式：

课程：

int id
varchar名称
int学校ID

学校：

int id
varchar名称

合作伙伴关系：

int id
varchar合作伙伴名称

学校合作伙伴关系：

int id
int学校ID
int合作伙伴ID

这里有超过100000门课程和大约300所学校。我不知道如何按照SQL中指定的方式对课程进行排序，我认为这是我最大的问题。我只需要每页显示10个结果，但由于我不能在SQL查询中进行排序，我必须提取整个结果集并在PHP中手动排序，然后才能将结果集减少到10个结果。

我目前正在使用Doctrine 2在具有多个联接的单个查询中提取我需要的数据，将结果水合为数组。然后，我们的计划是在PHP中操作这个大的记录数组，使其按正确的顺序排列。由于这个数组的大小，我担心这个排序过程会非常慢，所以我正在寻找如何使其更快的建议，可以通过：

处理SQL查询中的排序
建议如何在Solr这样的搜索引擎中实现所描述的算法（我对这方面的基本知识有一点经验，但不执行复杂的排序）
如果其他两个选项不可行，建议如何最好地在PHP中执行排序

编辑：

我在这方面取得了一些不错的进展，谢谢（尤其是@Neil）。我打开了一个单独的问题（关于子查询的Groupwise MAX（）），其中包含了我到目前为止的一些进展。

通过匹配课程的数量找到学校很简单：

SELECT schoolId, COUNT(*) AS schoolCount
  FROM Courses
  WHERE name IN ('History', 'French')
  GROUP BY schoolId

如果这就是你所需要的，你可以ORDER BY schoolCount DESC按照你想要的顺序得到它们。

要找到与匹配课程的合作伙伴关系，您首先需要找到至少在一所学校拥有该课程的合作关系：

SELECT partnershipId, COUNT(DISTINCT name) AS partnershipCount
  FROM SchoolPartnership
  INNER JOIN Courses ON Course.schoolId = SchoolPartnership.schoolId
  WHERE name IN ('History', 'French')
  GROUP BY partnershipId

请注意，DISTINCT是必要的，因为我们不在乎合作伙伴中有多少学校有这门课程。如果你没有DISTINCT，那么你可以使用子选择：

SELECT partnershipId, COUNT(*) AS partnershipCount
  FROM (
    SELECT DISTINCT partnershipId, name
      FROM SchoolPartnership
      INNER JOIN Courses ON Course.schoolId = SchoolPartnership.schoolId
      WHERE name IN ('History', 'French'))
  GROUP BY partnershipId

然后，您可以在与SchoolPartnership的联接中使用上面的第一个和最后一个查询作为子选项，按partnershipMatches和schoolMatches的降序排列学校。（注意，我假设所有学校都是至少一所学校的合作伙伴。）我认为最后的查询会是这样的：

SELECT SchoolMatches.schoolID
  FROM (
    SELECT schoolId, COUNT(*) AS schoolCount
      FROM Courses
      WHERE name IN ('History', 'French')
      GROUP BY schoolId
  ) SchoolMatches
  JOIN SchoolPartnership ON SchoolMatches.schoolID = SchoolPartnership.schoolID
  JOIN (
    SELECT partnershipId, COUNT(DISTINCT name) AS partnershipCount
      FROM SchoolPartnership
      INNER JOIN Courses ON Course.schoolId = SchoolPartnership.schoolId
      WHERE name IN ('History', 'French')
      GROUP BY partnershipId
   ) PartnershipMatches ON SchoolPartnership.schoolId = PartnershipMatches.schoolId
   ORDER BY PartnershipMatches.partnershipCount DESC, SchoolMatches.SchoolCount DESC

我们在网站页面上也遇到了类似的问题。我们创建了带有所有参数的特殊非规范化搜索表，以执行无子查询或联接的搜索。所有数据都是重复的，所以当某些事情发生变化时，我们会更新所有非规范化的数据。我们使用后台任务来同步数据，因此搜索结果可能在很短的时间内不真实。

也许这看起来很复杂，但只有当你的数据和请求增长时，这才是唯一的方法。

filter_var('sgamgee@example.com', FILTER_VALIDATE_EMAIL); // Returns "sgamgee@example.com"

这是一个有效的电子邮件地址。