game-sdk/simplewind/extend/huoLtv/sql/runltv.sql

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `runltv`(IN `iday` date)
    BEGIN
        DECLARE Tflag INT DEFAULT 0;
        DECLARE Tappid INT;
        DECLARE Tdate DATE;
        DECLARE Tregtime INT;
        DECLARE Tregday INT;
        DECLARE Tagentid INT;
        DECLARE Tmobile_prefix INT DEFAULT 156;
        DECLARE Tregcnt INT;
        DECLARE Tsummoney DECIMAL(12,2);
        DECLARE Tsetdate DATE DEFAULT iday;
        DECLARE Tstarttime INT;
        DECLARE Tendtime INT;
        DECLARE TdayFlag varchar(20);
        DECLARE Tdata_exist CHAR(2);
        DECLARE TformatDate DATE DEFAULT date_format(Tsetdate,'%Y-%m-%d');

# 渠道游戏
        DECLARE Tagcursor CURSOR FOR SELECT reg_time,app_id,agent_id,reg_days,sum_money FROM `log_ltv_day_mem_temp` WHERE reg_days < 361 AND sum_money > 0 GROUP BY app_id,agent_id,reg_days;
# 渠道
        DECLARE Tacursor CURSOR FOR SELECT reg_time,agent_id,reg_days,SUM(sum_money) as sum_money FROM `log_ltv_day_mem_temp` WHERE reg_days < 361 AND sum_money > 0 GROUP BY agent_id,reg_days;
# 游戏
        DECLARE Tgcursor CURSOR FOR SELECT reg_time,app_id,reg_days,SUM(sum_money) as sum_money FROM `log_ltv_day_mem_temp` WHERE reg_days < 361 AND sum_money > 0 GROUP BY app_id,reg_days;
# 每日
        DECLARE Tcursor CURSOR FOR SELECT reg_time,reg_days,SUM(sum_money) as sum_money FROM `log_ltv_day_mem_temp` WHERE reg_days < 361 AND sum_money > 0 GROUP BY reg_days;

        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Tflag = 1;

        SET Tstarttime=unix_timestamp(Tsetdate);
        SET Tendtime=Tstarttime+86400;

        SET @Ttable = CONCAT('db_sdk_test_log.log_day_mem_',date_format(Tsetdate,'%Y%m') );

#判断是否存在临时表
        CREATE TABLE IF NOT EXISTS `log_ltv_day_mem_temp` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `date` date NOT NULL DEFAULT '0000-00-00' COMMENT '日期',
            `mem_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '玩家ID',
            `agent_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '归属渠道',
            `app_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
            `reg_time` bigint(11) NOT NULL DEFAULT '0' COMMENT '玩家注册时间',
            `reg_days` int(11) NOT NULL DEFAULT '0' COMMENT '注册天数',
            `device_id` varchar(64) NOT NULL DEFAULT '' COMMENT '手机设备码',
            `sum_money` double(12,2) NOT NULL DEFAULT '0.00' COMMENT '累计充值',
            `sum_real_money` double(12,2) NOT NULL DEFAULT '0.00' COMMENT '自然流水金额',
            PRIMARY KEY (`id`),
            UNIQUE KEY `lldmt_date_mem_app_unique` (`date`,`mem_id`,`app_id`),
            KEY `ldmt_agent_index` (`agent_id`),
            KEY `ldmt_app_index` (`app_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ltv玩家每日临时';

#清除数据
        TRUNCATE `log_ltv_day_mem_temp`;
        ALTER TABLE `log_ltv_day_mem_temp` DISABLE KEYS;

        SET @ldmt_sql = "REPLACE INTO `log_ltv_day_mem_temp`  (`date`,`mem_id`,`agent_id`,`app_id`,`reg_time`,`reg_days`,`device_id`,`sum_money`,`sum_real_money`) SELECT `date`,`mem_id`,`agent_id`,`app_id`,`reg_time`,`reg_days`,`device_id`,`sum_money`,`sum_real_money`";

        SET  @ldmt_sql = concat(@ldmt_sql, ' FROM ',@Ttable,' where `date`=\'',Tsetdate,'\'');

        PREPARE ldmt_sql_ex FROM @ldmt_sql;
        EXECUTE ldmt_sql_ex;
        ALTER TABLE log_ltv_day_mem_temp ENABLE KEYS;


        DROP TABLE IF EXISTS log_ldgac_temp;
        CREATE TABLE IF NOT EXISTS `log_ldgac_temp` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `date` date NOT NULL DEFAULT '0000-00-00' COMMENT '日期',
            `app_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '游戏ID',
            `agent_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '渠道ID',
            `reg_cnt` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '注册玩家数',
            `sum_money` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '充值金额',
            `sum_real_money` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '自然流水金额',
            PRIMARY KEY (`id`) USING BTREE,
            UNIQUE KEY `dga_dga_unique` (`date`,`app_id`,`agent_id`) USING BTREE,
            KEY `dga_game_index` (`app_id`) USING BTREE,
            KEY `dga_agent_index` (`agent_id`) USING BTREE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='ltv每日游戏推广员统计表';



