使用 Distinct 进行 MYSQL 查询优化

Question

我想查找每天通过通知访问该应用程序的用户数量。我写的查询非常慢。

SELECT DATE(user_statistics.timestamp) date, COUNT(*) count FROM post
INNER JOIN user_statistics ON user_statistics.post_id = post.id
AND user_statistics.access_type = 'notification' AND post.type = 'given_type'
GROUP BY DATE(user_statistics.timestamp)

我重写了它以通过使用DISTINCT使其更快。

SELECT DISTINCT date(user_statistics.timestamp), count(DISTINCT user_statistics.post_id) 
FROM user_statistics INNER JOIN post on post.id = user_statistics.post_id
WHERE post.type = 'given_type' AND access_type = 'notification'

新查询不起作用。它说

在没有 GROUP BY 的聚合查询中，SELECT 列表的表达式 #1 包含非聚合列"user_statistics.时间戳";这 与 sql_mode=only_full_group_by 不兼容

Answer 1

您忘了添加分组依据子句：

SELECT DISTINCT date(user_statistics.timestamp), count(DISTINCT user_statistics.post_id) 
FROM user_statistics INNER JOIN post on post.id = user_statistics.post_id
WHERE post.type = 'given_type' AND access_type = 'notification'
group by date(user_statistics.timestamp)