最后X个订单的平均值
另一种选择是一个CTE和ROW_NUMBER()。以下将给你客户的平均订单。
Declare @YourTable table (CustID int,OrderDate Date,OrderAmount int)
Insert Into @YourTable values
(1,'2016-01-18',2500),
(1,'2016-02-13',5000),
(1,'2016-03-31',3000),
(2,'2016-03-18',1800),
(2,'2016-04-13',2200),
(2,'2016-05-31',2500)
;with cteBase as (
Select *
,RowNr=Row_Number() over (Partition By CustID Order By OrderDate Desc)
From @YourTable
)
Select CustID
,AvgOrder = avg(OrderAmount)
From cteBase
Where RowNr<=2
Group By CustID
返回
CustID AvgOrder
1 4000
2 2350
谢谢!有效。 – user107242
@ user107242很高兴知道。老实说,对窗口功能感到舒服...他们是非常宝贵的。干杯 –
使用ROW_NUMBER
窗函数
select [Customer ID],Avg([Total Amount]) as avg_amt
(
select row_number()over(partition by [Customer ID] order by [Date] desc) rn, *
from yourtable
) A
Where Rn <= 5 --change it based on requirement
Group by [Customer ID]
貌似当前表是事务表,所以你可能有一个单独的表称为Customer
其中[Customer ID]
是独一无二的,那么你可以用这个方法也
SELECT c.[Customer ID],
Avg([Total Amount])
FROM customers c
CROSS apply (SELECT TOP 5 [Total Amount]
FROM yourtable y
WHERE c.[Customer ID] = y.[Customer ID]
ORDER BY [Date] DESC) cs
GROUP BY [Customer ID]
上面的查询可以用OUTER APPLY
被改变,以获得列表所有customers
即使他没有与平均Total Amount
零交易。
谢谢!有效。 – user107242
一种方法使用row_number()
:
select customerid, avg(totalamount)
from (select t.*,
row_number() over (partition by customerid order by date desc) as seqnum
from t
) t
where seqnum <= x;
请张贴一些示例数据和预期的输出。 –
一种方法是计算由客户划分的row_number(),并按日期DESC排序 - 然后将该查询用作内部查询并选择row_number Cato
您的问题不清楚,请在这里查看以改善问题:https://spaghettidba.com/2015/04/24/how-to-post-at-sql-question-on-a-public-forum/ – TheGameiswar