按序号ID分组，5行为一组，求分组平均数？
表如下:
CREATE TABLE #G_Goods
( ID INT PRIMARY KEY, Val1 DECIMAL(5,2));INSERT INTO #G_Goods (ID, Val1)
SELECT 1, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 3 UNION ALL
SELECT 4, 4 UNION ALL
SELECT 5, 5 UNION ALL
SELECT 6, 6 UNION ALL
SELECT 7, 7 UNION ALL
SELECT 8, 8 UNION ALL
SELECT 9, 9 UNION ALL
SELECT 10, 10 UNION ALL
SELECT 11, 11 UNION ALL
SELECT 12, 12 UNION ALL
SELECT 13, 13 UNION ALL
SELECT 14, 14 UNION ALL
SELECT 15, 15 UNION ALL
SELECT 16, 16 UNION ALL
SELECT 17, 17 UNION ALL
SELECT 18, 18 UNION ALL
SELECT 19, 19 UNION ALL
SELECT 20, 20;
按序号ID分组，连续5行为一组，如：ID序号1至5行为一组，ID序号6至10行为一组，将每组Val1的和除以5得到每组平均数？
想得到平均数“Val2”结果如下：
ID Val1 Val2
5 5 3
10 10 8
15 15 13
20 20 18
------解决思路----------------------
select MAX(id)id,MAX(val1)val1,AVG(val1)val2 from (
select (case when id%5<>0 then (ID/5)else (ID/5)-1 end) groupid ,id,val1 from #G_Goods
)t group by groupid
ID Val1 Val2
5 5.00 3.000000
10 10.00 8.000000
15 15.00 13.000000
20 20.00 18.000000
------解决思路----------------------
我也写一个~~
declare @i int = ceiling((select count(1) from #G_Goods)/5.0);
with cte as
(
select NTILE(@i)over(order by id)nid,* from #G_Goods
)
select MAX(id)id,MAX(val1)val1,AVG(val1)val2 from
cte
group by nid
------解决思路----------------------
select id,val1,val2 from (select *,avg(val1) over(partition by groupid)as val2
from (select*,(id-1)/5 as groupid from #g_goods) as p)as t
where id%5=0
--结果
id val1 val2
----------- --------------------------------------- ---------------------------------------
5 5.00 3.000000
10 10.00 8.000000
15 15.00 13.000000
20 20.00 18.000000