当前位置：文江博客话题详情

MySQL 聚合函数中的其他聚合函数

发布于 2024-11-19 13:20:14 字数 112 浏览 5 评论 0原文

我有一张桌子，上面有帖子。就像（id int，date datetime）。如何使用一个 sql 请求选择每个月每天的平均帖子数？

谢谢你！

原文

分享到QQ

分享到微博

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

发布评论

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

小苏打饼 2024-11-26 13:20:14

这应该可以为您完成：

select month, avg(posts_per_day)
from (select day(date), month(date) as month, count(*) as posts_per_day
      from posts group by 1,2) x
group by 1

说明：因为您正在聚合上进行聚合，所以无法绕过对查询进行查询：

内部查询计算每天的数字并捕获月份。
外部查询对该计数进行平均，按月分组。

This should do it for you:

select month, avg(posts_per_day)
from (select day(date), month(date) as month, count(*) as posts_per_day
      from posts group by 1,2) x
group by 1

Explanation: Because you are doing an aggregate on an aggregate, there is no getting around doing a query on a query:

The inner query calculates the number per day and captures the month.
The outer query averages this count , grouping by month.

回复收藏 0 原文

迷你仙 2024-11-26 13:20:14

您可以这样获取每月的帖子数量：

SELECT COUNT(*) AS num_posts_per_month FROM table GROUP BY MONTH(date);

现在我们需要一个月的天数：

SELECT COUNT(*) / DATEDIFF(MAKEDATE(YEAR(date), MONTH(date)) + INTERVAL 1 MONTH, MAKEDATE(YEAR(date), MONTH(date))) AS avg_over_month
FROM table GROUP BY MONTH(date);

这将获得帖子所在日历月内每天的平均帖子数量。也就是说，当月的平均值将持续上升，直到月底。如果您想要当月的真实平均值，则必须输入条件才能获得真实的经过天数。

You can get the number of posts per month like this:

SELECT COUNT(*) AS num_posts_per_month FROM table GROUP BY MONTH(date);

Now we need the number of days in a month:

SELECT COUNT(*) / DATEDIFF(MAKEDATE(YEAR(date), MONTH(date)) + INTERVAL 1 MONTH, MAKEDATE(YEAR(date), MONTH(date))) AS avg_over_month
FROM table GROUP BY MONTH(date);

This will get the average number of posts per day during the calendar month of the post. That is, averages during the current month will continue to rise until the end of the month. If you want real averages during the current month, you have to put in a conditional to get the true number of elapsed days.

回复收藏 0 原文

~没有更多了~