您可以按天和用户进行汇总，然后每天在HAVING子句中使用相关的子查询来过滤顶部用户：

SELECT DATE(s.search_date) search_day,s.user_id,COUNT(*)
FROM search s
GROUP BY DATE(s.search_date),s.user_id
HAVING COUNT(*) = (
    SELECT COUNT(*) 
    FROM search s1
    WHERE 
        s1.search_date >= DATE(s.search_date) 
        AND s1.search_date < DATE(s.search_date) + interval 1 day
    GROUP BY s1.user_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
)
DATE(s.search_date),s.user_id
ORDER BY s.search_day;

如果运行的是MySQL 8.0，则可以使用windwo函数进行过滤：

SELECT search_day,user_id,cnt
FROM (
    SELECT
        t.*,RANK() OVER(PARTITION BY search_day ORDER BY cnt desc) rn
    FROM (
        SELECT DATE(search_date) search_day,COUNT(*) cnt
        FROM search
        GROUP BY DATE(search_date),user_id
    ) t
) t
WHERE rn = 1
ORDER BY search_day;

根据您提供的内容，我相信您正在研究以下内容：

SELECT 
    user_id,COUNT(*),CONCAT_WS("-",YEAR(date),MONTH(date),DAY(date)) 
FROM USERS 
GROUP BY user_id,DAY(date))

这将显示每人每天的使用量。由于已使用“ Grafana”标签对其进行了标记，因此您可能还希望在查询的末尾添加一个“ where”子句，以接受Grafana本机日期函数。

如果您希望每天 个最近的用户，那么n个MySQL 8+，可以将窗口函数用于聚合：

SELECT s.*
FROM (SELECT DATE(s.search_date) as search_date,COUNT(*) as cnt
             RANK() OVER (PARTITION BY DATE(s.search_date) ORDER BY COUNT(*) DESC) as seqnum
      FROM search s
      WHERE s.search_date >= curdate() - interval <y> day
      GROUP BY DATE(s.search_date),user_id
     ) s
WHERE seqnum = <x>;  -- or however many you want on each day
ORDER BY search_date;

如果希望整个范围内最活跃的用户，则：

select user_id,count(*)
from search s
where search_date >= curdate() - interval <y> day
group by user_id
order by count(*) desc
limit <x>;