计算订单存续期日期差，对期间各月订单计数加1的统计需求

需求回顾 #

需实现准确呈现月度订单典型数量的功能，规则为：订单从创建当月至关闭当月（含首尾月），每个月该订单的计数加1。举例：2017年2月创建2笔订单，则2月计数为2；订单4自6月起每个月计数加1。同时需先计算日期差，再对期间每个月份执行加1操作。

现有订单示例数据：

实现思路 #

核心是为每个订单生成其覆盖的所有月份，然后按月份（可结合仓库）分组统计订单数量。下面提供两种主流工具的实现方式：

1. SQL 实现（以PostgreSQL为例） #

用递归CTE生成每个订单的月份序列，再聚合计数：

WITH order_months AS (
    -- 初始化：获取每个订单的起始月和结束月（未关闭订单用当前日期）
    SELECT
        WAREHOUSENO,
        ORDERNO,
        DATE_TRUNC('month', ORDER_DATE) AS order_month,
        DATE_TRUNC('month', COALESCE(CLOSED_DATE, CURRENT_DATE)) AS closed_month
    FROM orders
    UNION ALL
    -- 递归生成中间月份
    SELECT
        WAREHOUSENO,
        ORDERNO,
        DATE_ADD(order_month, INTERVAL 1 MONTH),
        closed_month
    FROM order_months
    WHERE order_month < closed_month
)
-- 按仓库和月份分组统计订单数
SELECT
    WAREHOUSENO,
    TO_CHAR(order_month, 'YYYY-MM') AS month,
    COUNT(ORDERNO) AS order_count
FROM order_months
GROUP BY WAREHOUSENO, order_month
ORDER BY WAREHOUSENO, order_month;

说明：

• DATE_TRUNC('month', ...) 把日期截断到当月第一天，统一月份统计维度
• COALESCE(CLOSED_DATE, CURRENT_DATE) 处理未关闭的订单，默认统计到当前月
• 递归CTE会自动生成起始月到结束月之间的所有月份，保证每个覆盖的月都被计数

2. Python Pandas 实现 #

适合做数据分析时的批量处理：

import pandas as pd
from datetime import datetime

# 加载订单数据（替换成你的数据源）
data = [
    ['1', 'ABC', '2/22/17', '3/10/17'],
    ['2', 'DEF', '2/23/17', '4/1/17'],
    ['1', 'GHI', '6/5/17', None]
]
df = pd.DataFrame(data, columns=['WAREHOUSENO', 'ORDERNO', 'ORDER_DATE', 'CLOSED_DATE'])

# 日期格式转换与缺失值处理
df['ORDER_DATE'] = pd.to_datetime(df['ORDER_DATE'], format='%m/%d/%y')
df['CLOSED_DATE'] = pd.to_datetime(df['CLOSED_DATE'], format='%m/%d/%y').fillna(datetime.today())

# 生成每个订单覆盖的月份序列
def generate_month_range(row):
    start_month = row['ORDER_DATE'].to_period('M')
    end_month = row['CLOSED_DATE'].to_period('M')
    return pd.period_range(start=start_month, end=end_month, freq='M')

# 展开月份为单独行
df['covered_months'] = df.apply(generate_month_range, axis=1)
df_exploded = df.explode('covered_months')

# 分组统计月度订单数
monthly_order_count = df_exploded.groupby(
    ['WAREHOUSENO', 'covered_months']
)['ORDERNO'].count().reset_index(name='order_count')

# 打印结果
print(monthly_order_count)

说明：

• to_period('M') 把日期转换为月份周期（如2017-02），方便生成连续月份
• explode('covered_months') 把每个订单的月份列表展开为单独行，确保每个月都有一条记录
• 分组后计数即可得到每个仓库、每个月的订单覆盖数

结果验证 #

针对示例数据，运行后会得到如下核心结果（以2017年为例）：

• 2017-02：订单ABC、DEF都覆盖，计数为2
• 2017-03：订单ABC、DEF都覆盖，计数为2
• 2017-04：仅订单DEF覆盖，计数为1
• 2017-06及之后：订单GHI持续覆盖，每个月计数加1（如果未关闭）

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