1. 窗口函数可视化
点击字段切换 PARTITION BY / ORDER BY / 排名函数，下方表格会用颜色分组并标上行号 / 排名。
PARTITION BY：
category
city
无
ORDER BY：
price ASC
price DESC
sales ASC
sales DESC
排名函数：
ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE(3)
| # | name | category | city | price | sales | rank |
|---|
2. 窗口框架：移动平均可视化
窗口框架：
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW (3日均)
7日均
UNBOUNDED PRECEDING (累计)
原始 revenue
移动平均/累计
当前窗口框架
递归 CTE：组织架构逐层展开
点击「步进」观察递归 CTE 每一轮产生的新行；点击「一键播放」观看全过程。
Level 0 / 等待启动
WITH RECURSIVE org AS ( -- 第 0 轮：anchor 找根节点 SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL -- 第 N 轮：上一轮的 id 去找子节点 SELECT e.id, e.name, e.manager_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.id ) SELECT * FROM org;
高级查询语法速查
| 场景 | 语法 | 说明 |
|---|---|---|
| JOIN 全家桶 | INNER / LEFT / RIGHT / FULL / CROSS JOIN | FULL OUTER 只有 PG 支持 |
| 半连接 | EXISTS / IN (subquery) | PG 里二者性能几乎等价 |
| 反连接 | NOT EXISTS | 避免 NOT IN 的 NULL 陷阱 |
| CTE | WITH x AS (...) SELECT ... | PG 12+ 默认内联 |
| 可写 CTE | WITH ins AS (INSERT ... RETURNING *) ... | PG 特色 |
| 递归 CTE | WITH RECURSIVE x AS (anchor UNION ALL recursive) | 树 / 图展开 |
| 窗口函数 | f() OVER (PARTITION BY... ORDER BY... ROWS BETWEEN ...) | 不合并行 |
| 分组扩展 | GROUPING SETS / ROLLUP / CUBE | 多维度小计 |
| UPSERT | INSERT ... ON CONFLICT (col) DO UPDATE SET ... = EXCLUDED.col | PG 特色 |
| LATERAL | LEFT JOIN LATERAL (SELECT ... WHERE t.x = LEFT.x LIMIT N) ON true | 右可引用左 |
| DISTINCT ON | SELECT DISTINCT ON (col) ... ORDER BY col, ... | 按字段去重保留极值 |
| 集合运算 | UNION / UNION ALL / INTERSECT / EXCEPT | 列数类型要兼容 |
| DML 返回 | INSERT/UPDATE/DELETE ... RETURNING * | PG 特色 |
| 随机抽样 | TABLESAMPLE BERNOULLI(1) / SYSTEM(1) | PG 9.5+ |