结合 SQL Group By 计算 Top n 的平均值 [英] Calculate average for Top n combined with SQL Group By
问题描述
这是一个非常假设的示例,我将连接两个表(ORDERS 和 ORDHIST).我正在使用 MS-ACCESS 2010.我正在尝试编写一个联接或子查询,它将返回两位销售人员在 DATE 前 2 个最近订单的平均值
订单
SalesPersonID OrderID 价值
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
订单ID 日期
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 排序的记录:
选择 Orders.SalesPersonID、Orders.OrderID、Orders.Value、OrdHist.Date从订单在 Orders.OrderID = OrdHist.OrderID 内加入 OrdHistORDER BY Orders.SalesPersonID、OrdHist.Date DESC;
<上一页>SalesPersonID OrderID 值日1 128 45 30/03/20111 125 55 25/03/20111 124 30 24/03/20111 123 100 22/03/20112 130 35 01/04/20112 129 60 30/03/20112 127 20 29/03/20112 126 80 26/03/2011
所以我现在想为每个 SalesPerson 选择前两行,并只显示这两行的平均值来给出我正在寻找的最终结果:
<上一页>销售人员 ID 最新平均1 502 47.5我是编写联接和内部查询的新手,我无法超越销售人员为他们的所有订单提取平均值
SELECT Orders.SalesPersonID, Avg(Orders.Value) AS AvgOfValue从订单内连接 OrdHistON Orders.OrderID = OrdHist.OrderIDGROUP BY Orders.SalesPersonID;
<上一页>SalesPersonID AvgOfValue1 57.52 48.75
但这并没有过滤组内最近 2 个日期,因此非常感谢任何有关如何在子查询中选择或创建另一个联接的指导
您可以使用自联接仅获取每个销售人员的前 2 行,并使用派生表将这些行分组为他们的平均值.
SELECT SalesPersonID, AVG(Value) As AvgOfValue FROM (选择 Orders.SalesPersonID、Orders.Value从订单在 Orders.OrderID = OrdHist.OrderID 内加入 OrdHisto2.SalesPersonId = Orders.SalesPersonId 上的 INNER JOIN 订单 o2INNER JOIN OrderHist oh2 on oh2.OrderID = o2.OrderID 和 oh2.Date <= Orders.Date按 Orders.SalesPersonID、Orders.OrderID、Orders.Value 分组有 COUNT(*) <= 2) t1 按销售人员 ID 分组
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 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
I have the SQL to list records sorted by SalesPersonID and 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
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
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
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
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
这篇关于结合 SQL Group By 计算 Top n 的平均值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!