如何确定每个用户的平均笔记数？

I have the following two tables in my MySQL database:

我的MySQL数据库中有以下两个表:

User (id, created_at, ...)
Note (id, created_at, ...)

What I'm hoping to do with my DB is generate a report like so:

我希望用我的数据库做的是生成如下报告:

Users created X weeks ago | X of users | average number of notes
1 | 20 | 2
2 | 31 | 3
3 |  5 | 4

Last row example... 3 weeks ago, 5 users were created in just that week range, and they on average have created a total of 4 notes.

最后一行示例... 3周前,仅在该周范围内创建了5个用户,他们平均创建了4个笔记。

Where the users selected per row is based on that week range... and the average number of notes is for the user during any time frame...

每行选择的用户基于该周范围...并且在任何时间范围内用户的平均笔记数...

Can this be done with SQL or is this to complex and I'll need to build this into my Rails app?

这可以用SQL完成,还是复杂的,我需要将它构建到我的Rails应用程序中?

Thank you for the help.

感谢您的帮助。

2 个解决方案

#1

You would do something like this:

你会做这样的事情:

select floor(datediff(u.created_at, curdate()) / 7) as weeks_ago,
       count(distinct u.id) as numusers, 
       count(n.id) / count(distinct u.id) as avg_notes
from users u left join
     notes n
     on u.id = n.id
group by weeks_ago;

The main caveat in this query is how you define "weeks ago". This simply takes the number of days and divides by 7.

此查询中的主要警告是您如何定义“周前”。这只需要天数并除以7。

#2

Solution 1 : - A single query

解决方案1: - 单个查询

It can be done in MySql but you might need to think of performance of the query if not properly built and indexed

它可以在MySql中完成,但如果没有正确构建和索引,您可能需要考虑查询的性能

Solution2 : Multiple queries (Algo below)

解决方案2:多个查询(下面的Algo)

Loop for each week
1) query 1 - Get all userids created in that week. Interval in MySql might be useful.
2) Use these userids to get number of notes based on userd id and created_at

每周循环1)查询1 - 获取在该周创建的所有用户标识。 MySql中的间隔可能很有用。 2)使用这些用户ID根据用户ID和created_at获取笔记数

#1