报表查询实战

概述

报表是企业管理和决策的重要工具。本章节通过各种实际的报表场景，展示如何使用SQL构建高效、准确的报表查询，涵盖财务报表、运营报表、管理报表等多个方面。

数据模型设计

报表数据表结构

财务交易表 (financial_transactions)

CREATE TABLE financial_transactions (
    transaction_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id VARCHAR(20) NOT NULL,
    transaction_type ENUM('income', 'expense', 'transfer') NOT NULL,
    category VARCHAR(50) NOT NULL,
    subcategory VARCHAR(50),
    amount DECIMAL(15,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'CNY',
    transaction_date DATE NOT NULL,
    transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description TEXT,
    reference_number VARCHAR(50),
    department_id INT,
    project_id INT,
    created_by INT,
    INDEX idx_date_type (transaction_date, transaction_type),
    INDEX idx_account_date (account_id, transaction_date),
    INDEX idx_category_date (category, transaction_date)
);

员工信息表 (employees)

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    position VARCHAR(100),
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2),
    manager_id INT,
    status ENUM('active', 'inactive', 'terminated') DEFAULT 'active',
    INDEX idx_department (department_id),
    INDEX idx_manager (manager_id)
);

部门信息表 (departments)

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL,
    parent_department_id INT,
    manager_id INT,
    budget DECIMAL(15,2),
    cost_center VARCHAR(20),
    INDEX idx_parent (parent_department_id)
);

项目信息表 (projects)

CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(200) NOT NULL,
    project_code VARCHAR(20) UNIQUE NOT NULL,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(15,2),
    actual_cost DECIMAL(15,2) DEFAULT 0,
    status ENUM('planning', 'active', 'completed', 'cancelled') DEFAULT 'planning',
    manager_id INT,
    INDEX idx_status_dates (status, start_date, end_date)
);

财务报表

1. 损益表 (P&L Statement)

-- 月度损益表
WITH monthly_pnl AS (
    SELECT 
        DATE_FORMAT(transaction_date, '%Y-%m') as month,
        transaction_type,
        category,
        SUM(amount) as total_amount
    FROM financial_transactions
    WHERE transaction_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    GROUP BY DATE_FORMAT(transaction_date, '%Y-%m'), transaction_type, category
),
pivot_data AS (
    SELECT 
        month,
        category,
        SUM(CASE WHEN transaction_type = 'income' THEN total_amount ELSE 0 END) as income,
        SUM(CASE WHEN transaction_type = 'expense' THEN total_amount ELSE 0 END) as expense
    FROM monthly_pnl
    GROUP BY month, category
),
monthly_summary AS (
    SELECT 
        month,
        SUM(income) as total_income,
        SUM(expense) as total_expense,
        SUM(income) - SUM(expense) as net_profit
    FROM pivot_data
    GROUP BY month
)
SELECT 
    month,
    total_income,
    total_expense,
    net_profit,
    ROUND(net_profit * 100.0 / NULLIF(total_income, 0), 2) as profit_margin,
    LAG(net_profit) OVER (ORDER BY month) as prev_month_profit,
    ROUND(
        (net_profit - LAG(net_profit) OVER (ORDER BY month)) * 100.0 / 
        NULLIF(LAG(net_profit) OVER (ORDER BY month), 0), 2
    ) as profit_growth_rate
FROM monthly_summary
ORDER BY month DESC;

2. 现金流量表

