4.1.3 PostgreSQL 深度 · vs MySQL + CTE + 窗口函数 + JSONB

-- 订单 + 客户 + 商品 三表关联,用 CTE 分步写
WITH high_value_orders AS (
    SELECT id, customer_id, total_amount
    FROM orders
    WHERE total_amount > 1000
      AND created_at >= '2026-01-01'
),
customer_stats AS (
    SELECT customer_id,
           COUNT(*)              AS order_cnt,
           SUM(total_amount)     AS total_spent,
           AVG(total_amount)     AS avg_spent
    FROM high_value_orders
    GROUP BY customer_id
),
top_customers AS (
    SELECT customer_id, total_spent
    FROM customer_stats
    WHERE total_spent > 10000
)
SELECT c.name, c.email, tc.total_spent
FROM top_customers tc
JOIN customers c ON c.id = tc.customer_id
ORDER BY tc.total_spent DESC
LIMIT 100;

-- 员工表: id, name, manager_id
-- 找出 Alice 及其所有下属(任意层级)
WITH RECURSIVE org_tree AS (
    -- 基础查询:锚点(根节点)
    SELECT id, name, manager_id, 1 AS depth,
           ARRAY[name]::text[] AS path
    FROM employees
    WHERE name = 'Alice'

    UNION ALL

    -- 递归查询:子节点
    SELECT e.id, e.name, e.manager_id, ot.depth + 1,
           ot.path || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
    WHERE ot.depth < 10          -- 防止异常死循环
)
SELECT depth, path, name
FROM org_tree
ORDER BY depth, path;