#写入每日临时
        TRUNCATE `log_ldgac_temp`;
        ALTER TABLE log_ldgac_temp DISABLE KEYS;
        REPLACE INTO `log_ldgac_temp`
        (
            `date`,
            `app_id`,
            `agent_id`,
            `reg_cnt`,
            `sum_money`,
            `sum_real_money`
        )
            SELECT
                IFNULL(lldmt.`date`,TformatDate) AS `date`,
                IFNULL(lldmt.`app_id`,0) AS `app_id`,
                IFNULL(lldmt.`agent_id`,0) AS `agent_id`,
                COUNT(DISTINCT(CASE WHEN lldmt.`reg_days`=0 AND lldmt.`mem_id` > 0 THEN `mem_id` END)) AS `reg_cnt`	,
                IFNULL(SUM(lldmt.`sum_money`),0) AS `sum_money` ,
                SUM(lldmt.`sum_real_money`) AS `sum_real_money`
            FROM log_ltv_day_mem_temp lldmt
            GROUP BY agent_id, app_id;
        ALTER TABLE log_ldgac_temp ENABLE KEYS;


#每日游戏推广统计表
        ALTER TABLE dw_ltv_game_agent DISABLE KEYS;
        REPLACE INTO `dw_ltv_game_agent`
        (
            `date`,
            `app_id`,
            `agent_id`,
            `reg_cnt`
        )
            SELECT
                IFNULL(`date`,TformatDate) AS `date`,
                IFNULL(`app_id`,0) AS `app_id`,
                IFNULL(`agent_id`,0) AS `agent_id`,
                IFNULL(`reg_cnt`,0) AS `reg_cnt`
            FROM log_ldgac_temp
            WHERE `reg_cnt`>0;
        ALTER TABLE dw_ltv_game_agent ENABLE KEYS;


#每日推广员统计表
        ALTER TABLE dw_ltv_agent DISABLE KEYS;
        REPLACE INTO `dw_ltv_agent`
        (
            `date`,
            `agent_id`,
            `reg_cnt`
        )
            SELECT
                IFNULL(ldmt.`date`,TformatDate) AS `date`,
                ldmt.`agent_id` AS `agent_id`,
                COUNT(DISTINCT(CASE WHEN ldmt.`reg_days`=0 AND ldmt.`mem_id` > 0 THEN `mem_id` END)) AS `reg_cnt`
            FROM log_ltv_day_mem_temp ldmt
            WHERE ldmt.`reg_days` = 0 AND ldmt.`mem_id` > 0
            GROUP BY agent_id;
        ALTER TABLE dw_ltv_agent ENABLE KEYS;


#每日游戏统计表
        ALTER TABLE dw_ltv_game DISABLE KEYS;
        REPLACE INTO `dw_ltv_game`
        (
            `date`,
            `app_id`,
            `reg_cnt`
        )
            SELECT
                IFNULL(`date`,TformatDate) AS `date`,
                IFNULL(`app_id`,0) AS `app_id`,
                SUM(`reg_cnt`) AS `reg_cnt`
            FROM log_ldgac_temp
            WHERE `reg_cnt`>0
            GROUP BY app_id;
        ALTER TABLE dw_ltv_game ENABLE KEYS;



#每日统计表
        ALTER TABLE dw_ltv_day DISABLE KEYS;
        REPLACE INTO `dw_ltv_day`
        (
            `date`,
            `reg_cnt`
        )
            SELECT
                IFNULL(ldmt.`date`,TformatDate) AS `date`,
                COUNT(DISTINCT(CASE WHEN ldmt.`reg_days`=0 AND ldmt.`mem_id` > 0 THEN `mem_id` END)) AS `reg_cnt`
            FROM log_ltv_day_mem_temp ldmt
            WHERE ldmt.`reg_days` = 0 AND ldmt.`mem_id` > 0;
        ALTER TABLE dw_ltv_day ENABLE KEYS;


