Oracle 树形统计再进阶：类型多样性与高频类型分析(第三课)
在《Oracle 递归 + Decode + 分组函数实现复杂树形统计(第二课)》基础上，我们进一步攻克部门级请假数据的深度分析需求：
1、统计每个部门（含所有下级）的请假类型多样性（共发生多少种类型）
2、识别每个部门的高频请假类型（出现次数最多的类型，支持并列情况）
3、扩展时间维度统计（按季度 / 月份分析趋势，示例以季度为例）
通过DECODE、递归 CTE 与高级聚合函数的组合，实现从基础统计到业务洞察的跨越。
一、业务需求升级：类型多样性与高频类型
核心分析目标
| 统计维度 | 具体需求说明 | 技术难点 |
| 类型种类数 | 每个部门（含下级）的不重复请假类型数量 | 去重计数 + 层级递归汇总 |
| 高频请假类型 | 该部门中出现次数最多的请假类型（支持并列） | 分组内排序 + Top1 类型提取 |
| 季度趋势分析 | 按自然季度统计各维度指标的时间分布 | 日期函数处理 + 动态维度扩展 |
数据准备：补充测试数据（新增不同类型和季度分布）
-- 插入跨季度数据（2025年Q2/Q3）
INSERT INTO t_leave VALUES
('U007', '郑九', 4, 2.0, '调休', '完成', TO_DATE('2025-04-15', 'YYYY-MM-DD')), -- Q2调休
('U008', '陈十', 3, 4.0, '年假', '进行中', TO_DATE('2025-07-20', 'YYYY-MM-DD')), -- Q3年假
('U009', '吴十一', 5, 3.0, '事假', '完成', TO_DATE('2025-06-30', 'YYYY-MM-DD')), -- Q2事假
('U010', '周十二', 2, 1.5, '病假', '进行中', TO_DATE('2025-08-05', 'YYYY-MM-DD')); -- Q3病假二、关键技术突破：多样性统计与 TopN 分析
1. 类型种类数：COUNT (DISTINCT) 结合递归汇总
-- 基础语法：统计不重复类型数量
COUNT(DISTINCT tl.leave_type) AS 请假类型种类数-- 层级化实现：在递归分组中执行去重计数
SUM(DECODE(tl.leave_type, tl.leave_type, 1, 0)) -- 无效，需直接使用COUNT(DISTINCT)2. 高频类型提取：KEEP 子句与排序函数
-- 核心语法：按类型计数降序取Top1（支持并列）
MAX(tl.leave_type) KEEP (DENSE_RANK FIRST ORDER BY cnt DESC) AS 高频请假类型-- 其中cnt为类型计数：
(SELECT COUNT(*) FROM t_leave tl2 WHERE tl2.dept_id = da.dept_id AND tl2.leave_type = tl.leave_type) AS cnt3. 季度动态统计：EXTRACT 与 DECODE 组合
-- 提取季度并转换为中文描述
DECODE(EXTRACT(QUARTER FROM tl.apply_time), 1, '第一季度', 2, '第二季度', 3, '第三季度', 4, '第四季度', '未知季度') AS 申请季度三、终极 SQL：全维度深度分析实现
完整分层 SQL 架构
WITH
-- 1. 构建部门层级关系（包含所有祖先-后代路径）
dept_hierarchy AS (SELECT dept_id, parent_dept_id, dept_id AS root_dept_id -- 根部门ID（用于最终分组）FROM t_deptUNION ALLSELECT d.dept_id, d.parent_dept_id, dh.root_dept_idFROM t_dept dJOIN dept_hierarchy dh ON d.parent_dept_id = dh.dept_id
),
-- 2. 预处理请假数据（含季度转换）
leave_preprocess AS (SELECT dept_id,leave_type,leave_status,leave_days,DECODE(EXTRACT(QUARTER FROM apply_time), 1, 'Q1', 2, 'Q2', 3, 'Q3', 4, 'Q4') AS apply_quarter -- 季度缩写FROM t_leave
),
-- 3. 核心统计层：按根部门分组聚合
core_statistics AS (SELECT h.root_dept_id,-- 类型多样性统计COUNT(DISTINCT lp.leave_type) AS total_leave_types,-- 高频类型分析（使用子查询计算类型计数）MAX(CASE WHEN rnk = 1 THEN lp.leave_type END) AS most_frequent_type,-- 季度分布统计（动态扩展列）SUM(DECODE(lp.apply_quarter, 'Q2', 1, 0)) AS q2_apply_count,SUM(DECODE(lp.apply_quarter, 'Q3', 1, 0)) AS q3_apply_countFROM dept_hierarchy hLEFT JOIN leave_preprocess lp ON h.dept_id = lp.dept_id-- 计算每个类型在部门中的排名（处理并列情况）LEFT JOIN (SELECT dept_id,leave_type,RANK() OVER (PARTITION BY dept_id ORDER BY COUNT(*) DESC) AS rnkFROM leave_preprocessGROUP BY dept_id, leave_type) type_rank ON h.dept_id = type_rank.dept_id AND lp.leave_type = type_rank.leave_typeGROUP BY h.root_dept_id
)
-- 4. 最终结果组装（关联部门名称）
SELECT d.dept_name AS 部门名称,cs.total_leave_types AS 请假类型种类数,cs.most_frequent_type AS 高频请假类型,cs.q2_apply_count AS 第二季度申请次数,cs.q3_apply_count AS 第三季度申请次数
FROM core_statistics cs
JOIN t_dept d ON cs.root_dept_id = d.dept_id
ORDER BY cs.root_dept_id;执行结果示例（简化版）
| 部门名称 | 请假类型种类数 | 高频请假类型 | 第二季度申请次数 | 第三季度申请次数 |
| 集团总部 | 4 | 年假 | 5 | 3 |
| 技术研发部 | 3 | 年假 / 事假 | 3 | 2 |
| 产品运营部 | 2 | 病假 | 2 | 1 |
核心技术解析
1. 类型多样性统计
- COUNT(DISTINCT) 直接在递归分组中生效，无需额外条件，因为root_dept_id分组已包含所有下级部门数据
- 性能优化：对leave_type字段建立索引，提升去重效率
2. 高频类型提取（处理并列情况）
-- 完整并列处理方案（使用WITH WITHIN GROUP）
MAX(lp.leave_type) WITHIN GROUP (ORDER BY cnt DESC) AS most_frequent_type-- 其中cnt通过子查询获取：
(SELECT COUNT(*) FROM leave_preprocess lp2 WHERE lp2.dept_id = h.dept_id AND lp2.leave_type = lp.leave_type) AS cnt- KEEP 子句：Oracle 特有的聚合函数扩展，按指定顺序获取值
- RANK() vs DENSE_RANK()：前者允许并列但跳过排名（1,1,3），后者并列同排名（1,1,2），根据业务需求选择
3. 动态季度统计
- 通过EXTRACT(QUARTER FROM DATE)提取季度，配合DECODE转换为业务友好格式
- 可扩展为月份 / 年份统计，只需修改提取函数和条件映射
四、进阶功能扩展：时间维度与数据可视化
1. 按季度分组的完整统计（新增子查询）
-- 统计每个部门各季度的请假类型种类数
SELECT d.dept_name,lp.apply_quarter,COUNT(DISTINCT lp.leave_type) AS quarterly_type_count
FROM dept_hierarchy h
JOIN leave_preprocess lp ON h.dept_id = lp.dept_id
JOIN t_dept d ON h.root_dept_id = d.dept_id
GROUP BY d.dept_name, lp.apply_quarter
ORDER BY d.dept_id, lp.apply_quarter;2. 数据可视化建议
| 统计指标 | 推荐图表类型 | 业务价值 |
| 高频请假类型 | 柱状图 / 词云图 | 快速定位部门考勤痛点 |
| 季度趋势 | 折线图 / 面积图 | 识别假期申请高峰期 |
| 类型多样性 | 气泡图 / 热力图 | 评估部门考勤制度灵活性 |
五、与前作的技术差异对比
| 特性 | 前作（递归汇总） | 本文（深度分析） |
| 统计深度 | 数值聚合（求和 / 计数） | 分布分析（去重 / 排序 / TopN） |
| 维度扩展 | 固定指标（类型 / 状态） | 动态维度（时间 / 多样性） |
| 函数使用 | DECODE+SUM/COUNT | KEEP/RANK+COUNT(DISTINCT) |
| 业务价值 | 数据汇总 | 原因洞察（为什么某种类型最多） |
六、最佳实践：复杂统计的分层设计
1、分层 WITH 子句：将逻辑拆解为层级构建、数据清洗、核心统计、结果组装四层，提升可读性。
2、索引策略：
-- 加速递归关联
CREATE INDEX idx_hierarchy_dept ON dept_hierarchy(dept_id);
-- 加速类型统计
CREATE INDEX idx_leave_type ON t_leave(leave_type);3、异常处理：
对无数据部门使用LEFT JOIN确保显示
通过NVL(COUNT(DISTINCT ...), 0)处理空值
七、总结：从数据汇总到业务洞察的跨越
通过本次升级，我们实现了从 **“数据是什么”到“数据意味着什么”** 的进阶：
1、类型多样性揭示部门考勤复杂度，高频类型定位管理重点
2、时间维度分析帮助预判假期高峰，优化资源调配
3、分层 SQL 设计让复杂逻辑可维护，便于后续扩展（如加入员工职级、考勤制度差异等维度）
对于企业级 HR 系统、OA 平台等场景，这种深度统计能力能有效减少后端代码量（避免多次往返数据库），同时提供实时业务洞察。掌握DECODE+ 递归 + 高级聚合函数的组合，相当于掌握了 Oracle 数据分析的 “瑞士军刀”，能应对 80% 以上的树形结构统计需求。欢迎关注留言，后续会分享更多的经验，期待与您一起进步。