-- 现金流量表
WITH cash_flow_categories AS (
    SELECT 
        DATE_FORMAT(transaction_date, '%Y-%m') as month,
        CASE 
            WHEN category IN ('sales_revenue', 'service_revenue', 'other_income') THEN 'operating_inflow'
            WHEN category IN ('cost_of_goods', 'salaries', 'rent', 'utilities', 'marketing') THEN 'operating_outflow'
            WHEN category IN ('equipment_purchase', 'software_license', 'office_setup') THEN 'investing_outflow'
            WHEN category IN ('asset_sale', 'investment_income') THEN 'investing_inflow'
            WHEN category IN ('loan_received', 'equity_investment') THEN 'financing_inflow'
            WHEN category IN ('loan_payment', 'dividend_payment') THEN 'financing_outflow'
            ELSE 'other'
        END as cash_flow_type,
        SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE -amount END) as net_amount
    FROM financial_transactions
    WHERE transaction_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
    GROUP BY DATE_FORMAT(transaction_date, '%Y-%m'), cash_flow_type
),
cash_flow_summary AS (
    SELECT 
        month,
        SUM(CASE WHEN cash_flow_type = 'operating_inflow' THEN net_amount ELSE 0 END) -
        SUM(CASE WHEN cash_flow_type = 'operating_outflow' THEN -net_amount ELSE 0 END) as operating_cash_flow,
        SUM(CASE WHEN cash_flow_type = 'investing_inflow' THEN net_amount ELSE 0 END) -
        SUM(CASE WHEN cash_flow_type = 'investing_outflow' THEN -net_amount ELSE 0 END) as investing_cash_flow,
        SUM(CASE WHEN cash_flow_type = 'financing_inflow' THEN net_amount ELSE 0 END) -
        SUM(CASE WHEN cash_flow_type = 'financing_outflow' THEN -net_amount ELSE 0 END) as financing_cash_flow
    FROM cash_flow_categories
    GROUP BY month
)
SELECT 
    month,
    operating_cash_flow,
    investing_cash_flow,
    financing_cash_flow,
    operating_cash_flow + investing_cash_flow + financing_cash_flow as net_cash_flow,
    SUM(operating_cash_flow + investing_cash_flow + financing_cash_flow) 
        OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) as cumulative_cash_flow
FROM cash_flow_summary
ORDER BY month;

3. 预算执行报表

-- 部门预算执行情况
WITH department_budget AS (
    SELECT 
        d.department_id,
        d.department_name,
        d.budget as annual_budget,
        d.budget / 12 as monthly_budget
    FROM departments d
    WHERE d.budget IS NOT NULL
),
actual_spending AS (
    SELECT 
        d.department_id,
        DATE_FORMAT(ft.transaction_date, '%Y-%m') as month,
        SUM(ft.amount) as actual_expense
    FROM financial_transactions ft
    INNER JOIN departments d ON ft.department_id = d.department_id
    WHERE ft.transaction_type = 'expense'
      AND ft.transaction_date >= DATE_FORMAT(CURDATE(), '%Y-01-01')
    GROUP BY d.department_id, DATE_FORMAT(ft.transaction_date, '%Y-%m')
),
budget_analysis AS (
    SELECT 
        db.department_id,
        db.department_name,
        db.annual_budget,
        db.monthly_budget,
        COALESCE(SUM(as1.actual_expense), 0) as ytd_actual,
        db.annual_budget - COALESCE(SUM(as1.actual_expense), 0) as remaining_budget,
        ROUND(COALESCE(SUM(as1.actual_expense), 0) * 100.0 / db.annual_budget, 2) as budget_utilization,
        MONTH(CURDATE()) * db.monthly_budget as expected_ytd_spending,
        COALESCE(SUM(as1.actual_expense), 0) - (MONTH(CURDATE()) * db.monthly_budget) as variance
    FROM department_budget db
    LEFT JOIN actual_spending as1 ON db.department_id = as1.department_id
    GROUP BY db.department_id, db.department_name, db.annual_budget, db.monthly_budget
)
SELECT 
    department_name,
    annual_budget,
    ytd_actual,
    remaining_budget,
    budget_utilization,
    expected_ytd_spending,
    variance,
    CASE 
        WHEN variance > monthly_budget * 0.1 THEN '超支警告'
        WHEN variance < -monthly_budget * 0.1 THEN '执行偏低'
        ELSE '正常'
    END as status,
    ROUND(remaining_budget / (12 - MONTH(CURDATE())), 2) as avg_monthly_remaining
FROM budget_analysis
ORDER BY budget_utilization DESC;

运营报表

1. 销售业绩报表