# 渠道游戏LTV
        SET Tflag=0;
        OPEN Tagcursor;
        FETCH Tagcursor INTO Tregtime,Tappid,Tagentid,Tregday,Tsummoney;
        WHILE Tflag<>1 DO
            IF Tregday < 360 THEN
                IF Tregday < 60 THEN
                    CASE Tregday
                        WHEN 0 THEN
                        SET TdayFlag = 'day1';
                        WHEN 1 THEN
                        SET TdayFlag = 'day2';
                        WHEN 2 THEN
                        SET TdayFlag = 'day3';
                        WHEN 3 THEN
                        SET TdayFlag = 'day4';
                        WHEN 4 THEN
                        SET TdayFlag = 'day5';
                        WHEN 5 THEN
                        SET TdayFlag = 'day6';
                        WHEN 6 THEN
                        SET TdayFlag = 'day7';
                        WHEN 7 THEN
                        SET TdayFlag = 'day8';
                        WHEN 8 THEN
                        SET TdayFlag = 'day9';
                        WHEN 9 THEN
                        SET TdayFlag = 'day10';
                        WHEN 10 THEN
                        SET TdayFlag = 'day11';
                        WHEN 11 THEN
                        SET TdayFlag = 'day12';
                        WHEN 12 THEN
                        SET TdayFlag = 'day13';
                        WHEN 13 THEN
                        SET TdayFlag = 'day14';
                        WHEN 14 THEN
                        SET TdayFlag = 'day15';
                        WHEN 15 THEN
                        SET TdayFlag = 'day16';
                        WHEN 16 THEN
                        SET TdayFlag = 'day17';
                        WHEN 17 THEN
                        SET TdayFlag = 'day18';
                        WHEN 18 THEN
                        SET TdayFlag = 'day19';
                        WHEN 19 THEN
                        SET TdayFlag = 'day20';
                        WHEN 20 THEN
                        SET TdayFlag = 'day21';
                        WHEN 21 THEN
                        SET TdayFlag = 'day22';
                        WHEN 22 THEN
                        SET TdayFlag = 'day23';
                        WHEN 23 THEN
                        SET TdayFlag = 'day24';
                        WHEN 24 THEN
                        SET TdayFlag = 'day25';
                        WHEN 25 THEN
                        SET TdayFlag = 'day26';
                        WHEN 26 THEN
                        SET TdayFlag = 'day27';
                        WHEN 27 THEN
                        SET TdayFlag = 'day28';
                        WHEN 28 THEN
                        SET TdayFlag = 'day29';
                        WHEN 29 THEN
                        SET TdayFlag = 'day30';
                        WHEN 30 THEN
                        SET TdayFlag = 'day31';
                        WHEN 31 THEN
                        SET TdayFlag = 'day32';
                        WHEN 32 THEN
                        SET TdayFlag = 'day33';
                        WHEN 33 THEN
                        SET TdayFlag = 'day34';
                        WHEN 34 THEN
                        SET TdayFlag = 'day35';
                        WHEN 35 THEN
                        SET TdayFlag = 'day36';
                        WHEN 36 THEN
                        SET TdayFlag = 'day37';
                        WHEN 37 THEN
                        SET TdayFlag = 'day38';
                        WHEN 38 THEN
                        SET TdayFlag = 'day39';
                        WHEN 39 THEN
                        SET TdayFlag = 'day40';
                        WHEN 40 THEN
                        SET TdayFlag = 'day41';
                        WHEN 41 THEN
                        SET TdayFlag = 'day42';
                        WHEN 42 THEN
                        SET TdayFlag = 'day43';
                        WHEN 43 THEN
                        SET TdayFlag = 'day44';
                        WHEN 44 THEN
                        SET TdayFlag = 'day45';
                        WHEN 45 THEN
                        SET TdayFlag = 'day46';
                        WHEN 46 THEN
                        SET TdayFlag = 'day47';
                        WHEN 47 THEN
                        SET TdayFlag = 'day48';
                        WHEN 48 THEN
                        SET TdayFlag = 'day49';
                        WHEN 49 THEN
                        SET TdayFlag = 'day50';
                        WHEN 50 THEN
                        SET TdayFlag = 'day51';
                        WHEN 51 THEN
                        SET TdayFlag = 'day52';
                        WHEN 52 THEN
                        SET TdayFlag = 'day53';
                        WHEN 53 THEN
                        SET TdayFlag = 'day54';
                        WHEN 54 THEN
                        SET TdayFlag = 'day55';
                        WHEN 55 THEN
                        SET TdayFlag = 'day56';
                        WHEN 56 THEN
                        SET TdayFlag = 'day57';
                        WHEN 57 THEN
                        SET TdayFlag = 'day58';
                        WHEN 58 THEN
                        SET TdayFlag = 'day59';
                        WHEN 59 THEN
                        SET TdayFlag = 'day60';
                    END CASE;
                ELSEIF Tregday < 90 THEN
                    SET TdayFlag = 'day90';
                ELSEIF Tregday < 120 THEN
                    SET TdayFlag = 'day120';
                ELSEIF Tregday < 180 THEN
                    SET TdayFlag = 'day180';
                ELSEIF Tregday < 270 THEN
                    SET TdayFlag = 'day270';
                ELSE
                    SET TdayFlag = 'day360';
                END IF;

