统计用户注册人数（7天日期）

Yourisa

已于 2023-02-15 14:20:07 修改

阅读量667

点赞数

分类专栏： java 文章标签： mysql 数据库

于 2022-07-07 14:04:45 首次发布

本文链接：https://blog.csdn.net/yourisa/article/details/125657952

版权

java 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

		SELECT
        date_add( curdate(), INTERVAL ( cast( help_topic_id AS signed INTEGER ) - 7 ) DAY ) time,ifnull( am.count, 0 ) total
        FROM mysql.help_topic h
        LEFT JOIN (
        	SELECT
        	date_format( created_at, '%Y-%m-%d' ) time,
        	count( 0 ) count
        	FROM
        	user_detail
        	GROUP BY
        	date_format( created_at, '%Y-%m-%d' )
        ) am ON date_add( curdate(), INTERVAL ( cast( h.help_topic_id AS signed INTEGER ) - 7 ) DAY ) = am.time
        WHERE h.help_topic_id <= 7 ORDER BY h.help_topic_id
        
		其中mysql.help_topic为系统表，主要的业务表为user_detail（用户详情表）

SELECT
    DATE_FORMAT( t1.date, '%Y-%m-%d' ) AS date,
    IFNULL( t2.num, 0 ) AS registerNum
FROM
    (SELECT
        @i := @i + 1 AS 'NO',
        DATE(
        DATE_ADD( '2021-11-20', INTERVAL @i DAY )) AS date     -- 开始时间[创建日期表]
    FROM
        mysql.help_topic,
        ( SELECT @i := - 1 ) t
    WHERE
    @i < DATEDIFF( '2021-12-30', '2021-11-20' )) t1                -- 结束时间,开始时间
LEFT JOIN
    (SELECT
        DATE( CreateTime ) AS date,
        COUNT( 1 ) AS num
    FROM
        USER
    WHERE
        CreateTime >= '2021-11-20' ) t2 ON t1.date = t2.date  -- 开始时间[联表查询]]
GROUP BY date