过去 6 个月每周结束时的用户总数 [英] Total number of users at end of each week for last 6 months

问题描述

我试图找出过去 26 周内每周结束时的用户总数.

I am trying to find the total number of users at the end of each week for the last 26 weeks.

例如

• 第 26 周开始时有 100 名用户，该周有 20 名新用户注册，因此第 26 周结束时有 120 名
• 第 25 周开始有 120 名用户，该周有 20 名新用户注册，因此第 25 周结束时有 140 名
• 第 24 周开始时有 140 个用户和 0 个新用户注册，10 个用户删除了他们的帐户，因此第 24 周结束时有 130 个
• 第 23 周开始时有 130 个用户，该周有 10 个新用户注册，因此第 24 周结束时有 140 个

等等

我可以找到每周的新用户数量，但如何让它显示总数(即本周新用户 + 本周之前的所有用户)等等，而不必创建 26 个单独的选择?是否可以?

I can find the number of new users each week but how can I get it to show me the total (i.e. new this week + all those prior to this week) and so on without having to create 26 seperate selects? Is it possible?

我发现 将总活跃用户分组为前 8 周中的每一周，但它是针对 SQL Server 的，我认为它不能完全满足我的需求.

I found Grouping total active users for each of the previous 8 weeks but it is for SQL Server and I don't think it does quite what I need.

我每周都用它来获取新的:

I am using this to get the new each week:

select count(id) as total
    from users
    where join_date>='$sixmonths'
    group by WEEK(join_date)
    order by WEEK(join_date) desc
    limit 26

推荐答案

查询:

select count(u.id) as total,
       (SELECT COUNT(u.id)
        FROM users u2
        WHERE WEEK(u2.join_date) <=
              WEEK(u.join_date)
        AND u2.id = u.id) AS Total_count
from users u
where u.join_date>='$sixmonths'
group by WEEK(u.join_date)
order by WEEK(u.join_date) desc
limit 26

要使查询在年份之间工作:

For Query to work between years:

select count(u.id) as total,
       (SELECT COUNT(u.id)
        FROM users u2
        WHERE YEARWEEK(u2.join_date) <=
              YEARWEEK(u.join_date)
        AND u2.id = u.id) AS Total_count
from users u
where u.join_date>='$sixmonths'
group by YEARWEEK(u.join_date)
order by YEARWEEK(u.join_date) desc
limit 26

这篇关于过去 6 个月每周结束时的用户总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！