MariaDB [Playback_Stats]> show create procedure Playback_Stats.caltex_stats_calculate_action\G
*************************** 1. row ***************************
           Procedure: caltex_stats_calculate_action
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`lax`@`192.168.50.128` PROCEDURE `caltex_stats_calculate_action`(
IN p_period_id CHAR(3),
IN p_action_type_id CHAR(3),
IN p_start_date DATETIME,
IN p_end_date DATETIME
)
BEGIN

DECLARE v_period_id CHAR(3);
DECLARE v_action_type_id CHAR(3);
DECLARE v_start_date DATETIME;
DECLARE v_end_date DATETIME;
DECLARE v_has_error BOOLEAN DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
   SET v_has_error = 1;
   SHOW errors;
   DROP TEMPORARY TABLE IF EXISTS Stats_Content_Temp;
   DROP TEMPORARY TABLE IF EXISTS Stats_Action_Temp;
   ROLLBACK;
END;

DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
   if NOT v_has_error THEN
    SHOW WARNINGS;
   DROP TEMPORARY TABLE IF EXISTS Stats_Content_Temp;
   DROP TEMPORARY TABLE IF EXISTS Stats_Action_Temp;
    ROLLBACK;
   END IF;
END;


-- Sanity check calculate start date and end date
SELECT p_period_id, p_action_type_id, p_start_date, p_end_date INTO v_period_id , v_action_type_id , v_start_date , v_end_date FROM DUAL;


-- Temporary Lookup Tables: Memory Engine
DROP TEMPORARY TABLE IF EXISTS Stats_Content_Temp;
DROP TEMPORARY TABLE IF EXISTS Stats_Action_Temp;

-- Sales_Order_Types - Order Types that describe a sale
CREATE TEMPORARY TABLE IF NOT EXISTS Stats_Content_Temp
(
    period_id         CHAR(3)    NOT NULL,
    action_type_id    CHAR(3)    NOT NULL,
    content_id        INT UNSIGNED  NOT NULL,
    stats_count       INT UNSIGNED  DEFAULT 0,
    average_playback_duration SMALLINT UNSIGNED,
    PRIMARY KEY (period_id, action_type_id, content_id)
)
engine=memory;

CREATE TEMPORARY TABLE IF NOT EXISTS Stats_Action_Temp
(
    period_id         CHAR(3)    NOT NULL,
    action_type_id    CHAR(3)    NOT NULL,
    device_type_id    CHAR(3)    NOT NULL,
    stats_count       INT UNSIGNED  DEFAULT 0,
    average_playback_duration SMALLINT UNSIGNED,
    PRIMARY KEY (period_id, action_type_id, device_type_id)
)
engine=memory;

-- Calculate Stats -
INSERT INTO Stats_Content_Temp
SELECT v_period_id as period_id,
   v_action_type_id as action_type_id,
   PS.content_id as content_id,
   sum(PS.stat_count) as stats_count,
   avg(PS.playback_duration) as average_playback_duration
   FROM Playback_Stats PS
   WHERE PS.action_type_id=v_action_type_id AND PS.stat_timestamp BETWEEN v_start_date AND v_end_date
   GROUP BY PS.content_id
;

INSERT INTO Stats_Action_Temp
SELECT v_period_id as period_id,
   v_action_type_id as action_type_id,
   PS.device_type_id as device_type_id,
   count(*) as stats_count,
   avg(PS.playback_duration) as average_playback_duration
   FROM Playback_Stats_Raw PS
   WHERE PS.action_type_id=v_action_type_id AND PS.stat_timestamp BETWEEN v_start_date AND v_end_date
   GROUP BY PS.device_type_id
;

START TRANSACTION;

-- DELETE EXISTING DATA
DELETE FROM Content_Stats
WHERE
    period_id = v_period_id
    AND action_type_id = v_action_type_id;

DELETE FROM Album_Stats
WHERE
    period_id = v_period_id
    AND action_type_id = v_action_type_id;

DELETE FROM Artist_Stats
WHERE
    period_id = v_period_id
    AND action_type_id = v_action_type_id;

DELETE FROM Action_Stats
WHERE
    period_id = v_period_id
    AND action_type_id = v_action_type_id;

-- Actual Query
INSERT INTO Content_Stats
(
    period_id ,
    action_type_id ,
    content_id ,
    stats_count,
    average_playback_duration
)
SELECT
    v_period_id as period_id,
    action_type_id ,
    content_id ,
    stats_count,
    average_playback_duration
  FROM
Stats_Content_Temp;

INSERT INTO Album_Stats
(
    period_id ,
    action_type_id ,
    album_id ,
    stats_count
)
SELECT
    v_period_id as period_id,
    ST.action_type_id ,
    AC.album_id ,
    ST.stats_count
  FROM
Stats_Content_Temp ST
JOIN caltex_cms.album_contents AC ON AC.content_id=ST.content_id
ON DUPLICATE KEY UPDATE
    stats_count = Album_Stats.stats_count + ST.stats_count
;

INSERT INTO Artist_Stats
(
    period_id ,
    action_type_id ,
    artist_id ,
    stats_count
)
SELECT
    v_period_id as period_id,
    ST.action_type_id ,
    AC.artist_id ,
    ST.stats_count
  FROM
Stats_Content_Temp ST
JOIN caltex_cms.content_artist AC ON AC.content_id=ST.content_id
ON DUPLICATE KEY UPDATE
    stats_count = Artist_Stats.stats_count + ST.stats_count
;

INSERT INTO Action_Stats
(
    period_id ,
    action_type_id ,
    device_type_id ,
    stats_count,
    average_playback_duration
)
SELECT
    v_period_id as period_id,
    action_type_id ,
    device_type_id ,
    stats_count,
    average_playback_duration
  FROM
Stats_Action_Temp;

COMMIT;

-- Housekeeping
DROP TEMPORARY TABLE IF EXISTS Stats_Content_Temp;
DROP TEMPORARY TABLE IF EXISTS Stats_Action_Temp;

END
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_unicode_ci
  