mysql - MySQL 事件未按计划运行
问题描述
MySQL 事件未按计划运行,但在事件外成功执行插入语句。如果有人可以提供任何想法,我将不胜感激。代码如下:
CREATE DEFINER=```christupw```@```%``` EVENT `ProcessAnalyticsEvent` ON SCHEDULE EVERY 1 DAY STARTS '2020-05-20 13:59:59' ON COMPLETION PRESERVE ENABLE
DO
INSERT INTO process_analytics (action_date, list_id, li_connections_sent, li_connections_accepted, li_replies, li_messages_sent, liconnection, lireply, em_sent, em_opened, em_clicks, em_replies, em_unsubs, emopen, emclick, emreply, emunsub)
SELECT
date(Now()) AS action_date,
T.list_id AS list_id,
T.actid_1_status_1 AS li_connections_sent,
T.actid_2_status_1 AS li_connections_accepted,
T.actid_4_status_1 AS li_replies,
T.actid_3_status_1 AS li_messages_sent,
CAST(IF(T.actid_1_status_1 = 0, 0, T.actid_2_status_1 / T.actid_1_status_1) AS DECIMAL(10,4)) AS liconnection,
CAST(IF(T.actid_1_status_1 = 0, 0, T.actid_4_status_1 / T.actid_1_status_1) AS DECIMAL(10,4)) AS lireply,
T.actid_70_stage_2 AS em_sent,
T.actid_70_stage_3 AS em_opened,
T.actid_70_stage_4 AS em_clicks,
T.actid_70_stage_5 AS em_replies,
T.actid_70_stage_6 AS em_unsubs,
CAST(IF(T.actid_70_stage_2 = 0, 0, T.actid_70_stage_3 / T.actid_70_stage_2) AS DECIMAL(10,4)) AS emopen,
CAST(IF(T.actid_70_stage_3 = 0, 0, T.actid_70_stage_4 / T.actid_70_stage_3) AS DECIMAL(10,4)) AS emclick,
CAST(IF(T.actid_70_stage_2 = 0, 0, T.actid_70_stage_5 / T.actid_70_stage_2) AS DECIMAL(10,4)) AS emreply,
CAST(IF(T.actid_70_stage_2 = 0, 0, T.actid_70_stage_6 / T.actid_70_stage_2) AS DECIMAL(10,4)) AS emunsub
FROM(
SELECT pal.list_id,
SUM(CASE WHEN ( pal.activity_id = 1 AND pal.activity_status = 1 ) THEN 1 ELSE 0 END) AS actid_1_status_1,
SUM(CASE WHEN ( pal.activity_id = 2 AND pal.activity_status = 1 ) THEN 1 ELSE 0 END) AS actid_2_status_1,
SUM(CASE WHEN ( pal.activity_id = 3 AND pal.activity_status = 1 ) THEN 1 ELSE 0 END) AS actid_3_status_1,
SUM(CASE WHEN ( pal.activity_id = 4 AND pal.activity_status = 1 ) THEN 1 ELSE 0 END) AS actid_4_status_1,
SUM(CASE WHEN ( pal.activity_id = 70 AND pal.stage_id = 2 ) THEN 1 ELSE 0 END) AS actid_70_stage_2,
SUM(CASE WHEN ( pal.activity_id = 70 AND pal.stage_id = 3 ) THEN 1 ELSE 0 END) AS actid_70_stage_3,
SUM(CASE WHEN ( pal.activity_id = 70 AND pal.stage_id = 4 ) THEN 1 ELSE 0 END) AS actid_70_stage_4,
SUM(CASE WHEN ( pal.activity_id = 70 AND pal.stage_id = 5 ) THEN 1 ELSE 0 END) AS actid_70_stage_5,
SUM(CASE WHEN ( pal.activity_id = 70 AND pal.stage_id = 6 ) THEN 1 ELSE 0 END) AS actid_70_stage_6
FROM process_activity_log pal
LEFT JOIN processchart pc ON pal.process_id = pc.processid
LEFT JOIN prospect_lists pl ON pal.list_id = pl.id
WHERE pc.is_active=1 AND pl.is_active=1 AND date(pal.created_at)<=date(now())
GROUP BY pal.list_id) T
解决方案
推荐阅读
- google-sheets - 如何使用 4 个标准进行索引和匹配
- go - 在 Kubernetes 源代码中获取 Pod 的当前节点
- html - 如何在表格中居中特定项目?
- c# - 处理控制器动作中解析可空参数引起的异常
- javascript - 浏览器不会呈现动态分配给 dataTable 单元格的 Javascript 变量中的 HTML 锚点
- python - Python,我想在导入csv文件后转换特定的列类型
- ios - 无法在 react-native 中清除 TextInput 的值
- html - 自动调整大小的 textarea 元素底部出现额外的空白
- javascript - 有没有办法让所有的 Facebook 广告?
- javascript - 怎么修?由于值错误,Discord.js Bot 一直离线