推送记录日志查询方法

1.问题背景

用户侧需要统计推送的历史记录信息，包含报告名称、推送状态、创建时间、推送对象、推送频率等

2.SQL示例

SELECT TIMESTAMP(DATE_FORMAT(`t1`.`create_time`, '%Y-%m-%d %H:%i:%s')) AS `d0`,
  `t2`.`title` AS `d1`,
  `t5`.`title` AS `d2`,
  `t3`.`unique_id` AS `d3`,
  TIMESTAMP(DATE_FORMAT(`t2`.`create_time`, '%Y-%m-%d %H:%i:%s')) AS `d4`,
  (CASE WHEN (`t2`.`frequency` = 4) THEN '小时' WHEN (`t2`.`frequency` = 3) THEN '月' WHEN (`t2`.`frequency` = 2) THEN '周' WHEN (`t2`.`frequency` = 1) THEN '天' ELSE CAST(`t2`.`frequency` AS CHAR(255)) END) AS `d5`,
  (CASE WHEN (`t2`.`category` = '1') THEN '立即推送' WHEN (`t2`.`category` = '0') THEN '定时推送' ELSE CAST(`t2`.`category` AS CHAR(255)) END) AS `d6`,
  `t2`.`receiver_list` AS `d7`,
  `t4`.`name` AS `d8`,
  (CASE WHEN (`t1`.`status` = 1) THEN '成功' WHEN (`t1`.`status` = 0) THEN '失败' ELSE CAST(`t1`.`status` AS CHAR(255)) END) AS `d9`
FROM `youdata`.`dashboard_mail_record` AS `t1`
LEFT JOIN `youdata`.`dashboard_mail` AS `t2` ON (`t1`.`dashboard_mail_id` = `t2`.`id`)
LEFT JOIN `youdata`.`bigviz_user` AS `t3` ON (`t2`.`creator_id` = `t3`.`id`)
LEFT JOIN `youdata`.`project` AS `t4` ON (`t2`.`project_id` = `t4`.`id`)
LEFT JOIN `youdata`.`new_report` AS `t5` ON (`t2`.`resource_id` = `t5`.`id`)
GROUP BY TIMESTAMP(DATE_FORMAT(`t1`.`create_time`, '%Y-%m-%d %H:%i:%s')),
  `t2`.`title`, BINARY `t2`.`title`,
  `t5`.`title`, BINARY `t5`.`title`,
  `t3`.`unique_id`, BINARY `t3`.`unique_id`,
  TIMESTAMP(DATE_FORMAT(`t2`.`create_time`, '%Y-%m-%d %H:%i:%s')),
  (CASE WHEN (`t2`.`frequency` = 4) THEN '小时' WHEN (`t2`.`frequency` = 3) THEN '月' WHEN (`t2`.`frequency` = 2) THEN '周' WHEN (`t2`.`frequency` = 1) THEN '天' ELSE CAST(`t2`.`frequency` AS CHAR(255)) END), BINARY (CASE WHEN (`t2`.`frequency` = 4) THEN '小时' WHEN (`t2`.`frequency` = 3) THEN '月' WHEN (`t2`.`frequency` = 2) THEN '周' WHEN (`t2`.`frequency` = 1) THEN '天' ELSE CAST(`t2`.`frequency` AS CHAR(255)) END),
  (CASE WHEN (`t2`.`category` = '1') THEN '立即推送' WHEN (`t2`.`category` = '0') THEN '定时推送' ELSE CAST(`t2`.`category` AS CHAR(255)) END), BINARY (CASE WHEN (`t2`.`category` = '1') THEN '立即推送' WHEN (`t2`.`category` = '0') THEN '定时推送' ELSE CAST(`t2`.`category` AS CHAR(255)) END),
  `t2`.`receiver_list`, BINARY `t2`.`receiver_list`,
  `t4`.`name`, BINARY `t4`.`name`,
  (CASE WHEN (`t1`.`status` = 1) THEN '成功' WHEN (`t1`.`status` = 0) THEN '失败' ELSE CAST(`t1`.`status` AS CHAR(255)) END), BINARY (CASE WHEN (`t1`.`status` = 1) THEN '成功' WHEN (`t1`.`status` = 0) THEN '失败' ELSE CAST(`t1`.`status` AS CHAR(255)) END)
ORDER BY (TIMESTAMP(DATE_FORMAT(`t1`.`create_time`, '%Y-%m-%d %H:%i:%s')) IS NULL) ASC, TIMESTAMP(DATE_FORMAT(`t1`.`create_time`, '%Y-%m-%d %H:%i:%s')) DESC
LIMIT 20