实战：高级特性综合
实战：高级特性综合
项目需求
本课将通过四个实战场景，综合运用高级 SQL 特性：
- 排名查询：使用窗口函数实现各类排名
- 累计计算：实现累计求和、移动平均
- 递归查询：使用 CTE 处理层级数据
- 复杂业务逻辑：结合事务处理批量业务操作
数据准备
首先确保我们有统一的四表数据库结构：
SQL
-- 部门表
CREATE TABLE IF NOT EXISTS departments (
id INTEGER PRIMARY KEY,
department_name TEXT NOT NULL,
manager_id INTEGER,
location TEXT
);
-- 员工表
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER,
position TEXT,
salary REAL,
hire_date TEXT,
manager_id INTEGER,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
-- 产品表
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
category TEXT,
price REAL,
stock_quantity INTEGER
);
-- 订单表
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
customer_name TEXT,
product_id INTEGER,
quantity INTEGER,
amount REAL,
order_date TEXT,
status TEXT,
FOREIGN KEY (product_id) REFERENCES products(id)
);
-- 插入测试数据
INSERT OR IGNORE INTO departments VALUES
(1, '技术部', 1, '北京'),
(2, '市场部', 4, '上海'),
(3, '财务部', 6, '北京');
INSERT OR IGNORE INTO employees VALUES
(1, '张三', 1, '高级工程师', 15000, '2020-01-15', NULL),
(2, '李四', 1, '中级工程师', 12000, '2021-03-20', 1),
(3, '王五', 1, '初级工程师', 8000, '2022-06-10', 1),
(4, '赵六', 2, '市场总监', 18000, '2019-05-01', NULL),
(5, '钱七', 2, '市场专员', 9000, '2021-08-15', 4),
(6, '孙八', 3, '财务经理', 16000, '2020-02-28', NULL),
(7, '周九', 3, '会计', 10000, '2021-11-05', 6),
(8, '吴十', 1, '实习工程师', 5000, '2023-07-01', 2);
INSERT OR IGNORE INTO products VALUES
(1, '笔记本电脑', '电子产品', 6999, 50),
(2, '无线鼠标', '电子产品', 199, 200),
(3, '机械键盘', '电子产品', 599, 100),
(4, '办公椅', '家具', 1299, 30),
(5, '显示器', '电子产品', 2499, 80);
INSERT OR IGNORE INTO orders VALUES
(1, '客户A', 1, 2, 13998, '2024-01-10', 'completed'),
(2, '客户B', 2, 5, 995, '2024-01-15', 'completed'),
(3, '客户A', 3, 3, 1797, '2024-02-01', 'completed'),
(4, '客户C', 1, 1, 6999, '2024-02-15', 'pending'),
(5, '客户B', 4, 2, 2598, '2024-03-01', 'completed'),
(6, '客户D', 5, 4, 9996, '2024-03-10', 'completed'),
(7, '客户A', 2, 10, 1990, '2024-03-15', 'completed'),
(8, '客户C', 3, 1, 599, '2024-04-01', 'cancelled');
实战1：排名查询
员工薪资排名
SQL
-- 各类排名查询
WITH salary_ranking AS (
SELECT
e.id,
e.name,
d.department_name,
e.salary,
e.hire_date,
-- 全公司薪资排名
RANK() OVER (ORDER BY e.salary DESC) AS company_rank,
-- 全公司薪资排名（无并列）
ROW_NUMBER() OVER (ORDER BY e.salary DESC) AS company_row_num,
-- 部门内薪资排名
RANK() OVER (
PARTITION BY e.department_id
ORDER BY e.salary DESC
) AS dept_rank,
-- 薪资百分比排名
PERCENT_RANK() OVER (ORDER BY e.salary DESC) AS percentile,
-- 薪资分组（分为4组）
NTILE(4) OVER (ORDER BY e.salary DESC) AS salary_quartile
FROM employees e
JOIN departments d ON e.department_id = d.id
)
SELECT
name,
department_name,
salary,
company_rank AS "公司排名",
dept_rank AS "部门排名",
CASE salary_quartile
WHEN 1 THEN '高薪组'
WHEN 2 THEN '中高薪组'
WHEN 3 THEN '中低薪组'
WHEN 4 THEN '低薪组'
END AS "薪资分组",
ROUND(percentile * 100, 1) || '%' AS "超过百分比"
FROM salary_ranking
ORDER BY company_rank;
产品销量排名
SQL
-- 产品销量和收入排名
WITH product_sales AS (
SELECT
p.id,
p.product_name,
p.category,
p.price,
COALESCE(SUM(o.quantity), 0) AS total_quantity,
COALESCE(SUM(o.amount), 0) AS total_revenue,
COUNT(o.id) AS order_count
FROM products p
LEFT JOIN orders o ON p.id = o.product_id AND o.status = 'completed'
GROUP BY p.id, p.product_name, p.category, p.price
)
SELECT
product_name AS "产品名称",
category AS "类别",
price AS "单价",
total_quantity AS "总销量",
total_revenue AS "总收入",
order_count AS "订单数",
RANK() OVER (ORDER BY total_revenue DESC) AS "收入排名",
RANK() OVER (
PARTITION BY category
ORDER BY total_quantity DESC
) AS "类内销量排名",
DENSE_RANK() OVER (ORDER BY total_quantity DESC) AS "销量排名"
FROM product_sales
ORDER BY total_revenue DESC;
实战2：累计计算
月度累计销售额
SQL
-- 月度销售额及累计计算
WITH monthly_sales AS (
SELECT
strftime('%Y-%m', order_date) AS month,
SUM(amount) AS monthly_total,
COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY strftime('%Y-%m', order_date)
)
SELECT
month AS "月份",
monthly_total AS "月销售额",
order_count AS "订单数",
-- 累计销售额
SUM(monthly_total) OVER (ORDER BY month) AS "累计销售额",
-- 累计订单数
SUM(order_count) OVER (ORDER BY month) AS "累计订单数",
-- 月环比增长率
ROUND(
(monthly_total - LAG(monthly_total) OVER (ORDER BY month))
/ LAG(monthly_total) OVER (ORDER BY month) * 100,
2
) AS "环比增长%",
-- 与上月差额
monthly_total - LAG(monthly_total) OVER (ORDER BY month) AS "与上月差额"
FROM monthly_sales
ORDER BY month;
移动平均计算
SQL
-- 订单金额的移动平均
WITH order_details AS (
SELECT
o.id,
o.order_date,
o.amount,
o.customer_name,
-- 3日移动平均
AVG(o.amount) OVER (
ORDER BY o.order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS avg_3day,
-- 5日移动平均
AVG(o.amount) OVER (
ORDER BY o.order_date
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
) AS avg_5day,
-- 累计平均
AVG(o.amount) OVER (ORDER BY o.order_date) AS cumulative_avg,
-- 最近订单金额
LAG(o.amount, 1) OVER (ORDER BY o.order_date) AS prev_amount,
-- 最大订单金额（历史到当前）
MAX(o.amount) OVER (ORDER BY o.order_date) AS running_max
FROM orders o
WHERE o.status = 'completed'
)
SELECT
order_date AS "订单日期",
amount AS "订单金额",
ROUND(avg_3day, 2) AS "3日移动平均",
ROUND(avg_5day, 2) AS "5日移动平均",
ROUND(cumulative_avg, 2) AS "累计平均",
running_max AS "历史最大金额"
FROM order_details
ORDER BY order_date;
实战3：递归查询
组织架构层级查询
SQL
-- 递归查询组织架构
WITH RECURSIVE org_hierarchy AS (
-- 锚点：顶级管理者（没有上级的员工）
SELECT
e.id,
e.name,
e.position,
e.manager_id,
0 AS level,
e.name AS path,
e.salary
FROM employees e
WHERE e.manager_id IS NULL
UNION ALL
-- 递归部分：查找下属
SELECT
e.id,
e.name,
e.position,
e.manager_id,
oh.level + 1,
oh.path || ' -> ' || e.name,
e.salary
FROM employees e
JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT
CASE level
WHEN 0 THEN ''
WHEN 1 THEN '├─ '
WHEN 2 THEN '│ ├─ '
ELSE '│ │ ├─ '
END || name AS "组织架构",
position AS "职位",
level AS "层级",
path AS "汇报路径",
salary AS "薪资"
FROM org_hierarchy
ORDER BY path;
部门层级统计
SQL
-- 统计每个部门的人员层级和薪资分布
WITH RECURSIVE dept_stats AS (
-- 基础查询：部门基本信息
SELECT
d.id,
d.department_name,
COUNT(e.id) AS employee_count,
COALESCE(SUM(e.salary), 0) AS total_salary,
COALESCE(AVG(e.salary), 0) AS avg_salary,
COALESCE(MAX(e.salary), 0) AS max_salary,
COALESCE(MIN(e.salary), 0) AS min_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.department_name
)
SELECT
department_name AS "部门",
employee_count AS "员工数",
ROUND(total_salary, 2) AS "薪资总额",
ROUND(avg_salary, 2) AS "平均薪资",
max_salary AS "最高薪资",
min_salary AS "最低薪资",
max_salary - min_salary AS "薪资差距",
-- 薪资分布
CASE
WHEN avg_salary > 15000 THEN '高薪部门'
WHEN avg_salary > 10000 THEN '中等薪资'
ELSE '基础薪资'
END AS "薪资等级"
FROM dept_stats
ORDER BY total_salary DESC;
实战4：复杂业务逻辑
批量订单处理（事务）
SQL
-- 使用事务处理批量订单
BEGIN TRANSACTION;
-- 保存点：方便回滚
SAVEPOINT before_process;
-- 1. 更新已完成订单的产品库存
UPDATE products
SET stock_quantity = stock_quantity - (
SELECT COALESCE(SUM(o.quantity), 0)
FROM orders o
WHERE o.product_id = products.id
AND o.status = 'completed'
AND o.order_date >= '2024-01-01'
)
WHERE id IN (
SELECT DISTINCT product_id
FROM orders
WHERE status = 'completed'
AND order_date >= '2024-01-01'
);
-- 2. 检查库存是否为负数
SELECT
CASE
WHEN MIN(stock_quantity) < 0 THEN 'ERROR'
ELSE 'OK'
END AS inventory_check
FROM products;
-- 3. 如果库存检查通过，生成月度报表
INSERT INTO monthly_report (month, total_revenue, total_orders)
SELECT
strftime('%Y-%m', order_date),
SUM(amount),
COUNT(*)
FROM orders
WHERE status = 'completed'
GROUP BY strftime('%Y-%m', order_date);
-- 提交事务
COMMIT;
-- 查看结果
SELECT * FROM products ORDER BY id;
客户价值分析
SQL
-- 综合分析客户价值
WITH customer_analysis AS (
SELECT
customer_name,
COUNT(*) AS order_count,
SUM(amount) AS total_spent,
AVG(amount) AS avg_order_value,
MIN(order_date) AS first_order,
MAX(order_date) AS last_order,
-- 计算客户生命周期（天）
julianday(MAX(order_date)) - julianday(MIN(order_date)) AS customer_lifetime,
-- 最近一次购买距今天数
julianday('now') - julianday(MAX(order_date)) AS days_since_last_order
FROM orders
WHERE status = 'completed'
GROUP BY customer_name
),
customer_rfm AS (
SELECT
*,
-- R值：最近购买评分（1-5，5最近）
CASE
WHEN days_since_last_order <= 30 THEN 5
WHEN days_since_last_order <= 60 THEN 4
WHEN days_since_last_order <= 90 THEN 3
WHEN days_since_last_order <= 180 THEN 2
ELSE 1
END AS r_score,
-- F值：购买频率评分
CASE
WHEN order_count >= 5 THEN 5
WHEN order_count >= 3 THEN 4
WHEN order_count >= 2 THEN 3
WHEN order_count >= 1 THEN 2
ELSE 1
END AS f_score,
-- M值：消费金额评分
NTILE(5) OVER (ORDER BY total_spent) AS m_score
FROM customer_analysis
)
SELECT
customer_name AS "客户",
order_count AS "订单数",
ROUND(total_spent, 2) AS "总消费",
ROUND(avg_order_value, 2) AS "平均客单价",
first_order AS "首次购买",
last_order AS "最近购买",
r_score AS "R评分",
f_score AS "F评分",
m_score AS "M评分",
r_score + f_score + m_score AS "RFM总分",
CASE
WHEN r_score + f_score + m_score >= 12 THEN 'VIP客户'
WHEN r_score + f_score + m_score >= 9 THEN '重要客户'
WHEN r_score + f_score + m_score >= 6 THEN '普通客户'
ELSE '低价值客户'
END AS "客户等级"
FROM customer_rfm
ORDER BY r_score + f_score + m_score DESC;
薪资带分析
SQL
-- 薪资带分析报告
WITH salary_bands AS (
SELECT
e.id,
e.name,
d.department_name,
e.salary,
e.hire_date,
-- 计算工作年限
(julianday('now') - julianday(e.hire_date)) / 365.25 AS years_employed,
-- 薪资带分类
CASE
WHEN e.salary >= 15000 THEN 'Band A (15000+)'
WHEN e.salary >= 12000 THEN 'Band B (12000-14999)'
WHEN e.salary >= 8000 THEN 'Band C (8000-11999)'
WHEN e.salary >= 5000 THEN 'Band D (5000-7999)'
ELSE 'Band E (<5000)'
END AS salary_band
FROM employees e
JOIN departments d ON e.department_id = d.id
)
SELECT
salary_band AS "薪资带",
COUNT(*) AS "员工数",
ROUND(AVG(salary), 2) AS "平均薪资",
ROUND(AVG(years_employed), 1) AS "平均工龄",
MIN(salary) AS "最低薪资",
MAX(salary) AS "最高薪资",
GROUP_CONCAT(name, ', ') AS "员工列表"
FROM salary_bands
GROUP BY salary_band
ORDER BY salary_band;
❓ 常见问题
Q：窗口函数和 GROUP BY 有什么区别？ A： GROUP BY 会将多行合并为一行（聚合），而窗口函数保留所有原始行，只是在每行上添加计算结果。窗口函数适合需要同时查看明细和汇总的场景。
Q：递归 CTE 有深度限制吗？ A： SQLite 默认递归深度为1000，可以通过
sqlite3_limit(db, SQLITE_LIMIT_RECURSION_DEPTH, n)调整。过深的递归可能导致性能问题，建议优化查询逻辑。
Q：事务中发生错误会自动回滚吗？ A： 不会自动回滚。需要显式使用 ROLLBACK 语句，或者使用 SAVEPOINT 和 ROLLBACK TO SAVEPOINT 进行部分回滚。SQLite 中如果使用 BEGIN...COMMIT，遇到错误需要手动回滚。
Q：如何优化复杂查询的性能？ A： 1) 使用 EXPLAIN QUERY PLAN 分析执行计划；2) 为常用查询字段创建索引；3) 避免在 WHERE 子句中对列使用函数；4) 合理使用 CTE 提高可读性；5) 对于频繁查询的结果，考虑创建视图。
📖 小节
本课我们通过实战练习掌握了：
- 使用窗口函数实现各类排名查询
- 累计计算和移动平均的实现方法
- 递归 CTE 处理层级数据的技巧
- 复杂业务逻辑中的事务处理
- 客户价值分析（RFM模型）
- 薪资带分析等高级查询技巧
📝 作业
-
排名练习：编写查询，找出每个部门中工龄最长的前2名员工，显示其姓名、部门、入职日期和工龄。
-
累计计算：编写查询，计算每个产品类别的月度销售额，并显示：
- 月度销售额
- 累计销售额
- 月环比增长率
- 该类别销售额占当月总销售额的百分比
-
递归查询：假设产品有分类层级（电子产品 -> 电脑配件 -> 鼠标），设计一个分类表并编写递归查询，显示完整的分类路径。
-
综合实战：设计一个"员工绩效评估"查询，综合以下指标：
- 销售业绩（关联订单表）
- 工龄
- 薪资在部门内的排名
- 给出综合评分和建议
