基于起止日期统计月度活跃客户数的技术咨询

现有客户数据表：

Customer	Start Date	End Date
1	2020-01	2020-04
2	2020-01	2020-03
3	2020-02

规则：无End Date的客户视为持续活跃，需计算每个月份的活跃客户数量，期望输出如示例所示。

SQL 实现方案 #

核心逻辑是先生成目标统计月份序列，再关联客户表判断每个客户是否在对应月份处于活跃状态。

1. 生成固定月份序列（以MySQL为例） #

如果统计范围固定（比如2020-01至2020-06），直接构造月份列表：

WITH months AS (
    SELECT '2020-01' AS month UNION ALL
    SELECT '2020-02' UNION ALL
    SELECT '2020-03' UNION ALL
    SELECT '2020-04' UNION ALL
    SELECT '2020-05' UNION ALL
    SELECT '2020-06'
)

若需动态生成（从最早开始日期到当前月份），用递归CTE：

WITH RECURSIVE months AS (
    SELECT MIN(STR_TO_DATE(start_date, '%Y-%m')) AS month_date
    FROM customers
    UNION ALL
    SELECT DATE_ADD(month_date, INTERVAL 1 MONTH)
    FROM months
    WHERE month_date <= CURDATE() -- 可替换为指定结束日期
)
SELECT DATE_FORMAT(month_date, '%Y-%m') AS month FROM months;

2. 关联客户表计算活跃数 #

将月份序列与客户表关联，判断客户活跃区间是否覆盖当前月份：

WITH months AS (
    SELECT '2020-01' AS month UNION ALL
    SELECT '2020-02' UNION ALL
    SELECT '2020-03' UNION ALL
    SELECT '2020-04' UNION ALL
    SELECT '2020-05' UNION ALL
    SELECT '2020-06'
)
SELECT
    m.month,
    COUNT(DISTINCT c.customer) AS active_customers,
    GROUP_CONCAT(DISTINCT c.customer ORDER BY c.customer SEPARATOR '+') AS customer_list
FROM months m
LEFT JOIN customers c
ON STR_TO_DATE(m.month, '%Y-%m') >= STR_TO_DATE(c.start_date, '%Y-%m')
AND (c.end_date IS NULL OR STR_TO_DATE(m.month, '%Y-%m') <= STR_TO_DATE(c.end_date, '%Y-%m'))
GROUP BY m.month
ORDER BY m.month;

该查询会直接输出每个月份的活跃客户数及对应客户列表，与示例格式匹配。

Python Pandas 实现方案 #

通过生成月度时间序列，逐个判断客户活跃区间是否覆盖目标月份，最终统计结果。

1. 准备数据与格式转换 #

import pandas as pd

# 构造客户数据集
data = {
    'Customer': [1, 2, 3],
    'Start Date': ['2020-01', '2020-01', '2020-02'],
    'End Date': ['2020-04', '2020-03', None]
}
df = pd.DataFrame(data)

# 转换为日期格式，无结束日期的设为指定统计截止月份（如2020-06）
df['Start Date'] = pd.to_datetime(df['Start Date'], format='%Y-%m')
df['End Date'] = pd.to_datetime(df['End Date'], format='%Y-%m').fillna(pd.to_datetime('2020-06'))

2. 生成月度序列并统计活跃数 #

# 生成目标统计月份列表
start_month = df['Start Date'].min().floor('D')
end_month = pd.to_datetime('2020-06')
months = pd.date_range(start=start_month, end=end_month, freq='MS').strftime('%Y-%m')

# 遍历每个月份统计活跃客户
result = []
for month in months:
    month_dt = pd.to_datetime(month)
    # 筛选当前月份活跃的客户
    active_customers = df[(df['Start Date'] <= month_dt) & (df['End Date'] >= month_dt)]
    # 整理结果格式
    result.append({
        '月份': month,
        '活跃客户数': len(active_customers),
        '客户列表': '+'.join(map(str, active_customers['Customer'].tolist()))
    })

# 打印或输出结果
for item in result:
    print(f"{item['月份']}：活跃客户数={item['活跃客户数']}（客户{item['客户列表']}）")

运行后会直接输出与示例完全一致的结果文本。

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