#校验数据是否存在
                SELECT IFNULL((SELECT 'Y' from `dw_ltv_game_agent` where `date`=FROM_UNIXTIME(Tregtime,'%Y-%m-%d') AND `agent_id`=Tagentid AND `app_id`=Tappid limit 1),'N') INTO Tdata_exist;
                IF 'N' = Tdata_exist THEN
#不存在数据则创建
                    SET @sqlstr=CONCAT('INSERT INTO `dw_ltv_game_agent` (`date`,`app_id`,`agent_id`,`reg_cnt`,`sum_money`,',TdayFlag,') VALUES (FROM_UNIXTIME(',Tregtime,',"%Y-%m-%d"),',Tappid,',',Tagentid,',1,',Tsummoney,',',Tsummoney,')');
                    PREPARE stmt FROM @sqlstr;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                ELSE
                    SET @sqlstr=CONCAT('update dw_ltv_game_agent SET ',TdayFlag,'=',TdayFlag,'+',Tsummoney,', sum_money = sum_money + ',Tsummoney,' WHERE date=FROM_UNIXTIME(',Tregtime,',"%Y-%m-%d") AND agent_id=',Tagentid,' AND app_id=',Tappid);
                    PREPARE stmt FROM @sqlstr;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                END IF;
            END IF;
            FETCH Tagcursor INTO Tregtime,Tappid,Tagentid,Tregday,Tsummoney;
        END WHILE;
        CLOSE  Tagcursor;

