mysql能使用一条sql实现这样的数据查询吗?
我有一张消息日志表记录用户发送的消息,主要包含字段user_id(用户id),create_time(发送时间)。
现在我希望统计每个用户每天发送前10条消息的平均时间,忽略少于10条的用户,同时当用户当天发送消息超过10条时只取最早发送的前10条数据做分组。我目前的 方案时先按用户分组查出超过10条发言的用户,然后再对分组结果进行处理:sql如下:
select user_id,group_concat(create_time order by create_time asc) ct from msg_log
where create_time > CURDATE() and create_time < DATE_ADD(CURDATE(),INTERVAL 1 day)
having count(*) >= 10
拿到ct字段后取前10个值求平均然后忽略10条后面的数据,这因为group_concat只能取1024个字符可能会出现截断问题,我期望能够直接通过sql实现这样的功能,网络上搜以一圈似乎没有类的需求。
先排序生成序号,然后再计算,这样试试
SELECT user_id, AVG(create_time) AS avg_time FROM (
SELECT user_id, create_time FROM (
SELECT user_id, create_time, ROW_NUMBER() OVER(PARTITION BY user_id, DATE(create_time) ORDER BY create_time) AS row_num
FROM msg_log
WHERE create_time >= CURDATE() AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
) AS t
WHERE row_num <= 10
) AS t2
GROUP BY user_id
HAVING COUNT(*) >= 10;
可以使用SUBSTRING_INDEX函数来避免group_concat截断的问题,同时使用LIMIT和OFFSET来选取前10个值并忽略后面的数据。具体的SQL语句如下:
SELECT user_id, AVG(SUBSTRING_INDEX(group_concat(create_time ORDER BY create_time ASC), ',', 10)) AS ct
FROM msg_log
WHERE create_time > CURDATE() AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
GROUP BY user_id
HAVING COUNT(*) >= 10;
可以尝试使用子查询和LIMIT语句实现这个需求,具体的SQL语句如下:
SELECT user_id, AVG(create_time) AS avg_timeFROM (
SELECT user_id, create_time FROM msg_log WHERE create_time > CURDATE() AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
ORDER BY create_time ASC LIMIT 10) AS t1GROUP BY user_idHAVING COUNT(*) >= 10
这个SQL语句的思路是先按照时间升序排列消息记录,然后使用LIMIT语句只选择前10条记录,再按照用户ID分组,计算平均时间。如果某个用户当天的消息记录少于10条,则不会被查询到;如果某个用户当天的消息记录超过10条,则只会选择最早的前10条记录计算平均时间。