php - 复杂的报告 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%   

报告可以水平和垂直缩放。横向 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%..基于公式

Yes Counts / (Yes Counts + No Counts)在 response Forms_Responses栏目 table 。

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

我在 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 之间)来完成的。