PostgreSQL 高级 SQL 查询实战：窗口函数、CTE、递归与 JSONB 深度操作

-- ROW_NUMBER：每行唯一排名（不并列）
SELECT
  name,
  sales,
  ROW_NUMBER() OVER (ORDER BY sales DESC) AS rank_num,
  RANK() OVER (ORDER BY sales DESC) AS rank_equal,         -- 并列跳号
  DENSE_RANK() OVER (ORDER BY sales DESC) AS rank_dense    -- 并列不跳号
FROM employees;

-- 结果：
-- name    | sales  | rank_num | rank_equal | rank_dense
-- Alice   | 1000   | 1        | 1          | 1
-- Bob     | 1000   | 2        | 1          | 1
-- Charlie | 800    | 3        | 3          | 2

-- LEAD/LAG：查看前/后行的值（无上/下行返回 NULL）
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS delta,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_revenue
FROM daily_sales;

-- FIRST_VALUE / LAST_VALUE：窗口内的首尾值
SELECT
  department,
  name,
  salary,
  FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner,
  LAST_VALUE(name) OVER (
    PARTITION BY department ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_earner
FROM employees;

-- 计算部门平均与员工差异
SELECT
  department,
  name,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  salary / SUM(salary) OVER (PARTITION BY department) * 100 AS pct_of_dept
FROM employees;

-- 找出每个类目中评分最高的 3 本书
WITH ranked_books AS (
  SELECT
    id, title, category, rating,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY rating DESC, id) AS rn
  FROM books
)
SELECT * FROM ranked_books WHERE rn <= 3;

WITH
  monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total
    FROM orders
    GROUP BY 1
  ),
  growth AS (
    SELECT
      month,
      total,
      LAG(total) OVER (ORDER BY month) AS prev_total,
      (total - LAG(total) OVER (ORDER BY month)) / NULLIF(LAG(total) OVER (ORDER BY month), 0) * 100 AS growth_rate
    FROM monthly_sales
  )
SELECT * FROM growth WHERE month >= '2025-01-01';

-- 查找某员工的全部下属（组织架构）
WITH RECURSIVE org_tree AS (
  -- 锚点：从 CEO 开始
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 递归步：找到下属的下属
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, level, REPEAT('  ', level) || name AS indented_name
FROM org_tree
ORDER BY level, name;

-- 结果：
-- id | name    | level | indented_name
-- 1  | Alice   | 0     | Alice
-- 2  | Bob     | 1     |   Bob
-- 3  | Charlie | 1     |   Charlie
-- 4  | Dave    | 2     |     Dave

-- 文件系统路径拼接（层级目录树）
WITH RECURSIVE path_tree AS (
  SELECT id, name, parent_id, name AS full_path
  FROM folders
  WHERE parent_id IS NULL

  UNION ALL

  SELECT f.id, f.name, f.parent_id, pt.full_path || '/' || f.name
  FROM folders f
  JOIN path_tree pt ON f.parent_id = pt.id
)
SELECT full_path FROM path_tree WHERE id = 42;
-- 结果：Root/Projects/Backend/Database

-- 不用 LATERAL：自连接 + 子查询，复杂且慢
-- 用 LATERAL：清晰直观
SELECT u.id, u.name, p.title, p.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT id, title, created_at
  FROM posts
  WHERE author_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) p ON true
ORDER BY u.id, p.created_at DESC;

-- 计算每个订单的运费（按重量分区对应不同费率）
SELECT o.id, o.total, o.weight,
       COALESCE(fr.rate * o.weight, 0) AS shipping_cost,
       o.total + COALESCE(fr.rate * o.weight, 0) AS grand_total
FROM orders o
LEFT JOIN LATERAL (
  SELECT rate FROM freight_rates
  WHERE o.weight >= min_weight AND o.weight < max_weight
  LIMIT 1
) fr ON true;

-- 创建含数组列的表
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  tags TEXT[]
);

-- 插入数组
INSERT INTO articles (title, tags) VALUES
  ('Postgres Arrays', ARRAY['sql', 'advanced', 'postgres']),
  ('Window Functions', '{sql, window}');  -- 两种语法

-- 追加/删除元素
UPDATE articles SET tags = array_append(tags, 'tutorial') WHERE id = 1;
UPDATE articles SET tags = array_remove(tags, 'old') WHERE id = 1;

