MySQL查询,用户"月"留存怎么计算?

自己研究算了半天,发现过往基础不行,特把问题发着求助各位:
计算月留存(不是30日留存)

fi_user: 用户数据表

图片说明

fi_user_action:用户活跃行为表

图片说明

预期结果:

图片说明

怎么写这个Mysql查询?

4个回答

如果只是对目前这些数据, 倒是可以用 union all 把数据连到一个查询语句里。 如果还有更多月的数据, 那要根据数据具体改下语句。
一般应用中,为了逻辑简单,还是按楼上的思路比较合适。


SELECT '1月' AS 日期 , SUM(n_flag) AS 新增用户 ,   SUM(m2_flag) AS 2月留存 , SUM(m3_flag) AS 3月留存 FROM (
 SELECT 0 AS n_flag  ,  CASE  month_flag WHEN '2020-02' THEN 1 ELSE 0 END  AS m2_flag ,
 CASE  month_flag WHEN '2020-03' THEN 1 ELSE 0 END  AS m3_flag
  FROM (
 SELECT DISTINCT userid , MID(createtime,1,7) AS month_flag FROM fi_user_action
 ) A -- 计算2,3月留存
UNION ALL
 SELECT COUNT(1) AS n_flag, 0 AS m2_flag, 0 AS m3_flag
 FROM fi_user WHERE created_at LIKE '2020-01%'
-- 计算1月新增
) B;


seakingx
陈年椰子 回复Fred红叶: 如果那样, 一月份的数就是0 , 因为新增数是另外一个表算的。 我的SQL 是算总体用户数量变动情况。 如果你要单独计算1月份新增的客户变动情况,那要复杂的多。要查询出1月份新增客户列表,再和2,3月份活动客户比较才得到统计数。
5 个月之前 回复
weixin_38079732
Fred红叶 这样算在实际运算中会不会有问题:比如实际上1月份留下的用户一个都没有留存下来, 二月和三月有留存数,是因为1月份之前的2019年老用户数留下了。这样会不会有漏洞?
5 个月之前 回复

这个需求无法用一条sql写出来,得拆分开写

新增用户

SELECT
    count(DISTINCT user_openid)
FROM
    fi_user
WHERE
    created_at >= '2020-01-01'
AND created_at < '2020-02-01'

一月用户到二月的留存人数

SELECT
    count(DISTINCT user_openid)
FROM
    fi_user
WHERE
    created_at >= '2020-01-01'
AND created_at < '2020-02-01'
AND user_openid IN (
    SELECT DISTINCT
        userID
    FROM
        user_action where CreateTime >= '2020-02-01' and CreateTime < '2020-03-01'
)

建议新创建一张表,然后写个统计脚本把数据填写进去

weixin_38079732
Fred红叶 感谢解答,这样算是挺快捷的,但每次只能算一个月的数据,而由于之后模型的需求,所以会有多月份的需求。
5 个月之前 回复

可以指定每个月的数据统计,示例:
SELECT
sum( CASE MONTH ( a.register_time ) WHEN '1' THEN 1 ELSE 0 END ) AS january,
sum( CASE MONTH ( a.register_time ) WHEN '2' THEN 1 ELSE 0 END ) AS february,
sum( CASE MONTH ( a.register_time ) WHEN '3' THEN 1 ELSE 0 END ) AS march,
sum( CASE MONTH ( a.register_time ) WHEN '4' THEN 1 ELSE 0 END ) AS april,
sum( CASE MONTH ( a.register_time ) WHEN '5' THEN 1 ELSE 0 END ) AS may,
sum( CASE MONTH ( a.register_time ) WHEN '6' THEN 1 ELSE 0 END ) AS june,
sum( CASE MONTH ( a.register_time ) WHEN '7' THEN 1 ELSE 0 END ) AS july,
sum( CASE MONTH ( a.register_time ) WHEN '8' THEN 1 ELSE 0 END ) AS august,
sum( CASE MONTH ( a.register_time ) WHEN '9' THEN 1 ELSE 0 END ) AS september,
sum( CASE MONTH ( a.register_time ) WHEN '10' THEN 1 ELSE 0 END ) AS october,
sum( CASE MONTH ( a.register_time ) WHEN '11' THEN 1 ELSE 0 END ) AS november,
sum( CASE MONTH ( a.register_time ) WHEN '12' THEN 1 ELSE 0 END ) AS december
FROM
associator a
WHERE
YEAR ( a.register_time ) = 2018;

图片说明

一个sql写太麻烦了,还达不到最佳效果,建议拆分

SET @date = "2020-01,2020-02,2020-03,2020-04";
SELECT
da.businessAt,
ut.registerNum,
ut.registerTime,
aw.existTime,
aw.num
FROM
(-- 罗列月份
SELECT
substring_index( substring_index( @date, ',', help_topic_id + 1 ), ',',- 1 ) AS businessAt
FROM
mysql.help_topic ht
WHERE
help_topic_id < ( length( @date ) - length( REPLACE ( @date, ',', '' ) ) + 1 )
) da
LEFT JOIN ( -- 统计每月增量用户
SELECT count( userId ) registerNum, DATE_FORMAT( create_at, '%Y-%m' ) registerTime FROM user_table GROUP BY DATE_FORMAT( create_at, '%Y-%m' ) ) ut ON ut.registerTime = da.businessAt
LEFT JOIN (
SELECT
dat.businessAt existTime,
exs.registerAt,
exs.num
FROM
(-- 罗列月份
SELECT
substring_index( substring_index( @date, ',', help_topic_id + 1 ), ',',- 1 ) AS businessAt
FROM
mysql.help_topic ht
WHERE
help_topic_id < ( length( @date ) - length( REPLACE ( @date, ',', '' ) ) + 1 )
) dat
LEFT JOIN (
SELECT
COUNT( ut.userId ) num,
DATE_FORMAT( ut.create_at, '%Y-%m' ) registerAt,
DATE_FORMAT( ua.create_at, '%Y-%m' ) existAt
FROM
user_action ua
LEFT JOIN user_table ut ON ua.userId = ut.userId
GROUP BY
DATE_FORMAT( ut.create_at, '%Y-%m' ),
DATE_FORMAT( ua.create_at, '%Y-%m' )
) exs ON exs.existAt = dat.businessAt
) aw ON aw.registerAt = da.businessAt
ORDER BY da.businessAt,aw.existTime

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