什么时候放弃使用SQL进行集合操作并进行过程化? [英] When do you give up set operations in SQL and go procedural?

问题描述

曾经让我在RDBMS中执行此任务:

I was once given this task to do in an RDBMS:

给出客户，订单，订单行和产品表.一切都由通常的字段和关系完成，在订单行表上带有注释备注字段.

Given tables customer, order, orderlines and product. Everything done with the usual fields and relationships, with a comment memo field on the orderline table.

对于一个客户，检索该客户订购过的所有产品的列表，其中包括产品名称，首次购买的年份，最后三次购买的日期，最新订单的注释，该产品-客户组合最近12天的总收入之和个月.

For one customer retrieve a list of all products that customer has ever ordered with product name, year of first purchase, dates of three last purchases, comment of the latest order, sum of total income for that product-customer combination last 12 months.

几天后，我放弃了做为查询的方式，而是选择只为客户获取每个订单行，每个产品并按程序运行数据以构建所需的客户端表.

After a couple of days I gave up doing it as a Query and opted to just fetch every orderline for a customer, and every product and run through the data procedurally to build the required table clientside.

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

I regard this a symptom of one or more of the following:

• 我是个懒惰的白痴，应该已经知道如何在SQL中做到这一点
• 设置操作不像过程操作那样表现力
• SQL的表现力不足

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

Did I do the right thing? Did I have other options?

推荐答案

您绝对应该能够执行此练习，而无需执行与应用程序代码中的JOIN等效的工作，即，通过从订单行和产品中获取所有行并遍历它们.您无需成为SQL向导即可执行该操作. JOIN对于SQL来说就像是对过程语言的循环一样-两者都是您应该知道如何使用的基本语言功能.

You definitely should be able to do this exercise without doing the work equivalent to a JOIN in application code, i.e. by fetching all rows from both orderlines and products and iterating through them. You don't have to be an SQL wizard to do that one. JOIN is to SQL what a loop is to a procedural language -- in that both are fundamental language features that you should know how to use.

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

One trap people fall into is thinking that the whole report has to be produced in a single SQL query. Not true! Most reports don't fit into a rectangle, as Tony Andrews points out. There are lots of rollups, summaries, special cases, etc. so it's both simpler and more efficient to fetch parts of the report in separate queries. Likewise, in a procedural language you wouldn't try do all your computation in a single line of code, or even in a single function (hopefully).

某些报告工具坚持认为，报告是通过单个查询生成的，因此您没有机会合并到多个查询中.如果是这样，则您需要生成多个报告(如果老板希望在一页上进行报告，则需要手动进行一些粘贴操作).

Some reporting tools insist that a report is generated from a single query, and you have no opportunity to merge in multiple queries. If so, then you need to produce multiple reports (and if the boss wants it on one page, then you need to do some paste-up manually).

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

To get a list of all products ordered (with product name), dates of last three purchases, and comment on latest order is straightforward:

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;

最好逐行迭代结果以提取最新订单的日期和注释，因为无论如何您都要获取这些行.但是，通过要求数据库返回按日期排序的结果，可以使自己更轻松.

It's fine to iterate over the result row-by-row to extract the dates and comments on the latest orders, since you're fetching those rows anyway. But make it easy on yourself by asking the database to return the results sorted by date.

首次购买的年份可从上一个查询中获得，如果按order_date排序并逐行获取结果，则可以访问第一个订单.否则，您可以按照以下方式进行操作:

Year of first purchase is available from the previous query, if you sort by the order_date and fetch the result row-by-row, you'll have access to the first order. Otherwise, you can do it this way:

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

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

Sum of product purchases for the last 12 months is best calculated by a separate query:

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中获取最近三笔购买的日期:

edit: You said in another comment that you'd like to see how to get the dates of the last three purchases in standard 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:

If you can use a wee bit of vendor-specific SQL features, you can use Microsoft/Sybase TOP n, or 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;

这篇关于什么时候放弃使用SQL进行集合操作并进行过程化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！