• 模糊查询用逗号分隔的数据

find_in_set

   WHERE Value LIKE '10,%'
   OR Value LIKE '%,10'
   OR Value LIKE '%,10,%'
   OR Value= '10';

• 查看是否有重复user

SELECT count(*) as number,openid,max(id),max(CREATE_DATE)  FROM `user` GROUP BY openid HAVING number > 1

• 近七天的数据

SELECT
	`from`,
	DATE( create_date ) AS 'date',
	count(*) AS 'count'
FROM
	link_record
WHERE
	DATE_SUB( CURDATE(), INTERVAL 6 DAY ) <= create_date 
GROUP BY
	DATE(create_date),`from`
ORDER BY DATE(create_date)

• 次日留存

DROP PROCEDURE IF EXISTS p2;
CREATE PROCEDURE p2 ( IN startDate DATE, OUT startDatePracticeNumber INT, OUT nextDatePracticeNumber INT, OUT startDate_format DATE, OUT startDateRegNumber INT ) BEGIN
	SET @nextDate = DATE_ADD( startDate, INTERVAL 1 DAY );
	SET startDate_format := DATE_FORMAT( startDate, "%Y-%m-%d" );
	SET @nextDate_format = DATE_FORMAT( @nextDate, "%Y-%m-%d" );
	SET startDateRegNumber := ( SELECT COUNT(*) FROM `user` WHERE DATE_FORMAT( CREATE_DATE, "%Y-%m-%d" ) = startDate_format );
	SET startDatePracticeNumber := (
		SELECT
			COUNT( DISTINCT ( user_id ) ) 
		FROM
			`practice_card_record` 
		WHERE
			user_id IN ( SELECT ID FROM `user` WHERE DATE_FORMAT( CREATE_DATE, "%Y-%m-%d" ) = startDate_format ) 
			AND DATE_FORMAT( practice_date, "%Y-%m-%d" ) = startDate_format 
		);
	
	SET nextDatePracticeNumber := (
		SELECT
			COUNT( DISTINCT ( user_id ) ) 
		FROM
			`practice_card_record` 
		WHERE
			user_id IN ( SELECT ID FROM `user` WHERE DATE_FORMAT( CREATE_DATE, "%Y-%m-%d" ) = startDate_format ) 
			AND DATE_FORMAT( practice_date, "%Y-%m-%d" ) = @nextDate_format 
		);	
END;

DROP PROCEDURE IF EXISTS p3;
	
CREATE PROCEDURE p3 ( IN startDate DATE, IN dayCount INT ) BEGIN
	set @i = 0;
	-- 临时表
	DROP TABLE if EXISTS T2;
	CREATE TEMPORARY TABLE T2 (startDate_format date,startDateRegNumber INT,startDatePracticeNumber INT,nextDatePracticeNumber INT,ratio DOUBLE);

	WHILE @i <=dayCount  DO
		CALL p2(DATE_ADD(startDate,INTERVAL @i day),@startDatePracticeNumber,@nextDatePracticeNumber,@startDate_format,@startDateRegNumber);
		INSERT INTO T2 (startDate_format,startDateRegNumber,startDatePracticeNumber,nextDatePracticeNumber,ratio) VALUES (@startDate_format,@startDateRegNumber,@startDatePracticeNumber,@nextDatePracticeNumber,@nextDatePracticeNumber/@startDateRegNumber);
		SET @i = @i+1;
	END WHILE;
	
	SELECT startDate_format AS '注册时间',startDateRegNumber AS '注册人数',startDatePracticeNumber AS '当天练习人数',nextDatePracticeNumber AS '次日练习人数',ratio AS '次日练习人数比例' FROM T2;
END;

-- 起始日期
SET @startDate = "20230201";
-- 需要查看的天数
SET @dayCount = 5;

CALL p3(@startDate,@dayCount)