📊 Data Analyst Pro - 专业数据分析技能包

从数据到洞察，让 AI 成为你的数据分析师

🎯 这个技能能帮你做什么？

✅ SQL 查询生成 - 自动生成复杂 SQL 查询 ✅ 数据分析 - Python/Pandas 自动化分析 ✅ 数据可视化 - 自动生成图表和报告 ✅ 数据清洗 - 处理缺失值、异常值 ✅ 统计分析 - 描述性统计、相关性分析 ✅ 自动化报告 - 生成专业分析报告

📚 包含内容

第一部分：SQL 查询模式（30+ 模板）

基础查询

-- 数据探索
SELECT COUNT(*) FROM table_name;
SELECT * FROM table_name LIMIT 10;

-- 列统计
SELECT 
    column_name,
    COUNT(*) as count,
    COUNT(DISTINCT column_name) as unique_values,
    MIN(column_name) as min_val,
    MAX(column_name) as max_val
FROM table_name
GROUP BY column_name;

时间序列分析

-- 日聚合
SELECT 
    DATE(created_at) as date,
    COUNT(*) as daily_count,
    SUM(amount) as daily_total
FROM transactions
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- 环比增长
SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as count,
    LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', created_at)) as prev_month,
    (COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', created_at))) / 
        NULLIF(LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', created_at)), 0) * 100 as growth_pct
FROM transactions
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

漏斗分析

-- 转化漏斗
WITH funnel AS (
    SELECT
        COUNT(DISTINCT CASE WHEN event = 'page_view' THEN user_id END) as views,
        COUNT(DISTINCT CASE WHEN event = 'signup' THEN user_id END) as signups,
        COUNT(DISTINCT CASE WHEN event = 'purchase' THEN user_id END) as purchases
    FROM events
    WHERE date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT 
    views,
    signups,
    ROUND(signups * 100.0 / NULLIF(views, 0), 2) as signup_rate,
    purchases,
    ROUND(purchases * 100.0 / NULLIF(signups, 0), 2) as purchase_rate
FROM funnel;

用户分层

-- RFM 分析
WITH rfm AS (
    SELECT 
        user_id,
        DATEDIFF(CURRENT_DATE, MAX(order_date)) as recency,
        COUNT(*) as frequency,
        SUM(amount) as monetary
    FROM orders
    GROUP BY user_id
)
SELECT 
    CASE 
        WHEN recency <= 30 THEN 'Active'
        WHEN recency <= 90 THEN 'Churning'
        ELSE 'Churned'
    END as segment,
    COUNT(*) as users,
    AVG(frequency) as avg_frequency,
    AVG(monetary) as avg_monetary
FROM rfm
GROUP BY segment;

第二部分：Python 数据分析

Pandas 快速操作

import pandas as pd

# 加载数据
df = pd.read_csv('data.csv')

# 基础探索
print(df.shape)  # (rows, columns)
print(df.info())  # 列类型和空值
print(df.describe())  # 统计摘要

# 数据清洗
df = df.drop_duplicates()
df['date'] = pd.to_datetime(df['date'])
df['amount'] = df['amount'].fillna(0)

# 聚合分析
summary = df.groupby('category').agg({
    'amount': ['sum', 'mean', 'count'],
    'quantity': 'sum'
}).round(2)

# 导出
summary.to_csv('analysis_output.csv')

常用分析模式

# 过滤
filtered = df[df['status'] == 'active']
filtered = df[df['amount'] > 1000]
filtered = df[df['date'].between('2024-01-01', '2024-12-31')]