mysql - 在选择时插入 0 以表示空返回(在任务计划程序上运行的 SQL 脚本)

Question

我每天早上 7:45 到下午 5 点在任务计划中运行一个 php 脚本。它工作得很好，但第一次运行时它不会返回任何内容，因为尚未创建记录(记录是由接听电话的 CSR 代理创建的)。我在脚本中运行 SQL 查询来选择、聚合值并将其插入表中，然后根据这些值创建一个 JSON 文件。再说一遍，除了第一次运行之外，所有这些都是完美的。

有人问我是否可以添加一些内容，这样如果第一个作业没有返回任何内容，那么它只会为所有内容插入 0。这样，即使运行了 5 次，系统中没有记录/调用，仍然会有数据，并且 CSR 屏幕会显示一些内容。有没有一种相当简单的方法来做到这一点？

这是脚本的大部分内容:

    $data = mysqli_query($conn2,
        "SELECT
          case
          when callingpartyno       in (select extension from test.test_users)
          then callingpartyno
          when finallycalledpartyno in (select extension from test.test_users)
          then finallycalledpartyno
          end as extension
            , sum(duration) as total_talk_time_seconds
            , round(sum(duration) / 60,2) as total_talk_time_minutes
            , sum(if(legtype1 = 1,1,0)) as total_outbound
            , sum( case when(legtype1 = 1 and duration > 60) then 1 else 0 end) as credit_for_outbound
            , sum(if(legtype1 = 2,1,0) and answered = 1) as total_inbound
            , sum(if(legtype1 = 2,1,0) and answered = 0) as total_missed
            , sum(if(legtype1 = 1, 1, 0)) +                   -- outbound calls
            sum(if(legtype1 = 2, 1, 0))  as total_calls
            ,round((sum(if(legtype1 = 2,1,0) and answered = 1))/(sum(if(legtype1 = 1, 1, 0)) +                   -- outbound calls
            sum(if(legtype1 = 2, 1, 0))) * 100,2) as percent_answered
            , now() as time_of_report
            , curdate() as date_of_report
        FROM
            test.session a
            join test.callsummary b
            on a.notablecallid = b.notablecallid
            where
            date(a.ts) >= curdate()
            and (
            callingpartyno in (select extension from test.test_users
            )
            or  finallycalledpartyno in (select extension from test.test_users
            )
            )
            group by
            extension")  or die(mysqli_error( $conn2));


    $stmt = mysqli_prepare($conn2,
        "INSERT into test.test_totals
              (extension,
              total_talk_time_seconds,
              total_talk_time_minutes,
              total_outbound,
              credit_for_outbound,
              total_inbound,
              missed_calls,
              total_calls,
              percent_answered,
              date_of_report,
                  time_of_report)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON duplicate key
        update
              total_talk_time_seconds = values(total_talk_time_seconds),
              total_talk_time_minutes = values(total_talk_time_minutes),
              total_inbound = values(total_inbound),
              total_outbound = values(total_outbound),
              credit_for_outbound = values(credit_for_outbound),
              missed_calls = values(missed_calls),
              total_calls = values(total_calls),
              percent_answered = values(percent_answered),
              time_of_report = NOW()") or die(mysqli_error( $conn2));

Answer 1

问题的出现是因为有时分机行数为零(没有调用、没有分机、没有结果)

更改您的查询，以便您在 select 中使用的 now() 来自一个简单的子查询，并使用始终为 true 的 ON 子句将其余数据左连接到它

参见http://sqlfiddle.com/#!9/9b2a1/6例如，产生伪表 t 的子查询将始终返回一行。如果 Table1 子查询返回一行或多行，则 t 行将完全相同地重复。如果 Table1 子查询返回零行，则整个查询仍将有一行。此查询永远不能返回零行；这是您需要对查询执行的操作，以确保即使您的实际数据表在给定时间段内没有行，整个查询也将返回一行(带有空扩展；如果需要，您可以将其合并为一个值)

编辑:

就您的查询而言，它看起来更像是这样:

SELECT
  case
    when YOUR_SUBQUERY.callingpartyno in (select extension from test.test_users) then YOUR_SUBQUERY.callingpartyno
    when YOUR_SUBQUERY.finallycalledpartyno in (select extension from test.test_users) then YOUR_SUBQUERY.finallycalledpartyno
  end as extension
  , sum(YOUR_SUBQUERY.duration) as total_talk_time_seconds
  , round(sum(YOUR_SUBQUERY.duration) / 60,2) as total_talk_time_minutes
  , sum(if(YOUR_SUBQUERY.legtype1 = 1,1,0)) as total_outbound
  , sum(case when(YOUR_SUBQUERY.legtype1 = 1 and duration > 60) then 1 else 0 end) as credit_for_outbound
  , sum(if(YOUR_SUBQUERY.legtype1 = 2,1,0) and YOUR_SUBQUERY.answered = 1) as total_inbound
  , sum(if(YOUR_SUBQUERY.legtype1 = 2,1,0) and YOUR_SUBQUERY.answered = 0) as total_missed
  , sum(if(YOUR_SUBQUERY.legtype1 = 1, 1, 0)) +                   -- outbound calls
  sum(if(YOUR_SUBQUERY.legtype1 = 2, 1, 0))  as total_calls
  , round((sum(if(YOUR_SUBQUERY.legtype1 = 2,1,0) and YOUR_SUBQUERY.answered = 1))/(sum(if(YOUR_SUBQUERY.legtype1 = 1, 1, 0)) +                   -- outbound calls
    sum(if(YOUR_SUBQUERY.legtype1 = 2, 1, 0))) * 100,2) as percent_answered
  , TIME_OF_REPORT
  , DATE_OF_REPORT
FROM
  (SELECT NOW() AS TIME_OF_REPORT, CURDATE() AS DATE_OF_REPORT) T
  LEFT OUTER JOIN
  (
      SELECT * FROM
        test.session a
        join test.callsummary b
          on a.notablecallid = b.notablecallid
        where
          date(a.ts) >= curdate() and 
          (
            callingpartyno in (select extension from test.test_users)
            or  
            finallycalledpartyno in (select extension from test.test_users)
          )
  ) YOUR_SUBQUERY
  ON 1=1
  group by
    DATE_OF_REPORT, TIME_OF_REPORT, extension

你的sql本质上是小写的。我用大写添加了这些位。我无法对此进行测试，因为我对您的表结构和示例数据一无所知，因此可能需要您进行一些调试，但本质上是 (SELECT NOW() AS TIME_OF_REPORT, CURDATE() AS DATE_OF_REPORT) T 应始终返回一行。 YOUR_SUBQUERY 可以返回 0 到 N 行。在 YOUR_SUBQUERY 中有 0 行的情况下，总体报告仍应返回 1 行，带有时间(实际上是两行，尽管我不确定为什么使用 NOW() 来提供时间和日期，以及 CURDATE( ) - curdate 似乎是多余的，因为它只提供 NOW() 已经给出的信息，但我已经离开了)和一个空扩展。所有总数应该为 0，尽管我不确定是否例如MySQL 的 IF 函数，当像 IF(something_that_is_null = 2, 1, 0) 一样运行时返回 null (在我看来，它不应该；测试应该为 false，应该返回 0 并求和为 0)。如果您确实发现第一次运行时所有计数都是 null 而不是 0，您可以 COALESCE(logic_doing_sum_here, 0) 将它们从 null 转换为 0。如果 null 扩展对您来说很麻烦，请使用 COALESCE 将其转换为一些有意义的值默认值