问优化存储过程

EN

CREATE DEFINER=`xxx`@`%` PROCEDURE `testing`(IN _MONTH INT, IN _YEAR INT, IN _PRODUCTS TEXT)
BEGIN

set @_START = date(_YEAR * 10000 + _MONTH * 100 + 1); 
set @_END = date_add(date(_YEAR * 10000 + _MONTH * 100 + 1), interval 1 month);

DROP TABLE IF EXISTS ReportThreeTotal;
DROP TABLE IF EXISTS ReportThreeAbandondCalls;
DROP TABLE IF EXISTS ReportThreeSpillGain;
DROP TABLE IF EXISTS ReportThreeStamp;
DROP TABLE IF EXISTS ReportThreeResults;

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeTotal AS (
    SELECT count(*) as 'count', DAYOFWEEK(`end`) as 'DOW' 
    FROM callstats.cdrdata_archive 
    where end >= @_START and end < @_END
    AND calling IN (
        SELECT Number FROM callstats.products WHERE products.id IN (_PRODUCTS) )
    GROUP BY DAYOFWEEK(`end`)
);

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeAbandondCalls AS (
    SELECT count(*) as 'count', DAYOFWEEK(`end`) as 'DOW'
    FROM callstats.cdrdata_archive 
    WHERE calling IN (
        SELECT Number 
        FROM callstats.products 
        WHERE products.id IN (_PRODUCTS) ) 
    AND end >= @_START and end < @_END
    AND CAST(`duration` AS UNSIGNED) < 30 
    GROUP BY DAYOFWEEK(`end`)
);

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeSpillGain AS (
SELECT (case when (SUM(count) is null) then 0 else SUM(count) end) as 'SPILLGAIN', DAYOFWEEK(`end`) as 'DOW'
FROM (SELECT count(*) as 'count', called, end
    FROM (
        SELECT called, calling, count(*) as 'count', end
        FROM cdrdata_archive 
        JOIN products ON Number = calling 
        WHERE end >= @_START AND end < @_END
        AND products.id IN (_PRODUCTS) 
        AND called != 'Anonymous' 
        GROUP BY called, calling, DAYOFWEEK(`end`) 
        ORDER BY `end` ASC ) 
    AS tbl1 
     GROUP BY called having count(*) > 1) 
 AS tbl2
 GROUP BY DAYOFWEEK(`end`));

CREATE TEMPORARY TABLE ReportThreeStamp(stamp text, DOW INT);
    SET @Counter = 1;
    while @Counter <= 7 do
        INSERT INTO ReportThreeStamp VALUES(TIMESTAMP_FROM_DATE(_YEAR * 10000 + _MONTH * 100 + @Counter), @Counter);
        SET @Counter=@Counter+1;
    end while;

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeResults AS (
SELECT CONCAT('[',S.`stamp`,',', (
    case when (A.`count` = 0) 
         then 0 
         else (A.`count` / T.`count`) * 100 end),']') AS 'JSON_PERCENT',
    CONCAT('[',S.`stamp`,',',T.`count`,']') AS 'JSON_TOTAL',
    A.`DOW` as 'DOTW',
 A.`COUNT` as 'ATOTAL', T.`COUNT` as 'TOTAL',
SG.SPILLGAIN as 'GAINPER', SG.SPILLGAIN as 'SPILLPER'
FROM ReportThreeAbandondCalls A LEFT JOIN ReportThreeTotal T ON A.`DOW` = T.`DOW` LEFT JOIN ReportThreeSpillGain SG ON SG.`DOW` = A.`DOW` 
LEFT JOIN ReportThreeStamp S ON A.`DOW` = S.`DOW`);

SELECT * FROM ReportThreeResults;

DROP TABLE IF EXISTS ReportThreeTotal;
DROP TABLE IF EXISTS ReportThreeAbandondCalls;
DROP TABLE IF EXISTS ReportThreeSpillGain;
DROP TABLE IF EXISTS ReportThreeStamp;
DROP TABLE IF EXISTS ReportThreeResults;

END