第3章 高级 SQL 与编程对象 ​

-- 非递归 CTE：把「先算活跃用户，再算他们的订单额」分步命名，可读性远超嵌套子查询
WITH active_users AS (
    SELECT id, name FROM t_user WHERE last_login > now() - INTERVAL '30 days'
),
user_amount AS (
    SELECT o.user_id, SUM(o.amount) AS total
    FROM t_order o
    JOIN active_users a ON a.id = o.user_id
    GROUP BY o.user_id
)
SELECT name, total FROM user_amount ORDER BY total DESC LIMIT 20;

WITH RECURSIVE dept_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM department WHERE parent_id IS NULL          -- 锚点：根节点
    UNION ALL
    SELECT d.id, d.name, d.parent_id, t.level + 1    -- 递归：子节点
    FROM department d
    JOIN dept_tree t ON d.parent_id = t.id
)
SELECT repeat('  ', level - 1) || name AS tree, level FROM dept_tree ORDER BY path;

SELECT
    user_id,
    amount,
    created,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,   -- 组内排名
    RANK()       OVER (PARTITION BY user_id ORDER BY amount DESC) AS rk,   -- 并列跳号
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS drk,  -- 并列不跳号
    SUM(amount)  OVER (PARTITION BY user_id ORDER BY created
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total  -- 累计
FROM t_order;

-- 条件聚合 FILTER：一行内同时统计「已支付」和「全部」（MySQL 要写一堆 CASE WHEN）
SELECT
    user_id,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid_cnt,
    SUM(amount)                              AS all_amount
FROM t_order GROUP BY user_id;

SELECT u.id, u.name, recent.*
FROM t_user u
JOIN LATERAL (
    SELECT o.order_id, o.amount, o.created
    FROM t_order o
    WHERE o.user_id = u.id            -- ← 这里能引用左侧 u 的每一行，这就是 LATERAL 的威力
    ORDER BY o.created DESC
    LIMIT 3
) recent ON TRUE;                      -- ON TRUE：LATERAL 配合的惯用连接条件

SELECT
    category,
    EXTRACT(YEAR FROM created) AS yr,
    SUM(amount) AS total
FROM t_order
GROUP BY GROUPING SETS (
    (category, yr),   -- 明细级
    (category),       -- 按分类小计
    (yr),             -- 按年份小计
    ()                -- 总计
)
ORDER BY category, yr;

-- 普通视图：保存的查询，每次访问实时执行（和 MySQL 视图一样）
CREATE VIEW v_active_order AS SELECT * FROM t_order WHERE status <> 'cancelled';

-- 物化视图：把结果"落盘"存一份，访问快，但数据是快照，需手动/定时刷新
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', created) AS day, category, SUM(amount) AS total
FROM t_order GROUP BY 1, 2;

REFRESH MATERIALIZED VIEW mv_daily_sales;                       -- 全量刷新（会短暂锁读）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;          -- 并发刷新（需视图有唯一索引）

CREATE OR REPLACE FUNCTION add_tax(price numeric, rate numeric)
RETURNS numeric AS $$
BEGIN
    RETURN price * (1 + rate);
END;
$$ LANGUAGE plpgsql;

SELECT add_tax(100, 0.13);   -- 直接在 SQL 里用，13.00

CREATE OR REPLACE PROCEDURE p_archive_order(p_before date)
LANGUAGE plpgsql AS $$
DECLARE
    v_cnt int;                         -- 变量集中在 DECLARE 段声明（对应 MySQL 的 BEGIN...END 内 DECLARE）
BEGIN
    DELETE FROM t_order WHERE created < p_before;   -- 批量归档删除
    GET DIAGNOSTICS v_cnt = ROW_COUNT;              -- 受影响行数（对应 MySQL 的 ROW_COUNT()）
    RAISE NOTICE '归档了 % 行', v_cnt;  -- 打印日志（%s/% 占位，对应 MySQL 的 SELECT 输出调试）
END;
$$;

CALL p_archive_order('2025-01-01');

CREATE OR REPLACE FUNCTION top_users(n int)
RETURNS TABLE(user_id int, total numeric) AS $$
BEGIN
    RETURN QUERY
    SELECT o.user_id, SUM(o.amount)
    FROM t_order o GROUP BY o.user_id ORDER BY 2 DESC LIMIT n;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM top_users(10);   -- 像查表一样调用

-- 需求：t_user 每次更新自动刷新 updated_at
CREATE OR REPLACE FUNCTION trg_touch_updated()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();       -- 行级触发器用 NEW/OLD 引用新旧行（同 MySQL）
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_before_update
BEFORE UPDATE ON t_user
FOR EACH ROW
EXECUTE FUNCTION trg_touch_updated();     -- MySQL: EXECUTE PROCEDURE / BEGIN...END 内联；PG: EXECUTE FUNCTION 调独立函数

-- generate_series：造测试数据、生成日期序列，MySQL 无此原生函数
SELECT generate_series(1, 5);                        -- 输出 1..5 五行
SELECT date_trunc('day', d)::date AS day
FROM generate_series('2026-01-01'::date, '2026-01-31'::date, '1 day') AS d;  -- 一整月日期

-- 造 10 万行测试数据（配合第6章性能实验）
INSERT INTO t_order(user_id, amount, created)
SELECT (random()*1000)::int,
       round((random()*1000)::numeric, 2),
       now() - (random() || ' days')::interval
FROM generate_series(1, 100000);

CREATE EXTENSION IF NOT EXISTS pg_trgm;    -- 装模糊匹配扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE TABLE t_3x_comment (
  id        int PRIMARY KEY,
  parent_id int REFERENCES t_3x_comment(id),   -- 自引用外键，顶层为 NULL
  body      text NOT NULL
);

INSERT INTO t_3x_comment(id, parent_id, body) VALUES
  (1, NULL, '活动预告'),                       -- 根 A
  (2, 1,    '问：报名截止哪天'),                 -- A 的子
  (3, 2,    '答：本周五 18:00'),                 -- A 的孙（最深一层）
  (4, 1,    '顶一个'),                           -- A 的另一个子
  (5, 4,    '回复顶帖：你也来啦'),               -- A 的孙
  (6, NULL, '发票问题'),                         -- 根 B
  (7, 6,    '我的还没收到');                     -- B 的子

WITH RECURSIVE tree AS (
    -- ① 锚点：根节点（parent_id 为 NULL），路径初始化为自己的 id
    SELECT id, parent_id, body, 1 AS level, ARRAY[id] AS path
      FROM t_3x_comment
     WHERE parent_id IS NULL

    UNION ALL

    -- ② 递归：用上一轮结果 tree 去 join 未访问的子行，层级 +1、路径追加
    SELECT c.id, c.parent_id, c.body, t.level + 1, t.path || c.id
      FROM t_3x_comment c
      JOIN tree t ON c.parent_id = t.id
)
SELECT repeat('│   ', level - 1) || '├─ ' || body AS floor_tree,
       level, path
  FROM tree
 ORDER BY path;

-- 把上面的 CTE 包一层即可：
SELECT count(*) AS total, max(level) AS max_depth FROM tree;   -- → 7 | 3
-- 与 SELECT count(*) FROM t_3x_comment; → 7 一致，无丢行；3 = 活动预告→问→答 这条链长

-- ① 忘了写 RECURSIVE：
WITH tree AS (SELECT ... UNION ALL SELECT ... FROM t_3x_comment c JOIN tree t ON ...) ...
-- ERROR:  relation "tree" does not exist        （SQLSTATE 42P01，PG 还不认识自己）

-- ② 锚点与递归部分列数不一致：
-- ERROR:  each UNION query must have the same number of columns

-- ③ 数据里有环（1 的子是 2、2 的子又是 1）时会无限递归：
-- ERROR:  infinite recursion detected

WITH RECURSIVE tree AS (
    SELECT id, parent_id, body, 1 AS level, ARRAY[id] AS path
      FROM t_3x_comment WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.body, t.level + 1, t.path || c.id
      FROM t_3x_comment c JOIN tree t ON c.parent_id = t.id
) CYCLE id SET is_cycle USING path           -- 同一个 id 再出现就标 is_cycle，不再往下展
SELECT level, body, is_cycle FROM tree ORDER BY path;

CREATE TABLE t_3x_sale (id serial PRIMARY KEY, category text, sku text, qty int);
INSERT INTO t_3x_sale(category, sku, qty) VALUES
  ('手机', 'A1', 100), ('手机', 'A2',  90), ('手机', 'A3',  95),   -- 手机取 A1(100) A3(95)
  ('电脑', 'B1',  60), ('电脑', 'B2',  70),                         -- 电脑取 B2(70) B1(60)
  ('耳机', 'C1',  50);                                              -- 耳机只有 1 个，照出

WITH cat AS (SELECT DISTINCT category FROM t_3x_sale)
SELECT c.category, t.sku, t.qty
  FROM cat c
  JOIN LATERAL (
        SELECT s.sku, s.qty
          FROM t_3x_sale s
         WHERE s.category = c.category      -- ★ 引用左行，普通子查询做不到
         ORDER BY s.qty DESC
         LIMIT 2
       ) t ON TRUE                          -- ★ ON TRUE 是 LATERAL 的惯用连接条件
 ORDER BY c.category, t.qty DESC;

SELECT category, sku, qty FROM (
  SELECT s.*, ROW_NUMBER() OVER (PARTITION BY category ORDER BY qty DESC) rn FROM t_3x_sale s
) x WHERE rn <= 2 ORDER BY category, qty DESC;   -- 输出与第 3 步完全一致

CREATE INDEX ON t_3x_sale(category, qty DESC);
EXPLAIN (COSTS OFF)
WITH cat AS (SELECT DISTINCT category FROM t_3x_sale)
SELECT c.category, t.sku, t.qty FROM cat c
  JOIN LATERAL (SELECT s.sku, s.qty FROM t_3x_sale s
                 WHERE s.category = c.category ORDER BY s.qty DESC LIMIT 2) t ON TRUE;

CREATE TABLE t_3x_ord (id serial PRIMARY KEY, category text NOT NULL,
                       amount numeric(10,2) NOT NULL, created timestamptz NOT NULL);
INSERT INTO t_3x_ord(category, amount, created) VALUES
  ('手机', 100.00, '2026-09-01 10:00+08'),
  ('手机',  50.00, '2026-09-01 15:00+08'),
  ('电脑', 200.00, '2026-09-01 09:00+08'),
  ('手机',  80.00, '2026-09-02 10:00+08'),
  ('电脑', 120.00, '2026-09-02 11:00+08');
-- 手算预期：09-01 手机 2 单 150.00、电脑 1 单 200.00；09-02 手机 1 单 80.00、电脑 1 单 120.00（共 4 组）

CREATE MATERIALIZED VIEW mv_category_sales AS
SELECT date_trunc('day', created)::date AS day,
       category,
       count(*)        AS ord_cnt,
       sum(amount)     AS total
  FROM t_3x_ord
 GROUP BY 1, 2;

SELECT * FROM mv_category_sales ORDER BY day, category;
--    day       | category | ord_cnt | total
-- -------------+----------+---------+--------
--  2026-09-01  | 手机     |       2 | 150.00
--  2026-09-01  | 电脑     |       1 | 200.00
--  2026-09-02  | 手机     |       1 |  80.00
--  2026-09-02  | 电脑     |       1 | 120.00
-- 总计 150+200+80+120 = 550.00，与 SELECT sum(amount) FROM t_3x_ord; 一致 ✓

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_category_sales;
-- ERROR:  cannot refresh materialized view "public.mv_category_sales" concurrently:
--         rel has no UNIQUE index that can be used

CREATE UNIQUE INDEX ON mv_category_sales(day, category);

INSERT INTO t_3x_ord(category, amount, created) VALUES ('电脑', 30.00, '2026-09-02 18:00+08');
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_category_sales;

SELECT * FROM mv_category_sales WHERE day = '2026-09-02' ORDER BY category;
--    day       | category | ord_cnt | total
-- -------------+----------+---------+--------
--  2026-09-02  | 手机     |       1 |  80.00    ← 没变
--  2026-09-02  | 电脑     |       2 | 150.00    ← 120+30，只有这一组被重算

SELECT n_tup_ins, n_tup_del, n_live_tup
  FROM pg_stat_user_tables WHERE relname = 'mv_category_sales';

-- ① 先重置计数，再走全量刷新：REFRESH MATERIALIZED VIEW mv_category_sales;
--    → 整表重建：旧 4 行全删、新 4 行全插，n_tup_ins / n_tup_del 至少各 +4
-- ② 再重置，走并发刷新：REFRESH MATERIALIZED VIEW CONCURRENTLY mv_category_sales;
--    → 只动 diff：没变化的行物理上原封不动，只有改过的那一组 1 增 1 删

-- 会话 A：灌到 500 万行，让刷新真的需要几秒
INSERT INTO t_3x_ord(category, amount, created)
SELECT (ARRAY['手机','电脑','耳机'])[1 + (random()*2)::int],
       round((random()*500)::numeric, 2),
       '2026-09-01 00:00+08'::timestamptz + (random() || ' days')::interval
  FROM generate_series(1, 5000000);

-- 会话 A：REFRESH MATERIALIZED VIEW mv_category_sales;      -- 全量
-- 会话 B（同时）：SELECT count(*) FROM mv_category_sales;    -- ← 挂住，等 A 刷完才返回

-- 会话 A：REFRESH MATERIALIZED VIEW CONCURRENTLY mv_category_sales;   -- 并发
-- 会话 B（同时）：SELECT count(*) FROM mv_category_sales;    -- ← 立即返回旧快照，不阻塞

-- MySQL 原版
DELIMITER //
CREATE PROCEDURE p_settle(IN p_user BIGINT, OUT p_total DECIMAL(10,2))
BEGIN
  DECLARE v_amt DECIMAL(10,2);
  DECLARE v_cnt INT;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION          -- ③ 异常处理靠 HANDLER
  BEGIN
    ROLLBACK;
    SET p_total = -1;
  END;

  START TRANSACTION;
  SELECT SUM(amount), COUNT(*) INTO v_amt, v_cnt
    FROM t_order WHERE user_id = p_user AND status = 'paid';
  IF v_amt IS NULL THEN SET v_amt = 0; END IF;
  WHILE v_cnt > 0 DO
    SET v_cnt = v_cnt - 1;                       -- ② SET 赋值
  END WHILE;
  IF (SELECT COUNT(*) FROM t_user WHERE id = p_user) = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'user not found';
  END IF;
  UPDATE t_user SET balance = balance + v_amt WHERE id = p_user;
  SET p_total = v_amt;
  COMMIT;
END //
DELIMITER ;
CALL p_settle(1, @t); SELECT @t;

CREATE TABLE t_3x_user (id serial PRIMARY KEY, name text, balance numeric(10,2) NOT NULL DEFAULT 0);
INSERT INTO t_3x_user(id, name, balance) VALUES (1, 'amy', 100.00), (2, 'bob', 0.00);

CREATE TABLE t_3x_order (id serial PRIMARY KEY, user_id int, amount numeric(10,2), status text);
INSERT INTO t_3x_order(user_id, amount, status) VALUES
  (1, 50.00, 'paid'), (1, 30.00, 'paid'), (1, 99.00, 'unpaid'), (2, 10.00, 'paid');
-- 手算预期：amy 已付 = 50+30 = 80.00（2 单，unpaid 的 99.00 不算），结算后余额 100+80 = 180.00

CREATE OR REPLACE PROCEDURE p_settle(p_user int)
LANGUAGE plpgsql
AS $proc$                                        -- 不需 DELIMITER，$$ / $tag$ 就是定界
DECLARE
  v_amt numeric(10,2) := 0;                     -- ① 变量全部集中在 BEGIN 之前的 DECLARE 段，还能给初值
  v_cnt int := 0;
BEGIN
  IF NOT EXISTS (SELECT 1 FROM t_3x_user WHERE id = p_user) THEN
    RAISE EXCEPTION '用户 % 不存在', p_user;      -- 对应 SIGNAL SQLSTATE '45000'
  END IF;

  SELECT coalesce(sum(amount), 0), count(*)
    INTO v_amt, v_cnt                           -- INTO 直接落到声明变量，不用先建临时结果集
    FROM t_3x_order
   WHERE user_id = p_user AND status = 'paid';

  WHILE v_cnt > 0 LOOP                          -- MySQL：WHILE ... DO ... END WHILE
    v_cnt := v_cnt - 1;                         -- ② 赋值用 :=（不是 SET x =）
  END LOOP;

  UPDATE t_3x_user SET balance = balance + v_amt WHERE id = p_user;
  GET DIAGNOSTICS v_cnt = ROW_COUNT;            -- 对应 MySQL 的 ROW_COUNT()
  RAISE NOTICE '用户 % 结算 % 元', p_user, v_amt;

  COMMIT;                                       -- 过程里可以自己控制提交（PG11+）；函数里绝对不行
EXCEPTION WHEN OTHERS THEN                      -- ③ 独立的 EXCEPTION 块（不是 HANDLER）
  RAISE NOTICE '结算失败（已自动回滚）：%', SQLERRM;
END;
$proc$;

CALL p_settle(1);
-- NOTICE:  用户 1 结算 80.00 元
-- CALL

SELECT id, name, balance FROM t_3x_user ORDER BY id;
--  id | name | balance
-- ----+------+---------
--   1 | amy  |  180.00     ← 100.00 + 80.00，与第 2 步手算一致 ✓
--   2 | bob  |    0.00     ← 未被触碰

CALL p_settle(999);
-- NOTICE:  结算失败（已自动回滚）：用户 999 不存在
-- CALL

SELECT count(*) FROM t_3x_user WHERE id = 999;    -- → 0，数据没脏

-- ① psql 里 CALL 拿不到 OUT 参数的值（MySQL 习惯用 OUT + @var 接）。两种解法：
--    a) 过程序只做事，结果靠 RAISE NOTICE 或写进结果表；
--    b) 要「返回值」就改成函数，直接当表达式用（这是 MySQL 里做不到的写法）：
CREATE OR REPLACE FUNCTION f_settle(p_user int) RETURNS numeric
LANGUAGE plpgsql AS $fn$
DECLARE v_amt numeric(10,2);
BEGIN
  SELECT coalesce(sum(amount), 0) INTO v_amt FROM t_3x_order
   WHERE user_id = p_user AND status = 'paid';
  RETURN v_amt;
END;
$fn$;

SELECT id, name, balance + f_settle(id) AS after_settle FROM t_3x_user ORDER BY id;
--  id | name | after_settle
-- ----+------+--------------
--   1 | amy  |       180.00
--   2 | bob  |        10.00      ← 函数像列一样嵌在 SELECT 里，一条 SQL 算完全表

-- ② 函数里写 COMMIT/ROLLBACK 会直接报错：
-- ERROR:  transaction control statements are not allowed within a function
--    （过程可以，函数不可以——因为函数可能被外层语句/查询包着跑）