doris版本：
8月27号 00:30:47.611 相同的sql插入了727456条
早上发现表中日期少了
8月27号 10:21:21 相同的sql插入了2083223条
下面是dolphinscheduler的运行日志：
00:30
[INFO] 2025-08-27 00:31:04.231 +0800 - main statement execute update result: 727456, for sql: INSERT INTO ods_ajd.tf_retailitem (
billitem_id,
bill_id,
bill_date,
sku_id,
product_id,
price_tag_code,
qty,
standard_amt,
real_amt,
discount,
tot_amt_acc,
salesreps_name,
salesreps_rate,
salesrep_id,
c_markbaltype_id,
type,
orgdocno,
m_retailitem_id,
rqty,
rcanqty,
description,
sig_tdefposdis_moreid,
c_tsstore_id,
hr_tsemployee_id,
com_tdefposdis_moreid,
b_retaildissku_moreid,
webpos_retreason_id,
status,
orderno,
m_attributesetinstance_id,
old_trace,
old_authcode,
old_datetime,
old_flag,
old_remark,
handdis,
integral,
sig_tdefposdis_id,
com_tdefposdis_id,
b_retaildissku_id,
c_integralmin_id,
c_vipbirdis_id,
org_m_retailitem_id,
orgorderno,
set_status,
seterid,
settime,
amt_vouchers,
ownerid,
modifierid,
creationdate,
modifieddate,
isactive
)
select * from query("catalog" = "ajdbos_catalog", "query" = "
select a.ID as billitem_id, -- ID
a.M_RETAIL_ID as bill_id, -- 零售单
b.billdate as bill_date, -- 单据日期
a.M_PRODUCTALIAS_ID as sku_id, -- 条码
a.M_PRODUCT_ID as product_id, -- 商品
replace(a.MASTERCODE_PDT ,chr(0),null) as price_tag_code, -- 价签码
a.QTY as qty, -- 数量
a.PRICELIST as standard_amt, -- 零售价
a.PRICEACTUAL as real_amt, -- 成交价
a.DISCOUNT as discount, -- 折扣
a.TOT_AMT_ACC as tot_amt_acc, -- 结算金额
replace(a.SALESREPS_NAME,chr(0),null) as salesreps_name, -- 营业员(多选)
a.SALESREPS_RATE as salesreps_rate, -- 营业员销售比例
a.SALESREP_ID as salesrep_id, -- 营业员
a.C_MARKBALTYPE_ID as c_markbaltype_id, -- 商场结算类型
a.TYPE as type, -- 零售类型(零售类型,1正常零售,2退货,3赠品,4全额)
a.ORGDOCNO as orgdocno, -- 原单编号
a.M_RETAILITEM_ID as m_retailitem_id, -- 原零售单明细ID
a.RQTY as rqty, -- 已退数量
a.RCANQTY as rcanqty, -- 可退数量
a.DESCRIPTION as description, -- 备注
a.SIG_TDEFPOSDIS_MOREID as sig_tdefposdis_moreid, -- 单品策略（多个）
a.C_TSSTORE_ID as c_tsstore_id, -- 推手门店
a.HR_TSEMPLOYEE_ID as hr_tsemployee_id, -- 推手导购
a.COM_TDEFPOSDIS_MOREID as com_tdefposdis_moreid, -- 组合策略（多个）
a.B_RETAILDISSKU_MOREID as b_retaildissku_moreid, -- 单品补充策略（多个）
a.WEBPOS_RETREASON_ID as webpos_retreason_id, -- 退货原因
a.STATUS as status, -- 状态
a.ORDERNO as orderno, -- 序号
a.M_ATTRIBUTESETINSTANCE_ID as m_attributesetinstance_id, -- ASI
a.OLD_TRACE as old_trace, -- 凭证号
a.OLD_AUTHCODE as old_authcode, -- 授权号
a.OLD_DATETIME as old_datetime, -- 原交易时间
a.OLD_FLAG as old_flag, -- 标记符
a.OLD_REMARK as old_remark, -- 备注
a.HANDDIS as handdis, -- 手工折扣
a.INTEGRAL as integral, -- 积分消费数
a.SIG_TDEFPOSDIS_ID as sig_tdefposdis_id, -- 促销策略号（单品）
a.COM_TDEFPOSDIS_ID as com_tdefposdis_id, -- 促销策略号（组合）
a.B_RETAILDISSKU_ID as b_retaildissku_id, -- 促销策略号（补充）
a.C_INTEGRALMIN_ID as c_integralmin_id, -- 积分活动号（单品）
a.C_VIPBIRDIS_ID as c_vipbirdis_id, -- VIP生日补充策略号
a.ORG_M_RETAILITEM_ID as org_m_retailitem_id, -- 原单明细ID
a.ORGORDERNO as orgorderno, -- 原单序号
a.SET_STATUS as set_status, -- 设置状态
a.SETERID as seterid, -- 设置人
a.SETTIME as settime, -- 设置时间
a.AMT_VOUCHERS as amt_vouchers, -- 购物券付款金额
a.OWNERID as ownerid, -- 创建人
a.MODIFIERID as modifierid, -- 修改人
a.CREATIONDATE as creationdate, -- 创建时间
a.MODIFIEDDATE as modifieddate, -- 修改时间
a.ISACTIVE as isactive -- 可用
from bosnds3.m_retailitem a
left join bosnds3.m_retail b
on b.id = a.m_retail_id
where b.isactive = 'Y'
and b.status = 2
-- and a.billdate between 20210101 and 20211231
and b.billdate >= to_number(to_char(TRUNC(add_months(sysdate, -1), 'MM'),'yyyymmdd')) -- 上个月1号
and b.billdate < to_char(sysdate, 'yyyymmdd')
"
)
[INFO] 2025-08-27 00:31:04.233 +0800 -
***********************************************************************************************
[INFO] 2025-08-27 00:31:04.233 +0800 - ********************************* Finalize task instance ************************************
[INFO] 2025-08-27 00:31:04.233 +0800 - ***********************************************************************************************
[INFO] 2025-08-27 00:31:04.233 +0800 - Upload output files: [] successfully
[INFO] 2025-08-27 00:31:04.235 +0800 - Send task execute status: SUCCESS to master : 172.17.6.69:1234
[INFO] 2025-08-27 00:31:04.235 +0800 - Remove the current task execute context from worker cache
[INFO] 2025-08-27 00:31:04.235 +0800 - The current execute mode isn't develop mode, will clear the task execute file: /tmp/dolphinscheduler/exec/process/default/140446287466944/140906696503744_16/32336/200056
[INFO] 2025-08-27 00:31:04.236 +0800 - Success clear the task execute file: /tmp/dolphinscheduler/exec/process/default/140446287466944/140906696503744_16/32336/200056
[INFO] 2025-08-27 00:31:04.236 +0800 - FINALIZE_SESSION
10:21的
[INFO] 2025-08-27 10:21:21.625 +0800 - main statement execute update result: 2083223, for sql: INSERT INTO ods_ajd.tf_retailitem (
billitem_id,
bill_id,
bill_date,
sku_id,
product_id,
price_tag_code,
qty,
standard_amt,
real_amt,
discount,
tot_amt_acc,
salesreps_name,
salesreps_rate,
salesrep_id,
c_markbaltype_id,
type,
orgdocno,
m_retailitem_id,
rqty,
rcanqty,
description,
sig_tdefposdis_moreid,
c_tsstore_id,
hr_tsemployee_id,
com_tdefposdis_moreid,
b_retaildissku_moreid,
webpos_retreason_id,
status,
orderno,
m_attributesetinstance_id,
old_trace,
old_authcode,
old_datetime,
old_flag,
old_remark,
handdis,
integral,
sig_tdefposdis_id,
com_tdefposdis_id,
b_retaildissku_id,
c_integralmin_id,
c_vipbirdis_id,
org_m_retailitem_id,
orgorderno,
set_status,
seterid,
settime,
amt_vouchers,
ownerid,
modifierid,
creationdate,
modifieddate,
isactive
)
select * from query("catalog" = "ajdbos_catalog", "query" = "
select a.ID as billitem_id, -- ID
a.M_RETAIL_ID as bill_id, -- 零售单
b.billdate as bill_date, -- 单据日期
a.M_PRODUCTALIAS_ID as sku_id, -- 条码
a.M_PRODUCT_ID as product_id, -- 商品
replace(a.MASTERCODE_PDT ,chr(0),null) as price_tag_code, -- 价签码
a.QTY as qty, -- 数量
a.PRICELIST as standard_amt, -- 零售价
a.PRICEACTUAL as real_amt, -- 成交价
a.DISCOUNT as discount, -- 折扣
a.TOT_AMT_ACC as tot_amt_acc, -- 结算金额
replace(a.SALESREPS_NAME,chr(0),null) as salesreps_name, -- 营业员(多选)
a.SALESREPS_RATE as salesreps_rate, -- 营业员销售比例
a.SALESREP_ID as salesrep_id, -- 营业员
a.C_MARKBALTYPE_ID as c_markbaltype_id, -- 商场结算类型
a.TYPE as type, -- 零售类型(零售类型,1正常零售,2退货,3赠品,4全额)
a.ORGDOCNO as orgdocno, -- 原单编号
a.M_RETAILITEM_ID as m_retailitem_id, -- 原零售单明细ID
a.RQTY as rqty, -- 已退数量
a.RCANQTY as rcanqty, -- 可退数量
a.DESCRIPTION as description, -- 备注
a.SIG_TDEFPOSDIS_MOREID as sig_tdefposdis_moreid, -- 单品策略（多个）
a.C_TSSTORE_ID as c_tsstore_id, -- 推手门店
a.HR_TSEMPLOYEE_ID as hr_tsemployee_id, -- 推手导购
a.COM_TDEFPOSDIS_MOREID as com_tdefposdis_moreid, -- 组合策略（多个）
a.B_RETAILDISSKU_MOREID as b_retaildissku_moreid, -- 单品补充策略（多个）
a.WEBPOS_RETREASON_ID as webpos_retreason_id, -- 退货原因
a.STATUS as status, -- 状态
a.ORDERNO as orderno, -- 序号
a.M_ATTRIBUTESETINSTANCE_ID as m_attributesetinstance_id, -- ASI
a.OLD_TRACE as old_trace, -- 凭证号
a.OLD_AUTHCODE as old_authcode, -- 授权号
a.OLD_DATETIME as old_datetime, -- 原交易时间
a.OLD_FLAG as old_flag, -- 标记符
a.OLD_REMARK as old_remark, -- 备注
a.HANDDIS as handdis, -- 手工折扣
a.INTEGRAL as integral, -- 积分消费数
a.SIG_TDEFPOSDIS_ID as sig_tdefposdis_id, -- 促销策略号（单品）
a.COM_TDEFPOSDIS_ID as com_tdefposdis_id, -- 促销策略号（组合）
a.B_RETAILDISSKU_ID as b_retaildissku_id, -- 促销策略号（补充）
a.C_INTEGRALMIN_ID as c_integralmin_id, -- 积分活动号（单品）
a.C_VIPBIRDIS_ID as c_vipbirdis_id, -- VIP生日补充策略号
a.ORG_M_RETAILITEM_ID as org_m_retailitem_id, -- 原单明细ID
a.ORGORDERNO as orgorderno, -- 原单序号
a.SET_STATUS as set_status, -- 设置状态
a.SETERID as seterid, -- 设置人
a.SETTIME as settime, -- 设置时间
a.AMT_VOUCHERS as amt_vouchers, -- 购物券付款金额
a.OWNERID as ownerid, -- 创建人
a.MODIFIERID as modifierid, -- 修改人
a.CREATIONDATE as creationdate, -- 创建时间
a.MODIFIEDDATE as modifieddate, -- 修改时间
a.ISACTIVE as isactive -- 可用
from bosnds3.m_retailitem a
left join bosnds3.m_retail b
on b.id = a.m_retail_id
where b.isactive = 'Y'
and b.status = 2
-- and a.billdate between 20210101 and 20211231
and b.billdate >= to_number(to_char(TRUNC(add_months(sysdate, -1), 'MM'),'yyyymmdd')) -- 上个月1号
and b.billdate < to_char(sysdate, 'yyyymmdd')
"
)
[INFO] 2025-08-27 10:21:21.626 +0800 -
***********************************************************************************************
[INFO] 2025-08-27 10:21:21.627 +0800 - ********************************* Finalize task instance ************************************
[INFO] 2025-08-27 10:21:21.627 +0800 - ***********************************************************************************************
[INFO] 2025-08-27 10:21:21.627 +0800 - Upload output files: [] successfully
[INFO] 2025-08-27 10:21:21.628 +0800 - Send task execute status: SUCCESS to master : 172.17.6.69:1234
[INFO] 2025-08-27 10:21:21.628 +0800 - Remove the current task execute context from worker cache
[INFO] 2025-08-27 10:21:21.628 +0800 - The current execute mode isn't develop mode, will clear the task execute file: /tmp/dolphinscheduler/exec/process/default/140446287466944/140906696503744_16/32423/200598
[INFO] 2025-08-27 10:21:21.629 +0800 - Success clear the task execute file: /tmp/dolphinscheduler/exec/process/default/140446287466944/140906696503744_16/32423/200598
[INFO] 2025-08-27 10:21:21.629 +0800 - FINALIZE_SESSION
