Oracle SQL计算各年龄分组驾驶员有责事故占比问题求助
SQL代码修正方案
原代码问题说明
- 派生表
SUBQ的作用域仅在定义它的外层查询生效,嵌套在SELECT子句中的子查询无法访问该别名,因此抛出table or view does not exist错误 - 过滤条件
age_groups = age_groups未区分内外层字段,属于恒成立条件,会返回全表总条数而非当前年龄组的总条数,无法实现分组占比计算
最优写法(兼容绝大多数数据库,性能更高)
直接用条件聚合实现统计,无需嵌套子查询:
SELECT age_groups, 100 * TRUNC( COUNT(CASE WHEN AT_FAULT = 1 THEN 1 END) / COUNT(*), 3 ) AS ratio FROM ( SELECT AT_FAULT, CASE WHEN PARTY_AGE <= 18 THEN 'underage' WHEN PARTY_AGE <= 21 THEN 'young I' WHEN PARTY_AGE <= 24 THEN 'young II' WHEN PARTY_AGE <= 60 THEN 'adult' WHEN PARTY_AGE <= 64 THEN 'elder I' ELSE 'elder II' END AS age_groups FROM PARTY ) SUBQ GROUP BY age_groups ORDER BY ratio DESC;
逻辑说明
- 去掉了外层的
AT_FAULT = 1过滤条件,COUNT(*)可直接统计每个年龄组的总事故数作为分母 - 用
COUNT(CASE WHEN AT_FAULT = 1 THEN 1 END)统计每个年龄组中责任方的事故数作为分子,避免了跨作用域引用派生表的问题 - CASE语句按顺序匹配规则,简化了重复的上下限判断逻辑,运行结果和原逻辑完全一致
子查询写法(如果必须保留原有的子查询逻辑)
将派生表改为CTE(公用表表达式),让它可在子查询作用域访问,同时给表加别名区分内外层的age_groups字段:
WITH SUBQ AS ( SELECT AT_FAULT, CASE WHEN PARTY_AGE <= 18 THEN 'underage' WHEN PARTY_AGE <= 21 THEN 'young I' WHEN PARTY_AGE <= 24 THEN 'young II' WHEN PARTY_AGE <= 60 THEN 'adult' WHEN PARTY_AGE <= 64 THEN 'elder I' ELSE 'elder II' END AS age_groups FROM PARTY ) SELECT s1.age_groups, 100 * TRUNC( COUNT(*) / (SELECT COUNT(*) FROM SUBQ s2 WHERE s2.age_groups = s1.age_groups), 3 ) AS ratio FROM SUBQ s1 WHERE s1.AT_FAULT = 1 GROUP BY s1.age_groups ORDER BY ratio DESC;
内容的提问来源于stack exchange,提问作者Dime
相关产品推荐
相关产品推荐

