110,534
社区成员
发帖
与我相关
我的任务
分享
-->借用叶子的数据
--> 测试数据:@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
--> 测试数据:@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次的平均值