CREATE EVENT update_sell_data
ON SCHEDULE EVERY 1 DAY
STARTS '2023-08-24 18:00:00'
COMMENT '1일 1회 판매량 데이터 최신화'
DO BEGIN
    UPDATE TRIM AS tm
    JOIN (
    SELECT tm.trim_id, ROUND(COUNT(sd.trim_id) / (SELECT COUNT(*) FROM SALES_DATA) * 100) AS percent
    FROM SALES_DATA AS sd
    RIGHT JOIN TRIM AS tm ON sd.trim_id = tm.trim_id
    GROUP BY tm.trim_id
    ) AS subquery
ON tm.trim_id = subquery.trim_id
SET tm.purchase_rate = subquery.percent;

UPDATE BODY_TYPE AS bt
    JOIN (
    SELECT bt.body_type_id, ROUND(COUNT(sd.body_type_id) / (SELECT COUNT(*) FROM SALES_DATA) * 100) AS percent
    FROM SALES_DATA AS sd
    RIGHT JOIN BODY_TYPE AS bt ON sd.body_type_id = bt.body_type_id
    GROUP BY bt.body_type_id
    ) AS subquery
ON bt.body_type_id = subquery.body_type_id
SET bt.purchase_rate = subquery.percent;

UPDATE ENGINE AS EG
    JOIN (
    SELECT EG.engine_id, ROUND(COUNT(sd.engine_id) / (SELECT COUNT(*) FROM SALES_DATA) * 100) AS percent
    FROM SALES_DATA AS sd
    RIGHT JOIN ENGINE AS EG ON sd.engine_id = EG.engine_id
    GROUP BY EG.engine_id
    ) AS subquery
ON EG.engine_id = subquery.engine_id
SET EG.purchase_rate = subquery.percent;

UPDATE DRIVING_METHOD AS DM
    JOIN (
    SELECT DM.driving_method_id, ROUND(COUNT(sd.driving_method_id) / (SELECT COUNT(*) FROM SALES_DATA) * 100) AS percent
    FROM SALES_DATA AS sd
    RIGHT JOIN DRIVING_METHOD AS DM ON sd.driving_method_id = DM.driving_method_id
    GROUP BY DM.driving_method_id
    ) AS subquery
ON DM.driving_method_id = subquery.driving_method_id
SET DM.purchase_rate = subquery.percent;

UPDATE EXTERIOR_COLOR AS EC
    JOIN (
    SELECT EC.exterior_color_id, ROUND(COUNT(sd.exterior_color_id) / (SELECT COUNT(*) FROM SALES_DATA) * 100) AS percent
    FROM SALES_DATA AS sd
    RIGHT JOIN EXTERIOR_COLOR AS EC ON sd.exterior_color_id = EC.exterior_color_id
    GROUP BY EC.exterior_color_id
    ) AS subquery
ON EC.exterior_color_id = subquery.exterior_color_id
SET EC.purchase_rate = subquery.percent;

UPDATE INTERIOR_COLOR AS IC
    JOIN (
    SELECT IC.color, ROUND(COUNT(sd.interior_color_id) / trim_count.count * 100) AS percent
    FROM SALES_DATA AS sd
    RIGHT JOIN INTERIOR_COLOR AS IC ON sd.interior_color_id = IC.interior_color_id
    INNER JOIN (
    SELECT trim_id, COUNT(*) as count
    FROM SALES_DATA
    GROUP BY trim_id
    ) AS trim_count
    ON sd.trim_id = trim_count.trim_id
    GROUP BY sd.trim_id, IC.color
    ) AS subquery
ON IC.color = subquery.color
SET IC.purchase_rate = subquery.percent;

UPDATE `OPTION` AS OT
    JOIN (
    SELECT OT.option_id, ROUND(COUNT(SDO.option_id) / (SELECT COUNT(*) FROM SALES_DATA) * 100) AS percent
    FROM SALES_DATA_OPTION AS SDO
    RIGHT JOIN `OPTION` AS OT ON SDO.option_id = OT.option_id
    GROUP BY OT.option_id
    ) AS subquery
ON OT.option_id = subquery.option_id
SET OT.purchase_rate = subquery.percent;
END;