登入用户，订购用户，活跃用户，留存用户

本文通过SQL查询展示了如何分析特定时间段内用户的行为数据，包括登录用户数、订购用户数、资源访问用户数、活跃用户数及沉默用户数，为产品运营提供数据支持。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、登入用户，订购用户，资源访问用户

    SELECT 
        P.d,P.user_c,Q.pay_c,R.res_c
    FROM 
    (
        SELECT 
            DATE_FORMAT(create_date,'%Y-%m') AS d,
            COUNT(DISTINCT(userid)) AS user_c
        FROM user_table 
        WHERE create_date LIKE '2019-11%'
             AND product_key = "product_key1"
        GROUP BY d
    ) P
    LEFT JOIN
    (
        SELECT 
            DATE_FORMAT(create_time,'%Y-%m') AS d, 
            COUNT(DISTINCT userid) AS pay_c
        FROM
            order  
        WHERE  status = 1 
                AND create_time like '2019-11%' 
                AND product_key = "product_key1"
        GROUP BY d
    ) Q
    ON P.d = Q.d
    LEFT JOIN 
    (
        SELECT 
            DATE_FORMAT(create_date,'%Y-%m') AS d,
            COUNT(DISTINCT(userid)) AS res_c
        FROM pv.userres
        WHERE create_date LIKE '2019-11%'
            AND product_key = "product_key1"
        GROUP BY d
    ) R
    ON Q.d = R.d

2、某段时间留存用户UV，订购UV，资源访问UV，活跃UV，沉默UV

   SELECT 
        "2019-8-01 ~ now" AS month_range,
        COUNT(DISTINCT userid) AS c_login_user,
        COUNT(DISTINCT pay_userid) AS c_pay_userid,
        COUNT(DISTINCT res_userid) AS c_res_userid,
        COUNT(DISTINCT activate_userid) AS c_activate_userid,
        COUNT(DISTINCT silence_user) AS c_silence_user
    FROM(
        SELECT 
            userid,
            (CASE WHEN -- 最近某段时间订购用户数
                    EXISTS( 
                        SELECT 1 FROM order t1 
                            WHERE t1.userid = t5.userid 
                                    AND status=1 
                                    AND product_key ="product_key1"
                                    AND create_time>='2019-8-01' 
                                    
                    )
                  THEN t5.userid ELSE NULL 
            END) AS pay_userid,
            
            (CASE WHEN  -- 最近某段时间资源访问用户数
                    EXISTS( 
                        SELECT 1 FROM pv.userres t2 
                            WHERE t2.userid = t5.userid 
                            AND product_key ="product_key1" 
                            AND create_date>='2019-8-01' 
                            
                    )
                THEN t5.userid ELSE NULL
            END) AS res_userid,
            
            (CASE WHEN  -- 最近三个月活跃用户数，登入天数>20
                    (
                     SELECT 
                         count(DISTINCT create_date,t3.userid )
                     FROM pv_table t3 
                     WHERE t3.userid = t5.userid
                         AND create_date>='2019-8-01' 
                         AND product_key ="product_key1" 
                    )>=20
                THEN t5.userid ELSE NULL
            END) AS activate_userid,
            
            (CASE WHEN  -- 最近某段时间沉默用户数，没有访问记录
                NOT EXISTS( 
                    SELECT 1 FROM pv_table t4 
                    WHERE t4.userid = t5.userid 
                    AND product_key = "product_key1"
                    AND create_date>='2019-8-01' 
                )
                THEN t5.userid ELSE NULL
             END) AS silence_user,
            
            create_date 
        FROM user_table t5
        WHERE last_login_date>='2019-8-01' 
             AND product_key ="product_key1"
    )X