sql - 什么时候放弃 SQL 中的集合操作并转向过程化操作？

Question

我曾经被要求在 RDBMS 中完成这项任务:

给定表 customer、order、orderlines 和 product。使用常用字段和关系完成的所有操作，以及订单行表上的注释备注字段。

对于一个客户，检索客户曾经订购过的所有产品的列表，其中包含产品名称、首次购买年份、最近三次购买的日期、最新订单的评论、该产品-客户组合最近 12 年的总收入总和个月。

几天后，我放弃了将其作为查询来执行，而是选择只获取客户的每个订单行和每个产品，并按程序运行数据以构建所需的客户端表。

我认为这是以下一种或多种症状的表现:

• 我是个懒惰的白痴，应该知道如何用 SQL 实现
• 集合操作不像过程操作那样富有表现力
• SQL 没有达到应有的表现力

我做对了吗？我还有其他选择吗？

Answer 1

您绝对应该能够完成此练习，而无需在应用程序代码中执行相当于 JOIN 的工作，即从订单行和产品中获取所有行并遍历它们。您不必成为 SQL 向导就可以执行该操作。 JOIN 之于 SQL 就像循环之于过程语言 -- 因为两者都是您应该知道如何使用的基本语言功能。

人们掉入的一个陷阱是认为必须在单个 SQL 查询中生成整个报告。不对!正如托尼·安德鲁斯 (Tony Andrews) 指出的那样，大多数报告都不适合矩形。有很多汇总、摘要、特殊情况等，因此在单独的查询中获取报告的各个部分既简单又高效。同样，在过程语言中，您不会尝试在一行代码中完成所有计算，甚至在单个函数中(希望如此)。

一些报告工具坚持认为报告是由单个查询生成的，您没有机会合并多个查询。如果是这样，那么你需要制作多份报告(如果老板想要它在一页上，那么你需要手动进行一些粘贴)。

要获取所有订购产品(包括产品名称)、最近三笔购买的日期以及对最新订单的评论的列表很简单:

SELECT o.*, l.*, p.*
FROM Orders o
 JOIN OrderLines l USING (order_id)
 JOIN Products p USING (product_id)
WHERE o.customer_id = ?
ORDER BY o.order_date;

逐行遍历结果以提取最新订单的日期和评论是很好的，因为无论如何您都在获取这些行。但是，通过要求数据库返回按日期排序的结果，让您自己轻松一些。

首次购买年份可从上一个查询中获得，如果您按order_date 排序并逐行获取结果，您将可以访问第一个订单。否则，您可以这样做:

SELECT YEAR(MIN(o.order_date)) FROM Orders o WHERE o.customer_id = ?;

过去 12 个月的产品购买总和最好通过单独的查询来计算:

SELECT SUM(l.quantity * p.price)
FROM Orders o
 JOIN OrderLines l USING (order_id)
 JOIN Products p USING (product_id)
WHERE o.customer_id = ?
 AND o.order_date > CURDATE() - INTERVAL 1 YEAR;

编辑:您在另一条评论中说您想了解如何在标准 SQL 中获取最近三笔购买的日期:

SELECT o1.order_date
FROM Orders o1
  LEFT OUTER JOIN Orders o2 
  ON (o1.customer_id = o2.customer_id AND (o1.order_date < o2.order_date 
      OR (o1.order_date = o2.order_date AND o1.order_id < o2.order_id)))
WHERE o1.customer_id = ?
GROUP BY o1.order_id
HAVING COUNT(*) <= 3;

如果您可以使用一点供应商特定的 SQL 功能，您可以使用 Microsoft/Sybase TOP n，或 MySQL/PostgreSQL LIMIT:

SELECT TOP 3 order_date
FROM Orders
WHERE customer_id = ?
ORDER BY order_date DESC;

SELECT order_date
FROM Orders
WHERE customer_id = ?
ORDER BY order_date DESC
LIMIT 3;