mysql中如何使用group by关键字获取每天的信息

发布于 2024-11-07 02:46:56 字数 339 浏览 3 评论 0原文

我是 mysql 新手，目前遇到查询问题。我需要获得一周内每天每项活动的平均持续时间。日期格式如下：'2000-01-01 01:01:01'，但我想摆脱 01:01:01 的东西，只关心日期。我该怎么做？

表格是这样的：

record_id   int(10)      NOT NULL,
activity_id varchar(100) NOT NULL,
start_time  datetime     NOT NUll,
end_time    datetime     NOT NULL,
duration    int(10)      NOT NULL;

谢谢。

原文

I'm new in mysql and I'm currently having an issue with a query. I need to get an average duration for each activity each day within a week. The date format is like: '2000-01-01 01:01:01', but I want to get rid of the 01:01:01 thing and only care about the date. How do I do that?

The table is something like this:

record_id   int(10)      NOT NULL,
activity_id varchar(100) NOT NULL,
start_time  datetime     NOT NUll,
end_time    datetime     NOT NULL,
duration    int(10)      NOT NULL;

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

静待花开 2024-11-14 02:46:56

你可以做类似下面的事情

select activity_id, dayofweek(datetime) as day, avg(duration) as average
from table_name  where datetime between start_date and end_date
group by activity_id,dayofweek(datetime)

You could do something like the following

select activity_id, dayofweek(datetime) as day, avg(duration) as average
from table_name  where datetime between start_date and end_date
group by activity_id,dayofweek(datetime)

回复收藏 0 原文

陪你搞怪i 2024-11-14 02:46:56

如果我理解的话，您希望按不同的活动时间进行分组，并查看每项活动开始和结束之间的平均天数。这应该适合你。

SELECT activity_id, avg(DATEDIFF(end_time, start_time)) AS Average 
FROM tablename 
GROUP BY activity_id, DAYOFWEEK(start_time)

编辑：误解了，您还希望它按天细分，因此这应该拉动每个组，按 start_time 所在的星期几细分，然后是 start_time 和 end_time 之间的平均天数。

If I'm understanding, you want to group by the different activity times and see the average days between the start and end of each activity. This should do it for you.

SELECT activity_id, avg(DATEDIFF(end_time, start_time)) AS Average 
FROM tablename 
GROUP BY activity_id, DAYOFWEEK(start_time)

Edit: Misunderstood, you want it broken down by the day as well, so this should pull each group, broken down by the day of the week that the start_time falls on, and then the average days between start_time and end_time.

回复收藏 0 原文