复杂的报表PHP MySQL

Question

我的数据库中有四个表：

Metrics
->id
->description

Agents
->id
->name

Forms
->id
->agent_id
->campaign_id
->calldatetime
->phone

Forms_Responses
->id
->form_id
->metrics_id
->response

其中一个用户让我写一份这样的报告：

Metric ID | Agent_name | Agent_name | Agent_name .. and so on (depends how many from db)
__________|____________|____________|______________
          |            |            |
1         | 90%        | 80%        | 70%
__________|____________|____________|_______________
2         | 80%        | 100%       | 50%   

报告在水平和垂直方向上缩放。Horizontally by Agent_ID

为了获得水平缩放(列)，我使用以下查询

SELECT a.id, b.name FROM forms a
INNER JOIN agents b ON a.agent_id = b.id
WHERE a.calldatetime >= '2015-12-12 00:00:00' 
AND a.calldatetime <= '2015-12-17 23:00:37' AND campaign_id = 22 GROUP BY agent_id ORDER BY id ASC;

我有GROUP BY agent_id，所以不会有重复的数据。然而，我的问题是在ROWS上。到目前为止，我已经

select form_id, metrics_id, response, remarks
from forms_responses 
where form_id >= 6951 and form_id <= 6953 ORDER BY id ASC;

请不要介意where form_id >= 6951 and form_id <= 6953。所以这应该是我的排了？问题是，我如何在我的列中链接它，加上90%, 80%..基于公式的值

Forms_Responses表的response列中的Yes Counts / (Yes Counts + No Counts)。

因此，基本上这是一个评分报告，它根据给定公式中的响应值获取每个座席在每个指标上的百分比。

我正在做这件事在Laravel PHP中，并将输出为excel与上述格式，但我不知道我的查询将是什么样子或如何处理进行，或者它是可能的？

Answer 1

你应该用PHP做交叉表，因为MySql不支持这种数据透视表格式。

对于SQL查询，您的目标是输出类似以下内容：

metrics_id  agent_name  score
    1       Chewbacca    1
    1       Luke         0.5
    1       Yoda         0
    2       Chewbacca    1
    2       Luke         1
    2       Yoda         1
    3       Chewbacca    1
    3       Luke         0.5
    3       Yoda         0

这可以使用如下的select来完成：

select     r.metrics_id,
           a.name as agent_name, 
           sum(if(r.response='Yes',1,0))/count(*) as score
from       forms_responses r
cross join agents a
inner join forms f
        on f.id = r.form_id
      and  f.agent_id = a.id
      and  f.calldatetime between '2015-12-12 00:00:00' and '2015-12-17 23:00:37'
      and  f.campaign_id = 22
where      a.id in (
              select agent_id
              from   forms
              where  calldatetime between '2015-12-12 00:00:00' and '2015-12-17 23:00:37'
              and    campaign_id = 22
           )
group by   r.metrics_id, a.name;

这是一个SQL fiddle。

此查询确保您获得每个指标的所有相关代理，即使对于某些指标，它们还没有响应。这使得PHP中的处理变得更容易一些。

在PHP中，您将执行旋转：

$header = array("Metric ID");
$scores = array();
$current_metric = "-1";
$rowno = 0;
$mysqli->query($sql); // SQL as provided above
while ($row = $result->fetch_assoc()) { // fetch from query
    if ($row["metrics_id"] <> $current_metric) {
        // new metrics_id, start a new line
        $current_metric = $row["metrics_id"];
        $rowno++;
        $scores[$rowno] = array();
        $scores[$rowno][] = $current_metric;
    }
  if ($rowno == 1) {
      $header[] = $row["name"];
  }
  $scores[$rowno][] = $row["score"];
}
// put header row (with agent names) at top of table
array_unshift($scores, $header);

// output table -- you would do this with CSV or Excel library
echo "<table>";
foreach ($scores as $line) {
    echo "<tr>";
    foreach ($line as $cell) {
        echo "<td>$cell</td>";
    }
    echo "</tr>";
}
echo "</table>";

上面的代码将输出如下所示：

Metric ID   Chewbacca   Luke    Yoda
   1           1         0.5     0
   2           1         1       1
   3           1         0.5     0

如果您更喜欢输出百分比，那么您只需将分数乘以100并添加一个"%“符号，但请注意，在Excel中，这通常是通过格式化完成的，而不是实际的数字(保持在0和1之间)。