--> 测试数据:@T declare @T table([商家id] int,[时间值] int) insert @T select 1,2 union all select 1,4 union all select 1,5 union all select 1,6 union all select 1,7 union all select 2,8 union all select 2,4 union all select 2,1 union all select 2,2 union all select 2,4;WITH m1 AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY 商家id ORDER BY GETDATE() ) AS Num , * FROM @T ), m2 AS ( SELECT * FROM m1 WHERE num <= 2 ) SELECT 商家id , AVG(时间值) AS 平均值 FROM m2 GROUP BY 商家id /* 商家id 平均值 ----------- ----------- 1 3 2 6 */ 这是前2次的平均值
先根据ID找出50条记录,再求平均值 SELECT avg(时间) FROM (select top 50 * from table where id='厂家' order by 时间 desc)
SELECT avg(ord_spendtime) FROM (select top 20 * from table where sho_id='11' ) //取出11编号的商家的20条记录
我记得有个avg函数的,还有sum,count,其他不记得了
-->借用叶子的数据 --> 测试数据:@T declare @T table(shop_id int,orderid int,ord_sendtime int) insert @T select 1,2,20 union all select 1,4,40 union all select 1,5,50 union all select 1,6,60 union all select 1,7,70 union all select 2,8,80 union all select 2,9,40 union all select 2,1,10 union all select 2,3,20 union all select 2,4,40--SELECT * FROM @t;;WITH cte AS ( SELECT rn=ROW_NUMBER() OVER(PARTITION BY shop_id ORDER BY orderid asc),* FROM @T ) SELECT shop_id,AVG([ord_sendtime]) AS 平均时间 FROM cte WHERE rn<=50 GROUP BY shop_id
时间类型是int 我怎么区分是前后?
--> 测试数据:@T
declare @T table([商家id] int,[时间值] int)
insert @T
select 1,2 union all
select 1,4 union all
select 1,5 union all
select 1,6 union all
select 1,7 union all
select 2,8 union all
select 2,4 union all
select 2,1 union all
select 2,2 union all
select 2,4;WITH m1 AS
( SELECT ROW_NUMBER() OVER ( PARTITION BY 商家id ORDER BY GETDATE() ) AS Num , * FROM @T ),
m2 AS ( SELECT * FROM m1 WHERE num <= 2 )
SELECT
商家id , AVG(时间值) AS 平均值
FROM m2 GROUP BY 商家id
/*
商家id 平均值
----------- -----------
1 3
2 6
*/
这是前2次的平均值
SELECT avg(时间) FROM (select top 50 * from table where id='厂家' order by 时间 desc)
-->借用叶子的数据
--> 测试数据:@T
declare @T table(shop_id int,orderid int,ord_sendtime int)
insert @T
select 1,2,20 union all
select 1,4,40 union all
select 1,5,50 union all
select 1,6,60 union all
select 1,7,70 union all
select 2,8,80 union all
select 2,9,40 union all
select 2,1,10 union all
select 2,3,20 union all
select 2,4,40--SELECT * FROM @t;;WITH cte AS (
SELECT rn=ROW_NUMBER() OVER(PARTITION BY shop_id ORDER BY orderid asc),* FROM @T
)
SELECT shop_id,AVG([ord_sendtime]) AS 平均时间 FROM cte WHERE rn<=50 GROUP BY shop_id