SQL子查询问题与分组、平均

In MS Transact SQL, let's say I have a table (Orders) like this:

在MS Transact SQL中，假设我有这样一个表(订单):

 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

What I need out of this is: for each customer the average order amount for the most recent two orders. So for Customer #1, I should get 7.00 (and not 7.83).

我需要从中得到的是:对于每个客户，最近两个订单的平均订单量。因此对于客户1，我应该得到7.00(而不是7.83)。

I've been staring at this for an hour now (inside a larger problem, which I've solved) and I think my brain has frozen. Help for a simple problem?

我已经盯着这个看了一个小时了(在一个更大的问题里，我已经解决了)，而且我认为我的大脑已经冻结了。帮助解决一个简单的问题?

3 个解决方案

#1

This should make it

这将使它

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

#2

In SQL Server 2005 you have the RANK function, used with partition:

在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

Link

链接

#3

One option would be for you to use a cursor to loop through all the customer Id's, then do the averages as several subqueries.

一种选择是使用游标循环所有客户Id，然后将平均数作为几个子查询。

Fair warning though, for large datasets, queries are not very efficient and can take a long time to process.

不过，公平的警告是，对于大型数据集，查询不是非常有效，而且可能需要很长时间来处理。

#1