带有左外连接的 MySQL 查询无法加载

Question

我想使用LEFT OUTER JOIN从多个表生成一个大表，但在我得到结果之前服务器似乎超时了。我做错了什么吗？

SELECT
    mtl_extended_info.TM,
    mtl_extended_info.EMP_ID,
    mtl_extended_info.DNAME,
    mtl_extended_info.STATUS,
    mtl_extended_info.SHIFT,
    mtl_extended_info.SKILL,
    IFNULL(cdl_metrics_all.Sched_Adh, '-') as Sched_Adh,
    IFNULL(cdl_metrics_all.Sched_Compl, '-') as Sched_Compl,
    IFNULL(SUM(enterprise_rep_agent_stats.Calls_Handled_Ct), '-') as Call_Handled,
    IFNULL(ROUND((SUM(enterprise_rep_agent_stats.I_Talk_Time_Sec) + SUM(enterprise_rep_agent_stats.Hold_Time_Sec) + SUM(enterprise_rep_agent_stats.I_Work_Time_Sec) + SUM(enterprise_rep_agent_stats.I_AUX_Out_Time_Sec)) / SUM(enterprise_rep_agent_stats.Calls_Handled_Ct)), '-') as AHT,
    IFNULL(cdl_metrics_all.AHT_Target, '-') as AHT_Target,
    IFNULL(ROUND(SUM(enterprise_rep_agent_stats.I_Talk_Time_Sec) / SUM(enterprise_rep_agent_stats.Calls_Handled_Ct)), '-') as TALK,
    IFNULL(ROUND(SUM(enterprise_rep_agent_stats.I_Work_Time_Sec) / SUM(enterprise_rep_agent_stats.Calls_Handled_Ct)), '-') as ACW,
    IFNULL(ROUND(SUM(enterprise_rep_agent_stats.Hold_Time_Sec) / SUM(enterprise_rep_agent_stats.Calls_Handled_Ct)), '-') as Hold,
    IFNULL(SUM(cdl_rr7_all.Score), '-') as R7,
    IFNULL(cdl_deact_all.Deact_Rate_Excl_Port, '-') AS DeactWOPort,
    IFNULL(cdl_deact_all.Deact_Rate_Incl_Port, '-') AS DeactPort,
    IFNULL(cdl_deact_all.Deac_Target, '-') as Deact_Target,
    IFNULL(TRUNCATE((SUM(cdl_cmp_oe_all.CMP_OE_Virtual) / SUM(cdl_cmp_oe_all.CMP_Surveys)), 2), '-') AS CMP_OE,
    IFNULL(SUM(cdl_cmp_oe_all.CMP_Surveys), '-') AS CMP_Surveys,
    IFNULL(cdl_metrics_all.ICV_WR, '-') as ICV_WR,
    IFNULL(cdl_metrics_all.ICV_WR_Target, '-') as ICV_WR_Target,
    IFNULL(cdl_metrics_all.ICV_CA, '-') as ICV_CA,
    IFNULL(cdl_metrics_all.ICV_CA_Target, '-') as ICV_CA_Target
FROM mtl_extended_info
    LEFT OUTER JOIN enterprise_rep_agent_stats ON enterprise_rep_agent_stats.Employee_ID = mtl_extended_info.EMP_ID
    LEFT OUTER JOIN cdl_metrics_all ON cdl_metrics_all.EMP_ID = mtl_extended_info.EMP_ID
    LEFT OUTER JOIN cdl_rr7_all ON cdl_rr7_all.EMP_ID = mtl_extended_info.EMP_ID
    LEFT OUTER JOIN cdl_deact_all ON cdl_deact_all.EMP_ID = mtl_extended_info.EMP_ID
    LEFT OUTER JOIN cdl_cmp_oe_all ON cdl_cmp_oe_all.EMP_ID = mtl_extended_info.EMP_ID
WHERE VISIBLE = 1
GROUP BY mtl_extended_info.EMP_ID, enterprise_rep_agent_stats.Month_Date
ORDER BY SM, TM

预期结果(这只是我期望的 300 行中的一行):