-- 销售人员业绩报表
WITH sales_performance AS (
    SELECT 
        e.employee_id,
        e.name as sales_person,
        d.department_name,
        COUNT(ft.transaction_id) as total_transactions,
        SUM(CASE WHEN ft.transaction_type = 'income' THEN ft.amount ELSE 0 END) as total_sales,
        AVG(CASE WHEN ft.transaction_type = 'income' THEN ft.amount ELSE NULL END) as avg_transaction_value,
        SUM(CASE WHEN ft.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
                 AND ft.transaction_type = 'income' THEN ft.amount ELSE 0 END) as last_30_days_sales,
        SUM(CASE WHEN ft.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
                 AND ft.transaction_type = 'income' THEN ft.amount ELSE 0 END) as last_90_days_sales
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id
    LEFT JOIN financial_transactions ft ON e.employee_id = ft.created_by
    WHERE e.status = 'active' 
      AND d.department_name LIKE '%销售%'
      AND ft.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 365 DAY)
    GROUP BY e.employee_id, e.name, d.department_name
),
performance_ranking AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY total_sales DESC) as sales_rank,
        NTILE(4) OVER (ORDER BY total_sales) as performance_quartile
    FROM sales_performance
)
SELECT 
    sales_person,
    department_name,
    total_sales,
    last_30_days_sales,
    last_90_days_sales,
    avg_transaction_value,
    sales_rank,
    CASE performance_quartile
        WHEN 4 THEN '优秀'
        WHEN 3 THEN '良好'
        WHEN 2 THEN '一般'
        ELSE '需改进'
    END as performance_level,
    ROUND(last_30_days_sales * 12, 2) as projected_annual_sales,
    ROUND((last_30_days_sales - last_90_days_sales/3) * 100.0 / NULLIF(last_90_days_sales/3, 0), 2) as growth_trend
FROM performance_ranking
ORDER BY total_sales DESC;

2. 项目进度报表

-- 项目执行情况报表
WITH project_financials AS (
    SELECT 
        p.project_id,
        SUM(CASE WHEN ft.transaction_type = 'expense' THEN ft.amount ELSE 0 END) as actual_cost,
        SUM(CASE WHEN ft.transaction_type = 'income' THEN ft.amount ELSE 0 END) as actual_revenue
    FROM projects p
    LEFT JOIN financial_transactions ft ON p.project_id = ft.project_id
    GROUP BY p.project_id
),
project_timeline AS (
    SELECT 
        project_id,
        DATEDIFF(CURDATE(), start_date) as days_elapsed,
        DATEDIFF(end_date, start_date) as total_duration,
        CASE 
            WHEN end_date < CURDATE() AND status != 'completed' THEN 'overdue'
            WHEN CURDATE() BETWEEN start_date AND end_date THEN 'in_progress'
            WHEN start_date > CURDATE() THEN 'not_started'
            ELSE 'completed'
        END as timeline_status
    FROM projects
    WHERE start_date IS NOT NULL AND end_date IS NOT NULL
)
SELECT 
    p.project_name,
    p.project_code,
    p.status,
    pt.timeline_status,
    p.budget,
    pf.actual_cost,
    pf.actual_revenue,
    p.budget - COALESCE(pf.actual_cost, 0) as budget_remaining,
    ROUND(COALESCE(pf.actual_cost, 0) * 100.0 / NULLIF(p.budget, 0), 2) as budget_utilization,
    ROUND(COALESCE(pt.days_elapsed, 0) * 100.0 / NULLIF(pt.total_duration, 0), 2) as time_progress,
    e.name as project_manager,
    CASE 
        WHEN pt.timeline_status = 'overdue' THEN '延期'
        WHEN COALESCE(pf.actual_cost, 0) > p.budget * 1.1 THEN '超预算'
        WHEN pt.timeline_status = 'in_progress' AND 
             COALESCE(pt.days_elapsed, 0) * 100.0 / NULLIF(pt.total_duration, 0) > 
             COALESCE(pf.actual_cost, 0) * 100.0 / NULLIF(p.budget, 0) + 20 THEN '进度滞后'
        ELSE '正常'
    END as risk_status
FROM projects p
LEFT JOIN project_financials pf ON p.project_id = pf.project_id
LEFT JOIN project_timeline pt ON p.project_id = pt.project_id
LEFT JOIN employees e ON p.manager_id = e.employee_id
WHERE p.status IN ('active', 'planning')
ORDER BY 
    CASE 
        WHEN pt.timeline_status = 'overdue' THEN 1
        WHEN COALESCE(pf.actual_cost, 0) > p.budget * 1.1 THEN 2
        ELSE 3
    END,
    p.project_name;

