sql-analysis（SQL analysis 搜索）

SQL Analysis Skill

Purpose

Enable BAs to validate data requirements, understand current data states, and perform independent data analysis without relying solely on developers.

When to Use

• Validating migration data.
• Understanding current data structures (As-Is).
• Troubleshooting user issues ("Why can't I see this order?").
• Generating ad-hoc reports for stakeholders.

Core SQL Concepts for BAs

1. The SELECT Statement

SELECT column1, column2   -- What do you want?
FROM table_name           -- From where?
WHERE condition           -- Filter logic
ORDER BY column1 DESC;    -- Sorting

2. Filtering (WHERE)

• WHERE status = 'Active' (Exact match)
• WHERE quantity > 10 (Comparison)
• WHERE name LIKE 'Smith%' (Partial match)
• WHERE status IN ('New', 'Open') (List)
• WHERE end_date IS NULL (Finding missing data)

3. Aggregation (GROUP BY)

Summarizing data.

SELECT status, COUNT(*) as total_orders
FROM orders
GROUP BY status;

4. Joining Tables (JOIN)

Connecting related data.

• INNER JOIN: Only matching records (A ∩ B). "Show me customers who have orders."
• LEFT JOIN: All from Left, matches from Right (A + A∩B). "Show me ALL customers, and their orders if any." (Good for finding customers with NO orders).

Common Analysis Queries

Data Quality Check

"Are there any duplicate customers by email?"

SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

"Are there orders without a valid user?"

SELECT o.order_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

Process Analysis

"How long does it take to ship an order?"

SELECT
    AVG(DATEDIFF(day, order_date, shipped_date)) as avg_days_to_ship,
    MAX(DATEDIFF(day, order_date, shipped_date)) as max_days
FROM orders
WHERE shipped_date IS NOT NULL;

Integration Spec Validation

"What are the distinct values for 'Payment Method' so we can map them?"

SELECT DISTINCT payment_method
FROM transactions;

Best Practices

• Read-Only Access: Ensure you only have SELECT permissions (ro-user).
• Limit Results: Always use LIMIT 10 (or TOP 10) when exploring new tables.
• No Production Performance Impact: Avoid running heavy queries (e.g., Joining 5 huge tables) during peak hours.
• Understand NULLs: COUNT(column) ignores NULLs; COUNT(*) counts everything.

Tools

• DBeaver: Universal DB Tool.
• Metabase / Redash: Visual SQL Builders.
• Lark Base: Can import CSV results for sharing.