请教一个分组SQL

〇〇 · 发表于 2023-8-24 16:28

chengccy2010 发表于 2023-8-24 15:46
找变动点，标记1，然后累计求和

和我的last_value思路差不多

sql_tigerliu · 发表于 2023-8-24 20:27

这个问题我以前研究过，用两次分析函数能实现，跟前面的一个思路是一样的：
with  tmp(t_date,data1 )as
(
select '2023-08-01',40 from dual union all
select '2023-08-02',40 from dual union all
select '2023-08-03',40 from dual union all
select '2023-08-04',10 from dual union all
select '2023-08-05',10 from dual union all
select '2023-08-06',40 from dual union all
select '2023-08-07',40 from dual union all
select '2023-08-08',30 from dual union all
select '2023-08-09',30 from dual union all
select '2023-08-10',30 from dual
),
mid1 as
  (select a.*,lag(data1) over (order by t_date) as lag_data1
from tmp a
  ),
mid2 as
(select a.*
   ,case when data1=lag_data1  then 0
      else 1  end as  grp_flag
from mid1 a
)
select a.*,sum(grp_flag) over (order by t_date,data1) as final_grp_no
from mid2 a
;

newkid · 发表于 2023-8-24 22:29

〇〇发表于 2023-8-24 10:44
递归CTE很巧妙

递归CTE要做很多次自连接，效率太低了。
如果12C以上，我推荐这个方法：

select t_date,data1,grp
from tmp
match_recognize(
  order by t_date
  measures MATCH_NUMBER() AS grp
  ALL ROWS PER MATCH
  pattern(a b*)
  define b as b.data1=a.data1
);

其实类似的帖子我发过好几次了，但是现在全民去O,  match_recognize 再强大也没人关注了。