MySQL查询，用户"月"留存怎么计算？

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

fi_user: 用户数据表

fi_user_action：用户活跃行为表

预期结果：

怎么写这个Mysql查询？

如果只是对目前这些数据， 倒是可以用 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;

这个需求无法用一条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'
)

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

可以指定每个月的数据统计，示例：
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