管理报表

1. 高管仪表板

-- 高管仪表板关键指标
WITH kpi_metrics AS (
    -- 财务指标
    SELECT 
        'financial' as category,
        'monthly_revenue' as metric_name,
        SUM(CASE WHEN transaction_type = 'income' 
                 AND transaction_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') 
                 THEN amount ELSE 0 END) as current_value,
        SUM(CASE WHEN transaction_type = 'income' 
                 AND transaction_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                 AND transaction_date < DATE_FORMAT(CURDATE(), '%Y-%m-01')
                 THEN amount ELSE 0 END) as previous_value
    FROM financial_transactions
    
    UNION ALL
    
    -- 成本指标
    SELECT 
        'financial',
        'monthly_expense',
        SUM(CASE WHEN transaction_type = 'expense' 
                 AND transaction_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') 
                 THEN amount ELSE 0 END),
        SUM(CASE WHEN transaction_type = 'expense' 
                 AND transaction_date >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                 AND transaction_date < DATE_FORMAT(CURDATE(), '%Y-%m-01')
                 THEN amount ELSE 0 END)
    FROM financial_transactions
    
    UNION ALL
    
    -- 项目指标
    SELECT 
        'operational',
        'active_projects',
        COUNT(CASE WHEN status = 'active' THEN 1 END),
        NULL
    FROM projects
    
    UNION ALL
    
    -- 员工指标
    SELECT 
        'operational',
        'active_employees',
        COUNT(CASE WHEN status = 'active' THEN 1 END),
        NULL
    FROM employees
)
SELECT 
    category,
    metric_name,
    current_value,
    previous_value,
    CASE 
        WHEN previous_value IS NOT NULL AND previous_value != 0 THEN
            ROUND((current_value - previous_value) * 100.0 / previous_value, 2)
        ELSE NULL
    END as growth_rate,
    CASE 
        WHEN metric_name = 'monthly_revenue' AND current_value < previous_value * 0.95 THEN 'warning'
        WHEN metric_name = 'monthly_expense' AND current_value > previous_value * 1.1 THEN 'warning'
        ELSE 'normal'
    END as status
FROM kpi_metrics
ORDER BY category, metric_name;

2. 部门绩效对比

-- 部门绩效综合对比
WITH department_metrics AS (
    SELECT 
        d.department_id,
        d.department_name,
        COUNT(e.employee_id) as employee_count,
        AVG(e.salary) as avg_salary,
        SUM(CASE WHEN ft.transaction_type = 'income' THEN ft.amount ELSE 0 END) as total_revenue,
        SUM(CASE WHEN ft.transaction_type = 'expense' THEN ft.amount ELSE 0 END) as total_expense,
        COUNT(DISTINCT p.project_id) as project_count,
        AVG(CASE WHEN p.status = 'completed' THEN 
                DATEDIFF(p.end_date, p.start_date) ELSE NULL END) as avg_project_duration
    FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id AND e.status = 'active'
    LEFT JOIN financial_transactions ft ON d.department_id = ft.department_id 
        AND ft.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    LEFT JOIN projects p ON d.department_id IN (
        SELECT DISTINCT e2.department_id 
        FROM employees e2 
        WHERE e2.employee_id = p.manager_id
    )
    GROUP BY d.department_id, d.department_name
),
performance_scores AS (
    SELECT 
        *,
        total_revenue - total_expense as net_contribution,
        CASE WHEN employee_count > 0 THEN total_revenue / employee_count ELSE 0 END as revenue_per_employee,
        CASE WHEN total_expense > 0 THEN total_revenue / total_expense ELSE 0 END as roi_ratio
    FROM department_metrics
),
ranked_departments AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY net_contribution DESC) as contribution_rank,
        ROW_NUMBER() OVER (ORDER BY revenue_per_employee DESC) as efficiency_rank,
        ROW_NUMBER() OVER (ORDER BY roi_ratio DESC) as roi_rank
    FROM performance_scores
    WHERE employee_count > 0
)
SELECT 
    department_name,
    employee_count,
    ROUND(avg_salary, 0) as avg_salary,
    total_revenue,
    total_expense,
    net_contribution,
    ROUND(revenue_per_employee, 0) as revenue_per_employee,
    ROUND(roi_ratio, 2) as roi_ratio,
    project_count,
    ROUND(avg_project_duration, 0) as avg_project_days,
    contribution_rank,
    efficiency_rank,
    roi_rank,
    ROUND((contribution_rank + efficiency_rank + roi_rank) / 3.0, 1) as overall_score