TM          |   EMP_ID  |   DNAME       |   STATUS  |   mtl_extended_info.SHIFT |   mtl_extended_info.SKILL |   Sched_Adh   |   Sched_Compl |   Call_Handled    |   AHT |   AHT_Target  |   TALK    |   ACW |   Hold    |   R7  |   DeactWOPort |   DeactPort   |   Deact_Target    |   CMP_OE  |   CMP_Surveys |   ICV_WR  |   ICV_WR_Target   |   ICV_CA  |   ICV_CA_Target
Peter Pan   |   841255  |   John Doe    |   Active  |   Full Time               |   Cable Repair            |   90%         |   95%         |   641             |   954 |   800         |   641     |   83  |   56      |   86% |   73%         |   74%         |   Deact_Target    |   100%    |   14          |   86      |   1.4             |   -14.87  |   -12.4

我收到以下错误:错误代码:1317。查询执行被中断

Answer 1

作为起点，尝试这样的事情。必然存在语法错误。每个表都可以受益于 emp_id(或 month_date、employee_id)上的索引

Select
    ei.TM,
    ei.emp_id,
    ei.DNAME,
    ei.STATUS,
    ei.SHIFT,
    ei.SKILL,
    ifnull(cm.Sched_Adh, '-') as Sched_Adh,
    ifnull(cm.Sched_Compl, '-') as Sched_Compl,
    ifnull(es.Call_Handled_Ct, '-') as Call_Handled,
    ifnull(es.AHT, '-') as AHT,
    ifnull(cm.AHT_Target, '-') as AHT_Target,
    ifnull(es.TALK, '-') as TALK,
    ifnull(es.ACW, '-') as ACW,
    ifnull(es.Hold, '-') as Hold,
    ifnull(cr.R7, '-') as R7,
    ifnull(cd.Deact_Rate_Excl_Port, '-') AS DeactWOPort,
    ifnull(cd.Deact_Rate_Incl_Port, '-') AS DeactPort,
    ifnull(cd.Deac_Target, '-') as Deact_Target,
    ifnull(cc.CMP_OE, '-') AS CMP_OE,
    ifnull(cc.CMP_Surveys, '-') AS CMP_Surveys,
    ifnull(cm.ICV_WR, '-') as ICV_WR,
    ifnull(cm.ICV_WR_Target, '-') as ICV_WR_Target,
    ifnull(cm.ICV_CA, '-') as ICV_CA,
    ifnull(cm.ICV_CA_Target, '-') as ICV_CA_Target
From
    mtl_extended_info ei
    Left Outer Join cdl_metrics_all cm on cm.emp_id = ei.emp_id
    Left Outer Join cdl_deact_all cd on cd.emp_id = ei.emp_id
    Left Outer Join (
        Select
            es.Employee_ID,
            es.Month_Date
            sum(es.Calls_Handled_Ct) as Call_Handled,
            round((sum(es.I_Talk_Time_Sec) + sum(es.Hold_Time_Sec) + sum(es.I_Work_Time_Sec) + sum(es.I_AUX_Out_Time_Sec)) / sum(es.Calls_Handled_Ct)) as AHT,
            round(sum(es.I_Talk_Time_Sec) / sum(es.Calls_Handled_Ct)) as TALK,
            round(sum(es.I_Work_Time_Sec) / sum(es.Calls_Handled_Ct)) as ACW,
            round(sum(es.Hold_Time_Sec) / sum(es.Calls_Handled_Ct)) as Hold
        From
            enterprise_rep_agent_stats es 
        Where
            es.Month_Date = $dynamic_value$
        Group By
            es.Employee_ID
    ) es on es.Employee_ID = ei.emp_id
    Left Outer Join (
        Select
            cr.emp_id,
            sum(cr.Score) as R7
        From
            cdl_rr7_all cr
        Group By
            cr.emp_id
    ) cr on cr.emp_id = ei.emp_id
    Left Outer Join (
        Select
            cc.emp_id,
            truncate((sum(cc.CMP_OE_Virtual) / sum(cc.CMP_Surveys)), 2) as CMP_OE,
            sum(cc.CMP_Surveys) as CMP_Surveys
        From
            cdl_cmp_oe_all cc
        Group By
            cc.emp_id
    ) cc on cc.emp_id = ei.emp_id
Where
    ei.Visible = 1
Order By
    ei.SM,
    ei.TM

仍然存在问题。 R7 的示例数据产生了一个百分比，但计算的方式看起来很可疑。您之前的示例会对多项内容进行多重计数，我认为这是错误的，并将每个组拆分为单独的子查询。