Oracle实现分组统计FIELD3前缀为32/非32的计数并按阈值过滤
Oracle分组统计带阈值过滤查询实现
方案说明
场景前提
已知表中FIELD1与FIELD2取值一一对应,需要先按两个字段分组统计FIELD3的两类计数,再按FIELD2的前缀阈值过滤结果。
实现1:保留原参考写法的列式输出(带过滤)
如果不需要转成行式输出,仅在原有统计逻辑上加阈值过滤,直接新增HAVING子句即可:
SELECT FIELD1, FIELD2, SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 1 ELSE 0 END) AS FIELD3_EQU_32, SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 0 ELSE 1 END) AS FIELD3_NEQ_32 FROM 你的表名 -- 替换为实际表名 GROUP BY FIELD1, FIELD2 -- 新增阈值过滤逻辑 HAVING (SUBSTR(FIELD2, 1, 3) = '888' AND SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 1 ELSE 0 END) > 10) OR (SUBSTR(FIELD2, 1, 3) = '999' AND SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 0 ELSE 1 END) > 5);
实现2:符合预期行式输出(带过滤)
如果需要输出成FIELD1、FIELD2、FIELD3类型、计数的行式结构,可以用UNION ALL拆分统计结果:
WITH group_stats AS ( -- 先分组统计基础计数 SELECT FIELD1, FIELD2, SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 1 ELSE 0 END) AS EQU_32_CNT, SUM(CASE WHEN SUBSTR(FIELD3, 1, 2) = '32' THEN 0 ELSE 1 END) AS NEQ_32_CNT FROM 你的表名 -- 替换为实际表名 GROUP BY FIELD1, FIELD2 -- 先做阈值过滤,减少后续处理数据量 HAVING (SUBSTR(FIELD2, 1, 3) = '888' AND EQU_32_CNT > 10) OR (SUBSTR(FIELD2, 1, 3) = '999' AND NEQ_32_CNT > 5) ) -- 拆分两类计数为行输出 SELECT FIELD1, FIELD2, '32' AS FIELD3, EQU_32_CNT AS COUNT FROM group_stats UNION ALL SELECT FIELD1, FIELD2, 'NOT32' AS FIELD3, NEQ_32_CNT AS COUNT FROM group_stats ORDER BY FIELD1, FIELD2, FIELD3;
注意事项
- 请把代码中的
你的表名替换为实际业务表的名称 - 如果FIELD3是数值类型,需要先转成字符串再截取:
SUBSTR(TO_CHAR(FIELD3),1,2),避免数值前导零丢失导致截取错误 - 阈值规则如果后续有新增,直接在
HAVING子句中按相同逻辑追加条件即可
内容的提问来源于stack exchange,提问作者Lucas Rey
相关产品推荐
相关产品推荐