# 渠道LTV
        SET Tflag=0;
        OPEN Tacursor;
        FETCH Tacursor INTO Tregtime,Tagentid,Tregday,Tsummoney;
        WHILE Tflag<>1 DO
            IF Tregday < 360 THEN
                IF Tregday < 60 THEN
                    CASE Tregday
                        WHEN 0 THEN
                        SET TdayFlag = 'day1';
                        WHEN 1 THEN
                        SET TdayFlag = 'day2';
                        WHEN 2 THEN
                        SET TdayFlag = 'day3';
                        WHEN 3 THEN
                        SET TdayFlag = 'day4';
                        WHEN 4 THEN
                        SET TdayFlag = 'day5';
                        WHEN 5 THEN
                        SET TdayFlag = 'day6';
                        WHEN 6 THEN
                        SET TdayFlag = 'day7';
                        WHEN 7 THEN
                        SET TdayFlag = 'day8';
                        WHEN 8 THEN
                        SET TdayFlag = 'day9';
                        WHEN 9 THEN
                        SET TdayFlag = 'day10';
                        WHEN 10 THEN
                        SET TdayFlag = 'day11';
                        WHEN 11 THEN
                        SET TdayFlag = 'day12';
                        WHEN 12 THEN
                        SET TdayFlag = 'day13';
                        WHEN 13 THEN
                        SET TdayFlag = 'day14';
                        WHEN 14 THEN
                        SET TdayFlag = 'day15';
                        WHEN 15 THEN
                        SET TdayFlag = 'day16';
                        WHEN 16 THEN
                        SET TdayFlag = 'day17';
                        WHEN 17 THEN
                        SET TdayFlag = 'day18';
                        WHEN 18 THEN
                        SET TdayFlag = 'day19';
                        WHEN 19 THEN
                        SET TdayFlag = 'day20';
                        WHEN 20 THEN
                        SET TdayFlag = 'day21';
                        WHEN 21 THEN
                        SET TdayFlag = 'day22';
                        WHEN 22 THEN
                        SET TdayFlag = 'day23';
                        WHEN 23 THEN
                        SET TdayFlag = 'day24';
                        WHEN 24 THEN
                        SET TdayFlag = 'day25';
                        WHEN 25 THEN
                        SET TdayFlag = 'day26';
                        WHEN 26 THEN
                        SET TdayFlag = 'day27';
                        WHEN 27 THEN
                        SET TdayFlag = 'day28';
                        WHEN 28 THEN
                        SET TdayFlag = 'day29';
                        WHEN 29 THEN
                        SET TdayFlag = 'day30';
                        WHEN 30 THEN
                        SET TdayFlag = 'day31';
                        WHEN 31 THEN
                        SET TdayFlag = 'day32';
                        WHEN 32 THEN
                        SET TdayFlag = 'day33';
                        WHEN 33 THEN
                        SET TdayFlag = 'day34';
                        WHEN 34 THEN
                        SET TdayFlag = 'day35';
                        WHEN 35 THEN
                        SET TdayFlag = 'day36';
                        WHEN 36 THEN
                        SET TdayFlag = 'day37';
                        WHEN 37 THEN
                        SET TdayFlag = 'day38';
                        WHEN 38 THEN
                        SET TdayFlag = 'day39';
                        WHEN 39 THEN
                        SET TdayFlag = 'day40';
                        WHEN 40 THEN
                        SET TdayFlag = 'day41';
                        WHEN 41 THEN
                        SET TdayFlag = 'day42';
                        WHEN 42 THEN
                        SET TdayFlag = 'day43';
                        WHEN 43 THEN
                        SET TdayFlag = 'day44';
                        WHEN 44 THEN
                        SET TdayFlag = 'day45';
                        WHEN 45 THEN
                        SET TdayFlag = 'day46';
                        WHEN 46 THEN
                        SET TdayFlag = 'day47';
                        WHEN 47 THEN
                        SET TdayFlag = 'day48';
                        WHEN 48 THEN
                        SET TdayFlag = 'day49';
                        WHEN 49 THEN
                        SET TdayFlag = 'day50';
                        WHEN 50 THEN
                        SET TdayFlag = 'day51';
                        WHEN 51 THEN
                        SET TdayFlag = 'day52';
                        WHEN 52 THEN
                        SET TdayFlag = 'day53';
                        WHEN 53 THEN
                        SET TdayFlag = 'day54';
                        WHEN 54 THEN
                        SET TdayFlag = 'day55';
                        WHEN 55 THEN
                        SET TdayFlag = 'day56';
                        WHEN 56 THEN
                        SET TdayFlag = 'day57';
                        WHEN 57 THEN
                        SET TdayFlag = 'day58';
                        WHEN 58 THEN
                        SET TdayFlag = 'day59';
                        WHEN 59 THEN
                        SET TdayFlag = 'day60';
                    END CASE;
                ELSEIF Tregday < 90 THEN
                    SET TdayFlag = 'day90';
                ELSEIF Tregday < 120 THEN
                    SET TdayFlag = 'day120';
                ELSEIF Tregday < 180 THEN
                    SET TdayFlag = 'day180';
                ELSEIF Tregday < 270 THEN
                    SET TdayFlag = 'day270';
                ELSE
                    SET TdayFlag = 'day360';
                END IF;

#校验数据是否存在
                SELECT IFNULL((SELECT 'Y' from `dw_ltv_agent` where `date`=FROM_UNIXTIME(Tregtime,'%Y-%m-%d') AND `agent_id`=Tagentid limit 1),'N') INTO Tdata_exist;
                IF 'N' = Tdata_exist THEN
#不存在数据则创建
                    SET @sqlstr=CONCAT('INSERT INTO `dw_ltv_agent` (`date`,`agent_id`,`reg_cnt`,`sum_money`,',TdayFlag,') VALUES (FROM_UNIXTIME(',Tregtime,',"%Y-%m-%d"),',Tagentid,',1,',Tsummoney,',',Tsummoney,')');
                    PREPARE stmt FROM @sqlstr;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                ELSE
                    SET @sqlstr=CONCAT('update dw_ltv_agent SET ',TdayFlag,'=',TdayFlag,'+',Tsummoney,', sum_money = sum_money + ',Tsummoney,' WHERE date=FROM_UNIXTIME(',Tregtime,',"%Y-%m-%d") AND agent_id=',Tagentid);
                    PREPARE stmt FROM @sqlstr;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                END IF;
            END IF;
            FETCH Tacursor INTO Tregtime,Tagentid,Tregday,Tsummoney;
        END WHILE;
        CLOSE  Tacursor;


