sql - 根据一组计算出的时间范围获取平均值和计数mysql

我有两个表-情绪和评论-我试图加入平均值和计数从一组衍生的时间范围在mysql。
以下是我的个人作品：

  SELECT ROUND(AVG(sentiment.sent_value)) AS sentiment,
         ROUND( FLOOR( (sentiment.sent_video_time) /5000 ) *5000 ) AS start_time, ' - ',
         ROUND( (FLOOR( (sentiment.sent_video_time) /5000 ) +1 ) *5000) AS end_time
    FROM sentiment
GROUP BY end_time

…还有。。。

  SELECT COUNT(comment.commID) AS comments,
         ROUND( FLOOR( (comment.c_video_time) /5000 ) *5000 ) AS ctime, ' - ',
         ROUND( (FLOOR( (comment.c_video_time) /5000 ) +1 ) *5000) AS cend_time
    FROM comment
GROUP BY ctime

时间增量总是匹配的。每次递增都会有情绪值，但不总是有评论。
我想得到一个组合表来输出：

sentiment,  comments,    start_time - end_time
-----------------------------------------------
65,         8,           0 - 5000
42,         0,           5000 - 10000
35,         17,          10000 - 15000

谢谢！
更新：
多亏了下面的答案，我有了进一步的了解这将返回正确的数据，但如果对应的情绪时间段没有注释，则排除这些行。我想让它显示“0”在该部分的评论。
我知道这是因为我定义“分组依据”的方式，但我无法找出正确的条件来处理在给定的一段时间内对1+情绪有0条评论的情况。

SELECT x.sentiment AS senti,
       y.comments AS comments,
       x.start_time AS time
 FROM (SELECT campaign_campID,
              ROUND(AVG(sentiment.sent_value)) AS sentiment,
              ROUND( FLOOR( (sentiment.sent_video_time) /5000 ) *5000 ) AS start_time, ' - ',
              ROUND( (FLOOR( (sentiment.sent_video_time) /5000 ) +1 ) *5000) AS end_time
         FROM sentiment
     GROUP BY start_time, campaign_campID) x
 JOIN (SELECT campaign_campID,
              CASE
                WHEN COUNT(comment.commID) =  NULL THEN 0
                ELSE COUNT(comment.commID)
              END AS comments,
              ROUND( FLOOR( (comment.c_video_time) /5000 ) *5000 ) AS cstart_time, ' - ',
              ROUND( (FLOOR( (comment.c_video_time) /5000 ) +1 ) *5000) AS cend_time
         FROM comment
     GROUP BY cstart_time, campaign_campID) y ON y.campaign_campid = x.campaign_campid
WHERE y.cstart_time = x.start_time
GROUP BY x.start_time

这是桌子：

CREATE TABLE comment (
    commID INT NOT NULL AUTO_INCREMENT ,
    campaign_campID INT NULL DEFAULT NULL ,
    c_video_time BIGINT(20) NULL
)

CREATE TABLE sentiment (
    campaign_campID INT NULL ,
    sent_value TINYINT NULL ,
    sent_video_time BIGINT(20) NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci

最佳答案

左联表评论与表情绪。
在内部查询中使用Coalesce而不是case
如果表情感有0行，y.comments=NULL，那么coalesce（NULL，0）将返回0

SELECT x.sentiment AS senti,
       COALESCE(y.comments,0) AS comments,
       x.start_time AS time
 FROM (SELECT campaign_campID,
              ROUND(AVG(sentiment.sent_value)) AS sentiment,
              ROUND( FLOOR( (sentiment.sent_video_time) /5000 ) *5000 ) AS start_time, ' - ',
              ROUND( (FLOOR( (sentiment.sent_video_time) /5000 ) +1 ) *5000) AS end_time
         FROM sentiment
     GROUP BY start_time, campaign_campID) x
 LEFT JOIN (SELECT campaign_campID, COUNT(comment.commID)  comments
              ROUND( FLOOR( (comment.c_video_time) /5000 ) *5000 ) AS cstart_time, ' - ',
              ROUND( (FLOOR( (comment.c_video_time) /5000 ) +1 ) *5000) AS cend_time
         FROM comment
     GROUP BY cstart_time, campaign_campID) y ON x.campaign_campid = y.campaign_campid AND  x.start_time = y.cstart_time

关于sql - 根据一组计算出的时间范围获取平均值和计数mysql，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3644755/