mysql - 如何高效查询堆积柱形图？

Question

我的用例是创建一个 stacked column graph使用存储在 MySQL 中的日志表。

目前我有一个常规柱形图，但我想按 site_id 聚合数据，以便我可以看到柱形图的哪些部分归因于哪个网站。

我目前对现有柱形图的技术是获取日期列表，并在按日期分组的同时计算记录。然后我使用 for 循环创建我需要的 14 天周期，然后循环我的数据以将匹配计数填充到正确的日期。

SELECT DATE(`created`) AS `day`, 
       COUNT(`id`)     AS `count` 
FROM   `api_logs` `ApiLogs` 
WHERE  DATE(created) BETWEEN DATE_SUB(CURDATE(), INTERVAL 14 day) AND CURDATE() 
GROUP  BY DATE(`created`) 
ORDER  BY DATE(`created`) 

不过，对于堆叠图，我想不出一种方法来整理 MySQL 中的数据，而无需执行大量查询或构建子查询来整理每个站点的计数。

是否有一种既定的查询模式可以轻松地与堆叠柱形图兼容？

我的前端是用PHP搭建的，如果有什么查询后处理的方案。

Answer 1

问题是您需要为每个站点设置一列

如果您有一定数量的网站，那么您可以在 sql 中手动构建列

SELECT DATE(`created`) AS `day`,

       SUM(CASE WHEN `site_id` = 'A' THEN 1 ELSE 0 END) AS `site A`,
       SUM(CASE WHEN `site_id` = 'B' THEN 1 ELSE 0 END) AS `site B`

FROM   `api_logs` `ApiLogs`
WHERE  DATE(created) BETWEEN DATE_SUB(CURDATE(), INTERVAL 14 day) AND CURDATE()
GROUP  BY DATE(`created`)
ORDER  BY DATE(`created`)

否则，您可以动态构建列并聚合
使用谷歌的 DataView和 data.group

首先，将site_id添加到sql

SELECT DATE(`created`) AS `day`,
       `site_id`       AS `site_id`,
       COUNT(`id`)     AS `count`
FROM   `api_logs` `ApiLogs`
WHERE  DATE(created) BETWEEN DATE_SUB(CURDATE(), INTERVAL 14 day) AND CURDATE()
GROUP  BY DATE(`created`), `site_id`
ORDER  BY DATE(`created`), `site_id`

结果应该类似于以下...

['Date', 'Site', 'Count'],
[new Date('11/17/2016'), 'A', 10],
[new Date('11/17/2016'), 'B', 15],
[new Date('11/17/2016'), 'C', 22],

请参阅以下用于动态构建列的工作片段...

google.charts.load('current', {
  callback: function () {
    // raw table data
    var data = google.visualization.arrayToDataTable([
      ['Date', 'Site', 'Count'],
      [new Date('11/17/2016'), 'A', 10],
      [new Date('11/17/2016'), 'B', 15],
      [new Date('11/17/2016'), 'C', 22],
      [new Date('11/17/2016'), 'D', 8],
      [new Date('11/16/2016'), 'A', 12],
      [new Date('11/16/2016'), 'B', 6],
      [new Date('11/16/2016'), 'C', 13],
      [new Date('11/16/2016'), 'E', 14],
      [new Date('11/15/2016'), 'A', 9],
      [new Date('11/15/2016'), 'B', 16],
      [new Date('11/15/2016'), 'D', 11]
    ]);

    // create view with columns for each site, then agg view
    var view = new google.visualization.DataView(data);
    var aggColumns = [];
    var viewColumns = [0];

    data.getDistinctValues(1).forEach(function (site, index) {
      viewColumns.push({
        calc: function (dt, row) {
          if (dt.getValue(row, 1) === site) {
            return dt.getValue(row, 2);
          }
          return null;
        },
        label: site,
        type: 'number'
      });

      aggColumns.push({
        aggregation: google.visualization.data.sum,
        column: index + 1,
        label: site,
        type: 'number'
      });
    });

    view.setColumns(viewColumns);
    var group = google.visualization.data.group(
      view,
      [0],
      aggColumns
    );

    var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
    chart.draw(group, {
      isStacked: true
    });
  },
  packages: ['corechart']
});

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