数据集报错 - 永洪社区 - Powered by Discuz!

上传

点击文件名下载附件

作者: 永洪tech-Bella 时间: 2022-2-23 09:53
什么数据库呢，sql写的什么呢，如果复杂的话能否简化一下，看看是哪段导致的

另外数据集执行的sql在数据库执行正常吗？

作者: yhdata_7w8bnlYX 时间: 2022-2-23 09:57

永洪tech-Bella 发表于 2022-2-23 09:53
什么数据库呢，sql写的什么呢，如果复杂的话能否简化一下，看看是哪段导致的

另外数据集执行的sql在数据库 ...

sql在数据库里执行没报错

作者: yhdata_7w8bnlYX 时间: 2022-2-23 09:58

永洪tech-Bella 发表于 2022-2-23 09:53
什么数据库呢，sql写的什么呢，如果复杂的话能否简化一下，看看是哪段导致的

另外数据集执行的sql在数据库 ...

sql在数据库中执行没报错

作者: 永洪tech-Bella 时间: 2022-2-23 10:01

yhdata_7w8bnlYX 发表于 2022-2-23 09:58
sql在数据库中执行没报错

什么数据库呢，sql写的什么呢，如果复杂的话能否简化一下，看看是哪段导致的
这个呢？

作者: yhdata_7w8bnlYX 时间: 2022-2-23 10:05

永洪tech-Bella 发表于 2022-2-23 10:01
什么数据库呢，sql写的什么呢，如果复杂的话能否简化一下，看看是哪段导致的
这个呢？ ...

这个报错是什么意思

作者: 永洪tech-Bella 时间: 2022-2-23 10:09

yhdata_7w8bnlYX 发表于 2022-2-23 10:05
这个报错是什么意思

如果只是想要知道是什么意思，可以直接搜索一下呢
比如：https://www.cnblogs.com/yeyuchangfeng/p/10913816.html

作者: yhdata_7w8bnlYX 时间: 2022-2-23 10:15

永洪tech-Bella 发表于 2022-2-23 10:09
如果只是想要知道是什么意思，可以直接搜索一下呢
比如：https://www.cnblogs.com/yeyuchangfeng/p/10913 ...

这个数据库是gp数据库，sql是
select t.site as 公司,t.parea as 车间,
CASE
  WHEN t.cline = '' or t.cline = '-' or t.cline is null THEN t.line
  WHEN t.line = '' or t.line = '-' or t.line is null THEN t.cline
  ELSE t.cline || '/' || t.line
END AS 线体,
t.line as 整机线体 ,coalesce(t.cline,'-') as 模组线体,
CASE
  WHEN t.model_no = '' or t.model_no = '-' or t.model_no is null THEN c.model_no
  WHEN c.model_no = '' or c.model_no = '-' or c.model_no is null THEN t.model_no
  ELSE t.layout_key
