Oralce字段排列组合计数

Oralce字段排列组合计数

• 表结构
  create table app_impact_rule(
c_app_id varchar2(64),
ext_id varchar2(64),
oc_app_id varchar2(64),
rule_type varchar2(32)
);

• 查询SQL
  with temp as
(
select t.c_app_id,t.rule_type, count(t.rule_type) over(partition by c_app_id) n_count
from risk.app_impact_rule t
where t.c_app_id is not null
),
temp1 as(
select listagg(rule_type,',') within GROUP (order by rule_type) t_type
from temp
where n_count>1
group by n_count,C_APP_ID
)
select t_type 组合方式,
count(t_type) 数量
from temp1
group by t_type
order by length(t_type);

• temp数据

  

• temp1数据

  

• 最终结果