-- 包含某个标签
SELECT * FROM articles WHERE tags @> ARRAY['sql'];

-- 包含任意标签（交集）
SELECT * FROM articles WHERE tags && ARRAY['window', 'advanced'];

-- 数组长度
SELECT title, array_length(tags, 1) AS tag_count FROM articles;

-- 展开为行（unnest）
SELECT unnest(tags) AS tag FROM articles;
-- 结果 → sql, advanced, postgres, sql, window

-- 唯一标签统计
SELECT unnest(tags) AS tag, COUNT(*) AS cnt
FROM articles
GROUP BY tag
ORDER BY cnt DESC;

CREATE INDEX idx_articles_tags ON articles USING GIN(tags);

-- 含 'postgres' 标签的查询用 GIN 索引
SELECT * FROM articles WHERE tags @> ARRAY['postgres'];

-- 查找订单中购买了苹果产品的
SELECT * FROM orders
WHERE items @> '[{"product": {"brand": "Apple"}}]';

-- 按嵌套属性排序
SELECT * FROM products
ORDER BY (metadata->'specs'->>'rating')::NUMERIC DESC;

-- JSONB 聚合（多条记录聚合为单个 JSONB）
SELECT user_id,
  JSONB_AGG(
    JSONB_BUILD_OBJECT('title', title, 'created_at', created_at)
    ORDER BY created_at DESC
  ) AS post_history
FROM posts
GROUP BY user_id;

-- JSONB 列值批量更新（给所有用户的 metadata 加字段）
UPDATE users
SET metadata = metadata || '{"newsletter_opt_in": true}'::jsonb;

-- 为特定 JSONB 路径建索引，避免全表扫描
CREATE INDEX idx_orders_items ON orders (
  (items->0->>'product_id')
);

-- 更通用的 GIN 索引
CREATE INDEX idx_products_metadata ON products USING GIN(metadata jsonb_path_ops);

-- POSIX 正则
SELECT * FROM users WHERE email ~ '^[a-zA-Z0-9._%+-]+@gmail\.com$';

-- 不区分大小写
SELECT * FROM users WHERE name ~* '^alice';

-- 正则替换
SELECT REGEXP_REPLACE(phone, '^(\d{3})-(\d{4})$', '(\1) \2-XXXX');

-- 分组提取
SELECT REGEXP_MATCHES(email, '(.+)@(.+)', 'i') FROM users;

-- 多语言混合搜索（中文需要额外扩展）
SELECT id, title, ts_rank(tsv, query) AS rank
FROM articles,
  to_tsquery('english', 'postgres & optimization') query
WHERE tsv @@ query
ORDER BY rank DESC;

-- 创建加权 tsvector（标题权重 A，正文 B）
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    SETWEIGHT(to_tsvector('english', COALESCE(title, '')), 'A') ||
    SETWEIGHT(to_tsvector('english', COALESCE(body, '')), 'B')
  ) STORED;

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- 高亮摘要
SELECT id, title,
  ts_headline('english', body, plainto_tsquery('english', 'database')) AS highlight
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'database');

WITH monthly_stats AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    user_id,
    COUNT(*) AS session_count
  FROM sessions
  GROUP BY 1, 2
),
engagement AS (
  SELECT
    month,
    COUNT(DISTINCT user_id) AS total_users,
    COUNT(DISTINCT CASE WHEN session_count >= 5 THEN user_id END) AS power_users,
    COUNT(DISTINCT CASE WHEN session_count = 1 THEN user_id END) AS one_time_users,
    AVG(session_count)::NUMERIC(10,2) AS avg_sessions
  FROM monthly_stats
  GROUP BY month
)
SELECT
  month,
  total_users,
  power_users,
  ROUND(power_users::NUMERIC / total_users * 100, 2) AS power_user_rate,
  one_time_users,
  avg_sessions
FROM engagement
ORDER BY month;

WITH RECURSIVE category_path AS (
  SELECT id, name, parent_id, name AS path, 0 AS depth
  FROM categories WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, cp.path || ' > ' || c.name, cp.depth + 1
  FROM categories c
  JOIN category_path cp ON c.parent_id = cp.id
)
SELECT * FROM category_path ORDER BY path;