sum(d.printed_number) over( partition by substring(c.month, 1, 4), c.model_code order by c.month rows between unbounded preceding and current row ) as pn_YTM

这是一个 SQL 查询语句，其中使用了窗口函数 sum() over，对 printed_number 列进行求和。窗口中使用了 partition by 子句对数据进行分区按照指定的排序对数据进行排序。rows between 子句指定了窗口函数计算的范围，这里是从当前行往前计算到分区的第一行。最后，as 子句为本次查询结果命名为 pn_YTM。

相关问题

优化一下CREATE TRIGGER update_invoice_complex AFTER UPDATE ON t_sfi_inter_invoice FOR EACH ROW BEGIN INSERT INTO t_sfi_inter_bus_complex (OPER_TIME, OPERATION_TYPE, OLD_OBJECT,NEW_OBJECT) VALUES (NOW(), 'update',JSON_OBJECT(OLD.ID,OLD.INVOICE_NO,OLD.SUM,OLD.FACT_SUM,OLD.STATUS,OLD.BUSINESS_ID,OLD.REINVOICE,OLD.IS_PRINTED,OLD.PRINT_USER_ID,OLD.PRINT_USER_NAME,OLD.PRINT_DATE,OLD.INVOICE_TYPE,OLD.INVOICE_STATUS,OLD.TOMB_CERTIFICATE),JSON_OBJECT(NEW.ID,NEW.INVOICE_NO,NEW.SUM,NEW.FACT_SUM,NEW.STATUS,NEW.BUSINESS_ID,NEW.REINVOICE,NEW.IS_PRINTED,NEW.PRINT_USER_ID,NEW.PRINT_USER_NAME,NEW.PRINT_DATE,NEW.INVOICE_TYPE,NEW.INVOICE_STATUS,NEW.TOMB_CERTIFICATE)); END;

可以针对该触发器做如下优化：

1. 由于该触发器在每次更新t_sfi_inter_invoice表的一行数据时都会被触发，因此为了提高效率，可以将该触发器改为AFTER UPDATE ON t_sfi_inter_invoice FOR EACH STATEMENT，这样该触发器只会在更新语句执行完毕后被触发一次，而不是每更新一行数据就被触发一次。

2. 可以将JSON_OBJECT函数的调用次数减少为一次，即先将OLD和NEW两个对象分别转换为JSON格式的字符串，再将这两个字符串作为参数调用一次JSON_OBJECT函数，这样可以减少函数的调用次数，提高效率。

优化后的触发器代码如下所示：

CREATE TRIGGER update_invoice_complex AFTER UPDATE ON t_sfi_inter_invoice FOR EACH STATEMENT
BEGIN
DECLARE oldStr, newStr VARCHAR(10000);
SET oldStr = JSON_OBJECT('ID',OLD.ID,'INVOICE_NO',OLD.INVOICE_NO,'SUM',OLD.SUM,'FACT_SUM',OLD.FACT_SUM,'STATUS',OLD.STATUS,'BUSINESS_ID',OLD.BUSINESS_ID,'REINVOICE',OLD.REINVOICE,'IS_PRINTED',OLD.IS_PRINTED,'PRINT_USER_ID',OLD.PRINT_USER_ID,'PRINT_USER_NAME',OLD.PRINT_USER_NAME,'PRINT_DATE',OLD.PRINT_DATE,'INVOICE_TYPE',OLD.INVOICE_TYPE,'INVOICE_STATUS',OLD.INVOICE_STATUS,'TOMB_CERTIFICATE',OLD.TOMB_CERTIFICATE);
SET newStr = JSON_OBJECT('ID',NEW.ID,'INVOICE_NO',NEW.INVOICE_NO,'SUM',NEW.SUM,'FACT_SUM',NEW.FACT_SUM,'STATUS',NEW.STATUS,'BUSINESS_ID',NEW.BUSINESS_ID,'REINVOICE',NEW.REINVOICE,'IS_PRINTED',NEW.IS_PRINTED,'PRINT_USER_ID',NEW.PRINT_USER_ID,'PRINT_USER_NAME',NEW.PRINT_USER_NAME,'PRINT_DATE',NEW.PRINT_DATE,'INVOICE_TYPE',NEW.INVOICE_TYPE,'INVOICE_STATUS',NEW.INVOICE_STATUS,'TOMB_CERTIFICATE',NEW.TOMB_CERTIFICATE);
INSERT INTO t_sfi_inter_bus_complex (OPER_TIME, OPERATION_TYPE, OLD_OBJECT, NEW_OBJECT) VALUES (NOW(), 'update', oldStr, newStr);
END;

