SQL开发Skill sql

---

名称: sql 描述: 编写SQL查询、优化执行计划并设计模式。用于查询优化、复杂连接或数据库设计。

SQL开发

编写高效的SQL查询并设计模式。

使用场景

• 编写复杂查询
• 查询优化
• 模式设计
• 索引策略
• 迁移规划

查询模式

窗口函数

-- 运行总计
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d
FROM transactions;

-- 排名
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank,
    ROW_NUMBER() OVER (ORDER BY score DESC) as row_num
FROM players;

-- 按类别分区
SELECT
    category,
    product,
    sales,
    sales * 100.0 / SUM(sales) OVER (PARTITION BY category) as pct_of_category
FROM products;

CTEs (公共表表达式)

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_month,
        (total - LAG(total) OVER (ORDER BY month)) / NULLIF(LAG(total) OVER (ORDER BY month), 0) * 100 as growth_pct
    FROM monthly_sales
)
SELECT * FROM growth WHERE growth_pct < 0;

递归CTEs

-- 分层数据（组织结构图、类别）
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level, name;

查询优化

索引策略

-- 常见查询的复合索引
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);

-- 过滤查询的部分索引
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- 检查查询计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

常见问题

问题	症状	解决方案
缺少索引	顺序扫描	添加适当的索引
N+1 查询	许多小命中	使用 JOIN 或批处理
SELECT *	慢 + 内存消耗	只选择需要的列
无 LIMIT	大量结果	添加分页
列上的函数	索引未使用	重写条件

模式设计

-- 规范化模式
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'completed';

示例

输入: “优化这个慢查询” 动作: 运行 EXPLAIN，识别瓶颈，添加索引或重写查询

输入: “获取按收入排名的前10位客户” 动作: 编写带有适当连接、排序和限制的聚合查询