sql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SQL Development

SQL开发

Write efficient SQL queries and design schemas.

编写高效的SQL查询语句并设计数据库架构。

When to Use

适用场景

Writing complex queries
Query optimization
Schema design
Index strategy
Migration planning

编写复杂查询语句
查询优化
架构设计
索引策略
迁移规划

Query Patterns

查询模式

Window Functions

sql

-- Running totals
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;

-- Ranking
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;

-- Partition by category
SELECT
    category,
    product,
    sales,
    sales * 100.0 / SUM(sales) OVER (PARTITION BY category) as pct_of_category
FROM products;

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 (Common Table Expressions)

CTE（公共表表达式）

sql

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;

sql

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;

Recursive CTEs

递归CTE

sql

-- Hierarchical data (org chart, categories)
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;

sql

-- 层级数据（组织架构、分类体系）
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;

Query Optimization

查询优化

Index Strategy

索引策略

sql

-- Composite index for common queries
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);

-- Partial index for filtered queries
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Check query plan
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

sql

-- 针对常用查询的复合索引
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;

Common Issues

常见问题

Problem	Symptom	Solution
Missing index	Seq Scan	Add appropriate index
N+1 queries	Many small hits	Use JOIN or batch
SELECT *	Slow + memory	Select only needed columns
No LIMIT	Large result	Add pagination
Function on col	Index not used	Rewrite condition

问题类型	表现症状	解决方案
缺少索引	全表扫描（Seq Scan）	添加合适的索引
N+1查询问题	多次小型查询请求	使用JOIN或批量处理
SELECT * 语句	查询缓慢+占用内存	仅选择所需的列
未设置LIMIT	结果集过大	添加分页处理
列上使用函数	索引未被利用	重写查询条件

Schema Design

数据库架构设计

sql

-- Normalized schema
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';

sql

-- 规范化架构
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';

Examples

示例

Input: "Optimize this slow query" Action: Run EXPLAIN, identify bottlenecks, add indexes or rewrite query

Input: "Get top 10 customers by revenue" Action: Write aggregation with proper joins, ordering, and limit

输入： "优化这个慢查询" 操作： 运行EXPLAIN，识别瓶颈，添加索引或重写查询语句

输入： "获取收入TOP10的客户" 操作： 编写包含合适关联、排序和限制的聚合查询