SQL 格式化：自动化工具、风格争议与复杂模式布局

-- 尾随逗号（传统）
SELECT
    id,
    name,
    email,
    created_at
FROM users;

-- 前导逗号（更干净的 diff，无尾随逗号错误）
SELECT
    id
  , name
  , email
  , created_at
FROM users;

-- 左对齐（最常见）
SELECT
    id,
    name
FROM users
WHERE status = 'active'
ORDER BY created_at;

-- 右对齐 / "River" 风格
SELECT id,
       name
  FROM users
 WHERE status = 'active'
 ORDER BY created_at;

# 格式化文件
sqlfluff fix query.sql --dialect postgres

# 仅 lint，不修复
sqlfluff lint query.sql --dialect postgres

[sqlfluff]
dialect = postgres
templater = raw
max_line_length = 120

[sqlfluff:indentation]
indent_unit = space
tab_space_size = 4

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper

[sqlfluff:rules:layout.long_lines]
ignore_comment_lines = True

pip install shandy-sqlfmt
sqlfmt query.sql

-- 格式良好的 CTE 链
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    WHERE order_date >= '2025-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
),

revenue_growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
        (revenue - LAG(revenue) OVER (ORDER BY month))
            / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS growth_rate
    FROM monthly_revenue
)

SELECT
    month,
    revenue,
    prev_revenue,
    ROUND(growth_rate * 100, 1) AS growth_pct
FROM revenue_growth
ORDER BY month;

SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
        ORDER BY hire_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_avg,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank,
    salary - FIRST_VALUE(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS diff_from_max
FROM employees;

ROW_NUMBER() OVER (ORDER BY id) AS row_num

-- 简单 CASE：如果短可以内联
SELECT status, CASE status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inactive' END AS label
FROM users;

-- 复杂 CASE：每个 WHEN 一行
SELECT
    order_id,
    CASE
        WHEN total > 1000 AND customer_type = 'enterprise'
            THEN 'high_value_enterprise'
        WHEN total > 1000
            THEN 'high_value'
        WHEN total > 100
            THEN 'medium_value'
        ELSE 'standard'
    END AS order_tier
FROM orders;

SELECT
    d.name AS department,
    d.budget,
    (
        SELECT COUNT(*)
        FROM employees e
        WHERE e.department_id = d.id
            AND e.status = 'active'
    ) AS headcount,
    (
        SELECT AVG(e.salary)
        FROM employees e
        WHERE e.department_id = d.id
    ) AS avg_salary
FROM departments d
WHERE d.budget > 100000;

SELECT o.id, o.total
FROM orders o
INNER JOIN customers c
    ON c.id = o.customer_id
    AND c.region = o.shipping_region
LEFT JOIN promotions p
    ON p.id = o.promo_id
    AND p.valid_from <= o.order_date
    AND p.valid_until >= o.order_date
WHERE o.status = 'completed';

# 能捕获真实 bug 的 sqlfluff 规则
[sqlfluff:rules:ambiguous.column_references]
# 在多表查询中要求限定列名

[sqlfluff:rules:convention.select_trailing_comma]
# 防止导致语法错误的尾随逗号

[sqlfluff:rules:structure.subquery]
# 要求子查询有别名

# .pre-commit-config.yaml
repos:
  - repo: https://github.com/sqlfluff/sqlfluff
    rev: 3.0.0
    hooks:
      - id: sqlfluff-lint
        args: [--dialect, postgres]
      - id: sqlfluff-fix
        args: [--dialect, postgres]

# .github/workflows/sql-lint.yml
name: SQL Lint
on: [pull_request]
jobs:
  sqlfluff:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: '3.12'
      - run: pip install sqlfluff
      - run: sqlfluff lint --dialect postgres sql/

CREATE OR REPLACE FUNCTION calculate_discount(
    p_customer_id INTEGER,
    p_order_total NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_discount NUMERIC := 0;
    v_tier TEXT;
BEGIN
    SELECT loyalty_tier INTO v_tier
    FROM customers
    WHERE id = p_customer_id;

    v_discount := CASE v_tier
        WHEN 'gold' THEN p_order_total * 0.15
        WHEN 'silver' THEN p_order_total * 0.10
        ELSE 0
    END;

    RETURN v_discount;
END;
$$;

SELECT
    user_id,
    STRUCT(
        first_name,
        last_name,
        STRUCT(
            street,
            city,
            state
        ) AS address
    ) AS user_info,
    ARRAY_AGG(
        STRUCT(order_id, amount, order_date)
        ORDER BY order_date DESC
        LIMIT 10
    ) AS recent_orders
FROM users
LEFT JOIN orders USING (user_id)
GROUP BY user_id, first_name, last_name, street, city, state;