mysql - 表达式不在 GROUP BY 子句中

Question

我在 MySQL (v5.7) 中有一个记录用户请求的日志表，我从中提取了显示每月用户数量和总点击量的事件细目，例如:

Date            Users   Hits
September 2018  20      1,839
August 2018     23      2,723
July 2018       21      1,632
June 2018       22      2,981

目前这是使用以下查询实现的:

SELECT month(l.time) m, year(l.time) y, date_format(l.time, '%M %Y') monthyear, 
  (select count(distinct userid) from log lm 
    where month(lm.time) = month(l.time) and year(lm.time) = year(l.time)) users,
  count(u.name) hits
FROM log l left join users u on u.id=l.userid
group by date_format(l.time, '%M %Y')
order by l.time desc, l.id desc

此 SQL 在启用 only_full_group_by 时失败，这是 MySQL 中的默认设置，因为并非所有表达式都在 GROUP BY 子句中。我发现的解决方案通常涉及使用 MAX() 等聚合函数或将所有表达式添加到 GROUP BY 子句，但“用户”子查询使这些方法出现问题:我不能使用 MAX() 方法(无效的语法)并将其添加到 GROUP BY 子句会导致查询非常慢，以至于我还没有看到测试完成。

我觉得可能有一种优雅且高效的解决方案，无需诉诸于禁用 only_full_group_by，但我对 SQL 的了解有限。

Answer 1

这是一个简化的查询:

SELECT DATE_FORMAT(l.time, '%M %Y') AS monthyear, 
  COUNT(DISTINCT l.userid) AS users,
  COUNT(*) AS hits
FROM log l
GROUP BY monthyear

您不需要选择列表中的个别月份或年份，因为您没有在期望的结果中显示它。

您根本不需要连接到users 表，除非您打算只计算具有非 NULL name 列的用户的点击次数(COUNT 忽略NULL，我猜你的意思是计算日志中的所有命中，这意味着你应该使用 COUNT(*) 而不是 COUNT(u.name)。

我删除了 ORDER BY 子句，因为它引用了不在结果中的列。如果您想按月年排序，您应该考虑按照您想要的方式对月年进行格式化:

SELECT DATE_FORMAT(l.time, '%Y-%m') AS monthyear, 
  COUNT(DISTINCT l.userid) AS users,
  COUNT(*) AS hits
FROM log l
GROUP BY monthyear

默认情况下，GROUP BY 将按值对组进行排序。