-- 评论表: id, post_id, parent_id, content, created_at
-- 取出某帖子下所有评论,按嵌套层级展示
WITH RECURSIVE comment_tree AS (
    SELECT id, parent_id, content, created_at,
           0 AS depth,
           LPAD('0', 3, '0') || id::text AS sort_key
    FROM comments
    WHERE post_id = 12345 AND parent_id IS NULL

    UNION ALL

    SELECT c.id, c.parent_id, c.content, c.created_at,
           ct.depth + 1,
           ct.sort_key || '.' || LPAD(c.id::text, 3, '0')
    FROM comments c
    JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT depth, REPEAT('  ', depth) || content AS display,
       created_at
FROM comment_tree
ORDER BY sort_key;

-- 好友关系表: user_a, user_b(无向图,两条记录)
-- 找出 user=100 的二度好友(朋友的朋友)
WITH RECURSIVE friends AS (
    -- 一度
    SELECT friend_id AS uid, 1 AS degree
    FROM friendships
    WHERE user_id = 100

    UNION

    SELECT f.friend_id, 1 FROM friendships f WHERE f.user_id = 100

    UNION

    -- 二度(排除一度 + 自己)
    SELECT f.friend_id, 2 AS degree
    FROM friendships f
    JOIN friends fr ON f.user_id = fr.uid
    WHERE fr.degree = 1
      AND f.friend_id != 100
)
SELECT uid, MIN(degree) AS min_degree
FROM friends
WHERE uid NOT IN (SELECT friend_id FROM friendships WHERE user_id = 100)
GROUP BY uid
ORDER BY min_degree, uid
LIMIT 50;

-- 每组 Top N:每个班级成绩前 3 名
SELECT *
FROM (
    SELECT student_id, class_id, score,
           ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rn,
           RANK()       OVER (PARTITION BY class_id ORDER BY score DESC) AS rk,
           DENSE_RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS drk
    FROM exam_scores
) t
WHERE rn <= 3;          -- ROW_NUMBER:1,2,3,4...
                        -- RANK:1,2,2,4...(并列跳号)
                        -- DENSE_RANK:1,2,2,3...(并列不跳号)

-- 把用户按消费金额分 5 档
SELECT user_id, total_spent,
       NTILE(5) OVER (ORDER BY total_spent DESC) AS spend_bucket
FROM user_summary;

-- 同比环比:本月 vs 上月 vs 去年同期
WITH monthly AS (
    SELECT date_trunc('month', order_date) AS month,
           SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT month, revenue,
       LAG(revenue, 1)  OVER (ORDER BY month) AS prev_month,
       LAG(revenue, 12) OVER (ORDER BY month) AS prev_year,
       ROUND( (revenue - LAG(revenue,1) OVER (ORDER BY month))
              / NULLIF(LAG(revenue,1) OVER (ORDER BY month), 0) * 100, 2)
              AS mom_growth_pct,
       FIRST_VALUE(revenue) OVER (ORDER BY month) AS first_month_revenue,
       LAST_VALUE(revenue)  OVER (ORDER BY month
                                   ROWS BETWEEN UNBOUNDED PRECEDING
                                            AND UNBOUNDED FOLLOWING)
                              AS last_month_revenue
FROM monthly
ORDER BY month;

-- 累计占比 / 移动平均 / 占比分析
SELECT order_date, daily_revenue,
       SUM(daily_revenue) OVER (ORDER BY order_date) AS cum_revenue,
       AVG(daily_revenue) OVER (ORDER BY order_date
                                ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
                              AS moving_avg_7d,
       ROUND(daily_revenue / SUM(daily_revenue) OVER () * 100, 2)
             AS pct_of_total
FROM daily_revenue_view
ORDER BY order_date;

-- 用户画像表: id, name, profile JSONB
-- profile 结构: {"age": 28, "city": "Beijing", "tags": ["tech","music"], "prefs": {"theme": "dark"}}

-- 创建表
CREATE TABLE users (
    id       BIGSERIAL PRIMARY KEY,
    name     TEXT NOT NULL,
    profile  JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- 插入示例
INSERT INTO users (name, profile) VALUES
('Alice', '{"age": 28, "city": "Beijing", "tags": ["tech","music"],
            "prefs": {"theme": "dark", "lang": "zh"}}'),
('Bob',   '{"age": 35, "city": "Shanghai", "tags": ["finance"],
            "prefs": {"theme": "light", "lang": "en"}}');

-- 嵌套字段查询
SELECT name, profile->'prefs'->>'theme' AS theme
FROM users
WHERE profile->>'city' = 'Beijing';

-- 数组包含
SELECT name FROM users
WHERE profile->'tags' ? 'tech';          -- ? 包含键(数组元素)

-- 嵌套对象路径
SELECT name FROM users
WHERE profile #>> '{prefs,theme}' = 'dark';

-- JSONB 包含(子集)
SELECT name FROM users
WHERE profile @> '{"city": "Beijing"}';  -- 包含关系

-- JSONB 键存在
SELECT name FROM users
WHERE profile ? 'age';

-- GIN 通用索引:支持 @> / ? / ?& / ?| 操作符
CREATE INDEX idx_users_profile_gin ON users USING GIN (profile);

-- 单字段表达式索引(更精确,体积更小)
CREATE INDEX idx_users_city ON users ((profile->>'city'));
CREATE INDEX idx_users_tags ON users USING GIN ((profile->'tags') jsonb_path_ops);

-- 全文搜索(对 JSONB 内字符串)
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

CREATE TABLE products (
    id        BIGSERIAL PRIMARY KEY,
    title     TEXT,
    attrs     JSONB  -- {"color":["红","蓝"], "size":["S","M","L"], "material":"纯棉"}
);
CREATE INDEX idx_products_attrs ON products USING GIN (attrs);

-- 查「红色 + 纯棉 + 库存 > 0」
SELECT id, title FROM products
WHERE attrs @> '{"color":["红"], "material":"纯棉"}'
  AND (attrs->>'stock')::int > 0;

-- 创建物化视图(报表场景:每天的销售汇总)
CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT date_trunc('day', order_date) AS day,
       product_category,
       SUM(amount) AS total_amount,
       COUNT(*)    AS order_count,
       COUNT(DISTINCT customer_id) AS buyer_count
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE order_date >= '2025-01-01'
GROUP BY 1, 2
WITH DATA;     -- 立即填充,默认 NO DATA

-- 建索引加速查询
CREATE UNIQUE INDEX idx_daily_sales_mv_pk ON daily_sales_mv (day, product_category);
CREATE INDEX idx_daily_sales_mv_day ON daily_sales_mv (day);

-- 刷新(默认加 EXCLUSIVE LOCK,会阻塞读)
REFRESH MATERIALIZED VIEW daily_sales_mv;

-- 不阻塞读的刷新(PG 9.4+,需唯一索引)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_mv;

-- 定时刷新(pg_cron 或 Linux cron)
-- 每天凌晨 2 点刷新
SELECT cron.schedule('refresh-daily-sales', '0 2 * * *',
       'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_mv');

CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,
    quantity     INT NOT NULL,
    unit_price   NUMERIC(10,2) NOT NULL,
    -- 存储型生成列(物理存储,占空间)
    total_amount NUMERIC(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    -- 虚拟型生成列(PG 18+,不占空间)
    discount_price NUMERIC(12,2) GENERATED ALWAYS AS
        (quantity * unit_price * 0.9) VIRTUAL,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

-- 不能直接写,自动计算
INSERT INTO orders (quantity, unit_price) VALUES (10, 9.99);
-- total 自动 = 99.90,discount_price 自动 = 89.91

CREATE EXTENSION postgis;

CREATE TABLE shops (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(Point, 4326)    -- WGS84 坐标
);
CREATE INDEX idx_shops_geom ON shops USING GIST (geom);

-- 插入(上海人民广场附近)
INSERT INTO shops (name, geom) VALUES
('ShopA', ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326));

-- 查「3 公里内商家」(米为单位)
SELECT id, name,
       ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint(121.4737,31.2304),4326)::geography) AS dist_m
FROM shops
WHERE ST_DWithin(geom::geography,
                  ST_SetSRID(ST_MakePoint(121.4737,31.2304),4326)::geography,
                  3000)
ORDER BY dist_m;

CREATE EXTENSION vector;

CREATE TABLE doc_embeddings (
    id       BIGSERIAL PRIMARY KEY,
    content  TEXT,
    embedding VECTOR(1536)   -- OpenAI text-embedding-3-small 维度
);

-- HNSW 索引(速度快,内存高)
CREATE INDEX idx_doc_hnsw ON doc_embeddings USING hnsw (embedding vector_cosine_ops);

-- 查 Top 5 相似文档
SELECT id, content, embedding <=> $1 AS distance
FROM doc_embeddings
ORDER BY embedding <=> $1
LIMIT 5;

CREATE EXTENSION timescaledb;

-- 转为 hypertable(自动分区)
CREATE TABLE metrics (
    time       TIMESTAMPTZ NOT NULL,
    sensor_id  INT,
    cpu        REAL,
    mem        REAL
);
SELECT create_hypertable('metrics', 'time');

-- 连续聚合(自动物化的时序视图)
CREATE MATERIALIZED VIEW cpu_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       sensor_id,
       AVG(cpu) AS avg_cpu,
       MAX(cpu) AS max_cpu
FROM metrics
GROUP BY 1, 2;

-- 数据压缩(老数据节省 95% 空间)
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby   = 'time DESC'
);
SELECT add_compression_policy('metrics', INTERVAL '7 days');

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- 拼写错误也能搜(「Aliec」匹配「Alice」)
SELECT name, similarity(name, 'Aliec') AS sim
FROM users
WHERE name % 'Aliec'           -- % 操作符基于 trigram 阈值
ORDER BY sim DESC LIMIT 5;

-- 用户当前位置(经度,纬度)
WITH user_loc AS (
    SELECT ST_SetSRID(ST_MakePoint($lng, $lat), 4326)::geography AS g
),
nearby_shops AS (
    SELECT s.id, s.name,
           ST_Distance(s.geom::geography, ul.g) AS dist_m
    FROM shops s, user_loc ul
    WHERE ST_DWithin(s.geom::geography, ul.g, 3000)
      AND s.is_open = true
)
SELECT id, name, dist_m
FROM nearby_shops
ORDER BY dist_m ASC
LIMIT 20;

-- 错误:全表扫
EXPLAIN SELECT * FROM users WHERE profile @> '{"city":"Beijing"}';
-- Seq Scan on users  (cost=0.00..35000.00 rows=500 width=...)

-- 修复:建 GIN 索引
CREATE INDEX idx_users_profile_gin ON users USING GIN (profile);
-- Bitmap Heap Scan on users
--   Recheck Cond: (profile @> '{"city": "Beijing"}'::jsonb)
--   ->  Bitmap Index Scan on idx_users_profile_gin

-- 优化:加索引 + 提示 work_mem
SET work_mem = '256MB';
CREATE INDEX idx_emp_dept_score ON employees (dept_id, score DESC);
-- 窗口函数现在能走 Index Scan,8s -> 800ms

WITH RECURSIVE safe_tree AS (
    SELECT id, parent_id, 1 AS depth, ARRAY[id]::int[] AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.parent_id, st.depth + 1, st.path || c.id
    FROM categories c
    JOIN safe_tree st ON c.parent_id = st.id
    WHERE st.depth < 50                    -- 硬上限
      AND NOT (c.id = ANY(st.path))        -- 防环
)
SELECT * FROM safe_tree;

-- 安装 pg_cron
CREATE EXTENSION pg_cron;
-- 每天凌晨 2 点刷新
SELECT cron.schedule('refresh-mv', '0 2 * * *',
       $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_mv$$);

-- 查阻塞链
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- PG 端检查
SHOW server_encoding;        -- UTF8
SELECT datname, datcollate, datctype FROM pg_database WHERE datname = current_database();
-- 应该是 en_US.UTF8 / en_US.UTF8 或 C / C