php - 在 csv/excel 报告的 SQL 查询中按用户创建总和/总计行

Question

下面的查询提取数据以形成“报告”，但它基本上只是按以下分组的数字行项目企业社会责任代理。它的作用准确无误，查询后我将结果保存为 CSV。然而，它只给我个人记录，所以我必须将其放入Excel并使用公式插入“总计”行基本上总结了我需要总计的列。

这个 sql 查询在 php 脚本中运行，但我不确定修改其中任何一个的最佳方法，以便我可以为每个用户汇总这些列。它会以某种方式说“这是用户 7200 的记录的结尾”因此，插入用户 7200 的总计行，然后转到用户 7206' 的记录或类似的内容。

屏幕截图按原样显示了报告，使用“x”和空空格而不是 1 或 0，所以在我的 Excel 公式中我通常有一个计数 IF“x”变量。我希望我能用这个脚本做类似的事情。查询和截图如下。

这是 fiddle :http://sqlfiddle.com/#!9/b6a568/1

fiddle 的输出是精确查询的输出，唯一的区别是脚本将其放入 csv 中。

我只是希望有一种方法可以为每个用户创建一个“总计”行，将“x”的数量相加，持续时间和保持时间的秒数。

非常感谢任何关于如何重构它的建议。

$result = mysqli_query($conn2,
    "SELECT
          FirstN
          , LastN
        , Extension
        , Recieved
        , Recieved_Known
        , Outbound
        , Outbound_Known
        , Missed_No_VM
        , Missed_VM
        , Missed_Known
        , Calling_Number
        , Called_Number
        , Start_Time
        , End_Time
        , Talk_Time_Seconds
        , Hold_Time_Seconds

        FROM (
          SELECT distinct
           firstn
           , lastn
           , c.extension
           , CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 'x' ELSE '' END AS Recieved
           , case when LEGTYPE1 = 2 and answered = 1 and CALLINGPARTYNO = k.phone_number then 'x' ELSE '' end as Recieved_Known
           , CASE WHEN LEGTYPE1 = 1 then 'x' ELSE '' end  AS Outbound
           , case when  FINALLYCALLEDPARTYNO = kn.long_number then 'x' ELSE '' end as Outbound_Known
           , case when legtype1 = 2 and answered = 0 and finallycalledpartyno  not like '%oice%' then 'x' ELSE '' end as Missed_No_VM
           , case when finallycalledpartyno like '%oice%' then 'x' ELSE '' end as Missed_VM
           , case when legtype1 = 2 and ANSWERED = 0 and CALLINGPARTYNO = k.phone_number then 'x' ELSE '' end as
            Missed_Known
           , a.CALLINGPARTYNO AS Calling_Number
           , a.FINALLYCALLEDPARTYNO AS Called_Number
           , b.starttime as Start_Time
           , b.endtime as End_Time
           , b.duration as Talk_Time_Seconds
           , a.holdtimesecs as Hold_Time_Seconds

          FROM ambition.session a
            INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
            right join jackson_id.users c on a.callingpartyno = c.extension or a.finallycalledpartyno = c.extension
            LEFT JOIN ambition.known_numbers k ON a.callingpartyno = k.phone_number
        left join ambition.known_numbers kn on a.finallycalledpartyno = kn.long_number
                WHERE a.ts >= '2017-12-07' -- curdate()
                and(a.CALLINGPARTYNO in (select extension from ambition.ambition_users) OR a.finallycalledpartyno IN (select extension from ambition.ambition_users))
          ) x
          order by extension;") or die(mysqli_error( $conn2));

屏幕截图:

Answer 1

我不确定你如何唯一地识别用户。我假设该扩展对于每个用户都是唯一的。基于此，我提出以下查询:

SELECT
          Extension
        , Recieved
        , Recieved_Known
        , Outbound
        , Outbound_Known
        , Missed_No_VM
        , Missed_VM
        , Missed_Known
        , Talk_Time_Seconds
        , Hold_Time_Seconds

      FROM (
          SELECT distinct
           c.extension
           , sum(CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 1 ELSE 0 END) AS Recieved
           , sum(case when LEGTYPE1 = 2 and answered = 1 and CALLINGPARTYNO = k.phone_number then 1 ELSE 0 end) as Recieved_Known
           , sum(CASE WHEN LEGTYPE1 = 1 then 1 ELSE 0 end)  AS Outbound
           , sum(case when  FINALLYCALLEDPARTYNO = kn.long_number then 1 ELSE 0 end) as Outbound_Known
           , sum(case when legtype1 = 2 and answered = 0 and finallycalledpartyno  not like '%oice%' then 1 ELSE 0 end) as Missed_No_VM
           , sum(case when finallycalledpartyno like '%oice%' then 1 ELSE 0 end) as Missed_VM
           , sum(case when legtype1 = 2 and ANSWERED = 0 and CALLINGPARTYNO = k.phone_number then 1 ELSE 0 end) as
            Missed_Known
           , sum(b.duration) as Talk_Time_Seconds
           , sum(a.holdtimesecs) as Hold_Time_Seconds

          FROM session a
        INNER JOIN callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
        right join users c on a.callingpartyno = c.extension or a.finallycalledpartyno = c.extension
        LEFT JOIN known_numbers k ON a.callingpartyno = k.phone_number
        left join known_numbers kn on a.finallycalledpartyno = kn.long_number
          WHERE a.ts >= '2017-12-07' -- curdate()
          and(a.CALLINGPARTYNO in (select extension from ambition_users) OR a.finallycalledpartyno IN (select extension from ambition_users))
        group by c.extension    
      ) x

          order by extension;

上面的查询至少可以让您了解如何解决问题。