如何根据代码数生成条形图

2024-05-10 • 问答

Table name: data_detail
primary key: id
id | kpi_code | result_date | result_data | target |
1  | KPI1     | 11/13/2019  | 100         | 300    |
2  | KPI1     | 11/14/2019  | 150         | 300    |
3  | KPI2     | 11/13/2019  | 15          | 50     |
4  | KPI2     | 11/14/2019  | 30          | 50     |

<?php
require_once("dbconfig.php");
$query= "SELECT result_date,result_data FROM data_detail ";
$result = mysqli_query($conn,$query);
?>
<script type="text/javascript">
        google.charts.load('current',{'packages':['bar']});
        google.charts.setOnLoadCallback(drawChart);

        function drawChart() {

            var data = google.visualization.arrayToDataTable([
              ['Date','Sales'],<?php
                while($row = mysqli_fetch_array($result))
                {
                  echo "['" .$row["result_date"]."',".$row["result_data"]."],";
                }
               ?>

            ]);

            var options = {
              chart: {
                title: 'Sales for this week',subtitle: 'Number of sales for 11-13-2019 - 11-15-2019 ',}
            };

            var chart = new google.charts.Bar(document.getElementById('columnchart_material'));

            chart.draw(data,google.charts.Bar.convertOptions(options));
          }

      </script>
<div id="columnchart_material"></div>

由于表中有2个不同的kpi_code，我希望创建两个图表。但是结果是它仅创建了1张图表，其中4条柱形为result_data。问题之一应该是sql语句，因为我不知道如何获取表中的差异数kpi_code。

而不是更改sql或php，
我们可以将所有数据加载到一个Google数据表中，
然后使用数据视图过滤每个kpi，
并为每个绘制一个图表。

首先，以相同的方式加载所有数据，除了添加kpi列...

var data = google.visualization.arrayToDataTable([
  ['KPI','Date','Sales'],<?php
    while($row = mysqli_fetch_array($result))
    {
      echo "['" .$row["kpi_code"]."','" .$row["result_date"]."',".$row["result_data"]."],";
    }
   ?>
]);

然后我们可以使用数据表方法，
得到一组不同的kpi代码-> getDistinctValues(columnIndex)

// get distinct kpi codes
var kpiCodes = data.getDistinctValues(0);

此后，我们可以为每个kpi建立数据视图和图表。

// add a div container and chart for each kpi
kpiCodes.forEach(function (kpi,index) {
  // build data view for kpi
  var view = new google.visualization.DataView(data);

  // filter view rows for kpi code
  view.setRows(data.getFilteredRows([{
    column: 0,value: kpi
  }]));

  // remove kpi column from data view
  view.hideColumns([0]);

  // create kpi chart container
  container.insertAdjacentHTML('beforeEnd','<div id="kpi-' + index + '"></div>');

  // create kpi chart
  var chart = new google.charts.Bar(document.getElementById('kpi-' + index));

  // draw kpi chart using view
  chart.draw(view,google.charts.Bar.convertOptions(options));
});

请参阅以下工作片段...

google.charts.load('current',{
  packages:['bar']
}).then(function () {
  var data = google.visualization.arrayToDataTable([
    ['KPI',['KPI1','11/13/2019',100],'11/14/2019',150],['KPI2',15],30],]);

  // use div container for all charts
  var container = document.getElementById('columnchart_material');

  // get distinct kpi codes
  var kpiCodes = data.getDistinctValues(0);

  // add a div container and chart for each kpi
  kpiCodes.forEach(function (kpi,index) {
    // options for kpi
    var options = {
      chart: {
        title: 'Sales for this week - KPI: ' + kpi,subtitle: 'Number of sales for 11-13-2019 - 11-15-2019 ',}
    };

    // build data view for kpi
    var view = new google.visualization.DataView(data);

    // filter view rows for kpi code
    view.setRows(data.getFilteredRows([{
      column: 0,value: kpi
    }]));

    // remove kpi column from data view
    view.hideColumns([0]);

    // create kpi chart container
    container.insertAdjacentHTML('beforeEnd','<div id="kpi-' + index + '"></div>');

    // create kpi chart
    var chart = new google.charts.Bar(document.getElementById('kpi-' + index));

    // draw kpi chart using view
    chart.draw(view,google.charts.Bar.convertOptions(options));
  });
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="columnchart_material"></div>

如何根据代码数生成条形图

ccpig520520 回答：如何根据代码数生成条形图

大家都在问