END AS 机型,
t.model_no as 整机型号,t.bom_ver as Bom版本,coalesce(c.model_no,'-') as 模组型号, t.country as 出口国家 ,t.packing_method as 包装方式 ,t.layout_key as 一体线key,
coalesce(t_max_time,'0') as 整机瓶颈节拍, coalesce(c_max_time,'0') as 模组瓶颈节拍,
case when  coalesce(t_max_time,'0')>coalesce(c_max_time,'0')and c.station_id not  like 'E%'and  t.station_id not  like 'E%'
then coalesce(t_max_time,'0')
when  coalesce(t_max_time,'0')<=coalesce(c_max_time,'0')and c.station_id not  like 'E%'and  t.station_id not  like 'E%' then coalesce(c_max_time,'0')
end 一体线瓶颈,
case when  c.station_id  like 'E%' then coalesce(c.c_max_time,'0') end  设备瓶颈,
greatest (coalesce(t_max_time,'0'),coalesce(c_max_time,'0')) as 瓶颈节拍 ,
coalesce(t_total_time,'0') as 整机总时间, coalesce(c_total_time,'0') as 模组总时间, sum(coalesce(t_total_time,'0') + coalesce(c_total_time,'0')) as 总时间,
coalesce(t_person,'0') as 整机总人数, coalesce(c_person,'0') as 模组总人数,sum(coalesce(t_person,'0')+ coalesce(c_person,'0')) as 总人力,
case when t.station_id not  like 'E%' then
ROUND(sum(coalesce(t_total_time,'0') + case when t.area='整机' then t.board_time end *coalesce(t_person,'0'))
/decode(sum(coalesce(t_max_time,'0')::numeric + case when t.area='整机' then t.board_time else 0 end  ),0,null,
sum(coalesce(t_max_time,'0')::numeric + case when t.area='整机' then t.board_time else 0 end  ))
/sum(decode(t_person,0,null,t_person)::numeric) ,2) end  as 整机线平衡,
case when c.station_id not  like 'E%' then
ROUND(sum(coalesce(c.c_total_time,'0') + case when t.area='整机' then c.board_time end *coalesce(c.c_person,'0'))
/decode(sum(coalesce(c_max_time,'0')::numeric + case when c.area='整机' then c.board_time else 0 end  ),0,null,
sum(coalesce(c_max_time,'0')::numeric + case when t.area='整机' then c.board_time else 0 end  ))
/sum(decode(c_person,0,null,c_person)::numeric) ,2)end  as 模组线平衡,
sum(case when c.area='模组' then c.board_time end )模组走板时间,
sum(case when t.area='整机' then t.board_time end )整机走板时间,
greatest (case when  c.station_id  like 'E%' then coalesce(c.c_max_time,'0') end,case when  coalesce(t_max_time,'0')>coalesce(c_max_time,'0')and c.station_id not  like 'E%'and  t.station_id not  like 'E%'
then coalesce(t_max_time,'0')
when  coalesce(t_max_time,'0')<=coalesce(c_max_time,'0')and c.station_id not  like 'E%'and  t.station_id not  like 'E%' then coalesce(c_max_time,'0')
end )理论UPH,decode(t.lm_time,null,'1',t.lm_time),t.lm_user
from
(select mstd.site,mstd.parea,area,line,mlct.cline,model_no,bom_ver,country,packing_method,layout_key,
case when station_id like 'E%' then 'E' else 'a' end station_id,
string_agg(mstd.lm_time::varchar,',')lm_time,string_agg(mstd.lm_user,',')lm_user,
max(to_number(op_time)) as T_max_time ,sum(to_number(op_time)) as t_total_time ,sum(person_count) as t_person,avg(board_time::numeric )board_time
from ods.odsiss_c_ajs_mstd mstd
left join ods.odsiss_tv_c_ajs_mlct mlct
on mstd.line = mlct.tline
where area = '整机'
group by mstd.site,mstd.parea,area,line,mlct.cline,model_no,bom_ver,country,packing_method,case when station_id like 'E%' then 'E' else 'a' end,layout_key) T
left join
(select site,parea,area,line,model_no,bom_ver,country,packing_method,layout_key,avg(board_time::numeric)board_time ,case when station_id like 'E%' then 'E' else 'a' end station_id,
coalesce(max(to_number(op_time)),'0') as c_max_time ,coalesce(sum(to_number(op_time)),'0') as c_total_time ,sum(person_count) as c_person
from ods.odsiss_c_ajs_mstd where area = '模组'
group by site,parea,area,line,model_no,bom_ver,country,packing_method,layout_key,case when station_id like 'E%' then 'E' else 'a' end ) C
on T.parea = C.parea and T.country = C.country and T.packing_method = C.packing_method and T.layout_key = C.layout_key
and T.cline = C.line
where (T.cline is null and T.layout_key = '-') or (T.cline is not null and T.layout_key != '-' )
GROUP BY t.site,t.parea,t.line,t.cline,t.model_no,t.bom_ver,c.model_no, t.country,t.packing_method,t.layout_key
,t_max_time, c_max_time, t_total_time, c_total_time, t_person, c_person ,t.station_id,t.board_time,c.station_id,c.board_time,t.lm_time,t.lm_user
union
(select * from (
select site as 公司,parea as 车间, coalesce(line,'-') as 线体, '-' as 整机线体 ,coalesce(line,'-') as 模组线体 ,coalesce(model_no,'-') as 机型, '-' as 整机型号,
'-' as Bom版本,coalesce(model_no,'-') as 模组型号, country as 出口国家 ,packing_method as 包装方式 ,'-' as 一体线key,
0 as 整机瓶颈节拍, coalesce(max(to_number(op_time)),'0') as 模组瓶颈节拍,
case when  station_id not like 'E%' then coalesce(max(to_number(op_time)),'0')else 0 end  as 一体线瓶颈,
0 设备瓶颈,
coalesce(max(to_number(op_time)),'0') as 瓶颈节拍 ,
0 as 整机总时间, coalesce(sum(to_number(op_time)),'0')  as 模组总时间, coalesce(sum(to_number(op_time)),'0')  as 总时间,
0 as 整机总人数, coalesce(sum(person_count),'0') as 模组总人数, coalesce(sum(person_count),'0') as 总人力,
round(( sum(to_number(op_time)) / decode(sum(person_count),0,null)::numeric /decode(max(to_number(op_time)),0,null)::numeric)  ,2)  as 理论线平衡,
avg(case when a.area='模组' then to_number(board_time)  end )模组走板时间,0 整机线平衡,
case when station_id not  like 'E%' then
ROUND(coalesce(sum(to_number(op_time)),'0')+avg(case when a.area='模组' then to_number(board_time)  end )*coalesce(sum(person_count),'0')/
(coalesce(max(to_number(op_time)),'0')+avg(case when a.area='模组' then to_number(board_time)  end ))/coalesce(sum(person_count),'0'),2
)end as 模组线平衡,
case when  station_id not like 'E%' then coalesce(max(to_number(op_time)),'0')else 0 end 理论UPH,
string_agg(lm_time::varchar,',')lm_time,string_agg(lm_user,',')lm_user
from ods.odsiss_c_ajs_mstd a where (site,parea,area,line) in (
select site,parea,area,line from ods.odsiss_c_ajs_line where line not in (select tline from ods.odsiss_tv_c_ajs_mlct ) and
line not in (select cline from ods.odsiss_tv_c_ajs_mlct ) and parea in ('71车间','71车2','81车间','82车间') and area = '模组'
)
group by site,parea,line, model_no,bom_ver,country,packing_method,layout_key,station_id )a
)

作者: 永洪tech-Bella 时间: 2022-2-23 10:49

yhdata_7w8bnlYX 发表于 2022-2-23 10:15
这个数据库是gp数据库，sql是
select t.site as 公司,t.parea as 车间,
CASE

确认下咱们是什么版本呢，对应的数据源是否勾选了这个，如果有这个选项，又没有勾选的话，试下勾选上看看情况
(, 下载次数: 39)