如何按allocated_to_id统计总线索数及各LEAD_CATEGORY分类线索量？

一、SQL直接修改实现 #

基于你提供的原始查询，可将其作为子查询，通过分组聚合统计每个allocated_to_id的各类线索数：

SELECT
    allocated_to_id,
    COUNT(*) AS 总线索数,
    SUM(CASE WHEN LEAD_CATEGORY = 'OWN REFERRAL' THEN 1 ELSE 0 END) AS OWN_REFERRAL线索数,
    SUM(CASE WHEN LEAD_CATEGORY = 'Branch walkin' THEN 1 ELSE 0 END) AS Branch_walkin线索数,
    SUM(CASE WHEN LEAD_CATEGORY = 'CENTRALIZED LEAD' THEN 1 ELSE 0 END) AS CENTRALIZED_LEAD线索数
FROM (
    -- 原始查询语句
    SELECT 
        A.lead_id,
        A.allocated_to_id,
        A.leadsourceid,
        CASE 
            WHEN leadsourceid IN ('Own Referral','Field Activity') THEN 'OWN REFERRAL'
            WHEN leadsourceid IN ('Branch walk-in') THEN 'Branch walkin'
            ELSE 'CENTRALIZED LEAD'
        END AS LEAD_CATEGORY,
        A.lead_type,
        b.emp_grade,
        b.emp_dept,
        b.emp_cod_name,
        b.emp_doj
    FROM ids_dmz.CRM_ALL_LEAD_DATA A
    LEFT JOIN ids_dmz.emp_mstr b ON A.allocated_to_id = b.emp_id
    WHERE 
        b.emp_doj BETWEEN '01-Apr-2020' AND '31-Dec-2023'
        AND b.EMP_DEPT = 'BABP'
        AND b.EMP_COD_NAME IN ('Policy Bazaar','PSF NPS','Aristo','UPPER NORTH','MAHINDRA','POLICYX','DM CHANNEL','GIRNAR','PUNJAB ROYALS','RRB','BBK','STRIVE INSURANCE BROKERS','ESAF','Retirement & Protection','PNB ONLINE','CARE')
        AND b.emp_grade IN ('09S','10S','11S','12S')
) AS base_data
GROUP BY allocated_to_id
ORDER BY allocated_to_id;

核心逻辑 #

• 外层通过GROUP BY allocated_to_id实现按员工ID分组
• COUNT(*)统计该员工的总线索量
• SUM(CASE...)通过条件判断计数：符合分类条件的记1，否则记0，求和后得到对应分类的线索数

二、Pandas DataFrame统计方案 #

如果已将上述SQL结果读取到包含exactMatch列的DataFrame中，可通过以下代码实现统计：

import pandas as pd

# 假设DataFrame名为df
# 方式1：groupby+agg直接统计
grouped_result = df.groupby('allocated_to_id').agg(
    总线索数=('lead_id', 'count'),
    OWN_REFERRAL线索数=('LEAD_CATEGORY', lambda x: (x == 'OWN REFERRAL').sum()),
    Branch_walkin线索数=('LEAD_CATEGORY', lambda x: (x == 'Branch walkin').sum()),
    CENTRALIZED_LEAD线索数=('LEAD_CATEGORY', lambda x: (x == 'CENTRALIZED LEAD').sum())
).reset_index()

# 方式2：交叉表更简洁
cross_tab_result = pd.crosstab(df['allocated_to_id'], df['LEAD_CATEGORY'])
cross_tab_result['总线索数'] = cross_tab_result.sum(axis=1)
# 调整列顺序（可选）
cross_tab_result = cross_tab_result[['总线索数', 'OWN REFERRAL', 'Branch walkin', 'CENTRALIZED LEAD']].reset_index()

核心逻辑 #

• 方式1通过groupby+agg直接定义每个统计项的计算规则
• 方式2用crosstab生成交叉表自动统计各分类数量，再添加总线索数列

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