不同行的聚合值 [英] aggregate value in different rows

问题描述

我有这个表格的数据

ItemId  Value   Date
1        2      2017-12-18 17:00:00.000
1        2      2017-12-18 17:02:00.000
1        2      2017-12-18 17:04:00.000
1        3      2017-12-18 17:06:00.000
1        3      2017-12-18 17:08:00.000
1        2      2017-12-20 17:10:00.000
1        2      2017-12-20 17:12:00.000

我想在sql server中这样输出

i want to output like this in sql server

ItemId     Value   MaxDate

1          2       2017-12-18 17:04:00.000
1          3       2017-12-18 17:08:00.000
1          2       2017-12-20 17:12:00.000

对不起，我的标题没有更好的句子

I'm sorry, I did not get a better sentence for my title

推荐答案

这可以通过行号不同的方法来完成.查看将具有相同值的连续行归为一组的内部查询的结果，该组可用于获取最终结果.

This can be done with a difference of row numbers approach. Look at the results of the inner query that classifies consectuive rows with the same value into one group which can be used to get the final result.

select itemid,value,max(date)
from (
select t.*,
row_number() over(partition by itemid order by date)
-row_number() over(partition by itemid,value order by date) as grp
from tbl t
) t
group by itemid,value,grp

根据戈登的回答，只需获得 min 或 max 会更容易.但是，如果每个组都需要 min、max 和 sum 以及其他聚合，则此版本会更好.

Just getting the min or max would be easier with Gordon's answer. However, if both min, max and sum and other aggregations are needed per group, this version would be better.

这篇关于不同行的聚合值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！