按顺序分组的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

相关推荐