在《SQL Server 进阶：递归 CTE+CASE WHEN 实现复杂树形统计(第二课)》基础上，我们进一步探索部门级请假数据的深度分析能力，新增两大核心功能：
1、类型多样性统计：计算每个部门（含下级）的不重复请假类型数量
2、高频请假类型识别：找出各部门中出现次数最多的请假类型（支持并列情况）
3、时间维度扩展：按自然季度统计各指标，实现数据趋势分析
通过递归 CTE、CASE WHEN与高级聚合函数的深度组合，完成从数据汇总到业务洞察的跨越。
一、业务需求升级：多样性分析与时间维度
核心分析目标
统计维度 |
具体需求说明 |
SQL Server 实现要点 |
类型种类数 |
统计不重复请假类型的数量 |
COUNT(DISTINCT)结合递归分组 |
高频请假类型 |
出现次数最多的类型（支持并列） |
窗口函数排序 + 子查询筛选 Top1 |
季度趋势 |
按 Q1-Q4 统计各维度指标的分布情况 |
DATEPART(QUARTER)提取季度 + 动态列生成 |
补充测试数据（新增不同类型和季度分布）
INSERT INTO t_leave VALUES
('U007', '郑九', 4, 2.0, '调休', '完成', '2025-04-15'), -- Q2调休
('U008', '陈十', 3, 4.0, '年假', '进行中', '2025-07-20'), -- Q3年假
('U009', '吴十一', 5, 3.0, '事假', '完成', '2025-06-30'), -- Q2事假
('U010', '周十二', 2, 1.5, '病假', '进行中', '2025-08-05'); -- Q3病假二、关键技术突破：多样性统计与 TopN 分析
1. 类型种类数：COUNT(DISTINCT)层级化应用
-- 基础语法：统计单个部门的不重复类型数
COUNT(DISTINCT leave_type) AS total_leave_types
-- 层级化实现：在递归分组中去重计数
SELECT
ancestor_id,
COUNT(DISTINCT tl.leave_type) AS type_count
FROM dept_ancestor da
LEFT JOIN t_leave tl ON da.dept_id = tl.dept_id
GROUP BY ancestor_id2. 高频类型提取：窗口函数 + 子查询
-- 核心逻辑：按部门分组，计算类型出现次数并排序
WITH type_rank AS (
SELECT
da.ancestor_id,
tl.leave_type,
COUNT(*) AS type_count,
RANK() OVER (PARTITION BY da.ancestor_id ORDER BY COUNT(*) DESC) AS rnk -- 处理并列用DENSE_RANK()
FROM dept_ancestor da
LEFT JOIN t_leave tl ON da.dept_id = tl.dept_id
GROUP BY da.ancestor_id, tl.leave_type
)
SELECT
ancestor_id,
leave_type AS most_frequent_type
FROM type_rank
WHERE rnk = 1 -- 提取排名第一的类型（支持多条并列记录）3. 季度动态统计：DATEPART+CASE WHEN
-- 提取季度并转换为中文描述
CASE DATEPART(QUARTER, apply_time)
WHEN 1 THEN '第一季度'
WHEN 2 THEN '第二季度'
WHEN 3 THEN '第三季度'
WHEN 4 THEN '第四季度'
ELSE '未知季度'
END AS apply_quarter三、终极 SQL：全维度深度分析实现
完整分层 SQL 架构
WITH
-- 1. 构建部门层级（含所有祖先-后代关系）
dept_hierarchy AS (
SELECT
dept_id,
parent_dept_id,
dept_id AS root_dept_id -- 根部门ID（用于最终分组）
FROM t_dept
UNION ALL
SELECT
d.dept_id,
d.parent_dept_id,
dh.root_dept_id
FROM t_dept d
JOIN dept_hierarchy dh ON d.parent_dept_id = dh.dept_id
),
-- 2. 预处理请假数据（含季度提取）
leave_preprocess AS (
SELECT
dept_id,
leave_type,
leave_status,
leave_days,
DATEPART(QUARTER, apply_time) AS apply_quarter -- 提取季度（1-4）
FROM t_leave
),
-- 3. 核心统计层：计算类型排名与季度分布
core_statistics AS (
SELECT
h.root_dept_id,
-- 类型多样性
COUNT(DISTINCT lp.leave_type) AS total_leave_types,
-- 高频类型（取排名第一的类型，支持并列）
MAX(CASE WHEN tr.rnk = 1 THEN lp.leave_type END) AS most_frequent_type,
-- 季度统计（以Q2、Q3为例）
SUM(CASE WHEN lp.apply_quarter = 2 THEN 1 ELSE 0 END) AS q2_apply_count,
SUM(CASE WHEN lp.apply_quarter = 3 THEN 1 ELSE 0 END) AS q3_apply_count
FROM dept_hierarchy h
LEFT 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 rnk
FROM leave_preprocess
GROUP BY dept_id, leave_type
) tr ON h.dept_id = tr.dept_id AND lp.leave_type = tr.leave_type
GROUP 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添加索引，提升去重效率
CREATE INDEX idx_leave_type ON t_leave(leave_type);2. 高频类型处理（并列情况）
- 使用RANK()或DENSE_RANK()窗口函数排序，RANK()会跳过并列排名（如 1,1,3），DENSE_RANK()则为连续排名（1,1,2）
- 通过子查询筛选rnk = 1的记录，支持返回多个并列类型（需调整 SELECT 为返回所有并列行）
3. 季度动态扩展
- DATEPART(QUARTER, date)直接提取季度，配合CASE WHEN生成业务友好的季度列
- 可扩展为月份统计：DATEPART(MONTH, apply_time)，或年份统计：YEAR(apply_time)
四、进阶功能扩展：动态透视与存储过程
1. 按季度透视表（PIVOT 操作）
SELECT
d.dept_name AS 部门名称,
[1] AS Q1次数,
[2] AS Q2次数,
[3] AS Q3次数,
[4] AS Q4次数
FROM (
SELECT
h.root_dept_id,
DATEPART(QUARTER, lp.apply_time) AS qtr,
COUNT(*) AS apply_count
FROM dept_hierarchy h
LEFT JOIN t_leave lp ON h.dept_id = lp.dept_id
GROUP BY h.root_dept_id, DATEPART(QUARTER, lp.apply_time)
) src
PIVOT (
SUM(apply_count) FOR qtr IN ([1], [2], [3], [4])
) pvt
ORDER BY root_dept_id;2. 存储过程封装（自动生成季度报表）
CREATE OR ALTER PROCEDURE sp_generate_quarter_report
@year INT = 2025
AS
BEGIN
WITH quarterly_data AS (
SELECT
d.dept_name,
DATEPART(QUARTER, apply_time) AS qtr,
SUM(leave_days) AS total_days
FROM t_leave tl
JOIN t_dept d ON tl.dept_id = d.dept_id
WHERE YEAR(apply_time) = @year
GROUP BY d.dept_name, DATEPART(QUARTER, apply_time)
)
SELECT
dept_name,
MAX(CASE WHEN qtr = 1 THEN total_days END) AS Q1,
MAX(CASE WHEN qtr = 2 THEN total_days END) AS Q2,
MAX(CASE WHEN qtr = 3 THEN total_days END) AS Q3,
MAX(CASE WHEN qtr = 4 THEN total_days END) AS Q4
FROM quarterly_data
GROUP BY dept_name;
END;
-- 调用存储过程
EXEC sp_generate_quarter_report @year = 2025;五、与前作的技术差异对比
特性 |
第二课（递归汇总） |
本课（深度分析） |
统计深度 |
数值聚合（求和 / 计数） |
分布分析（去重 / 排序 / TopN） |
维度扩展 |
固定指标（类型 / 状态） |
动态维度（时间 / 多样性） |
函数使用 |
CASE WHEN+SUM |
COUNT(DISTINCT)+ 窗口函数 |
业务价值 |
数据汇总 |
原因洞察（如为何年假申请最多） |
六、最佳实践：复杂统计的性能优化
1. 索引优化策略
-- 加速递归关联：为parent_dept_id添加索引
CREATE INDEX idx_dept_parent ON t_dept(parent_dept_id);
-- 加速时间维度统计：为apply_time添加索引
CREATE INDEX idx_leave_apply_time ON t_leave(apply_time);2. 处理递归深度限制
SQL Server 默认递归 CTE 最大层数为 100，超过时需显式设置：
OPTION (MAXRECURSION 500); -- 设置最大递归层数为5003. 避免相关子查询
优先使用 JOIN 替代子查询，例如将高频类型的EXISTS子查询改写为窗口函数，提升执行效率。
七、总结：从数据统计到业务洞察的跨越
通过本次升级，SQL Server 实现了从 **“数据汇总”到“数据洞察”** 的进阶：
1、类型多样性反映部门考勤制度的灵活性，高频类型定位管理重点（如年假占比过高可能需优化假期政策）
2、季度趋势分析帮助预判假期高峰，辅助资源调配（如 Q3 申请量突增时提前储备人力）
3、分层 CTE 设计让复杂逻辑可维护，便于后续扩展（如加入员工职级、部门绩效等维度）
对于 HR 系统、OA 平台等企业级应用，这种深度统计能力能显著减少后端代码量，提升数据响应速度。掌握递归 CTE、CASE WHEN与窗口函数的组合用法，相当于掌握了 SQL Server 树形数据处理的核心技巧，可应对多数复杂业务场景。后续可进一步探索 XML/JSON 数据处理、机器学习集成等高级功能，持续挖掘数据库的潜力。