# 游戏LTV
        SET Tflag=0;
        OPEN Tgcursor;
        FETCH Tgcursor INTO Tregtime,Tappid,Tregday,Tsummoney;
        WHILE Tflag<>1 DO
            IF Tregday < 360 THEN
                IF Tregday < 60 THEN
                    CASE Tregday
                        WHEN 0 THEN
                        SET TdayFlag = 'day1';
                        WHEN 1 THEN
                        SET TdayFlag = 'day2';
                        WHEN 2 THEN
                        SET TdayFlag = 'day3';
                        WHEN 3 THEN
                        SET TdayFlag = 'day4';
                        WHEN 4 THEN
                        SET TdayFlag = 'day5';
                        WHEN 5 THEN
                        SET TdayFlag = 'day6';
                        WHEN 6 THEN
                        SET TdayFlag = 'day7';
                        WHEN 7 THEN
                        SET TdayFlag = 'day8';
                        WHEN 8 THEN
                        SET TdayFlag = 'day9';
                        WHEN 9 THEN
                        SET TdayFlag = 'day10';
                        WHEN 10 THEN
                        SET TdayFlag = 'day11';
                        WHEN 11 THEN
                        SET TdayFlag = 'day12';
                        WHEN 12 THEN
                        SET TdayFlag = 'day13';
                        WHEN 13 THEN
                        SET TdayFlag = 'day14';
                        WHEN 14 THEN
                        SET TdayFlag = 'day15';
                        WHEN 15 THEN
                        SET TdayFlag = 'day16';
                        WHEN 16 THEN
                        SET TdayFlag = 'day17';
                        WHEN 17 THEN
                        SET TdayFlag = 'day18';
                        WHEN 18 THEN
                        SET TdayFlag = 'day19';
                        WHEN 19 THEN
                        SET TdayFlag = 'day20';
                        WHEN 20 THEN
                        SET TdayFlag = 'day21';
                        WHEN 21 THEN
                        SET TdayFlag = 'day22';
                        WHEN 22 THEN
                        SET TdayFlag = 'day23';
                        WHEN 23 THEN
                        SET TdayFlag = 'day24';
                        WHEN 24 THEN
                        SET TdayFlag = 'day25';
                        WHEN 25 THEN
                        SET TdayFlag = 'day26';
                        WHEN 26 THEN
                        SET TdayFlag = 'day27';
                        WHEN 27 THEN
                        SET TdayFlag = 'day28';
                        WHEN 28 THEN
                        SET TdayFlag = 'day29';
                        WHEN 29 THEN
                        SET TdayFlag = 'day30';
                        WHEN 30 THEN
                        SET TdayFlag = 'day31';
                        WHEN 31 THEN
                        SET TdayFlag = 'day32';
                        WHEN 32 THEN
                        SET TdayFlag = 'day33';
                        WHEN 33 THEN
                        SET TdayFlag = 'day34';
                        WHEN 34 THEN
                        SET TdayFlag = 'day35';
                        WHEN 35 THEN
                        SET TdayFlag = 'day36';
                        WHEN 36 THEN
                        SET TdayFlag = 'day37';
                        WHEN 37 THEN
                        SET TdayFlag = 'day38';
                        WHEN 38 THEN
                        SET TdayFlag = 'day39';
                        WHEN 39 THEN
                        SET TdayFlag = 'day40';
                        WHEN 40 THEN
                        SET TdayFlag = 'day41';
                        WHEN 41 THEN
                        SET TdayFlag = 'day42';
                        WHEN 42 THEN
                        SET TdayFlag = 'day43';
                        WHEN 43 THEN
                        SET TdayFlag = 'day44';
                        WHEN 44 THEN
                        SET TdayFlag = 'day45';
                        WHEN 45 THEN
                        SET TdayFlag = 'day46';
                        WHEN 46 THEN
                        SET TdayFlag = 'day47';
                        WHEN 47 THEN
                        SET TdayFlag = 'day48';
                        WHEN 48 THEN
                        SET TdayFlag = 'day49';
                        WHEN 49 THEN
                        SET TdayFlag = 'day50';
                        WHEN 50 THEN
                        SET TdayFlag = 'day51';
                        WHEN 51 THEN
                        SET TdayFlag = 'day52';
                        WHEN 52 THEN
                        SET TdayFlag = 'day53';
                        WHEN 53 THEN
                        SET TdayFlag = 'day54';
                        WHEN 54 THEN
                        SET TdayFlag = 'day55';
                        WHEN 55 THEN
                        SET TdayFlag = 'day56';
                        WHEN 56 THEN
                        SET TdayFlag = 'day57';
                        WHEN 57 THEN
                        SET TdayFlag = 'day58';
                        WHEN 58 THEN
                        SET TdayFlag = 'day59';
                        WHEN 59 THEN
                        SET TdayFlag = 'day60';
                    END CASE;
                ELSEIF Tregday < 90 THEN
                    SET TdayFlag = 'day90';
                ELSEIF Tregday < 120 THEN
                    SET TdayFlag = 'day120';
                ELSEIF Tregday < 180 THEN
                    SET TdayFlag = 'day180';
                ELSEIF Tregday < 270 THEN
                    SET TdayFlag = 'day270';
                ELSE
                    SET TdayFlag = 'day360';
                END IF;

