当前位置： 首页 > news >正文

Oracle 树形统计再进阶：类型多样性与高频类型分析(第三课)

news 2025/7/1 15:31:38

在《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 cnt

3. 季度动态统计：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% 以上的树形结构统计需求。欢迎关注留言，后续会分享更多的经验，期待与您一起进步。

http://www.lqws.cn/news/587377.html

相关文章：

  • Monad：函数式编程中的 “容器模式”
  • 六自由度按摩机器人 MATLAB 仿真
  • Openssl升级
  • SQL规范
  • FastAPI 学习（二）
  • 在Flutter中生成App Bundle并上架Google Play
  • android BottomSheet及AlertDialog的几种material3 常见ui的用法
  • WSL/Linux 常用命令速查
  • Windows 11 安装 Linux 系统详细教程
  • docker安装RabbitMQ，创建RabbitMQ容器以及docker-compose.yml配置
  • 博图SCL编程：结构体（STRUCT）使用详解与实战案例
  • 英国研究团队启动合成完整人类基因组的前沿项目
  • 解决VSCode打开最近项目后终端shell不正常的问题
  • 数据结构入门-图的基本概念与存储结构
  • 数据结构与算法分析课设：一元多项式求值
  • STM32-第一节-新建工程，GPIO，点亮LED，蜂鸣器
  • 零成本接入+企业级部署：2025年AI大模型实战指南
  • 某只股票量化对冲策略计算绘图
  • 利用不坑盒子的Copilot，快速排值班表
  • JSON-LD 开发手册
  • 探索 AI 系统提示与模型资源库：`system-prompts-and-models-of-ai-tools`
  • 门控循环单元（GRU）：LSTM 的轻量级高效 “记忆专家”
  • Android Liunx ffmpeg交叉编译
  • 自己电脑搭建本地服务器并实现公网访问，内网也能提供互联网连接使用
  • 零基础学土壤物理建模｜Hydrus2D、Hydrus3D实操教程+参数设置技巧
  • 【算法】动态规划 70： 爬楼梯
  • ue xr 系统
  • 飞算 JavaAI 深度实战：从老项目重构到全栈开发的降本增效密码
  • 【Spring AI】 1接入 Ollama实践
  • 周赛98补题