listagg去重
-
去重思路：利用listagg会忽略null值的特点
-
按ENTITY_GROUP_RRN 分组，用 listagg 分别合并 EQPT_ID 与 STATION_ID ，同时要求去重
表 T_TEST 数据如下：
| EQPT_ID | ENTITY_GROUP_RRN | STATION_ID |
|---|---|---|
| TOOL-001 | 10493721 | JITAI-1 |
| TOOL-003 | 10493721 | JITAI-1 |
| TEST | 10493721 | S1 |
| TEST | 10493721 | S2 |
| TEST2 | 10493721 | S1 |
| TEST2 | 10493721 | S2 |
| TOOL-001 | 12345 | JITAI-1 |
| TOOL-003 | 12345 | JITAI-1 |
| TEST | 12345 | S1 |
| TEST | 12345 | S2 |
| TEST2 | 12345 | S1 |
| TEST2 | 12345 | S2 |
3、上SQL：
with T_TEST as(select 'TOOL-001' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union allselect 'TOOL-003' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union allselect 'TEST' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union allselect 'TEST' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual union allselect 'TEST2' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union allselect 'TEST2' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual union allselect 'TOOL-001' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union allselect 'TOOL-003' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union allselect 'TEST' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union allselect 'TEST' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual union allselect 'TEST2' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union allselect 'TEST2' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual
)
select t.entity_group_rrn,listagg(decode(rne, 1, t.eqpt_id, null), ',') within group(order by t.eqpt_id) eqpt_ids,listagg(decode(rns, 1, t.station_id, null), ',') within group(order by t.station_id) station_idsfrom (select row_number() over(partition by t.entity_group_rrn,t.eqpt_id order by rownum) rne,row_number() over(partition by t.entity_group_rrn,t.station_id order by rownum) rns,t.*from T_TEST t) tgroup by t.entity_group_rrn;查询结果
- 总结
1、通常情况可以先分组去重，再合并即可，除非遇到同一分组中，多列分别有重复值，才需要上面那样处理
2、还有另一种去重办法，使用 wm_concat + distinct ，如下：
with T_TEST as(select 'TOOL-001' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union allselect 'TOOL-003' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union allselect 'TEST' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union allselect 'TEST' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual union allselect 'TEST2' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union allselect 'TEST2' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual
)
select wm_concat(distinct t.EQPT_ID) ids,entity_group_rrn, wm_concat(distinct t.station_id) ids2
from T_TEST t group by t.entity_group_rrn
但是由于官方不推荐使用 wm_concat ，所以尽量不要用此方法
