mysql - 每隔 30 天生成一次报告，每日总计

Question

我有以下查询，用于计算每天的 CSR 调用指标和总数。我现在为每日报告运行它的方式是按扩展分组并将 now() 和 curdate() 值放在日期和时间列中。但是，我现在的任务是修改它以获取过去 30 天内每 15 个 CSR 的每日总计的一次性报告，因此请采取它现在正在做的事情，但收集过去 30 天的数据。

运行以下查询并从 ambilitationLog30Days 表中选择 * 后，我得到 354 行，这适合周末和缺勤的天数。然而，总数并不正确。对于下面的总和列，它返回 1 或 0，因此它实际上只显示每天每个 CSR 的一次调用而不是总数。

这是查询:

    Insert into test.ambitionLog30Days(Extension, ExtID, Total_Talk_Time_seconds,
       Total_Talk_Time_minutes,Total_Outbound, Total_Inbound, 
       Missed_Calls, Total_Calls, Time_of_report,Date_of_report  )  
    SELECT 
        c.extension 
       ,RESPONSIBLEUSEREXTENSIONID 
       , sum(Duration)  
       , round(sum(Duration) / 60,2) 
       , sum(if(LEGTYPE1 = 1,1,0)) 
       , sum(if(LEGTYPE1 = 2,1,0)) 
       , sum(if(Answered = 1,0,1)) 
       , sum(if(LEGTYPE1 = 1,1,0)) + sum(if(LEGTYPE1 = 2,1,0))   as total_calls 
       , b.ts
       , b.ts 
    FROM cdrdb.session a
    INNER JOIN cdrdb.callsummary b
        ON a.NOTABLECALLID = b.NOTABLECALLID
    INNER join cdrdb.mxuser c
        ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID
        WHERE b.ts BETWEEN curdate() - interval 30 day and curdate()
    AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)
    group by c.extension,b.ts
    ON duplicate key update Total_Talk_Time_seconds =values(Total_Talk_Time_seconds), 
       Total_Talk_Time_minutes =values(Total_Talk_Time_minutes), 
       Total_Outbound = values(Total_Outbound), 
       Total_Inbound = values(Total_Inbound), 
       Missed_calls = values(Missed_calls), 
       Total_Calls = values(Total_Calls),
       Time_of_report = values(Time_of_report); 

session 表列:

    RESPONSIBLEUSEREXTENSIONID  bigint(20)  YES     

调用汇总表列:

    NOTABLECALLID   bigint(20)  YES UNI     
    STARTTIME   datetime    YES MUL     
    ENDTIME datetime    YES MUL     
    DURATION    int(11) YES MUL     
    ANSWERED    smallint(6) YES         
    ts  timestamp   NO  MUL CURRENT_TIMESTAMP   on update CURRENT_TIMESTAMP
    firstcallid bigint(20)  YES         

mxuser 表列:

    EXTENSIONID bigint(20)  NO  PRI 0   
    USERID  bigint(20)  YES MUL     
    ISLAST  smallint(6) YES         
    FIRSTNAME   varchar(32) YES         
    LASTNAME    varchar(32) YES         
    USERPROFILENAME varchar(32) YES MUL     
    EXTENSION   varchar(8)  NO          
    ts  timestamp   NO  MUL CURRENT_TIMESTAMP   on update CURRENT_TIMESTAMP
    PRESENCENOTE    varchar(64) YES         

当前查询的一些示例数据:

   ID | Ext   | extID   | TTTS   | TTTM   | TI | TO | MC | TC | DOR       | TOR
    1     7200  35298       5       0.08    1      1  0   1   2017-08-03    2017-08-03 16:19:48
    2     7215  35295       116     1.93    1      1  0   1   2017-08-03    2017-08-03 16:41:32
    3     7218  35302       11      0.18    0      1  0   0   2017-08-03    2017-08-03 16:43:47
    4     7240  35417       79      1.32    1      1  0   1   2017-08-21    2017-08-21 19:13:38
    5     7247  35296       40      0.67    0      1  0   0   2017-08-03    2017-08-03 16:55:50
    6     7255  34935       65      1.08    1      1  0   1   2017-08-21    2017-08-21 18:57:07
    7     7276  35299       146     2.43    1      1  0   1   2017-08-21    2017-08-21 20:37:18
    8     7295  35439       92      1.53    1      1  0   1   2017-08-21    2017-08-21 15:44:36
    9     7306  35283       108     1.8     1      1  1   1   2017-08-03    2017-08-03 21:34:47
    10  7312    35404       54      0.9     1      1  1   1   2017-08-09    2017-08-09 16:56:51
    11  7314    35352       35      0.58    1      1  1   1   2017-08-03    2017-08-03 14:47:33
    12  7330    35297       38      0.63    1      1  0   1   2017-08-03    2017-08-03 16:45:05

预期数据的格式应相同，但每个 CSR 每个日期的调用次数应不止一次。 upsert 似乎是个问题，但 group by 也可能是个问题。

现在，如果我仅按 c.extension 对上述查询进行分组，我会得到 15 行(有 15 个 CSR)并且总数是正确的，但它们反射(reflect)了所有 30 天的累积情况，我需要一个具有每天总数的行在 30 天的时间间隔内。

Answer 1

您的查询(在您的问题中)包含这些行。

 WHERE b.ts BETWEEN curdate() - interval 30 day and curdate()
AND  ...
group by c.extension,b.ts

您正在按时间戳分组。尝试使用

group by c.extension

你可能会得到你需要的结果。

而且，如果您想按日历日分组，请使用 GROUP BY ... DATE(b.ts) .

另请注意，您的 WHERE 上有一个常见错误条款。 BETWEEN一般不适合 TIMESTAMP或 DATETIME数据，因为在范围末尾出现差一错误。

改用它。

WHERE b.ts >= curdate() - interval 30 day 
  AND b.ts <  curdate()

注意我是如何使用 < 的日期范围的末尾。这将使用 ts 拉入所有行但不包括今天午夜之前的值。另一方面，WHERE b.ts BETWEEN .... AND curdate()提取所有昨天的记录，以及今天恰好在午夜的所有记录。

两种公式都适用于 ts 上的索引专栏。