SQL添加SUM(timeSpent)报group by错误 如何计算页面访问时长占比

问题根因说明 #

1. 你直接在SELECT里加SUM(timeSpent)属于聚合操作，默认会将整个查询结果集合并为1行，你同时要返回未聚合的pageName、timeSpent字段，又没有对应的GROUP BY规则，就会触发SQL的严格模式校验报错。
2. 后来你加了GROUP BY pageName后，SUM(timeSpent)是按每个页面分组计算聚合值，自然和每行自身的timeSpent相等，不符合你要计算5行总和的需求。

可行方案 #

方案1：支持窗口函数的场景（MySQL 8.0+/PostgreSQL/Oracle等主流数据库均支持，无需修改配置）

先取出TOP5的页面数据，再用窗口函数计算全局总和，最后算占比即可，语句如下：

SELECT 
  pageName,
  timeSpent,
  totalTime,
  ROUND(timeSpent / totalTime * 100, 2) as rate -- 保留2位小数的百分比，可按需调整
FROM (
  SELECT 
    pageName,
    timeSpent,
    SUM(timeSpent) OVER() as totalTime -- 窗口函数无分区，直接计算整个结果集的timeSpent总和
  FROM (
    SELECT pages.pageString pageName, timeSpent
    FROM
         (SELECT `page_id`, SUM(`time_spent`) as timeSpent
          FROM `pageViews` 
          WHERE `time_spent` > 0 
          GROUP BY `page_id`) myTable
    JOIN pages ON pages.id = page_id 
    ORDER BY timeSpent DESC 
    LIMIT 5
  ) top5
) t

窗口函数SUM(timeSpent) OVER()不会对行做分组聚合，只会把所有行的总和填充到每一行的totalTime字段里，完全符合你的需求。

方案2：不支持窗口函数的老旧数据库版本场景

如果你的数据库版本太低不支持窗口函数，可以用交叉连接一个计算TOP5总和的子查询实现：

SELECT 
  pageName,
  timeSpent,
  totalTime,
  ROUND(timeSpent / totalTime * 100, 2) as rate
FROM 
  (
    SELECT pages.pageString pageName, timeSpent
    FROM
         (SELECT `page_id`, SUM(`time_spent`) as timeSpent
          FROM `pageViews` 
          WHERE `time_spent` > 0 
          GROUP BY `page_id`) myTable
    JOIN pages ON pages.id = page_id 
    ORDER BY timeSpent DESC 
    LIMIT 5
  ) top5,
  (
    SELECT SUM(timeSpent) as totalTime
    FROM (
      SELECT SUM(`time_spent`) as timeSpent
      FROM `pageViews` 
      WHERE `time_spent` > 0 
      GROUP BY `page_id`
      ORDER BY timeSpent DESC 
      LIMIT 5
    ) t
  ) total

这个方案虽然重复查了一次TOP5的总和，但兼容性最好，不需要修改任何数据库配置即可运行。

内容的提问来源于stack exchange，提问作者Marc-9