FROM ranked_departments
ORDER BY overall_score;

专项分析报表

1. 成本分析报表

-- 成本结构分析
WITH cost_analysis AS (
    SELECT 
        DATE_FORMAT(transaction_date, '%Y-%m') as month,
        category,
        subcategory,
        SUM(amount) as total_cost,
        COUNT(*) as transaction_count
    FROM financial_transactions
    WHERE transaction_type = 'expense'
      AND transaction_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    GROUP BY DATE_FORMAT(transaction_date, '%Y-%m'), category, subcategory
),
cost_trends AS (
    SELECT 
        category,
        subcategory,
        SUM(total_cost) as annual_cost,
        AVG(total_cost) as avg_monthly_cost,
        STDDEV(total_cost) as cost_volatility,
        MIN(total_cost) as min_monthly_cost,
        MAX(total_cost) as max_monthly_cost
    FROM cost_analysis
    GROUP BY category, subcategory
),
cost_percentage AS (
    SELECT 
        *,
        annual_cost * 100.0 / SUM(annual_cost) OVER () as cost_percentage,
        CASE 
            WHEN cost_volatility / NULLIF(avg_monthly_cost, 0) > 0.3 THEN '高波动'
            WHEN cost_volatility / NULLIF(avg_monthly_cost, 0) > 0.15 THEN '中波动'
            ELSE '低波动'
        END as volatility_level
    FROM cost_trends
)
SELECT 
    category,
    subcategory,
    ROUND(annual_cost, 2) as annual_cost,
    ROUND(avg_monthly_cost, 2) as avg_monthly_cost,
    ROUND(cost_percentage, 2) as cost_percentage,
    volatility_level,
    ROUND(cost_volatility, 2) as cost_volatility,
    ROUND(max_monthly_cost - min_monthly_cost, 2) as cost_range,
    CASE 
        WHEN cost_percentage > 20 THEN '重点关注'
        WHEN cost_percentage > 10 THEN '重要成本'
        WHEN cost_percentage > 5 THEN '一般成本'
        ELSE '次要成本'
    END as cost_importance
FROM cost_percentage
ORDER BY annual_cost DESC;

2. 趋势分析报表

-- 业务趋势分析
WITH monthly_trends AS (
    SELECT 
        DATE_FORMAT(transaction_date, '%Y-%m') as month,
        SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END) as revenue,
        SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END) as expense,
        COUNT(DISTINCT CASE WHEN transaction_type = 'income' THEN created_by END) as active_sales_people,
        COUNT(CASE WHEN transaction_type = 'income' THEN 1 END) as revenue_transactions
    FROM financial_transactions
    WHERE transaction_date >= DATE_SUB(CURDATE(), INTERVAL 24 MONTH)
    GROUP BY DATE_FORMAT(transaction_date, '%Y-%m')
),
trend_calculations AS (
    SELECT 
        month,
        revenue,
        expense,
        revenue - expense as profit,
        active_sales_people,
        revenue_transactions,
        LAG(revenue, 1) OVER (ORDER BY month) as prev_month_revenue,
        LAG(revenue, 12) OVER (ORDER BY month) as prev_year_revenue,
        AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as ma3_revenue,
        AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) as ma6_revenue
    FROM monthly_trends
),
final_trends AS (
    SELECT 
        month,
        revenue,
        expense,
        profit,
        ROUND(profit * 100.0 / NULLIF(revenue, 0), 2) as profit_margin,
        ROUND((revenue - prev_month_revenue) * 100.0 / NULLIF(prev_month_revenue, 0), 2) as mom_growth,
        ROUND((revenue - prev_year_revenue) * 100.0 / NULLIF(prev_year_revenue, 0), 2) as yoy_growth,
        ROUND(ma3_revenue, 2) as ma3_revenue,
        ROUND(ma6_revenue, 2) as ma6_revenue,
        active_sales_people,
        revenue_transactions,
        ROUND(revenue / NULLIF(active_sales_people, 0), 2) as revenue_per_salesperson,
        ROUND(revenue / NULLIF(revenue_transactions, 0), 2) as avg_transaction_value
    FROM trend_calculations
)
SELECT 
    month,
    revenue,
    expense,
    profit,
    profit_margin,
    mom_growth,
    yoy_growth,
    ma3_revenue,
    ma6_revenue,
    revenue_per_salesperson,
    avg_transaction_value,
    CASE 
        WHEN yoy_growth > 20 THEN '高增长'
        WHEN yoy_growth > 10 THEN '稳定增长'
        WHEN yoy_growth > 0 THEN '缓慢增长'
        WHEN yoy_growth > -10 THEN '轻微下降'
        ELSE '显著下降'
    END as growth_status,
    CASE 
        WHEN revenue > ma6_revenue * 1.1 THEN '超预期'
        WHEN revenue < ma6_revenue * 0.9 THEN '低于预期'
        ELSE '符合预期'
    END as performance_vs_trend
