大佬们。现在有个需要处理的数据问题遇到瓶颈了，就是当选某个月的某一天的时候要计算月累数据。当选择某月的最后一天则是这个月的所有缺陷类别的数量都可以平铺进来。如图所示。8月28号有个类别 没装到位 却不在31号月累数据填充了，请问是什么原因呢？有没有大佬指点迷津，谢谢！！不胜感激！下方还有部分代码。select t9.period_id ,'MTD'as period_type ,t9.day_format1 ,t9.day_same ,t9.day_last_month ,t9.day_last_week ,t9.month_curr ,t9.month_same ,t9.month_last ,t9.year_curr ,t9.biz_line_code ,t9.biz_line_name ,t9.dim_code ,t9.dim_name ,t9.index_name ,t9.unit ,t9.index_value ,t9.statistic_dim ,t9.data_levelfrom (select t8.period_id,t8.day_format1,t8.day_same,t8.day_last_month,t8.day_last_week,t8.month_curr,t8.month_same,t8.month_last,t8.year_curr,t8.biz_line_code,t8.biz_line_name,t8.dim_code ,t8.dim_name ,t8.index_name,t8.unit,t8.mtd_value_total as index_value,row_number()over (partition by t8.biz_line_name,t8.period_id order by cast(t8.mtd_value_total as int) desc) as rn,t8.statistic_dim,t8.data_levelfrom (selectt7.gregorian as period_id,replace(t7.gregorian,'-','') as day_format1,t7.day_same,t7.day_last_month,t7.day_last_week,t7.month_curr,t7.month_same,t7.month_last,t7.year_curr,t7.factory_code as biz_line_code,t7.factory_name as biz_line_name,t7.defect_code as dim_code ,t7.defect_name as dim_name ,t7.index_name ,'/' as unit,nvl(round(t7.index_value,2),0) as index_value,nvl(round(t7.mtd_value,2),0) as mtd_value_total,nvl(round(t7.ytd_value,2),0) as ytd_value_total ,'缺陷类别数量'as statistic_dim ,'2'as data_level from (select t6.gregorian,t6.months,t6.years,t6.factory_code,t6.factory_name,t6.defect_code,t6.defect_name,t6.index_value,t6.day_same,t6.day_last_month,t6.day_last_week,t6.month_curr,t6.month_same,t6.month_last,t6.year_curr,t6.index_name,sum(t6.index_value)over( partition by t6.index_name,t6.factory_name ,t6.defect_name,t6.months order by t6.gregorian) as mtd_value,sum(t6.index_value)over( partition by t6.index_name,t6.factory_name ,t6.defect_name,t6.years order by t6.gregorian) as ytd_valuefrom (select a3.gregorian as gregorian ,a3.year as years ,substr(a3.gregorian,1,7) as months,t5.factory_code as factory_code,t5.org_name as factory_name,t5.defect_type_code as defect_code,t5.defect_type_name as defect_name,case when a3.gregorian = t5.inspect_time then t5.unqualified_qty else 0 end as index_value,a3.day_same --当日同期(格式:yyyy-MM-dd) ,a3.day_last_month --上月当前日期(格式:yyyy-MM-dd),a3.day_last_week --上周当前日期(格式:yyyy-MM-dd),a3.month as month_curr -- 月,a3.month_same as month_same -- 月同期,a3.month_last as month_last -- 月上期 ,a3.year as year_curr -- 月,'成品开箱合格率' as index_name from (select * from dim.dim_period where gregorian between'2023-08-01' and '2023-08-31'and period_type_code = 'D') a3left join (select t1.factory_code,t1.org_name,substr(t1.inspect_time,1,10) as inspect_time,t2.defect_type_code,t3.name as defect_type_name,nvl(sum(case when t1.result ='NG' then t1.check_qty end ),0) as unqualified_qtyfrom dwd.dwd_mf_qm_product_inspection_main t1 left join dwd.dwd_mf_product_inspect_sample_detail t2on t2.inspect_main_id = t1.product_inspection_main_idleft join ods.ods_bd_msq_qms_sysm_qms_sysm_t_defect_library t3on t2.defect_type_code = t3.codewhere t1.org_code in ('KTZSJY','KTZSSY','KTWHJY')and t1.del_flag ='0'and t1.result is not nulland t2.org_code in ('KTZSJY','KTZSSY','KTWHJY')and t3.org_id in ('KTZSJY','KTZSSY','KTWHJY')and t3.belong ='成品检验'and t3.data_source ='reason'and t3.create_id <>'ex_kangsheng.lu'and t1.inspect_time between'2023-08-01' and '2023-08-31'group by t1.factory_code,t1.org_name,substr(t1.inspect_time,1,10) ,t2.defect_type_code,t3.name)t5--on a3.gregorian = t5.inspect_timeon 1=1)t6)t7 )t8)t9 where t9.rn <= 5
