为了更快的定位您的问题,请提供以下信息,谢谢
【详述】同一个sql反复查询,每次的返回结果都不一样,
【背景】查询view发现返回结果不一样,有查询原表还是如此
【业务影响】
【StarRocks版本】2.5.10-9feb716
【集群规模】3 fe+3be 混布
【机器信息】32c/256g
【联系方式】为了在解决问题过程中能及时联系到您获取一些日志信息,请补充下您的联系方式,例如:社区群4-小李或者邮箱,谢谢
【附件】
CREATE TABLE ods_biz_period_repayment_plan
(
Fid
bigint COMMENT ‘主键’,
Fuid
bigint COMMENT ‘用户id’,
Fcash_order_no
varchar(32) COMMENT ‘订单号’,
Frepayment_plan_no
varchar(32) COMMENT ‘分期订单号:loanNo+01…n’,
Floan_id
bigint COMMENT ‘贷款信息业务数据Id’,
Floan_no
varchar(32) COMMENT ‘贷款信息业务编号’,
Fstatus
int COMMENT ‘还款状态:1=贷款,2=正常已还,3=逾期,4=逾期已还,5=展期,6=展期结清,7=提前结清’,
Fearlier_settle_days
int COMMENT ‘提前结清天数’,
Fproduct_type
int COMMENT ‘产品类型:0=单期产品,1=分期产品’,
Freloan_num
int COMMENT ‘复借次数’,
Floan_amount
decimal(16, 2) COMMENT ‘总贷款金额’,
Fperiod
int COMMENT ‘总计期数’,
Fnperiod
int COMMENT ‘当期第几期’,
Floan_day
int COMMENT ‘单期贷款天数’,
Frepayment_date
date COMMENT ‘应还日期’,
Fstart_date
date COMMENT ‘开始日期’,
Finit_repayment_date
date COMMENT ‘第一次计算应还日期’,
Flast_repayment_date
date COMMENT ‘上一次应还日期’,
Fsettlement_time
datetime COMMENT ‘上一次结算时间’,
Fsettle_time
datetime COMMENT ‘实际结清时间’,
Fis_settled
int COMMENT ‘是否结清:0=未结清,1=已结清’,
Fsettle_type
int COMMENT ‘结清类型:0=正常结清,1=少缴,2=溢缴’,
Fexceeding_nums
int COMMENT ‘展期次数’,
Fexceeding_time
datetime COMMENT ‘上次展期时间’,
Fforce_exceeding_nums
int COMMENT ‘强制展期次数’,
Fmax_dpd
int COMMENT ‘最大逾期天数’,
Fcurrent_dpd
int COMMENT ‘当前逾期天数’,
Foverdue_days
int COMMENT ‘当前逾期天数’,
Fupdate_time
datetime COMMENT ‘还款计划刷新任务更新时间’,
Ftotal_amount
decimal(16, 2) COMMENT ‘当期还款总应还’,
Fpaid_amount
decimal(16, 2) COMMENT ‘当期已还金额’,
Funpaid_amount
decimal(16, 2) COMMENT ‘当期剩余应还’,
Ftotal_fee
decimal(16, 2) COMMENT ‘当期应还总息费’,
Fpaid_fee
decimal(16, 2) COMMENT ‘当期已还总息费’,
Funpaid_fee
decimal(16, 2) COMMENT ‘当期未还总息费’,
Ftotal_vat
decimal(16, 2) COMMENT ‘当期应还总VAT’,
Fpaid_vat
decimal(16, 2) COMMENT ‘当期已还总VAT’,
Funpaid_vat
decimal(16, 2) COMMENT ‘当期未还总VAT’,
Ftotal_principal
decimal(16, 2) COMMENT ‘当期总本金’,
Fpaid_principal
decimal(16, 2) COMMENT ‘当期已还本金’,
Funpaid_principal
decimal(16, 2) COMMENT ‘当期未还本金’,
Ftotal_interest
decimal(16, 2) COMMENT ‘当期总利息’,
Fpaid_interest
decimal(16, 2) COMMENT ‘当期已还利息’,
Fderate_interest
decimal(16, 2) COMMENT ‘当期已减免利息’,
Funpaid_interest
decimal(16, 2) COMMENT ‘当期未还利息’,
Ftotal_interest_vat
decimal(16, 2) COMMENT ‘当期总利息Vat’,
Fpaid_interest_vat
decimal(16, 2) COMMENT ‘当期已还利息Vat’,
Fderate_interest_vat
decimal(16, 2) COMMENT ‘当期已减免利息Vat’,
Funpaid_interest_vat
decimal(16, 2) COMMENT ‘当期未还利息Vat’,
Ftotal_service_fee
decimal(16, 2) COMMENT ‘当期总服务费用’,
Fpaid_service_fee
decimal(16, 2) COMMENT ‘当期已还服务费用’,
Funpaid_service_fee
decimal(16, 2) COMMENT ‘当期未还服务费用’,
Ftotal_service_fee_vat
decimal(16, 2) COMMENT ‘当期当期总服务费用Vat’,
Fpaid_service_fee_vat
decimal(16, 2) COMMENT ‘当期已还服务费用Vat’,
Funpaid_service_fee_vat
decimal(16, 2) COMMENT ‘当期未还服务费用Vat’,
Ftotal_overdue_fee
decimal(16, 2) COMMENT ‘当期总逾期罚息’,
Fpaid_overdue_fee
decimal(16, 2) COMMENT ‘当期已还逾期罚息’,
Fderate_overdue_fee
decimal(16, 2) COMMENT ‘当期已减免逾期罚息’,
Funpaid_overdue_fee
decimal(16, 2) COMMENT ‘当期未还逾期罚息’,
Ftotal_overdue_fee_vat
decimal(16, 2) COMMENT ‘当期总逾期罚息Vat’,
Fpaid_overdue_fee_vat
decimal(16, 2) COMMENT ‘当期已还逾期罚息Vat’,
Fderate_overdue_fee_vat
decimal(16, 2) COMMENT ‘当期已减免逾期罚息Vat’,
Funpaid_overdue_fee_vat
decimal(16, 2) COMMENT ‘当期未还逾期罚息Vat’,
Ftotal_late_fee
decimal(16, 2) COMMENT ‘当期总滞纳金’,
Fpaid_late_fee
decimal(16, 2) COMMENT ‘当期已还滞纳金’,
Fderate_late_fee
decimal(16, 2) COMMENT ‘当期已减免滞纳金’,
Funpaid_late_fee
decimal(16, 2) COMMENT ‘当期未还滞纳金’,
Ftotal_late_fee_vat
decimal(16, 2) COMMENT ‘当期总滞纳金Vat’,
Fpaid_late_fee_vat
decimal(16, 2) COMMENT ‘当期已还滞纳金Vat’,
Fderate_late_fee_vat
decimal(16, 2) COMMENT ‘当期已减免滞纳金Vat’,
Funpaid_late_fee_vat
decimal(16, 2) COMMENT ‘当期未还滞纳金Vat’,
Fcreated_time
datetime COMMENT ‘创建时间’,
Fmodified_time
datetime COMMENT ‘最后修改时间’,
fetl_time
datetime COMMENT “数据插入时间”,
dt
date COMMENT ‘分区dt’
)
ENGINE=olap
DUPLICATE KEY(Fid,Fuid,Fcash_order_no,Frepayment_plan_no)
COMMENT “ods|jarryqian|20230804|分期产品还款计划表-保留支持单期产品扩展”
PARTITION BY RANGE ( dt ) (
START (‘2023-08-03’) END (‘2023-08-04’) EVERY ( INTERVAL 1 DAY )
)
DISTRIBUTED BY HASH(fid,fuid) BUCKETS 16
PROPERTIES (
“in_memory” = “false”,
“replication_num” = “1”,
“enable_persistent_index”=“true”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.start” = “”,
“dynamic_partition.end” = “3”,
“dynamic_partition.prefix” = “P”,
“dynamic_partition.buckets” = “10”
);
SELECT
a.*
FROM
(select t.* from (select *,row_number() over (partition by fuid,Floan_no order by fetl_time desc)rk from ods.ods_biz_period_repayment_plan )t where t.rk=1)a
WHERE Fpaid_amount - Funpaid_amount >0
AND fstatus not in (1,2,4,5,6,7)
AND Fpaid_amount > 0
AND funpaid_amount < 200;