怎样实现订单汇总报表脚本

本文目录导读：

1. SQL 脚本 (最常用，用于数据库直接汇总)
2. Python 脚本 (使用 Pandas 处理数据)
3. Shell/Bash 脚本 (从日志或CSV文件汇总)
4. Node.js 脚本 (使用 Sequelize ORM)
5. 选择合适的方案
6. 注意要点

SQL 脚本 (最常用，用于数据库直接汇总)

适用于：报表工具、数据库任务、定时任务。

-- 1. 按日期统计每日订单汇总
SELECT 
    DATE(order_date) AS report_date,
    COUNT(order_id) AS total_orders,
    SUM(order_amount) AS total_amount,
    AVG(order_amount) AS avg_order_amount,
    COUNT(DISTINCT user_id) AS unique_buyers,
    SUM(IF(order_status = 'completed', 1, 0)) AS completed_orders,
    SUM(IF(order_status = 'cancelled', 1, 0)) AS cancelled_orders
FROM orders
WHERE order_date >= '2023-01-01' 
  AND order_date < '2023-12-31'
GROUP BY DATE(order_date)
ORDER BY report_date DESC;
-- 2. 按产品类别统计
SELECT 
    p.category,
    COUNT(oi.order_id) AS order_count,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.subtotal) AS total_revenue,
    ROUND(AVG(oi.subtotal), 2) AS avg_revenue_per_order
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY total_revenue DESC;
-- 3. 月度汇总（包含上期比较）
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(order_id) AS orders,
    SUM(order_amount) AS revenue,
    LAG(SUM(order_amount), 1) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')) AS prev_month_revenue,
    ROUND((SUM(order_amount) - LAG(SUM(order_amount), 1) OVER (
        ORDER BY DATE_FORMAT(order_date, '%Y-%m')
    )) / LAG(SUM(order_amount), 1) OVER (
        ORDER BY DATE_FORMAT(order_date, '%Y-%m')
    ) * 100, 2) AS month_over_month_growth
FROM orders
GROUP BY month
ORDER BY month DESC;

Python 脚本 (使用 Pandas 处理数据)

适用于：从多个数据源取数、需要复杂数据清洗、生成 Excel/CSV 报表。

import pandas as pd
from datetime import datetime, timedelta
# ========== 模拟数据（实际从数据库读取） ==========
# 示例：从CSV或DB读取
# df = pd.read_sql("SELECT * FROM orders", engine)
# 模拟订单数据
data = {
    'order_id': [1, 2, 3, 4, 5],
    'user_id': [101, 102, 101, 103, 102],
    'order_date': ['2023-06-01', '2023-06-01', '2023-06-02', '2023-06-02', '2023-06-03'],
    'order_amount': [100.0, 250.0, 300.0, 150.0, 200.0],
    'order_status': ['completed', 'completed', 'completed', 'cancelled', 'completed']
}
df = pd.DataFrame(data)
df['order_date'] = pd.to_datetime(df['order_date'])
# ========== 核心汇总函数 ==========
def generate_order_summary(df, period='day'):
    """
    生成订单汇总报表
    :param df: 原始订单DataFrame
    :param period: 汇总粒度 'day', 'week', 'month', 'quarter'
    :return: 汇总后的DataFrame
    """
    # 创建时间分组列
    if period == 'day':
        df['group_date'] = df['order_date'].dt.date
    elif period == 'week':
        df['group_date'] = df['order_date'].dt.isocalendar().week.astype(str) + '-' + df['order_date'].dt.year.astype(str)
    elif period == 'month':
        df['group_date'] = df['order_date'].dt.to_period('M')
    elif period == 'quarter':
        df['group_date'] = df['order_date'].dt.to_period('Q')
    elif period == 'year':
        df['group_date'] = df['order_date'].dt.year
    # 汇总计算
    summary = df.groupby('group_date').agg(
        订单总数=('order_id', 'count'),
        总金额=('order_amount', 'sum'),
        平均订单金额=('order_amount', 'mean'),
        最大订单金额=('order_amount', 'max'),
        最小订单金额=('order_amount', 'min'),
        独立买家数=('user_id', 'nunique'),
        完成订单数=('order_status', lambda x: (x == 'completed').sum()),
        取消订单数=('order_status', lambda x: (x == 'cancelled').sum())
    ).reset_index()
    # 添加计算字段
    summary['完成率'] = (summary['完成订单数'] / summary['订单总数'] * 100).round(2)
    summary['取消率'] = (summary['取消订单数'] / summary['订单总数'] * 100).round(2)
    # 排序
    summary = summary.sort_values('group_date', ascending=False).reset_index(drop=True)
    return summary
