将数据转换为十分之一并平均值

Question

所以我有一个千分之一英里的表格，如下所示：

id |fk |from  |to    |speed|score  
1  |123|10.002|10.003|20   |10  
2  |123|10.003|10.004|25   |11  
3  |123|10.004|10.005|40   |44  
4  |123|10.005|10.006|50   |23  
5  |123|10.006|10.007|20   |54  
6  |123|10.007|10.008|10   |24  
7  |123|10.008|10.009|65   |24  
8  |123|10.009|10.010|23   |24  
9  |123|10.010|10.011|74   |56  
10 |123|10.011|10.012|25   |43  
11 |123|10.012|10.013|10   |76  
12 |123|10.013|10.014|5    |10  
...

我需要将千分之一英里表转换为十分之一英里，并取平均速度和分数，如下所示：

id |fk |from  |to    |Avgspeed|Avgscore  
1  |123|10.002|10.012|35.2    |31.3  
10 |123|10.012|...   |...     |...  

我想出了这个查询来获得十分之一英里：

select CASE WHEN rn=1 then 1
when rn%10=0 then 1
else 0 end chk,x.* from 
(
select row_number() over (partition by fk order by fk,from) rn,* from dbo.Thous_Data
)x 

这给了我

id |fk |from  |to    |speed|score|chk|rn  
1  |123|10.002|10.003|20   |10   |1  |1  
2  |123|10.003|10.004|25   |11   |0  |2  
3  |123|10.004|10.005|40   |44   |0  |3   
4  |123|10.005|10.006|50   |23   |0  |4  
5  |123|10.006|10.007|20   |54   |0  |5  
6  |123|10.007|10.008|10   |24   |0  |6  
7  |123|10.008|10.009|65   |24   |0  |7  
8  |123|10.009|10.010|23   |24   |0  |8  
9  |123|10.010|10.011|74   |56   |0  |9  
10 |123|10.011|10.012|25   |43   |1  |10  
11 |123|10.012|10.013|10   |76   |0  |11  
12 |123|10.013|10.014|5    |10   |0  |12  
...  

不确定如何继续获取平均值并正确设置从和到。任何想法都值得赞赏。

Answer 1

像这样的东西？

SELECT 10 * FLOOR(id / 10)  , AVG(speed), MIN( [from] ) , MAX( [to]) 
FROM dbo.Thous_Data
GROUP BY 10 * FLOOR(id / 10);

Answer 2

我能够做到这一点的方法是从 0 开始计数，以便括号准确：

with cte as
(select CASE WHEN rn=0 then 1
when rn%10=0 then 1
else 0 end chk,x.*
from 
(
select ROW_NUMBER() over (partition by fk order by fk,from)-1 rn,* from dbo.Thous_Data 
)x
)
,cte2 as(
SELECT
distinct fk,to,from,
SUM([Chk]) OVER (PARTITION BY  fk ORDER BY  to,from) AS Groups
FROM cte    
)
Select distinct fk,
Min(from) over (partition by fk,groups) from_
,Max(to) over (partition by fk,groups) to_
,avg(speed) over (partition by fk,groups) Aspeed
,avg(score) over (partition by fk,groups) Ascore
from
cte2