CCNAIVE

20240118

2024/1/18

# 1 MySQL日历表 前后5年

-- 生成日历表
CREATE TABLE calendar (
    date_column DATE PRIMARY KEY,
    year INT,
    month INT,
    day INT,
    week INT
);
-- 填充日历表的数据
SET @start_date = CURDATE() - INTERVAL 5 YEAR;
SET @end_date = CURDATE() + INTERVAL 5 YEAR;

INSERT INTO calendar (date_column, year, month, day, week)
SELECT 
    d,
    YEAR(d),
    MONTH(d),
    DAY(d),
    WEEKDAY(d)
FROM (
    SELECT DATE_ADD(@start_date, INTERVAL a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) DAY) AS d
    FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d
) AS dates
WHERE d BETWEEN @start_date AND @end_date;

# 2 查询 COALESCE 为空时给0

SELECT 
    cal.date_column,
    SUM(COALESCE(emc.carbon_total, 0)) AS total_carbon
FROM 
    calendar AS cal
LEFT JOIN 
    lb_enterprise_employee_carbon AS emc 
ON 
    DATE(cal.date_column) = DATE(emc.create_date)
WHERE 
    emc.tenant_code = 12345 
    AND cal.date_column >= '2023-01-24' 
    AND cal.date_column <= '2023-12-31'
GROUP BY 
    cal.date_column
ORDER BY 
    cal.date_column;

# 查询的结果始终不连续

改了改这样可以。要把查询条件放on，不然全被where过滤了

on:在join的时候就只取emc的tenant_code = 12345

where：join之后的结果进行tenant_code = 12345 ，所以会被过滤掉

SELECT 
    cal.create_date,
    COALESCE(SUM(emc.carbon_total), 0) AS total_carbon
FROM 
    calendar AS cal
LEFT JOIN 
    lb_enterprise_employee_carbon AS emc 
ON 
    DATE(cal.create_date) = DATE(emc.create_date)
    AND emc.tenant_code = 12345 
WHERE 
    cal.create_date >= '2024-01-01' 
    AND cal.create_date <= '2024-01-31'
GROUP BY 
    cal.create_date
ORDER BY 
    cal.create_date;

# 3 Scheduled 定时任务cron 表达式

# 1.cron表达式格式：

{秒数} {分钟} {小时} {日期} {月份} {星期} {年份(可为空)}

# 2.cron表达式各占位符解释：

{秒数}{分钟} ==> 允许值范围: 0~59 ,不允许为空值，若值不合法，调度器将抛出SchedulerException异常
“*” 代表每隔1秒钟触发；
“,” 代表在指定的秒数触发，比如”0,15,45”代表0秒、15秒和45秒时触发任务
“-“代表在指定的范围内触发，比如”25-45”代表从25秒开始触发到45秒结束触发，每隔1秒触发1次
“/”代表触发步进(step)，”/”前面的值代表初始值(““等同”0”)，后面的值代表偏移量，比如”0/20”或者”/20”代表从0秒钟开始，每隔20秒钟触发1次，即0秒触发1次，20秒触发1次，40秒触发1次；”5/20”代表5秒触发1次，25秒触发1次，45秒触发1次；”10-45/20”代表在[10,45]内步进20秒命中的时间点触发，即10秒触发1次，30秒触发1次
{小时} ==> 允许值范围: 0~23 ,不允许为空值，若值不合法，调度器将抛出SchedulerException异常,占位符和秒数一样
{日期} ==> 允许值范围: 1~31 ,不允许为空值，若值不合法，调度器将抛出SchedulerException异常
{星期} ==> 允许值范围: 1~7 (SUN-SAT),1代表星期天(一星期的第一天)，以此类推，7代表星期六(一星期的最后一天)，不允许为空值，若值不合法，调度器将抛出SchedulerException异常
{年份} ==> 允许值范围: 1970~2099 ,允许为空，若值不合法，调度器将抛出SchedulerException异常

注意：除了{日期}和{星期}可以使用”?”来实现互斥，表达无意义的信息之外，其他占位符都要具有具体的时间含义，且依赖关系为：年->月->日期(星期)->小时->分钟->秒数

# 3.cron表达式的强大魅力在于灵活的横向和纵向组合以及简单的语法，用cron表达式几乎可以写出任何你想要触发的时间点与周期

经典案例：

“30 * * * * ?” 每半分钟触发任务
“30 10 * * * ?” 每小时的10分30秒触发任务
“30 10 1 * * ?” 每天1点10分30秒触发任务
“30 10 1 20 * ?” 每月20号1点10分30秒触发任务
“30 10 1 20 10 ? *” 每年10月20号1点10分30秒触发任务
“30 10 1 20 10 ? 2011” 2011年10月20号1点10分30秒触发任务
“30 10 1 ? 10 * 2011” 2011年10月每天1点10分30秒触发任务
“30 10 1 ? 10 SUN 2011” 2011年10月每周日1点10分30秒触发任务
“15,30,45 * * * * ?” 每15秒，30秒，45秒时触发任务
“15-45 * * * * ?” 15到45秒内，每秒都触发任务
“15/5 * * * * ?” 每分钟的每15秒开始触发，每隔5秒触发一次
“15-30/5 * * * * ?” 每分钟的15秒到30秒之间开始触发，每隔5秒触发一次
“0 0/3 * * * ?” 每小时的第0分0秒开始，每三分钟触发一次
“0 15 10 ? * MON-FRI” 星期一到星期五的10点15分0秒触发任务
“0 15 10 L * ?” 每个月最后一天的10点15分0秒触发任务
“0 15 10 LW * ?” 每个月最后一个工作日的10点15分0秒触发任务
“0 15 10 ? * 5L” 每个月最后一个星期四的10点15分0秒触发任务
“0 15 10 ? * 5#3” 每个月第三周的星期四的10点15分0秒触发任务

20240119