计算前n个与SQL Group By相结合的平均值 [英] Calculate average for Top n combined with SQL Group By

问题描述

这是一个非常假设的示例，其中我连接了两个表(ORDERS和ORDHIST).我正在使用MS-ACCESS2010.我正在尝试编写一个联接或子查询，该联接或子查询将返回DATE
之前两个TOP 2最新订单的销售人员的平均值. 订单
SalesPersonID OrderID    值
1              b     123    100
1              b     124    30
1              b     125    55
2              nbsp; b     126     80
2              nbsp; b     127     20
1            &nspsp;     128     45
2              nbsp; b     129     60
2              nbsp; b     130     35

This is a very hypothetical example where I am joining two tables (ORDERS and ORDHIST). I'm using MS-ACCESS 2010. I'm trying to write a join or subquery which will return the average for both salespeople for their TOP 2 most recent orders by DATE
ORDERS
SalesPersonID OrderID  Value
1                             123     100
1                             124     30
1                             125     55
2                             126     80
2                             127     20
1                             128     45
2                             129     60
2                             130     35

ORDHIST
OrderID     日期
123       2011年3月22日
125       25/03/2011
127       29/03/2011
124       24/03/2011
126        2011年3月26日
128       30/03/2011
129        30/03/2011
130       2011年1月4日

ORDHIST
OrderID    Date
123       22/03/2011
125       25/03/2011
127       29/03/2011
124       24/03/2011
126       26/03/2011
128       30/03/2011
129       30/03/2011
130       01/04/2011

我有SQL列出按 SalesPersonID 和 Date :

SELECT Orders.SalesPersonID, Orders.OrderID, Orders.Value, OrdHist.Date 
  FROM Orders 
  INNER JOIN OrdHist ON Orders.OrderID = OrdHist.OrderID 
  ORDER BY Orders.SalesPersonID, OrdHist.Date DESC;

SalesPersonID OrderID  Value   Date  
1              128       45    30/03/2011  
1              125       55    25/03/2011  
1              124       30    24/03/2011  
1              123      100    22/03/2011  
2              130       35    01/04/2011  
2              129       60    30/03/2011  
2              127       20    29/03/2011  
2              126       80    26/03/2011  

所以我现在想为每个SalesPerson选择前两行，并仅显示两行的平均值，以得出我正在寻找的最终结果:

So I now want to pick the first two rows for each SalesPerson and show just the average of the two to give this Final result I'm looking for:

SalesPersonID   LatestAvg
1               50
2               47.5

我是编写联接和内部查询的新手，我无法超越SalesPerson提取所有订单的平均值

I'm new to writing joins and inner queries and I can't get beyond extracting the average by SalesPerson for all of their orders

SELECT Orders.SalesPersonID, Avg(Orders.Value) AS AvgOfValue 
  FROM Orders 
  INNER JOIN OrdHist 
  ON Orders.OrderID = OrdHist.OrderID 
  GROUP BY Orders.SalesPersonID;

SalesPersonID   AvgOfValue
1               57.5
2               48.75

但这不会在组中的最近2个日期进行过滤，因此，有关如何在子查询中进行选择或创建另一个联接的任何指导都将受到赞赏

But this isn't filtering on most recent 2 dates within the group so any guidance on how to select within a subquery or create another join will be much appreciated

推荐答案

您可以使用自我联接仅获取每个销售人员的前2行，并使用派生表将这些行的平均值进行分组.

You can use a self join to only get the top 2 rows for each sales person and use a derived table to group those rows for their average.

SELECT SalesPersonID, AVG(Value) As AvgOfValue FROM (
  SELECT Orders.SalesPersonID, Orders.Value
  FROM Orders 
  INNER JOIN OrdHist ON Orders.OrderID = OrdHist.OrderID
  INNER JOIN Orders o2 on o2.SalesPersonId = Orders.SalesPersonId
  INNER JOIN OrderHist oh2 on oh2.OrderID = o2.OrderID and oh2.Date <= Orders.Date
  GROUP BY Orders.SalesPersonID, Orders.OrderID, Orders.Value
  HAVING COUNT(*) <= 2
) t1 GROUP BY SalesPersonID

这篇关于计算前n个与SQL Group By相结合的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！