多时间段SQL查询请求：按客户孵化期统计有效工单数量

需求概述 #

针对客户A、B、C，基于数据表中存储的各自孵化期起止日期，统计每个客户在孵化期内产生的工单数量，排除孵化期外的工单记录。已知三客户全年工单总数分别为100、150、200。

基于SQL的实现方案 #

假设存在两张核心数据表：

• customer：存储客户基础信息，字段包括customer_name（客户名称）、incubation_start（孵化开始日期）、incubation_end（孵化结束日期）
• ticket：存储工单记录，字段包括ticket_id（工单ID）、customer_name（关联客户名称）、create_date（工单创建日期）

执行以下SQL查询即可得到结果：

SELECT
    c.customer_name,
    COUNT(t.ticket_id) AS incubation_ticket_count
FROM
    customer c
LEFT JOIN
    ticket t 
    ON c.customer_name = t.customer_name
    AND t.create_date BETWEEN c.incubation_start AND c.incubation_end
WHERE
    c.customer_name IN ('A', 'B', 'C')
GROUP BY
    c.customer_name;

关键说明

• 使用LEFT JOIN保证即使客户在孵化期内无工单，也会返回0值，避免遗漏目标客户
• 通过BETWEEN关键字精准匹配工单创建日期在孵化期范围内的记录
• GROUP BY按客户名称分组，统计符合条件的工单总数

基于Python Pandas的实现方案 #

如果使用Python处理数据集，假设已有两个DataFrame：

• customer_df：包含customer_name、incubation_start、incubation_end列
• ticket_df：包含customer_name、create_date列

代码示例：

import pandas as pd

# 转换日期列格式为datetime
customer_df['incubation_start'] = pd.to_datetime(customer_df['incubation_start'])
customer_df['incubation_end'] = pd.to_datetime(customer_df['incubation_end'])
ticket_df['create_date'] = pd.to_datetime(ticket_df['create_date'])

# 合并客户表与工单表
merged_data = pd.merge(ticket_df, customer_df, on='customer_name')

# 筛选孵化期内的工单
valid_tickets = merged_data[
    (merged_data['create_date'] >= merged_data['incubation_start']) &
    (merged_data['create_date'] <= merged_data['incubation_end'])
]

# 统计各客户工单数量，并补全所有目标客户
result = valid_tickets.groupby('customer_name').size().reset_index(name='incubation_ticket_count')
result = pd.merge(customer_df[['customer_name']], result, on='customer_name', how='left').fillna(0)

# 输出结果
print(result)

关键说明

• 先转换日期格式，避免因类型不匹配导致的筛选错误
• 利用布尔索引精准筛选符合日期范围的工单记录
• 最后通过左连接补全所有目标客户，确保结果包含A、B、C三个客户的统计数据

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