对行进行分组以保持值的顺序 [英] Group rows Keeping the Order of values

问题描述

如何将以下数据分组:

DATENO    COL1   COL2
  1         A      1     
  2         B      1
  3         C      1
  4         C      1
  5         D      1
  6         C      1
  7         D      1
  8         D      1
  9         E      1

要得到这样的东西:

DATENO    COL1   COL2
  1        A      1     
  2        B      1
  3        C      2
  5        D      1
  6        C      1
  7        D      2
  9        E      1

将C和D的总和分组以保持顺序不变.有什么想法吗?

Sum for C and D are grouped keeping the order intact. Any ideas?

推荐答案

已更新:答案已根据评论进行了纠正.

Updated: answer corrected according to comments.

行可以按照以下方式进行分组:

Rows can be grouped as required on such a way:

-- leave only first rows of each group and substitute col2 with a sum.
select 
  dateno, 
  col1, 
  group_sum as col2 
from (
  -- Get sum of col2 for each bucket 
  select 
    dateno, 
    col1, 
    is_start, 
    sum(col2) over (partition by bucket_number) group_sum
  from (
    -- divide rows into buckets based on previous col1 change count
    select
      dateno, col1, col2, is_start, 
      sum(is_start) over(order by dateno rows unbounded preceding) bucket_number
    from (
      -- mark rows with change of col1 value as start of new sequence 
      select
        dateno, col1, col2,
        decode (nvl(prev_col1, col1||'X'), col1, 0, 1) is_start
      from (
        -- determine for each row value of col1 in previous row. 
        select 
          dateno, 
          col1, 
          col2,
          lag(col1) over (order by dateno)  prev_col1
        from t 
      )  
    )  
  )
)
where is_start = 1
order by dateno

SQLFiddle

这篇关于对行进行分组以保持值的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！