# ========== 生成并输出报表 ==========
day_summary = generate_order_summary(df, 'day')
month_summary = generate_order_summary(df, 'month')
print("=== 日报表 ===")
print(day_summary)
print("\n=== 月报表 ===")
print(month_summary)
# 导出到Excel
with pd.ExcelWriter('订单汇总报表.xlsx') as writer:
    day_summary.to_excel(writer, sheet_name='日报表', index=False)
    month_summary.to_excel(writer, sheet_name='月报表', index=False)
print("\n✅ 报表已导出到 '订单汇总报表.xlsx'")
# ========== 可选：生成趋势图 ==========
import matplotlib.pyplot as plt
plt.figure(figsize=(12, 6))
plt.plot(day_summary['group_date'], day_summary['总金额'], marker='o', label='每日销售额')'订单金额趋势')
plt.xlabel('日期')
plt.ylabel('金额')
plt.xticks(rotation=45)
plt.legend()
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.savefig('订单趋势图.png')

Shell/Bash 脚本 (从日志或CSV文件汇总)

适用于：Linux服务器环境中,快速处理文本日志或CSV文件。

#!/bin/bash
# 订单汇总报表生成脚本
# 假设订单文件格式：order_id,user_id,date,amount,status
# 示例：1001,101,2023-06-01,250.00,completed
INPUT_FILE="orders.csv"
OUTPUT_FILE="order_summary_report.txt"
# 检查输入文件
if [ ! -f "$INPUT_FILE" ]; then
    echo "错误：找不到输入文件 $INPUT_FILE"
    exit 1
fi
echo "=== 订单汇总报表 ===" > "$OUTPUT_FILE"
echo "生成时间：$(date '+%Y-%m-%d %H:%M:%S')" >> "$OUTPUT_FILE"
echo "===========================" >> "$OUTPUT_FILE"
# 1. 基本统计
total_orders=$(awk -F',' 'NR>1 {count++} END {print count}' "$INPUT_FILE")
total_amount=$(awk -F',' 'NR>1 {sum+=$4} END {printf "%.2f", sum}' "$INPUT_FILE")
avg_amount=$(awk -F',' 'NR>1 {sum+=$4; count++} END {printf "%.2f", sum/count}' "$INPUT_FILE")
echo "订单总数：$total_orders" >> "$OUTPUT_FILE"
echo "总金额：$total_amount" >> "$OUTPUT_FILE"
echo "平均金额：$avg_amount" >> "$OUTPUT_FILE"
echo "" >> "$OUTPUT_FILE"
# 2. 按日期统计日汇总
echo "=== 每日汇总 ===" >> "$OUTPUT_FILE"
awk -F',' 'NR>1 {
    date=$3;
    count[date]++;
    sum[date]+=$4;
} END {
    for(d in count) {
        printf "%s | 订单数: %d | 金额: %.2f\n", d, count[d], sum[d]
    }
}' "$INPUT_FILE" | sort >> "$OUTPUT_FILE"
echo "" >> "$OUTPUT_FILE"
# 3. 按状态统计
echo "=== 订单状态分布 ===" >> "$OUTPUT_FILE"
awk -F',' 'NR>1 {
    status=$5;
    status_count[status]++;
} END {
    for(s in status_count) {
        printf "%s: %d单 (%.2f%%)\n", s, status_count[s], status_count[s]/total*100
    }
}' "$INPUT_FILE" total=$total_orders >> "$OUTPUT_FILE"
echo "" >> "$OUTPUT_FILE"
# 4. 按客户统计（Top 10）
echo "=== Top 10 客户（按订单金额） ===" >> "$OUTPUT_FILE"
awk -F',' 'NR>1 {
    user=$2;
    amount=$4;
    user_sum[user]+=amount;
    user_count[user]++;
} END {
    for(u in user_sum) {
        printf "%s | %d单 | %.2f元\n", u, user_count[u], user_sum[u]
    }
}' "$INPUT_FILE" | sort -t'|' -k3 -rn | head -10 >> "$OUTPUT_FILE"
echo "" >> "$OUTPUT_FILE"
echo "✅ 报表已生成：$OUTPUT_FILE"

Node.js 脚本 (使用 Sequelize ORM)

