SQL查询如何删除antecedent与consequent互换的重复数据行

我现有一个查询返回的结果中存在逆序重复行，需要修改查询语句直接完成去重，不可通过外层编程语言实现。重复行的判定规则为：row1.antecedent = row2.consequent 且 row2.antecedent = row1.consequent，这类重复行仅保留任意一行即可，CONF值差异可忽略。

如下为数据样例，我期望筛选后仅保留RULE_ID为943、945、979的行：944与943、946与945、980与979都属于antecedent和consequent顺序相反的重复项，需要剔除。

select * from (
SELECT 
RULE_ID,
A.ATTRIBUTE_SUBNAME ANTECEDENT,
C.ATTRIBUTE_SUBNAME CONSEQUENT,
CONCAT(TO_CHAR(RULE_SUPPORT*100,'990.99'),'%') Support_Frequency,
CONCAT(TO_CHAR(RULE_CONFIDENCE*100,'990.99'),'%') Confidence_Association,
ROUND(RULE_LIFT, 2) Lift_Likelyhood
FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('AR_SH_SAMPLE')) T,
TABLE(T.CONSEQUENT) C,
TABLE(T.ANTECEDENT) A
) where rule_id in 
(
select min(rule_id) from
(
SELECT 
RULE_ID,
A.ATTRIBUTE_SUBNAME ANTECEDENT,
C.ATTRIBUTE_SUBNAME CONSEQUENT,
CONCAT(TO_CHAR(RULE_SUPPORT*100,'990.99'),'%') Support_Frequency,
CONCAT(TO_CHAR(RULE_CONFIDENCE*100,'990.99'),'%') Confidence_Association,
ROUND(RULE_LIFT, 2) Lift_Likelyhood,
(CASE 
WHEN A.ATTRIBUTE_SUBNAME > C.ATTRIBUTE_SUBNAME THEN CONCAT(A.ATTRIBUTE_SUBNAME,C.ATTRIBUTE_SUBNAME)
WHEN C.ATTRIBUTE_SUBNAME > A.ATTRIBUTE_SUBNAME THEN CONCAT(C.ATTRIBUTE_SUBNAME,A.ATTRIBUTE_SUBNAME)
END) CAT
FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('AR_SH_SAMPLE')) T,
TABLE(T.CONSEQUENT) C,
TABLE(T.ANTECEDENT) A
)
group by CAT
)
ORDER BY RULE_ID

• 核心逻辑是给每对逆序的规则生成唯一的分组标识：通过比较ANTECEDENT和CONSEQUENT的大小，把两个值按大在前、小在后的顺序拼接成CAT字段，这样逆序的两条规则会生成完全相同的CAT值
• 按CAT字段分组后取每组最小的RULE_ID，就能保证每组逆序规则仅保留一条
• 外层主查询仅筛选符合条件的RULE_ID即可完成去重

内容的提问来源于stack exchange，提问作者user16758151