mysql - 用于绘图的 SQL 查询

Question

所以我有一个数据挖掘程序，可以记录大量视频流的收视率数据。流不是全天候 24/7 的。应用程序每 60 秒检查一次每个流的统计信息。

我有 2 个表，一个“数据”表，其中包含时间戳列、流名称和当前查看者。仅当流是实时的时才添加记录。另一个表是总计表，它包含时间戳、总收视率和直播流的数量。

我想要做的是生成特定时间段内选定数量的流的收视率图表。

目前我一直在这样做以获得总计:

SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), "%k:%i") as time, viewers FROM
`tftv_totals` WHERE timestamp BETWEEN <START> and <END>

然后如果我想要单独的流，我会为每个流执行此操作并在 excel 中手动组合它:

SELECT timestamp, viewers FROM `tftv_data` 
WHERE timestamp
    name = <Stream Name>
    BETWEEN <Start> and <End>

我想知道是否有任何方法可以产生类似这样的输出:

+------------+---------+---------+---------+| timestamp  | stream1 | stream2 | stream3 |+------------+---------+---------+---------+| 1361962826 | 2       | 3       | 0       || 1361962886 | 3       | 4       | 0       || 1361962946 | 4       | 8       | 0       |+------------+---------+---------+---------+

重点是 stream3 一直处于离线状态。

我可以在 PHP 中做到这一点，但为了方便起见，我希望将它保留在 SQL 中，并希望有比几个嵌套循环更有效的方法来做到这一点。

Answer 1

也许是这样的:

SELECT
    timestamp,
    SUM(CASE WHEN tftv_data.name='stream1' THEN tftfv_data.viewers ELSE 0 END) AS stream1,
    SUM(CASE WHEN tftv_data.name='stream2' THEN tftfv_data.viewers ELSE 0 END) AS stream2,
    SUM(CASE WHEN tftv_data.name='stream3' THEN tftfv_data.viewers ELSE 0 END) AS stream3
FROM
    tftv_data
GROUP BY
    timestamp