sql 查询选定时间内容的用户总数和新增人数

sql 接口 theDate为选定日期的前一天，days 共几天

List<StatQueryPO> statEverydayTotalUser(@Param("theDate") String theDate,@Param("days") Long days);

sql语句

		SELECT
			d.date,
			d.num,
			@total := @total + d.num AS num 
		FROM
			(
			SELECT
				c.date AS date,
				Ifnull( newadd, 0 ) AS num 
			FROM
				(
				SELECT
					@cdate := date_add( @cdate, INTERVAL 1 DAY ) date 
				FROM
					( SELECT @cdate := '2021-5-24' FROM USER LIMIT 2 ) AS a 
				) AS c
				LEFT JOIN (
				SELECT
					date( create_time ) AS rat_time,
					count( id ) AS newadd  
				FROM
				city_challenge.user 
			
				GROUP BY
					rat_time 
				) AS temp ON c.date = temp.rat_time 
			) AS d,
			(
			SELECT
			@total := ( SELECT count( id ) FROM USER WHERE date( create_time ) <= '2021-5-24' )) AS T1

时间为选定日期的前一天(获取指定日期的前一天)

		   // 指定日期的前几天
	    public static String dateTheDelay( Date date ,int day) {
	       // Date date = new Date();// 取时间
	        Calendar calendar = Calendar.getInstance();
	        calendar.setTime(date);
	        calendar.add(calendar.DATE, day);
	        date = calendar.getTime();
	        String delayThreeDay = DateUtils.dateToString(date, "yyyy-MM-dd");
	        return delayThreeDay;
}

天数

public static long getDateDays(String starTime ,String endTime) throws ParseException {

    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    Date starDate = dateFormat.parse(starTime);
    Date endDate = dateFormat.parse(endTime);
    long nd = 1000 * 24 * 60 * 60;
    long nh = 1000 * 60 * 60;
    long nm = 1000 * 60;
    long ns = 1000;
    long diff = endDate.getTime() - starDate.getTime();
    long sec = diff / nd;
    return sec;
}