按顺序分组的SQL [英] SQL of group by in order by
本文介绍了按顺序分组的SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
[Raw data]
A B C
1 10 1
1 10 2
2 20 3
2 20 4
1 100 5
1 100 6
[Wanted result]
A SUM_OF_B
1 20
2 40
1 200
查询没有简单的"group by"子句和"dense_rank over partition by"是没有用的,因为分组对所有行有效.但是我想按有序状态分组.如何编写正确的查询?
It's unuseful that the query has the simple 'group by' clause and 'dense_rank over partition by' because grouping works all rows. However I want grouping in state of ordering. How do I write the proper query?
推荐答案
您需要标识相邻记录的组.实际上,您可以使用不同的行号方法来做到这一点-假设c
对行进行排序.对于连续的a
值,该差异是恒定的:
You need to identify the groups of adjacent records. You can actually do this by using a difference of row numbers approach -- assuming that c
orders the rows. The difference is constant for consecutive values of a
that are the same:
select a, sum(b)
from (select t.*,
(row_number() over (order by c) -
row_number() over (partition by a order by c)
) as grp
from table t
) t
group by grp, a
order by min(c);
这篇关于按顺序分组的SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文