MySQL 잔존율 쿼리 (SP & Query)

CREATE TABLE

CREATE TABLE `fact_retention` (
  `stats_date` date NOT NULL,
  `date` date NOT NULL,
  `period` int NOT NULL,
  `age` int NOT NULL,
  `new_users` int DEFAULT NULL,
  `retained_users` int DEFAULT NULL,
  `retention` float DEFAULT NULL,
  PRIMARY KEY (`stats_date`,`date`,`period`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

수집

CREATE PROCEDURE `db_canmore_statistics`.`usp_fact_make_retention`()
BEGIN

	SET @start_date = SUBDATE(CURRENT_DATE, 91);


	DELETE FROM db_canmore_statistics.fact_retention WHERE stats_date = SUBDATE(CURRENT_DATE, 1);
	
	INSERT INTO db_canmore_statistics.fact_retention
	WITH activities AS (
		SELECT stats_date as start, patient_id as person_id FROM fact_user_activity
		WHERE activity_type = 'SIGNIN'
		AND stats_date >= @start_date AND stats_date < SUBDATE(CURRENT_DATE, 1)
	), new_users AS (	
		SELECT patient_id AS person_id, stats_date AS start_time
		FROM fact_user_activity
		WHERE stats_date >= @start_date AND stats_date < SUBDATE(CURRENT_DATE, 1) AND activity_type = 'SIGNUP'
		ORDER BY 1, 2
	),user_activities AS (
		SELECT A.person_id, DATEDIFF(A.start, C.start_time) AS period_number
		FROM activities A
		LEFT JOIN new_users C ON A.person_id = C.person_id
		GROUP BY 1, 2	
		ORDER BY 1, 2
	),
	cohort_size AS (
	  SELECT start_time, COUNT(1) AS num_users
	  FROM new_users
	  GROUP BY 1
	  ORDER BY 1
	),
	retention_table AS (
	  SELECT C.start_time, A.period_number, COUNT(1) AS num_users
	  FROM user_activities A
	  LEFT JOIN new_users C ON A.person_id = C.person_id
	  GROUP BY 1, 2
	)
	SELECT
		SUBDATE(CURRENT_DATE, 1) as stats_date
		, B.start_time as date
		, S.num_users as new_users
		, B.period_number as period
		, B.num_users AS retained_users
		, B.num_users / B.num_users * 100 as retention
	FROM
		retention_table B
		LEFT JOIN cohort_size S ON B.start_time = S.start_time
	WHERE
		B.start_time IS NOT NULL
	ORDER BY 1, 3;
END

출력

 SELECT cohort_day
, SUM(CASE WHEN day_number = 1 THEN round(percentage,2) ELSE 0 END) AS 'day01'
, SUM(CASE WHEN day_number = 2 THEN round(percentage,2) ELSE 0 END) AS 'day02'
, SUM(CASE WHEN day_number = 3 THEN round(percentage,2) ELSE 0 END) AS 'day03'
, SUM(CASE WHEN day_number = 4 THEN round(percentage,2) ELSE 0 END) AS 'day04'
, SUM(CASE WHEN day_number = 5 THEN round(percentage,2) ELSE 0 END) AS 'day05'
, SUM(CASE WHEN day_number = 6 THEN round(percentage,2) ELSE 0 END) AS 'day06'
, SUM(CASE WHEN day_number = 7 THEN round(percentage,2) ELSE 0 END) AS 'day07'
, SUM(CASE WHEN day_number = 8 THEN round(percentage,2) ELSE 0 END) AS 'day08'
, SUM(CASE WHEN day_number = 9 THEN round(percentage,2) ELSE 0 END) AS 'day09'
, SUM(CASE WHEN day_number = 10 THEN round(percentage,2) ELSE 0 END) AS 'day10'
, SUM(CASE WHEN day_number = 11 THEN round(percentage,2) ELSE 0 END) AS 'day11'
, SUM(CASE WHEN day_number = 12 THEN round(percentage,2) ELSE 0 END) AS 'day12'
, SUM(CASE WHEN day_number = 13 THEN round(percentage,2) ELSE 0 END) AS 'day13'
, SUM(CASE WHEN day_number = 14 THEN round(percentage,2) ELSE 0 END) AS 'day14'
, SUM(CASE WHEN day_number = 15 THEN round(percentage,2) ELSE 0 END) AS 'day15'
, SUM(CASE WHEN day_number = 16 THEN round(percentage,2) ELSE 0 END) AS 'day16'
, SUM(CASE WHEN day_number = 17 THEN round(percentage,2) ELSE 0 END) AS 'day17'
, SUM(CASE WHEN day_number = 18 THEN round(percentage,2) ELSE 0 END) AS 'day18'
, SUM(CASE WHEN day_number = 19 THEN round(percentage,2) ELSE 0 END) AS 'day19'
, SUM(CASE WHEN day_number = 20 THEN round(percentage,2) ELSE 0 END) AS 'day20'
, SUM(CASE WHEN day_number = 21 THEN round(percentage,2) ELSE 0 END) AS 'day21'
, SUM(CASE WHEN day_number = 22 THEN round(percentage,2) ELSE 0 END) AS 'day22'
, SUM(CASE WHEN day_number = 23 THEN round(percentage,2) ELSE 0 END) AS 'day23'
, SUM(CASE WHEN day_number = 24 THEN round(percentage,2) ELSE 0 END) AS 'day24'
, SUM(CASE WHEN day_number = 25 THEN round(percentage,2) ELSE 0 END) AS 'day25'
, SUM(CASE WHEN day_number = 26 THEN round(percentage,2) ELSE 0 END) AS 'day26'
, SUM(CASE WHEN day_number = 27 THEN round(percentage,2) ELSE 0 END) AS 'day27'
, SUM(CASE WHEN day_number = 28 THEN round(percentage,2) ELSE 0 END) AS 'day28'
, SUM(CASE WHEN day_number = 29 THEN round(percentage,2) ELSE 0 END) AS 'day29'
, SUM(CASE WHEN day_number = 30 THEN round(percentage,2) ELSE 0 END) AS 'day30'
FROM fact_retention
WHERE stats_date = SUBDATE(CURRENT_DATE, 1)
GROUP BY cohort_day;