按 T-SQL 中的每 N 条记录分组 [英] Group by every N records in T-SQL
本文介绍了按 T-SQL 中的每 N 条记录分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我对数据库有一些性能测试结果,我想做的是每1000条记录分组(之前按日期升序排列),然后用AVG聚合结果.
I have some performance test results on the database, and what I want to do is to group every 1000 records (previously sorted in ascending order by date) and then aggregate results with AVG.
我实际上是在寻找标准的 SQL 解决方案,但也欢迎任何特定于 T-SQL 的结果.
I'm actually looking for a standard SQL solution, however any T-SQL specific results are also appreciated.
查询如下所示:
SELECT TestId,Throughput FROM dbo.Results ORDER BY id
推荐答案
WITH T AS (
SELECT RANK() OVER (ORDER BY ID) Rank,
P.Field1, P.Field2, P.Value1, ...
FROM P
)
SELECT (Rank - 1) / 1000 GroupID, AVG(...)
FROM T
GROUP BY ((Rank - 1) / 1000)
;
这样的事情应该会让你开始.如果您能提供您的实际架构,我可以酌情更新.
Something like that should get you started. If you can provide your actual schema I can update as appropriate.
这篇关于按 T-SQL 中的每 N 条记录分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文