1. 基础查询：不仅仅是 SELECT

在 Postgres 中，基础查询也包含了一些比标准 SQL 更灵活的特性。

• 别名与计算字段： 你可以在查询时直接进行运算或格式化。

SELECT name, (price * stock) AS total_value FROM products;

• DISTINCT ON： 这是 Postgres 的神技，可以根据某一列去重，同时保留另一列的特定行（如：每个用户最新的一条订单）。

-- 获取每个用户最近的一次登录记录
SELECT DISTINCT ON (user_id) user_id, login_time 
FROM user_logins 
ORDER BY user_id, login_time DESC;

• 模式匹配： 除了 LIKE，Postgres 支持 ~ (正则匹配) 和 ILIKE (忽略大小写的匹配)，这对前端搜索功能非常友好。

---

2. 特有数据类型的查询

作为前端开发者，你可能最关心如何查询 JSONB 和 数组。

• JSONB 查询： * ->> 获取文本值，@> 检查是否包含某个 JSON 结构。

-- 查询 settings 字段中 theme 为 'dark' 的用户
SELECT * FROM users WHERE settings @> '{"theme": "dark"}';

• 数组查询：
• 使用 ANY 或 && (重叠) 操作符。

-- 查询标签中包含 'javascript' 的文章
SELECT * FROM articles WHERE 'javascript' = ANY(tags);

---

3. 高级查询：CTE (公用表表达式)

CTE 是将复杂查询“模块化”的最佳方式，它使用 WITH 关键字。

• 非递归 CTE： 像定义临时变量一样，增加代码可读性。
• 递归 CTE (Recursive)： 用于处理树形结构（如菜单、组织架构、评论嵌套）。

WITH RECURSIVE category_tree AS (
    -- 初始步骤：找到根节点
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    -- 递归步骤：连接子节点
    SELECT c.id, c.name, c.parent_id 
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

---

4. 窗口函数 (Window Functions)

这是 Postgres 进阶的必杀技。它允许你在不合并行（不使用 GROUP BY）的情况下，对行集进行计算。

• 语法： 函数() OVER (PARTITION BY 分组列 ORDER BY 排序列)
• 常用函数：
• RANK() / DENSE_RANK(): 排名。
• ROW_NUMBER(): 给行编号。
• LAG() / LEAD(): 访问当前行的“前一行”或“后一行”数据（非常适合计算环比增长）。

---

5. 视图与物化视图 (Views & Materialized Views)

• 视图 (View)： 虚表。它只是保存了一段 SQL 逻辑，每次查询视图时都会实时执行背后的 SQL。
• 物化视图 (Materialized View)： 实表。它将查询结果物理存储在磁盘上。
• 优点： 极大提高复杂聚合查询的响应速度。
• 缺点： 数据不是实时的，需要手动或定时通过 REFRESH MATERIALIZED VIEW 更新。
• 场景： 仪表盘报表、历史数据分析。

---

6. 聚合与过滤 (Advanced Aggregation)

Postgres 允许在聚合函数内部使用 FILTER 语句，这比传统的 CASE WHEN 更简洁。

SELECT 
    count(*) AS total_orders,
    count(*) FILTER (WHERE status = 'completed') AS success_orders,
    count(*) FILTER (WHERE status = 'cancelled') AS failed_orders
FROM orders;