FROM final_trends
WHERE month >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), '%Y-%m')
ORDER BY month DESC;

报表自动化

1. 报表调度存储过程

-- 创建月度报表汇总存储过程
DELIMITER //
CREATE PROCEDURE GenerateMonthlyReport(
    IN report_month DATE
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 清理旧数据
    DELETE FROM monthly_report_summary 
    WHERE report_date = DATE_FORMAT(report_month, '%Y-%m-01');
    
    -- 插入财务汇总
    INSERT INTO monthly_report_summary (
        report_date, report_type, metric_name, metric_value, created_at
    )
    SELECT 
        DATE_FORMAT(report_month, '%Y-%m-01'),
        'financial',
        'total_revenue',
        SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END),
        NOW()
    FROM financial_transactions
    WHERE DATE_FORMAT(transaction_date, '%Y-%m') = DATE_FORMAT(report_month, '%Y-%m');
    
    -- 插入其他指标...
    
    COMMIT;
END //
DELIMITER ;

2. 报表性能优化

-- 创建报表专用的汇总表
CREATE TABLE daily_summary (
    summary_date DATE PRIMARY KEY,
    total_revenue DECIMAL(15,2),
    total_expense DECIMAL(15,2),
    transaction_count INT,
    unique_customers INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建增量更新的存储过程
DELIMITER //
CREATE PROCEDURE UpdateDailySummary(
    IN target_date DATE
)
BEGIN
    INSERT INTO daily_summary (
        summary_date, total_revenue, total_expense, transaction_count, unique_customers
    )
    SELECT 
        target_date,
        SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END),
        SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END),
        COUNT(*),
        COUNT(DISTINCT created_by)
    FROM financial_transactions
    WHERE DATE(transaction_date) = target_date
    ON DUPLICATE KEY UPDATE
        total_revenue = VALUES(total_revenue),
        total_expense = VALUES(total_expense),
        transaction_count = VALUES(transaction_count),
        unique_customers = VALUES(unique_customers),
        updated_at = NOW();
END //
DELIMITER ;

报表最佳实践

1. 性能优化

• 使用适当的索引支持报表查询
• 创建汇总表减少实时计算
• 使用分区表处理大数据量
• 合理使用缓存机制

2. 数据准确性

• 建立数据验证机制
• 实施数据质量检查
• 定期进行数据对账
• 建立异常数据告警

3. 报表设计

• 明确报表目标和受众
• 选择合适的可视化方式
• 提供钻取和筛选功能
• 确保报表的可读性

4. 维护管理

• 建立报表版本控制
• 定期审查报表需求
• 优化查询性能
• 建立报表使用文档

总结

通过这些报表查询实战案例，我们学习了：

• 财务报表的构建方法
• 运营报表的设计思路
• 管理报表的关键指标
• 专项分析的深度挖掘
• 报表自动化的实现
• 性能优化的最佳实践

这些技能可以帮助业务分析师和数据工程师构建高质量的报表系统，为企业决策提供有力支持。