#校验数据是否存在
                SELECT IFNULL((SELECT 'Y' from `dw_ltv_game` where `date`=FROM_UNIXTIME(Tregtime,'%Y-%m-%d') AND `app_id`=Tappid limit 1),'N') INTO Tdata_exist;
                IF 'N' = Tdata_exist THEN
#不存在数据则创建
                    SET @sqlstr=CONCAT('INSERT INTO `dw_ltv_game` (`date`,`app_id`,`reg_cnt`,`sum_money`,',TdayFlag,') VALUES (FROM_UNIXTIME(',Tregtime,',"%Y-%m-%d"),',Tappid,',1,',Tsummoney,',',Tsummoney,')');
                    PREPARE stmt FROM @sqlstr;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                ELSE
                    SET @sqlstr=CONCAT('update dw_ltv_game SET ',TdayFlag,'=',TdayFlag,'+',Tsummoney,', sum_money = sum_money + ',Tsummoney,' WHERE date=FROM_UNIXTIME(',Tregtime,',"%Y-%m-%d") AND app_id=',Tappid);
                    PREPARE stmt FROM @sqlstr;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                END IF;
            END IF;
            FETCH Tgcursor INTO Tregtime,Tappid,Tregday,Tsummoney;
        END WHILE;
        CLOSE  Tgcursor;