CREATE TRIGGER update_invoice_complex AFTER UPDATE ON t_sfi_inter_invoice FOR EACH ROW BEGIN INSERT INTO t_sfi_inter_bus_complex (OPER_TIME, OPERATION_TYPE, OLD_OBJECT,NEW_OBJECT) VALUES (NOW(), 'update',JSON_OBJECT(OLD.ID,OLD_OINVOICE_NO,OLD.SUM,OLD.FACT_SUM,OLD.STATUS,OLD.BUSINESS_ID,OLD.REINVOICE,OLD.IS_PRINTED,OLD.PRINT_USER_ID,OLD.PRINT_USER_NAME,OLD.PRINT_DATE,OLD.INVOICE_TYPE,OLD.INVOICE_STATUS,OLD.TOMB_CERTIFICATE),JSON_OBJECT(NEW.ID,OINVOICE_NO,NEW.SUM,NEW.FACT_SUM,NEW.STATUS,NEW.BUSINESS_ID,NEW.REINVOICE,NEW.IS_PRINTED,NEW.PRINT_USER_ID,NEW.PRINT_USER_NAME,NEW.PRINT_DATE,NEW.INVOICE_TYPE,NEW.INVOICE_STATUS,NEW.TOMB_CERTIFICATE)); END;优化一下

可以尝试将触发器中的JSON_OBJECT函数拆分成单独的变量，以提高可读性和易维护性。同时，将INSERT语句中的字段名改为明确的列名，而不是使用通配符。建议修改后的触发器代码如下：

CREATE TRIGGER update_invoice_complex 
AFTER UPDATE ON t_sfi_inter_invoice 
FOR EACH ROW 
BEGIN 
    DECLARE old_invoice_json JSON; 
    DECLARE new_invoice_json JSON; 

    SET old_invoice_json = JSON_OBJECT('id', OLD.ID, 
                                       'oinvoice_no', OLD.OINVOICE_NO, 
                                       'sum', OLD.SUM, 
                                       'fact_sum', OLD.FACT_SUM, 
                                       'status', OLD.STATUS, 
                                       'business_id', OLD.BUSINESS_ID, 
                                       'reinvoice', OLD.REINVOICE, 
                                       'is_printed', OLD.IS_PRINTED, 
                                       'print_user_id', OLD.PRINT_USER_ID, 
                                       'print_user_name', OLD.PRINT_USER_NAME, 
                                       'print_date', OLD.PRINT_DATE, 
                                       'invoice_type', OLD.INVOICE_TYPE, 
                                       'invoice_status', OLD.INVOICE_STATUS, 
                                       'tomb_certificate', OLD.TOMB_CERTIFICATE); 

    SET new_invoice_json = JSON_OBJECT('id', NEW.ID, 
                                       'oinvoice_no', NEW.OINVOICE_NO, 
                                       'sum', NEW.SUM, 
                                       'fact_sum', NEW.FACT_SUM, 
                                       'status', NEW.STATUS, 
                                       'business_id', NEW.BUSINESS_ID, 
                                       'reinvoice', NEW.REINVOICE, 
                                       'is_printed', NEW.IS_PRINTED, 
                                       'print_user_id', NEW.PRINT_USER_ID, 
                                       'print_user_name', NEW.PRINT_USER_NAME, 
                                       'print_date', NEW.PRINT_DATE, 
                                       'invoice_type', NEW.INVOICE_TYPE, 
                                       'invoice_status', NEW.INVOICE_STATUS, 
                                       'tomb_certificate', NEW.TOMB_CERTIFICATE); 

    INSERT INTO t_sfi_inter_bus_complex (OPER_TIME, OPERATION_TYPE, OLD_OBJECT, NEW_OBJECT) 
    VALUES (NOW(), 'update', old_invoice_json, new_invoice_json); 
END;