按条件对前序行group by统计未结束分配商户数方案咨询

核心逻辑 #

要统计的结果本质是筛选存续期覆盖目标日期的有效分配记录，满足start date ≤ 指定日期 ≤ end date、且分配已完成，再按assigned by和日期维度分组统计商户去重数即可。

方案1：SQL实现（适用数据库存储的数据集） #

假设表名为merchant_assign，代码如下：

-- 自定义目标统计日期
SET @target_date = '2024-01-01';

SELECT 
    `assigned by`,
    DATE(`start date`) AS stat_date,
    COUNT(DISTINCT merchant_id) AS alive_merchant_cnt
FROM merchant_assign
WHERE 
    `start date` <= @target_date
    AND `end date` >= @target_date
    -- 可补充分配已完成的状态判断条件，例如 assign_status = 'completed'
GROUP BY `assigned by`, DATE(`start date`)
ORDER BY `assigned by`, stat_date;

如果需要生成连续日期的每日统计结果，可关联日期维表实现：

WITH RECURSIVE date_list AS (
    SELECT MIN(`start date`) AS dt FROM merchant_assign
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM date_list WHERE dt < CURDATE()
)
SELECT 
    a.`assigned by`,
    d.dt AS stat_date,
    COUNT(DISTINCT a.merchant_id) AS alive_merchant_cnt
FROM date_list d
LEFT JOIN merchant_assign a
    ON d.dt BETWEEN a.`start date` AND a.`end date`
    -- 补充分配已完成的状态判断条件
GROUP BY a.`assigned by`, d.dt
ORDER BY a.`assigned by`, d.dt;

方案2：Pandas实现（适用本地文件存储的数据集） #

import pandas as pd

# 读取数据集，按实际存储格式调整
df = pd.read_excel('merchant_data.xlsx')
# 日期字段格式转换
df['start date'] = pd.to_datetime(df['start date'])
df['end date'] = pd.to_datetime(df['end date'])

# 自定义目标统计日期
target_date = pd.to_datetime('2024-01-01')

# 筛选有效记录
valid_df = df[
    (df['start date'] <= target_date) &
    (df['end date'] >= target_date)
    # 补充分配已完成的过滤条件，例如 & (df['assign_status'] == '已完成')
]

# 分组统计
result = valid_df.groupby(
    ['assigned by', valid_df['start date'].dt.date]
)['merchant_id'].nunique().reset_index(name='alive_merchant_cnt')

print(result)

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