第四章练习答案

本页包含第四章所有练习的答案和详细解释。

01-window-basics

练习 1：基本窗口

SELECT product, amount,
    ROUND(amount / SUM(amount) OVER () * 100, 2) AS percentage
FROM orders;

练习 2：分区窗口

SELECT u.name, o.product, o.amount,
    SUM(o.amount) OVER (PARTITION BY o.user_id) AS user_total,
    AVG(o.amount) OVER (PARTITION BY o.user_id) AS user_avg
FROM orders o JOIN users u ON o.user_id = u.id;

练习 3：累计求和

SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative
FROM orders;

练习 4：分组累计

SELECT u.name, o.order_date, o.amount,
    SUM(o.amount) OVER (PARTITION BY o.user_id ORDER BY o.order_date) AS user_cumulative
FROM orders o JOIN users u ON o.user_id = u.id;

练习 5：多窗口

SELECT product, amount,
    SUM(amount) OVER (PARTITION BY user_id) AS user_total,
    SUM(amount) OVER () AS global_total
FROM orders;

---

02-ranking

练习 1：基本排名

SELECT product, amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
    RANK() OVER (ORDER BY amount DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM orders;

练习 2：Top-N

WITH ranked AS (
    SELECT u.name, o.product, o.amount,
        ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.amount DESC) AS rn
    FROM orders o JOIN users u ON o.user_id = u.id
)
SELECT name, product, amount FROM ranked WHERE rn = 1;

练习 3：去重

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY product ORDER BY order_date) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

练习 4：分页

WITH numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
    FROM orders
)
SELECT * FROM numbered WHERE rn BETWEEN 4 AND 6;

---

03-window-agg

练习 1：累计求和

SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative_sales
FROM orders;

练习 2：移动平均

SELECT order_date, amount,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS ma_5
FROM orders;

练习 3：环比增长

SELECT order_date, amount,
    LAG(amount) OVER (ORDER BY order_date) AS prev_day,
    ROUND((amount - LAG(amount) OVER (ORDER BY order_date)) / LAG(amount) OVER (ORDER BY order_date) * 100, 2) AS growth_pct
FROM orders;

练习 4：LAG/LEAD

SELECT order_date, amount,
    LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
    LEAD(amount) OVER (ORDER BY order_date) AS next_amount
FROM orders;

---

04-cte

练习 1：基本 CTE

WITH user_stats AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total
    FROM orders GROUP BY user_id
)
SELECT u.name, us.order_count, us.total
FROM users u JOIN user_stats us ON u.id = us.user_id
WHERE us.total > 100;

练习 2：多个 CTE

WITH user_totals AS (
    SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
),
top_20_pct AS (
    SELECT user_id FROM user_totals
    WHERE total >= (SELECT PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY total) FROM user_totals)
)
SELECT u.name, ut.total
FROM users u
JOIN user_totals ut ON u.id = ut.user_id
JOIN top_20_pct t ON u.id = t.user_id;

练习 3：替换子查询

-- 将嵌套子查询改为 CTE
WITH avg_amount AS (
    SELECT AVG(amount) AS avg FROM orders
)
SELECT * FROM orders, avg_amount WHERE orders.amount > avg_amount.avg;

---

05-recursive

练习 1：生成序列

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers;

练习 2：组织结构

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE id = 2  -- Bob 的所有下属
    UNION ALL
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

练习 3：路径追踪

WITH RECURSIVE paths AS (
    SELECT id, name, manager_id, name AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, p.path || ' > ' || e.name
    FROM employees e JOIN paths p ON e.manager_id = p.id
)
SELECT name, path FROM paths;

练习 4：查找上级

WITH RECURSIVE managers AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE id = 5  -- Eve
    UNION ALL
    SELECT e.id, e.name, e.manager_id, m.level + 1
    FROM employees e JOIN managers m ON e.id = m.manager_id
)
SELECT * FROM managers WHERE level > 0;

练习 5：深度限制

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e JOIN subordinates s ON e.manager_id = s.id
    WHERE s.level < 3
)
SELECT * FROM subordinates;

---

复习要点

窗口函数关键

• OVER() 创建窗口
• PARTITION BY 分区
• ORDER BY 排序
• 窗口帧：ROWS BETWEEN

排名函数区别

• ROW_NUMBER：唯一连续编号
• RANK：并列排名，有间隙
• DENSE_RANK：并列排名，无间隙

CTE 优势

• 提高可读性
• 避免重复计算
• 模块化查询

递归 CTE

• 锚点 + 递归部分
• 必须有终止条件
• 适合层级数据

返回第四章概览 · 返回SQL 学习路线