sql每月现有用户的所有历史记录 [英] sql monthly existing user for all the historical records

问题描述

我有一个用于用户注册时间的用户数据库：

I have a users database for user sign up time:

id,  signup_time
100    2020-09-01
001    2018-01-01
....

我如何才能找到所有历史记录？使用该月的最后一天作为截止日期，现有用户意味着如果我观察到2020-07-31的最后一天，则该用户已经在2020-07-01之前注册。如果我在2020年6月30日的最后一天观察到，该用户已经在2020-06-01之前注册。

How could I find monthly existing user for all the history record? Use the last day in the month as the cut off day, existing users means if I observe in July last day, 2020-07-31, this user had already signed up before 2020-07-01. If I observe in June last day 2020-06-30, this user had already signed up before 2020-06-01.

新用户意味着我在2020年7月最后一天观察到- 07-31，用户仅在7月之前注册，而不是在2020-07-01至2020-07-31之间的7月之后注册。
我可以通过以下方式找到每月的new_user

New users means if I observe in July last day, 2020-07-31, the user only sign up in July not before July, not after July between 2020-07-01 to 2020-07-31. I could find monthly new_user by

SELECT COUNT(DISTINCT id) AS new_user_count, year_month
FROM(
SELECT id,
             date_part('month', signup_time) AS signup_time_month,
             date_part('year', signup_time) AS signup_time_year,
             concat(date_part('year', signup_time),'-',lpad(date_part('month', signup_time)::text,2,'0')) AS year_month, signup_time
             
      FROM users)
GROUP BY year_month

类似于其他语言中的for循环：

Similar as a for loop in other language:

observation_year_month_list = ['2020-04','2020-05','2020-06']

 for i in observation_year_month_list:
      
     if user signup_time  < i:
        monthly_existing_user_count+1
    

推荐答案

我希望我能理解您的工作。

I hope I understand what you are trying to do.

SELECT COUNT(*) AS existing_user_count, year_month
FROM(
SELECT id, DATEDIFF(signup_time - DATEADD(month, -1, GETDATE())) AS day_diff, 
         date_part('month', signup_time) AS signup_time_month,
         date_part('year', signup_time) AS signup_time_year,
         concat(date_part('year', signup_time),'-',lpad(date_part('month', signup_time)::text,2,'0')) AS year_month, signup_time
         
FROM users
WHERE day_diff > 0)
GROUP BY year_month

-希望这会越来越近-

--Hope this gets closer--

WITH View AS
(
SELECT year_month, COUNT(*) AS monthly_newuser
FROM(
    SELECT id, 
           concat(date_part('year', DATEADD(month, 1, signup_time)),'-'
                 ,lpad(date_part('month', DATEADD(month, 1, signup_time))::text,2,'0')) 
           AS year_month         
FROM users
GROUP BY year_month
)

SELECT year_month, 
       SUM(monthly_newuser) over (ORDER BY year_month) AS existing_user_cnt
FROM View

这篇关于sql每月现有用户的所有历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！