🗄️ 每日Skill学习 - SQL Toolkit#

1
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
2
CREATE TABLE users (
3
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
4
    ...
5
);

1
-- 存储
2
INSERT INTO orders (user_id, total, metadata)
3
VALUES ('...', 99.99, '{"source": "web", "coupon": "SAVE10"}');
4

5
-- 查询
6
SELECT * FROM orders WHERE metadata->>'source' = 'web';
7
SELECT * FROM orders WHERE metadata->'items' @> '[{"sku": "A1"}]';

1
CREATE OR REPLACE FUNCTION update_modified_column()
2
RETURNS TRIGGER AS $$
3
BEGIN
4
    NEW.updated_at = NOW();
5
    RETURN NEW;
6
END;
7
$$ LANGUAGE plpgsql;
8

9
CREATE TRIGGER update_users_modtime
10
    BEFORE UPDATE ON users
11
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

1
-- 累计求和
2
SELECT date, revenue,
3
    SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue
4
FROM daily_sales;
5

6
-- 组内排名
7
SELECT user_id, total,
8
    RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank
9
FROM orders;
10

11
-- 移动平均
12
SELECT date, revenue,
13
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
14
FROM daily_sales;

1
WITH monthly_revenue AS (
2
    SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue
3
    FROM orders WHERE status = 'paid' GROUP BY 1
4
),
5
growth AS (
6
    SELECT month, revenue,
7
           LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
8
           ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) /
9
                 NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1) AS growth_pct
10
    FROM monthly_revenue
11
)
12
SELECT * FROM growth ORDER BY month;

1
WITH RECURSIVE org_tree AS (
2
    SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL
3
    UNION ALL
4
    SELECT e.id, e.name, e.manager_id, t.depth + 1
5
    FROM employees e JOIN org_tree t ON e.manager_id = t.id
6
)
7
SELECT REPEAT('  ', depth) || name AS org_chart FROM org_tree ORDER BY depth, name;

1
-- 复合索引：等值条件放前面，范围条件放后面
2
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
3

4
-- 覆盖索引：避免回表查询
5
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);
6

7
-- 部分索引：只索引需要查询的数据，更小更快
8
CREATE INDEX idx_orders_pending ON orders(user_id) WHERE status = 'pending';
9

10
-- GIN 索引：JSONB 专用
11
CREATE INDEX idx_orders_metadata ON orders USING GIN(metadata);

1
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
2
SELECT * FROM orders WHERE user_id = '...' AND status = 'paid';

1
migrations/
2
  001_create_users.sql
3
  002_create_orders.sql
4
  003_add_users_phone.sql

1
-- 003_add_users_phone.sql
2
-- Up
3
ALTER TABLE users ADD COLUMN phone TEXT;
4
-- Down: ALTER TABLE users DROP COLUMN phone;