# 每日LTV
        SET Tflag=0;
        OPEN Tcursor;
        FETCH Tcursor INTO Tregtime,Tregday,Tsummoney;
        WHILE Tflag<>1 DO
            IF Tregday < 360 THEN
                IF Tregday < 60 THEN
                    CASE Tregday
                        WHEN 0 THEN
                        SET TdayFlag = 'day1';
                        WHEN 1 THEN
                        SET TdayFlag = 'day2';
                        WHEN 2 THEN
                        SET TdayFlag = 'day3';
                        WHEN 3 THEN
                        SET TdayFlag = 'day4';
                        WHEN 4 THEN
                        SET TdayFlag = 'day5';
                        WHEN 5 THEN
                        SET TdayFlag = 'day6';
                        WHEN 6 THEN
                        SET TdayFlag = 'day7';
                        WHEN 7 THEN
                        SET TdayFlag = 'day8';
                        WHEN 8 THEN
                        SET TdayFlag = 'day9';
                        WHEN 9 THEN
                        SET TdayFlag = 'day10';
                        WHEN 10 THEN
                        SET TdayFlag = 'day11';
                        WHEN 11 THEN
                        SET TdayFlag = 'day12';
                        WHEN 12 THEN
                        SET TdayFlag = 'day13';
                        WHEN 13 THEN
                        SET TdayFlag = 'day14';
                        WHEN 14 THEN
                        SET TdayFlag = 'day15';
                        WHEN 15 THEN
                        SET TdayFlag = 'day16';
                        WHEN 16 THEN
                        SET TdayFlag = 'day17';
                        WHEN 17 THEN
                        SET TdayFlag = 'day18';
                        WHEN 18 THEN
                        SET TdayFlag = 'day19';
                        WHEN 19 THEN
                        SET TdayFlag = 'day20';
                        WHEN 20 THEN
                        SET TdayFlag = 'day21';
                        WHEN 21 THEN
                        SET TdayFlag = 'day22';
                        WHEN 22 THEN
                        SET TdayFlag = 'day23';
                        WHEN 23 THEN
                        SET TdayFlag = 'day24';
                        WHEN 24 THEN
                        SET TdayFlag = 'day25';
                        WHEN 25 THEN
                        SET TdayFlag = 'day26';
                        WHEN 26 THEN
                        SET TdayFlag = 'day27';
                        WHEN 27 THEN
                        SET TdayFlag = 'day28';
                        WHEN 28 THEN
                        SET TdayFlag = 'day29';
                        WHEN 29 THEN
                        SET TdayFlag = 'day30';
                        WHEN 30 THEN
                        SET TdayFlag = 'day31';
                        WHEN 31 THEN
                        SET TdayFlag = 'day32';
                        WHEN 32 THEN
                        SET TdayFlag = 'day33';
                        WHEN 33 THEN
                        SET TdayFlag = 'day34';
                        WHEN 34 THEN
                        SET TdayFlag = 'day35';
                        WHEN 35 THEN
                        SET TdayFlag = 'day36';
                        WHEN 36 THEN
                        SET TdayFlag = 'day37';
                        WHEN 37 THEN
                        SET TdayFlag = 'day38';
                        WHEN 38 THEN
                        SET TdayFlag = 'day39';
                        WHEN 39 THEN
                        SET TdayFlag = 'day40';
                        WHEN 40 THEN
                        SET TdayFlag = 'day41';
                        WHEN 41 THEN
                        SET TdayFlag = 'day42';
                        WHEN 42 THEN
                        SET TdayFlag = 'day43';
                        WHEN 43 THEN
                        SET TdayFlag = 'day44';
                        WHEN 44 THEN
                        SET TdayFlag = 'day45';
                        WHEN 45 THEN
                        SET TdayFlag = 'day46';
                        WHEN 46 THEN
                        SET TdayFlag = 'day47';
                        WHEN 47 THEN
                        SET TdayFlag = 'day48';
                        WHEN 48 THEN
                        SET TdayFlag = 'day49';
                        WHEN 49 THEN
                        SET TdayFlag = 'day50';
                        WHEN 50 THEN
                        SET TdayFlag = 'day51';
                        WHEN 51 THEN
                        SET TdayFlag = 'day52';
                        WHEN 52 THEN
                        SET TdayFlag = 'day53';
                        WHEN 53 THEN
                        SET TdayFlag = 'day54';
                        WHEN 54 THEN
                        SET TdayFlag = 'day55';
                        WHEN 55 THEN
                        SET TdayFlag = 'day56';
                        WHEN 56 THEN
                        SET TdayFlag = 'day57';
                        WHEN 57 THEN
                        SET TdayFlag = 'day58';
                        WHEN 58 THEN
                        SET TdayFlag = 'day59';
                        WHEN 59 THEN
                        SET TdayFlag = 'day60';
                    END CASE;
                ELSEIF Tregday < 90 THEN
                    SET TdayFlag = 'day90';
                ELSEIF Tregday < 120 THEN
                    SET TdayFlag = 'day120';
                ELSEIF Tregday < 180 THEN
                    SET TdayFlag = 'day180';
                ELSEIF Tregday < 270 THEN
                    SET TdayFlag = 'day270';
                ELSE
                    SET TdayFlag = 'day360';
                END IF;

#校验数据是否存在
                SELECT IFNULL((SELECT 'Y' from `dw_ltv_day` where `date`=FROM_UNIXTIME(Tregtime,'%Y-%m-%d') limit 1),'N') INTO Tdata_exist;
                IF 'N' = Tdata_exist THEN
#不存在数据则创建
                    SET @sqlstr=CONCAT('INSERT INTO `dw_ltv_day` (`date`,`reg_cnt`,`sum_money`,',TdayFlag,') VALUES (FROM_UNIXTIME(',Tregtime,',"%Y-%m-%d"),1,',Tsummoney,',',Tsummoney,')');
                    PREPARE stmt FROM @sqlstr;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                ELSE
                    SET @sqlstr=CONCAT('update dw_ltv_day SET ',TdayFlag,'=',TdayFlag,'+',Tsummoney,', sum_money = sum_money + ',Tsummoney,' WHERE date=FROM_UNIXTIME(',Tregtime,',"%Y-%m-%d")');
                    PREPARE stmt FROM @sqlstr;
                    EXECUTE stmt;
                    DEALLOCATE PREPARE stmt;
                END IF;
            END IF;
            FETCH Tcursor INTO Tregtime,Tregday,Tsummoney;
        END WHILE;
        CLOSE  Tcursor;

    END