存储过程

CREATE PROCEDURE tongji ()
BEGIN

DECLARE done BOOLEAN DEFAULT 0;


DECLARE login_day_data INT (11) DEFAULT 0;


DECLARE date_ VARCHAR (255) DEFAULT NULL;


DECLARE zid_ INT (9) DEFAULT 0;


DECLARE temp_tab VARCHAR (255);


DECLARE pvp_tabs CURSOR FOR SELECT
    table_name
FROM
    information_schema. TABLES
WHERE
    table_schema = 'oss-backup'
AND table_name LIKE '%_tab_map_pvp_2018%';


DECLARE login_tabs CURSOR FOR SELECT
    table_name
FROM
    information_schema. TABLES
WHERE
    table_schema = 'oss-backup'
AND table_name LIKE '%_tab_login_2018%';


DECLARE login_data CURSOR FOR SELECT
    loginDayData,
    date,
    zid
FROM
    temp_login;


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

CREATE TABLE
IF NOT EXISTS `temp_tongji` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `loginDayData` INT (11) NOT NULL DEFAULT 0,
    `pvpDayData` INT (11) NOT NULL DEFAULT 0,
    `date` VARCHAR (255) NOT NULL,
    `zid` INT (11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 统计pvp日活
OPEN pvp_tabs;


REPEAT
    FETCH pvp_tabs INTO temp_tab;


IF done != 1 THEN

SET @sqlStr := CONCAT(
    "insert into temp_tongji (pvpDayData,date,zid) SELECT count(DISTINCT(t.user_id)),DATE_FORMAT(t.create_time,'%Y%m%d'),zid from ",
    temp_tab,
    " t"
);

PREPARE stmt
FROM
    @sqlStr;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;


END
IF;

UNTIL done
END
REPEAT
;

CLOSE pvp_tabs;


SET done = 0;

-- 统计 login
CREATE TABLE
IF NOT EXISTS `temp_login` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `loginDayData` INT (11) NOT NULL DEFAULT 0,
    `date` VARCHAR (255) NOT NULL,
    `zid` INT (11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

OPEN login_tabs;


REPEAT
    FETCH login_tabs INTO temp_tab;


IF done != 1 THEN

SET @sqlStr := CONCAT(
    "insert into temp_login (loginDayData,date,zid) SELECT
    count(*),
    a.tdate,
    a.zid
FROM
    (
        SELECT
            DATE_FORMAT(t.create_time, '%Y%m%d') tdate,
            t.user_id,
            t.zid,
            count(*)
        FROM
              ",
    temp_tab,
    " t
        GROUP BY
            tdate,
            t.user_id
    ) a
GROUP BY
    a.tdate 
"
);

PREPARE stmt
FROM
    @sqlStr;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;


END
IF;

UNTIL done
END
REPEAT
;

CLOSE login_tabs;


SET done = 0;

-- 合并数据
OPEN login_data;


REPEAT
    FETCH login_data INTO login_day_data,
    date_,
    zid_;


IF done != 1 THEN
    UPDATE temp_tongji
SET loginDayData = login_day_data
WHERE
    date = date_
AND zid = zid_;


END
IF;

UNTIL done
END
REPEAT
;

CLOSE login_data;


END;

-- 以上为存储过程
DROP PROCEDURE
IF EXISTS tongji;

CALL tongji ();