如何用SQLite3统计每周工单数量，生成单周≥20单的报销报告？

一、直接用SQL快速统计（推荐） #

从你的数据库快照来看，订单表包含日期字段，直接用SQL就能按周统计并筛选符合条件的记录，无需额外代码开发：

通用SQL（PostgreSQL、SQL Server等支持DATE_TRUNC的数据库） #

SELECT
  DATE_TRUNC('week', order_date) AS week_start_date,
  EXTRACT(YEAR FROM order_date) AS year,
  EXTRACT(WEEK FROM order_date) AS week_number,
  COUNT(order_id) AS total_orders
FROM orders
GROUP BY year, week_number, week_start_date
HAVING COUNT(order_id) >= 20
ORDER BY week_start_date;

MySQL适配版本 #

MySQL不支持DATE_TRUNC，改用DATE_FORMAT处理：

SELECT
  DATE_FORMAT(order_date, '%Y-%m-%d') AS week_start_date,
  YEAR(order_date) AS year,
  WEEK(order_date, 1) AS week_number, -- 参数1指定周一为一周起始
  COUNT(order_id) AS total_orders
FROM orders
GROUP BY year, week_number, week_start_date
HAVING COUNT(order_id) >= 20
ORDER BY week_start_date;

注意：如果公司按周日作为一周起始，调整对应的函数参数即可（比如MySQL的WEEK函数用参数0）。

二、Python自动化生成报告 #

如果需要导出成Excel等格式的正式报告，用pandas+SQLAlchemy可以快速实现：

1. 先安装依赖：

pip install pandas sqlalchemy openpyxl

2. 示例代码（按需修改数据库连接信息）：

import pandas as pd
from sqlalchemy import create_engine

# 替换为你的数据库连接字符串
# 示例：MySQL -> mysql+pymysql://用户名:密码@主机地址/数据库名
engine = create_engine('your_database_connection_string')

# 读取订单核心数据
df = pd.read_sql("SELECT order_id, order_date FROM orders", engine)

# 统一日期格式
df['order_date'] = pd.to_datetime(df['order_date'])

# 按ISO周（周一为起始）统计订单数
weekly_summary = df.resample('W-MON', on='order_date')['order_id'].count().reset_index(name='total_orders')

# 筛选达标周
eligible_weeks = weekly_summary[weekly_summary['total_orders'] >= 20]

# 补充年份、周数字段方便核对
eligible_weeks['year'] = eligible_weeks['order_date'].dt.year
eligible_weeks['week_number'] = eligible_weeks['order_date'].dt.isocalendar().week

# 导出为Excel报告
eligible_weeks.to_excel('parking_reimbursement_weeks.xlsx', index=False)

替代分组方式：如果对周起始规则有特殊要求，用ISO日历分组更精准：

# 按年份+周数分组
df['year_week'] = df['order_date'].dt.isocalendar().apply(lambda row: f"{row.year}-W{row.week:02d}", axis=1)
weekly_summary = df.groupby('year_week')['order_id'].count().reset_index(name='total_orders')
eligible_weeks = weekly_summary[weekly_summary['total_orders'] >= 20]

内容的提问来源于stack exchange，提问作者Joseph Manning