如何排除特定列值并按条件计算呼叫查询聚合指标

原始输入数据 #

字段说明：Call_ID为呼叫编号，UUID是同一场呼叫中对话轮次的唯一标识，Intent_Product为查询内容描述。

预期输出结果 #

计算规则 #

1. Resolved_Count：统计已解决的查询总数，直接排除所有Intent_Product包含"Request_Agent"的条目（此类属于未解决查询）。
2. Contained_Turns：统计已被管控的查询总数，但要排除「同一场呼叫中，该查询的后续轮次存在Intent_Product包含"Request_Agent"」的情况。
3. Contained_Calls：计算公式为 Contained_Turns / Resolved_Count，结果保留一位小数。

实现方法 #

方法一：SQL实现 #

假设数据存在名为call_records的表中，用以下SQL语句完成聚合：

WITH filtered_data AS (
    -- 筛选非Request_Agent条目，同时标记同呼叫下是否有后续Agent请求轮次
    SELECT 
        CASE 
            WHEN Intent_Product = 'Loan_BankAccount' THEN 'Loan_BankAcc'
            WHEN Intent_Product = 'StopCheque' THEN 'Stop_Cheque'
            ELSE Intent_Product 
        END AS Intent_Product,
        Call_ID,
        UUID,
        EXISTS (
            SELECT 1 
            FROM call_records r2 
            WHERE r2.Call_ID = r1.Call_ID 
              AND r2.UUID > r1.UUID 
              AND r2.Intent_Product LIKE '%Request_Agent%'
        ) AS has_followup_agent
    FROM call_records r1
    WHERE r1.Intent_Product NOT LIKE '%Request_Agent%'
),
resolved_stats AS (
    -- 统计每个Intent的已解决总数
    SELECT Intent_Product, COUNT(*) AS Resolved_Count
    FROM filtered_data
    GROUP BY Intent_Product
),
contained_stats AS (
    -- 统计每个Intent的有效管控数（排除有后续Agent请求的条目）
    SELECT Intent_Product, COUNT(*) AS Contained_Turns
    FROM filtered_data
    WHERE has_followup_agent = FALSE
    GROUP BY Intent_Product
)
-- 合并结果并计算最终比例
SELECT 
    rs.Intent_Product,
    rs.Resolved_Count,
    COALESCE(cs.Contained_Turns, 0) AS Contained_Turns,
    ROUND(COALESCE(cs.Contained_Turns, 0)::FLOAT / rs.Resolved_Count, 1) AS Contained_Calls
FROM resolved_stats rs
LEFT JOIN contained_stats cs ON rs.Intent_Product = cs.Intent_Product
ORDER BY rs.Intent_Product;

方法二：Python Pandas实现 #

假设数据已加载到Pandas DataFramedf中，代码如下：

import pandas as pd

# 1. 过滤掉含Request_Agent的条目
filtered_df = df[~df['Intent_Product'].str.contains('Request_Agent')].copy()

# 2. 统一Intent_Product命名，匹配预期输出格式
filtered_df['Intent_Product'] = filtered_df['Intent_Product'].replace({
    'Loan_BankAccount': 'Loan_BankAcc',
    'StopCheque': 'Stop_Cheque'
})

# 3. 标记每个条目所在呼叫是否有后续Agent请求轮次
agent_request_min_uuid = df[df['Intent_Product'].str.contains('Request_Agent')].groupby('Call_ID')['UUID'].min()
filtered_df['has_followup_agent'] = filtered_df.apply(
    lambda row: agent_request_min_uuid.get(row['Call_ID'], -1) > row['UUID'],
    axis=1
)

# 4. 统计各Intent的已解决总数
resolved_count = filtered_df.groupby('Intent_Product').size().reset_index(name='Resolved_Count')

# 5. 统计各Intent的有效管控数
contained_turns = filtered_df[~filtered_df['has_followup_agent']].groupby('Intent_Product').size().reset_index(name='Contained_Turns')

# 6. 合并结果并计算比例
result = pd.merge(resolved_count, contained_turns, on='Intent_Product', how='left').fillna(0)
result['Contained_Calls'] = (result['Contained_Turns'] / result['Resolved_Count']).round(1)

# 输出最终结果
print(result)

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