SQL 子查询问题：分组和平均值

Question

在MS Transact SQL中，假设我有一个类似这样的表（Orders）:

 Order Date       Order Total     Customer #
 09/30/2008       8.00            1
 09/15/2008       6.00            1
 09/01/2008       9.50            1
 09/01/2008       1.45            2
 09/16/2008       4.50            2
 09/17/2008       8.75            3
 09/18/2008       2.50            3

我需要的是：对于每个客户，最近两个订单的平均订单金额。因此，对于客户＃1，我应该得到7.00（而不是7.83）。

我已经盯着这个问题看了一个小时（在解决更大的问题时），我觉得我的大脑已经僵化了。请帮忙解决这个简单的问题？

- clintp

---

一个客户一天内可以有多个订单吗？ - Leon Tayson

Answer 1

这应该可以做到

select avg(total), customer 
from orders o1 
where orderdate in 
  ( select top 2 date 
    from orders o2 
    where o2.customer = o1.customer 
    order by date desc )
group by customer

- AlbertEin

---

如果在同一天内有多个订单，可能无法正常工作，例如如果某个客户在同一天内有3个订单。子查询将给出相同日期的2条记录，而顶部查询将考虑到所有3个订单——但在上下文中这可能不是真实情况。 - kristof

在现实生活场景中，日期列也会存储时间，因此这不会成为问题。 - albertein

如果您的OrderDate只有“天分辨率”，则还可以针对记录的CreatedDate目标前2个。如果您有一个CreatedDate的话，;-)。 - Tomalak

如果你没有这些数据，那么你就无法获取最后两次销售的数据，因此你的数据架构是错误的。 - albertein

实际上，这个表的真实版本存在分辨率问题（日期，但不包括时间），但对于我需要的信息来说已经足够接近了。 - Clinton Pierce

Answer 2

一种选择是使用游标循环遍历所有客户ID，然后将平均值作为若干子查询执行。

不过请注意，对于大型数据集，查询效率不高，可能需要较长时间来处理。

- Ian Jacobs

Answer 3

在SQL Server 2005中，您可以使用带分区的RANK函数。

USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS 'RANK'
FROM Production.ProductInventory i 
    INNER JOIN Production.Product p 
        ON i.ProductID = p.ProductID
ORDER BY p.Name;
GO

链接

- Sklivvz

---

这有点过度了。从性能角度来看，这样做有充分的理由吗？ - Tomalak

就这个问题而言，必须有一个非常好的理由才能偏离标准 SQL 的少数几行代码所能实现的相同结果。 - Tomalak

微软提供了一个函数（带有PARTITION的RANK函数）来处理这些情况。函数越具体，执行计划就越好。因此，在我看来，从未来可读性和性能方面考虑，使用该函数更为优秀。 - Sklivvz

如果有多个标准定义“排名”和分区，则rank方法更容易、更清洁。请参见/questions/150891/sql-query-remove-duplicates-with-caveats#151410，其中rank使查询变得更简单的示例。 - Darrel Miller