每天有多少用户参与(SQL查询) [英] What percentage of users participated on each day (SQL Query)

问题描述

所以我有下表( http://sqlfiddle.com/#!2/1b72f3/1 )，其中包含每个用户的所有已注册活动".

So I have the following table (http://sqlfiddle.com/#!2/1b72f3/1) which has all the "registered activities" for each user.

我要寻找的是-每天有多少用户参与.

What I'm looking to find out is - what percentage of users participated on every single day.

此外，我想获得比赛每一天的平均值-因此，每个日期都参加比赛的用户的平均百分比.

In addition, I'd like to get the average for each day of the competition - so the average % of users who participated on every single date.

我对此感到有些困惑-尝试按日期和用户ID分组，但到目前为止还没有运气.

I'm a bit stuck with this one - tried grouping by date and userid but no luck so far.

将感谢您的帮助！

推荐答案

我认为这是您想要的:

SELECT date(time),
       count(distinct userid) / cd.cnt
FROM activity_entries ae join
     (select count(distinct userid) as cnt
      from activity_entries) cd
GROUP BY date(time);

它使用子查询来计算表中的不重复用户总数，然后将每天的用户数相除以获得比例.

It uses a subquery to calculate the total distinct users in the table and then divides the number of users on each day to get the proportion.

这篇关于每天有多少用户参与(SQL查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！