DELIMITER $$
CREATE DEFINER=`vpRoot`@`%` PROCEDURE `retention`(IN datestr VARCHAR(20))
BEGIN
DECLARE s INT DEFAULT 0;
DECLARE sdate VARCHAR(20) CHARACTER SET utf8;
DECLARE country_zh VARCHAR(20) CHARACTER SET utf8;
DECLARE pkg_name VARCHAR(50) CHARACTER SET utf8 ;
DECLARE register_num INT DEFAULT 0;
DECLARE keep_number_1 INT DEFAULT 0;
DECLARE keep_number_2 INT DEFAULT 0;
DECLARE keep_number_3 INT DEFAULT 0;
DECLARE keep_number_7 INT DEFAULT 0;
DECLARE keep_number_15 INT DEFAULT 0;
DECLARE keep_number_30 INT DEFAULT 0;

DECLARE report CURSOR FOR
SELECT
	t5.pt sdate,
    t5.countryZh,
    t5.pkgName,
    t4.keep_number_1,
    t4.keep_number_2,
    t4.keep_number_3,
    t4.keep_number_7,
    t4.keep_number_15,
	t4.keep_number_30,
    t5.register_num
FROM
    (SELECT
        t3.sdate,
            t3.countryZh countryZh,
            t3.pkgName,
            CASE
                WHEN t3.delta = 1 THEN COUNT(userId)
            END 'keep_number_1',
            CASE
                WHEN t3.delta = 2 THEN COUNT(userId)
            END 'keep_number_2',
            CASE
                WHEN t3.delta = 3 THEN COUNT(userId)
            END 'keep_number_3',
            CASE
                WHEN t3.delta = 7 THEN COUNT(userId)
            END 'keep_number_7',
            CASE
                WHEN t3.delta = 15 THEN COUNT(userId)
            END 'keep_number_15',
            CASE
                WHEN t3.delta = 30 THEN COUNT(userId)
            END 'keep_number_30'
    FROM
        (SELECT
        CASE delta
                WHEN 1 THEN DATE_SUB(datestr, INTERVAL 1 DAY)
                WHEN 2 THEN DATE_SUB(datestr, INTERVAL 2 DAY)
                WHEN 3 THEN DATE_SUB(datestr, INTERVAL 3 DAY)
                WHEN 7 THEN DATE_SUB(datestr, INTERVAL 7 DAY)
                WHEN 15 THEN DATE_SUB(datestr, INTERVAL 15 DAY)
                WHEN 30 THEN DATE_SUB(datestr, INTERVAL 30 DAY)
            END AS sdate,
            t.countryZh,
            t.pkgName,
            t.userId,
            t.delta
    FROM
        (SELECT
        t1.countryZh,
            t1.pkgName,
            t1.userId,
            DATEDIFF(datestr, t2.pt) AS delta
    FROM
        (SELECT
        countryZh, pkgName, userId
    FROM
        hourly_active_user
    WHERE
        queryTime = datestr
            AND userId != 'undefined'
    GROUP BY countryZh , pkgName , userId) t1
    LEFT JOIN sys_user t2 ON t1.userId = t2.userId
        AND t1.countryZh = t2.countryZh
        AND t1.pkgName = t2.pkgName) t
    WHERE
        t.delta IN (1 , 2, 3, 7, 15, 30)) t3
    GROUP BY t3.sdate , t3.countryZh , t3.pkgName) t4
        RIGHT JOIN
    (SELECT
        countryZh, pkgName, pt, COUNT(userId) AS register_num
    FROM
        sys_user
	where pt in (
		date_sub(datestr, INTERVAL 1 DAY),
        date_sub(datestr, INTERVAL 2 DAY),
        date_sub(datestr, INTERVAL 3 DAY),
        date_sub(datestr, INTERVAL 7 DAY),
        date_sub(datestr, INTERVAL 15 DAY),
		date_sub(datestr, INTERVAL 30 DAY)
    ) and countryZh is not null
    GROUP BY countryZh , pkgName , pt) t5 ON t4.countryZh = t5.countryZh
        AND t4.pkgName = t5.pkgName
        AND t4.sdate = t5.pt order by t5.pt;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
select datestr;
OPEN report;
	FETCH report INTO sdate, country_zh, pkg_name, keep_number_1, keep_number_2, keep_number_3, keep_number_7, keep_number_15, keep_number_30, register_num;
    WHILE s<>1 DO

        INSERT INTO user_retention_gmt8 (pt, country_zh, pkg_name, register_num) VALUE (sdate, country_zh, pkg_name, register_num)
		ON DUPLICATE KEY UPDATE `register_num` = register_num;

        IF keep_number_1 IS NOT NULL THEN
            INSERT INTO user_retention_gmt8 (pt, country_zh, pkg_name, register_num, keep_number_1) VALUE (sdate, country_zh, pkg_name, register_num, keep_number_1)
            ON DUPLICATE KEY UPDATE `keep_number_1` = keep_number_1;
        END IF;

        IF keep_number_2 IS NOT NULL THEN

            INSERT INTO user_retention_gmt8(pt, country_zh, pkg_name, register_num, keep_number_2) VALUE (sdate, country_zh, pkg_name, register_num, keep_number_2)
            ON DUPLICATE KEY UPDATE `keep_number_2` = keep_number_2;
        END IF;

        IF keep_number_3 IS NOT NULL THEN
            INSERT INTO user_retention_gmt8(pt, country_zh, pkg_name, register_num, keep_number_3) VALUE (sdate, country_zh, pkg_name, register_num, keep_number_3)
            ON DUPLICATE KEY UPDATE `keep_number_3` = keep_number_3;
        END IF;

        IF keep_number_7 IS NOT NULL THEN
            INSERT INTO user_retention_gmt8(pt, country_zh, pkg_name, register_num, keep_number_7) VALUE (sdate, country_zh, pkg_name, register_num, keep_number_7)
            ON DUPLICATE KEY UPDATE `keep_number_7` = keep_number_7;
        END IF;

        IF keep_number_15 IS NOT NULL THEN
            INSERT INTO user_retention_gmt8(pt, country_zh, pkg_name, register_num, keep_number_15) VALUE (sdate, country_zh, pkg_name, register_num, keep_number_15)
            ON DUPLICATE KEY UPDATE `keep_number_15` = keep_number_15;
        END IF;

        IF keep_number_30 IS NOT NULL THEN
			INSERT INTO user_retention_gmt8(pt, country_zh, pkg_name, register_num, keep_number_30) VALUE (sdate, country_zh, pkg_name, register_num, keep_number_30)
            ON DUPLICATE KEY UPDATE `keep_number_30` = keep_number_30;
        END IF;

        FETCH report INTO sdate, country_zh, pkg_name, keep_number_1, keep_number_2, keep_number_3, keep_number_7, keep_number_15, keep_number_30, register_num;
    END WHILE;

CLOSE report;
	END$$
DELIMITER ;