适用于：Node.js 后端应用中生成报表。

const { Sequelize, DataTypes } = require('sequelize');
const ExcelJS = require('exceljs');
// 数据库连接配置
const sequelize = new Sequelize('database', 'username', 'password', {
    host: 'localhost',
    dialect: 'mysql'
});
// 定义 Order 模型
const Order = sequelize.define('Order', {
    id: { type: DataTypes.INTEGER, primaryKey: true },
    userId: DataTypes.INTEGER,
    totalAmount: DataTypes.DECIMAL(10, 2),
    status: DataTypes.STRING,
    createdAt: DataTypes.DATE
}, { tableName: 'orders', timestamps: false });
// ========== 生成汇总报表 ==========
async function generateOrderSummaryReport() {
    try {
        // 1. 按日期分组汇总
        const dailySummary = await Order.findAll({
            attributes: [
                [sequelize.fn('DATE', sequelize.col('createdAt')), 'date'],
                [sequelize.fn('COUNT', sequelize.col('id')), 'orderCount'],
                [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalAmount'],
                [sequelize.fn('AVG', sequelize.col('totalAmount')), 'avgAmount'],
                [sequelize.fn('COUNT', sequelize.fn('DISTINCT', sequelize.col('userId'))), 'uniqueUsers']
            ],
            group: [sequelize.fn('DATE', sequelize.col('createdAt'))],
            order: [[sequelize.fn('DATE', sequelize.col('createdAt')), 'DESC']],
            raw: true
        });
        // 2. 按月份汇总（消费趋势）
        const monthlySummary = await Order.findAll({
            attributes: [
                [sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), '%Y-%m'), 'month'],
                [sequelize.fn('COUNT', sequelize.col('id')), 'orderCount'],
                [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalAmount']
            ],
            where: { status: 'completed' },
            group: [sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), '%Y-%m')],
            order: [[sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), '%Y-%m'), 'DESC']],
            raw: true
        });
        // 3. 状态分布
        const statusDistribution = await Order.findAll({
            attributes: [
                'status',
                [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
                [sequelize.fn('SUM', sequelize.col('totalAmount')), 'totalAmount']
            ],
            group: ['status'],
            raw: true
        });
        // 创建 Excel 报表
        const workbook = new ExcelJS.Workbook();
        // 添加每日汇总工作表
        const dailySheet = workbook.addWorksheet('每日汇总');
        dailySheet.columns = [
            { header: '日期', key: 'date', width: 15 },
            { header: '订单数', key: 'orderCount', width: 12 },
            { header: '总金额', key: 'totalAmount', width: 15 },
            { header: '平均金额', key: 'avgAmount', width: 12 },
            { header: '独立用户', key: 'uniqueUsers', width: 12 }
        ];
        dailySheet.addRows(dailySummary);
        // 添加月度趋势工作表
        const monthlySheet = workbook.addWorksheet('月度趋势');
        monthlySheet.columns = [
            { header: '月份', key: 'month', width: 10 },
            { header: '订单数', key: 'orderCount', width: 12 },
            { header: '总金额', key: 'totalAmount', width: 15 }
        ];
        monthlySheet.addRows(monthlySummary);
        // 添加状态分布工作表
        const statusSheet = workbook.addWorksheet('状态分布');
        statusSheet.columns = [
            { header: '状态', key: 'status', width: 15 },
            { header: '订单数', key: 'count', width: 12 },
            { header: '总金额', key: 'totalAmount', width: 15 }
        ];
        statusSheet.addRows(statusDistribution);
        // 保存文件
        const filename = `订单汇总报表_${new Date().toISOString().slice(0,10)}.xlsx`;
        await workbook.xlsx.writeFile(filename);
        console.log(`✅ 报表已生成: ${filename}`);
    } catch (error) {
        console.error('生成报表失败:', error);
    } finally {
        await sequelize.close();
    }
}
// 执行
generateOrderSummaryReport();

选择合适的方案

注意要点

1. 数据量大时：考虑分页查询、使用索引、限制时间范围
2. 性能优化：定期对订单表的 order_date 和 status 字段建立索引
3. 定时任务：使用 cron(Linux) 或 任务调度器 定期自动执行脚本
4. 异常处理：加入重试机制、记录错误日志
5. 数据校验：对汇总结果进行交叉验证，确保准确性

根据您的具体需求（如数据规模、技术栈、报表格式），选择最适合的实现方式，如果需要针对特定场景的更多细节,欢迎进一步说明。